Functions

Trilogy provides a comprehensive set of built-in functions that closely map to standard SQL functions. These functions enable data transformation, aggregation, and manipulation within your queries. They are categorized into aggregate, date/time, string, numeric, collection, and utility functions.

Function Usage & Concepts

Each function definition includes its input requirements and output characteristics. Key information includes:

• Input Types: The allowed data types for each argument.
• Output Type: The data type of the value returned by the function.
• Output Purpose: Describes the conceptual role of the output.
  • PROPERTY: Represents an attribute derived from existing data, typically associated with the grain of the input(s). Example: UPPER(name).
  • METRIC: Represents a calculated value that summarizes data across rows, respecting the query's grouping. Example: SUM(revenue).
  • KEY: Represents an identifier or a dimension used for joining or grouping, often changing the grain of the data. Example: UNNEST(tags).
  • CONSTANT: Represents a fixed value, independent of the input data rows. Example: CURRENT_DATE().