AQL Functions Cheatsheet

Every AQL function in one place. Use this page to scan; jump to the dedicated reference page for full signatures, parameter details, and examples.

See also: Operators Cheatsheet · AQL Functions Overview.

Table Function

Function	Syntax	Purpose
select	select(table, field1, [field2, ...])	Returns a table containing only the specified fields.
group	group(table, dimension1, [dimension2, ...])	Returns a table grouped by one or more specified dimensions.
filter	filter(table, condition1, [condition2, ...])	Returns a table containing only the rows that satisfy one or more specified conditions.
unique	unique(dimension1, [dimension2, ...])	Returns a table with all unique combinations of the specified dimensions.
top	top(n, dimension, by: metric, [logic])	Returns the top N values of a specified dimension based on a metric.
bottom	bottom(n, dimension, by: metric, [logic])	Returns the bottom N values of a specified dimension based on a metric.

Condition Function

Function	Syntax	Purpose
where	where(metric, condition1, [condition2, ...])	Calculates a metric with specified conditions applied.

Aggregate Function

Function	Syntax	Purpose
count	count([table], expression)	Counts the total number of items in a group, excluding NULL values.
count_if	count_if([table], condition)	Counts the total rows from one table that satisfy the given condition.
count_distinct	count_distinct([table], expression)	Counts the total number of distinct items in a group, excluding NULL values.
approx_count_distinct	approx_count_distinct([table], expression)	Counts the approximate number of distinct items in a group, excluding NULL values.
average	average([table], expression)	Calculates the average of values in a group, excluding NULL values.
min	min([table], expression)	Returns the item in the group with the smallest value, excluding NULL values.
max	max([table], expression)	Returns the item in the group with the largest value, excluding NULL values.
sum	sum([table], expression)	Calculates the sum of values in the group, excluding NULL values.
median	median([table], expression)	Computes the median of the values in the group, excluding NULL values.
stdev	stdev([table], expression)	Computes the sample standard deviation of the values in the group, excluding NULL values.
stdevp	stdevp([table], expression)	Computes the population standard deviation of the values in the group, excluding NULL values.
var	var([table], expression)	Returns the sample variance of the values in the group, excluding NULL values.
varp	varp([table], expression)	Returns the population variance of the values in the group, excluding NULL values.
string_agg	string_agg([table], expression, [sep: _sep], [distinct: _distinct], [order: _order])	Returns a text that is the concatenation of all values of the expression.
corr	corr(table, field1, field2)	Returns the Pearson correlation coefficient of two number fields in the table.
max_by	max_by(table, value, by)	Returns the value of value from the row where by is maximum.
min_by	min_by(table, value, by)	Returns the value of value from the row where by is minimum.
percentile_cont	percentile_cont([table], expression, percentile)	Returns the value at the given percentile of the sorted expression values, interpolating between adjacent values if needed.
percentile_disc	percentile_disc([table], expression, percentile)	Returns the value at the given percentile of the sorted expression values.

Logical Function

Function	Syntax	Purpose
case	case(when: condition, then: value, [when: condition_expr, then: value, ...], [else: value])	Returns the value associated with the first condition that evaluates to true.
and	and(condition, ...)	Returns true only when all specified conditions are true.
or	or(condition, ...)	Returns true when at least one of the specified conditions is true.
not	not(condition)	Logical NOT takes a single truefalse expression and returns true when the expression is false.

Relationship Function

Function	Syntax	Purpose
with_relationships	with_relationships(metric, relationship1, [relationship2, ...])	Calculates a metric using specific relationships that are not active by default.

Level Of Detail Function

Function	Syntax	Purpose
of_all	of_all(metric, [model, dimension, ...], [keep_filters: false])	Returns a metric evaluated without certain dimensions or grains.
keep_grains	keep_grains(metric, [model, dimension, ...], [keep_filters: false])	Calculates a metric only against the specified dimensions or grains, ignoring all other dimensions.
dimensionalize	dimensionalize(metric, [dimension, ...])	Calculates a metric at a specific Level of Detail (LoD), regardless of the outer query context.
percent_of_total	percent_of_total(metric, total_type)	Calculates the percentage of a metric relative to a specified total type.

Time-based Function

Function	Syntax	Purpose
running_total	running_total(metric, [running_dimension, ...], [keep_filters: false])	Calculates a running total of a metric along specified dimensions from the starting point to the current period.
period_to_date	period_to_date(metric, date_part, date_dimension)	Calculates a metric from the beginning of a specified time period (year, quarter, month, etc.) to the current date.
exact_period	exact_period(metric, time_dimension, time_range)	Calculates a metric within a custom time period.
relative_period	relative_period(metric, time_dimension, offset)	Calculates a metric in the active time range shifted by a specified interval.
trailing_period	trailing_period(metric, date_dimension, period)	Calculates a metric over a specific number of date periods up to the current period.

Window Function

Function	Syntax	Purpose
rank	rank(order: order_expr, ..., [partition: partition_expr, ...])	Returns the rank of rows within a partition of a table.
dense_rank	dense_rank(order: order_expr, ..., [partition: partition_expr, ...])	Returns the rank of rows within a partition of a table.
percent_rank	percent_rank(order: order_expr, ..., [partition: partition_expr, ...])	Returns the relative percentile of a row within a partition of a table.
ntile	ntile(ranks, order: order_expr, ..., [partition: partition_expr, ...])	Divides the rows within a partition into a specified number of ranked groups.
next	next(expr, [offset], order: order_expr, ..., [partition: partition_expr, ...])	Returns the value from a following row at a specified offset relative to the current row.
previous	previous(expr, [offset], order: order_expr, ..., [partition: partition_expr, ...])	Returns the value from a preceding row at a specified offset relative to the current row.
first_value	first_value(expr, order: order_expr, ..., [partition: partition_expr, ...])	Returns the value of an expression from the first row of the window frame.
last_value	last_value(expr, order: order_expr, ..., [partition: partition_expr, ...])	Returns the value of an expression from the last row of the window frame.
nth_value	nth_value(expr, index, order: order_expr, ..., [partition: partition_expr, ...])	Returns the value of an expression from the Nth row of the window frame, where N is a positive integer.
window_sum	window_sum(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])	Returns the sum of values in rows within a specified range relative to the current row.
window_count	window_count(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])	Returns the count of values in rows within a specified range relative to the current row.
window_min	window_min(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])	Returns the min of rows in a range relative to the current row.
window_max	window_max(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])	Returns the maximum value in rows within a specified range relative to the current row.
window_avg	window_avg(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])	Returns the average of values in rows within a specified range relative to the current row.
window_stdev	window_stdev(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])	Returns the sample standard deviation of values in rows within a specified range relative to the current row.
window_stdevp	window_stdevp(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])	Returns the population standard deviation of values in rows within a specified range relative to the current row.
window_var	window_var(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])	Returns the sample variance of values in rows within a specified range relative to the current row.
window_varp	window_varp(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])	Returns the population variance of values in rows within a specified range relative to the current row.

Time Intelligence Function

Function	Syntax	Purpose
epoch	epoch([datetime])	Returns a Unix timestamp which is the number of seconds that have elapsed since '1970-01-01 00:00:00' UTC.
day	day(datetime_dimension)	Truncates a datetime_dimension value to the first day of the day (midnight).
week	week(datetime_dimension)	Truncates a datetime_dimension value to the first day of the week.
month	month(datetime_dimension)	Truncates a datetime_dimension value to the first day of the month.
quarter	quarter(datetime_dimension)	Truncates a datetime_dimension value to the first day of the quarter.
year	year(datetime_dimension)	Truncates a datetime_dimension value to the first day of the year.
hour	hour(datetime_dimension)	Truncates a datetime_dimension value to the first minute of the hour.
minute	minute(datetime_dimension)	Truncates a datetime_dimension value to the first second of the minute.
date_trunc	date_trunc(datetime_dimension, datetime_part)	Truncates a datetime_dimension value to the granularity of datetime_part.
date_part	date_part(datetime_part, datetime)	Extracts a specific numeric part from a date or datetime value.
year_num	year_num(datetime)	Extracts the numeric year from a datetime value.
quarter_num	quarter_num(datetime)	Extracts the quarter number from a datetime value.
month_num	month_num(datetime)	Extracts the month number from a datetime value.
week_num	week_num(datetime)	Extracts the week number from a datetime value.
dow_num	dow_num(datetime)	Alias for dayofweek_num, extracts the day of week number from a datetime value.
day_num	day_num(datetime)	Extracts the day of month number from a datetime value.
hour_num	hour_num(datetime)	Extracts the hour number from a datetime value.
minute_num	minute_num(datetime)	Extracts the minute number from a datetime value.
second_num	second_num(datetime)	Extracts the second number from a datetime value.
date_diff	date_diff(datetime_part, start, end)	Calculates the difference between two dates in the specified 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 (seconds since epoch) to a datetime value.
last_day	last_day(datetime, date_part)	Returns the last day of the period for a given date.
age	age(datetime)	Returns the age in years.

Null/Zero Handling Function

Function	Syntax	Purpose
coalesce	coalesce(val1, val2, ...)	Returns the first non-null value in a list of expressions.
nullif	nullif(val1, val2)	Returns NULL if two expressions are equal, otherwise returns the first expression.
safe_divide	safe_divide(dividend, divisor)	Returns the division with a safe mechanism to handle division by zero.

Mathematical Function

Function	Syntax	Purpose
abs	abs(number)	Returns the absolute value of a number, removing any negative sign and returning the non-negative magnitude.
sqrt	sqrt(number)	Calculates the square root of a given number.
ceil	ceil(number)	Returns the smallest integer greater than or equal to the given number.
floor	floor(number)	Returns the largest integer less than or equal to the given number.
round	round(number, [scale])	Rounds a number to a specified number of decimal places.
trunc	trunc(number, [scale])	Truncates a number to a specified number of decimal places, removing digits beyond the specified scale.
exp	exp(number)	Returns the value of the mathematical constant e (Euler's number) raised to the power of the given number.
ln	ln(number)	Calculates the natural logarithm (base e) of a given number.
log10	log10(number)	Calculates the base 10 logarithm of a given number.
log2	log2(number)	Calculates the base 2 logarithm of a given number.
pow	pow(base, exponent)	Raises a base number to the power of an exponent.
mod	mod(dividend, divisor)	Returns the remainder of a division operation.
div	div(dividend, divisor)	Returns the integer quotient of a division operation.
sign	sign(number)	Returns the sign of a number: 1 for positive, -1 for negative, and 0 for zero.
radians	radians(degrees)	Converts degrees to radians.
pi	pi()	Returns the mathematical constant π (pi).
acos	acos(number)	Returns the arccosine (inverse cosine) of a number.
asin	asin(number)	Returns the arcsine (inverse sine) of a number.
atan	atan(number)	Returns the arctangent (inverse tangent) of a number.
atan2	atan2(y, x)	Returns the two-argument arctangent, which computes the angle between the positive x-axis and the point given by the coordinates (x, y).
cos	cos(number)	Returns the cosine of a number.
sin	sin(number)	Returns the sine of a number.
tan	tan(number)	Returns the tangent of a number.
cot	cot(number)	Returns the cotangent of a number.

Miscellaneous Function

Function	Syntax	Purpose
cast	cast(expr, type)	Returns the input value casted to the specified data type.
concat	concat(text, [text ...])	Returns the concatenated string of multiple strings.
find	find(text, substring)	Returns the 1-based index of the first occurrence of a substring within a text string.
left	left(text, length)	Returns the leftmost characters of a text string, up to the specified length.
right	right(text, length)	Returns the rightmost characters of a text string, up to the specified length.
mid	mid(text, start, length)	Extracts a substring of a specified length from a text string, starting at a given position (1-based).
len	len(text)	Returns the length of a text string (number of characters).
lower	lower(text)	Converts a text string to lowercase.
upper	upper(text)	Converts a text string to uppercase.
trim	trim(text)	Removes leading and trailing whitespace from a text string.
ltrim	ltrim(text)	Removes leading whitespace from a text string.
rtrim	rtrim(text)	Removes trailing whitespace from a text string.
lpad	lpad(text, length, pad_string)	Pads the left side of a text string with a specified pad string until it reaches the specified length.
rpad	rpad(text, length, pad_string)	Pads the right side of a text string with a specified pad string until it reaches the specified length.
replace	replace(text, old_substring, new_substring)	Replaces all occurrences of a substring within a text string with a new substring.
split_part	split_part(text, delimiter, part_number)	Splits a text string into parts based on a delimiter and returns the specified part (1-based).
regexp_extract	regexp_extract(text, regex, [occurrence], [group: _group], [flags: _flags])	Extracts a substring from a text string that matches a regular expression pattern.
regexp_like	regexp_like(text, regex, [flags: _flags])	Checks if a text string matches a regular expression pattern.
regexp_replace	regexp_replace(text, regex, substitute, [flags: _flags])	Replaces substrings in a text that match a regular expression pattern with a specified replacement text.
is_at_level	is_at_level(dimension)	Returns true if the specified dimension is active in the Level of Detail (LoD) context, else false.

AI Function

Function	Syntax	Purpose
ai_complete	ai_complete(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, category1, category2, ...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.

SQL Passthrough Function

Function	Syntax	Purpose
sql_text	sql_text('FUNCTION_NAME', param1, param2, ...)	Calls a native SQL function that returns a text/string value.
sql_number	sql_number('FUNCTION_NAME', param1, param2, ...)	Calls a native SQL function that returns a numeric value.
sql_datetime	sql_datetime('FUNCTION_NAME', param1, param2, ...)	Calls a native SQL function that returns a datetime value.
sql_date	sql_date('FUNCTION_NAME', param1, param2, ...)	Calls a native SQL function that returns a date value.
sql_truefalse	sql_truefalse('FUNCTION_NAME', param1, param2, ...)	Calls a native SQL function that returns a boolean/truefalse value.
agg_text	agg_text(table, 'FUNCTION_NAME', param1, param2, ...)	Calls a native SQL aggregate function that returns a text/string value.
agg_number	agg_number(table, 'FUNCTION_NAME', param1, param2, ...)	Calls a native SQL aggregate function that returns a numeric value.
agg_datetime	agg_datetime(table, 'FUNCTION_NAME', param1, param2, ...)	Calls a native SQL aggregate function that returns a datetime value.
agg_date	agg_date(table, 'FUNCTION_NAME', param1, param2, ...)	Calls a native SQL aggregate function that returns a date value.
agg_truefalse	agg_truefalse(table, 'FUNCTION_NAME', param1, param2, ...)	Calls a native SQL aggregate function that returns a boolean/truefalse value.