AQL Functions Overview

AQL functions are the main building blocks of an AQL expression. They transform an input into an output based on specified arguments. They are typically combined using the AQL pipe operator.

Here are the main function types of AQL:

Function Type	Purpose
Table Functions	Transform an input table expression into an output table result
Metric Functions	Transform an input metric expression into another metric expression
Aggregation Functions	Group values of multiple rows into a single summary value. They are equivalent to aggregation functions that SQL supports (SUM, COUNT, AVG, MAX, MIN,...).
Logical Functions	Return value based on some logical conditions such as and, or, not, in, etc.
Text Functions	Manipulate and transform text strings
AI Functions	Provide natural language processing and machine learning capabilities
Time Intelligence Functions	Process date/time value such as truncating a timestamp or extracting a subfield from a date or time value
Null/Zero Handling Functions	Make working with null and zero values easier
Miscellaneous Functions	Other functions that don't fit into the above categories

Table Functions

Table functions transform an input table expression into an output table result.

Function	Syntax	Purpose
select	`select(table, field)`	Selects the fields to be returned in the query result.
group	`group(table, field)`	Groups the result set by the specified field(s).
filter	`filter(table, field)`	Filters and return the results that satisfy the conditions
unique	`unique(table, field)`	Returns the unique values of the specified field(s).

Metric Functions

Metric functions transform an input metric expression into another metric expression.

Function	Syntax	Purpose
where	`where(measure, condition)`	Apply condition(s) to original measure
exclude, exclude_grains, of_all	`exclude_grains(measure, model, dim)` `of_all(expression, model, dim)`	Exclude dimension(s) from a measure calculation
dimensionalize	`dimensionalize(measure, model, dim)`	Choose exact dimension(s) for this measure calculation
with_relationships	`with_relationships(measure, relationship)`	Specifies what existing relationship(s) to used for this measure calculation
exact_period	`exact_period(measure, time_dimension, time_range)`	Returns the same measure calculated in a custom period.
relative_period	`relative_period(measure, time_dimension, interval)`	Returns the same measure calculated with shifting time
running_total	`running_total(measure, running_dimension)`	Returns a running total measure
rank	`rank(order, partition)`	Returns a rank measure
period_to_date	`period_to_date(measure, date_part, date_dimension)`	Returns a cumulative period to date measure
trailing_period	`trailing_period(measure, date_dimension, periods)`	Returns a trailing period measure

Aggregation Functions

Aggregation functions group values of multiple rows into a single summary value. They are equivalent to aggregation functions that SQL supports (SUM, COUNT, AVG, MAX, MIN,...).

Function	Syntax	Purpose
count	`count(field)`	Counts the total number of items in a group, not including NULL values
count_if	`count_if(condition)`	Count total rows from one table that are satisfied the condition
count_distinct	`count_distinct(field)`	Counts the total number of distinct items in a group, not including NULL values.
average	`average(field)`	Averages the values of items in a group, not including NULL values.
min	`min(field)`	Computes the item in the group with the smallest numeric value.
max	`max(field)`	Computes the item in the group with the largest numeric value.
sum	`sum(field)`	Sums the total number of items in a group, not including NULL values.
median	`median(field)`	Computes the median of an expression, which is the value that the values in the expression are below 50% of the time.
stdev	`stdev(field)`	Returns the standard deviation (sample) of the column created by expression unless expression defines a column of lists, in which case returns the standard deviation (sample) of each list.
stdevp	`stdevp(field)`	Returns the standard deviation (population) of the column created by expression unless expression defines a column of lists, in which case returns the standard deviation (population) of each list.
var	`var(field)`	Returns the variance (sample) of the column created by expression unless expression defines a column of lists, in which case returns the variance (sample) of each list.
varp	`varp(field)`	Returns the variance (population) of the column created by expression unless expression defines a column of lists, in which case returns the variance (population) of each list.
corr	`corr(table, field1, field2)`	Returns the Pearson correlation coefficient of two number fields.
string_agg	`string_agg(expression, sep: separator)`	Returns a text that is the concatenation of all values of the expression.
percentile_cont	`percentile_cont(expression, percentile)`	Returns the value at the given percentile with interpolation between adjacent values.
percentile_disc	`percentile_disc(expression, percentile)`	Returns the value at the given percentile using discrete values.
min_by	`min_by(table, value, by)`	Returns the value from the row where another field is minimum.
max_by	`max_by(table, value, by)`	Returns the value from the row where another field is maximum.

Logical Functions

Logical Functions return value based on some logical conditions such as and, or, not, in, etc.

info

Except for case when function, the rest of the logical functions are only available in Business Calculation due to legacy usage. Logical slice-and-dice can also be achieved by using AQL Operator

Function	Syntax	Purpose
case when	`case(when: condition, then: value, else: value)`	goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement).
and	`and(condition1, condition2, [,...])`	compares between two Booleans as expression and returns true when both expressions are true.
or	`or(condition1, condition2, [,...])`	compares two Booleans as expression and returns true when one of the expressions is true.
not	`not(field)`	takes a single Boolean as an argument and invert it.
is	`is(field)`	evaluates the given statement and returns either True or False.
in	`in(field, value1, value2, [,...])`	takes a field expression and a list of values. Return true if that list of values contains the value of that field expression.

Time Intelligence Functions

Time intelligence functions process date/time value such as truncating a timestamp or extracting a subfield from a date or time value.

Function	Syntax	Purpose
epoch	`epoch(date); epoch(datetime)`	Returns a Unix timestamp which is the number of seconds that have elapsed since ‘1970-01-01 00:00:00’ UTC
date_trunc	`date_trunc(datetime, datetime_part)`	Truncate a datetime to the granularity of `datetime_part`
date_format	`date_format(datetime, format)`	Formats a date according to the specified format string
from_unixtime	`from_unixtime(number)`	Converts a Unix timestamp to a datetime value
last_day	`last_day(datetime, date_part)`	Returns the last day of the period for a given date
day	`day()`	Truncate a datetime to the day granularity
month	`month()`	Truncate a datetime to the month granularity
year	`year()`	Truncate a datetime to the year granularity
quarter	`quarter()`	Truncate a datetime to the quarter granularity
week	`week()`	Truncate a datetime to the week granularity
hour	`hour()`	Truncate a datetime to the hour granularity
minute	`minute()`	Truncate a datetime to the minute granularity

Null/Zero Handling Functions

Null/Zero handling functions make working with null and zero values easier.

Function	Syntax	Purpose
coalesce	`coalesce(value1, value2, [,...])`	returns the first non-null value in a list
nullif	`nullif(expr1, expr2)`	returns NULL if two expressions are equal, otherwise, it returns the first expression.
safe_divide	`safe_divide(value1, value2)`	Equivalent to the division granulity operator (X / Y), but returns NULL if an error occurs, such as a division by zero error.

Text Functions

Manipulate and transform text strings.

Function	Syntax	Purpose
concat	`concat(value1, value2, [,...])`	Concatenates two or more strings.
find	`find(text, substring)`	Returns the 1-based index of the first occurrence of a substring.
left	`left(text, length)`	Returns the leftmost characters of a text string.
right	`right(text, length)`	Returns the rightmost characters of a text string.
mid	`mid(text, start, length)`	Extracts a substring from a specified position.
len	`len(text)`	Returns the length of a text string.
lpad	`lpad(text, length, pad_string)`	Pads the left side of a string with a specified character.
rpad	`rpad(text, length, pad_string)`	Pads the right side of a string with a specified character.
lower	`lower(text)`	Converts text to lowercase.
upper	`upper(text)`	Converts text to uppercase.
trim	`trim(text)`	Removes leading and trailing whitespace.
ltrim	`ltrim(text)`	Removes leading whitespace.
rtrim	`rtrim(text)`	Removes trailing whitespace.
regexp_extract	`regexp_extract(text, regex, [occurrence], [group], [flags])`	Extracts substrings using regular expressions.
regexp_match	`regexp_match(text, regex, [flags])`	Checks if text matches a regular expression.
regexp_replace	`regexp_replace(text, regex, substitute, [flags])`	Replaces text using regular expressions.
replace	`replace(text, old_substring, new_substring)`	Replaces all occurrences of a substring.
split_part	`split_part(text, delimiter, part_number)`	Splits text and returns a specific part.

AI Functions

Provide natural language processing and machine learning capabilities within AQL queries.

Platform Availability

AI functions are only available on Databricks and Snowflake data platforms.

Function	Syntax	Purpose
ai_query	`ai_query(model, prompt)`	Queries an AI model with a text prompt and returns the generated response.
ai_similarity	`ai_similarity(text1, text2)`	Calculates the semantic similarity between two text strings.
ai_classify	`ai_classify(text, ...categories)`	Classifies text into one of the provided categories using AI.
ai_summarize	`ai_summarize(content)`	Generates a concise summary of the provided text content using AI.

Miscellaneous Functions

Other functions that don't fit into the above categories.

Function	Syntax	Purpose
cast	`cast(value, type)`	Converts a value to a specified data type.
is_at_level	`is_at_level(dimension)`	Returns true if the LoD context contains the specified dimension.

Table Functions​

Metric Functions​

Aggregation Functions​

Logical Functions​

Time Intelligence Functions​

Null/Zero Handling Functions​

Text Functions​

AI Functions​

Miscellaneous Functions​