Aggregate Functions

Aggregate functions perform calculations across a set of input rows and return a single summary value for each group. Aggregate functions can either have an explicit grain - SUM(x) by a,b,c, - or be unbound, in which case they will inherit the grain from the surrounding expression. Typically, aggregates should be unbound in models except where they represent a specific property- for example, total_revenue would be dynamic, whereas 'customer_lifetime_revenue' might be a aggregate at a customer grain, an implicit property of a customer id.

SUM

Computes the sum of non-null numeric values within each group. Returns NULL if the group is empty or contains only NULL values.

Inputs:

INTEGERFLOATNUMBERNUMERIC

Output:

Same as input (or promoted numeric type)

Purpose:

metric

Example:

SELECT SUM(order_total) AS total_revenue BY customer_id;

COUNT

Counts the number of non-null input rows within each group. Takes a single argument of any type.

Inputs:

Any

Output:

INTEGER

Purpose:

metric

Example:

SELECT COUNT(order_id) AS num_orders BY product_category;

COUNT_DISTINCT

Counts the number of distinct non-null input values within each group. Takes a single argument of any type. You ONLY need to use this function when counting unique values of properties; a count of a key is always distinct. In most code this function is not required.

Inputs:

Any

Output:

INTEGER

Purpose:

metric

Example:

SELECT COUNT_DISTINCT(customer_id) AS unique_customers BY month;

MAX

Returns the maximum non-null value across all input rows within each group. Comparison follows standard data type rules.

Inputs:

INTEGERFLOATNUMBERDATEDATETIMETIMESTAMPBOOLSTRING

Output:

Same as input type

Purpose:

metric

Example:

SELECT MAX(order_date) AS latest_order_date BY customer_id;

MIN

Returns the minimum non-null value across all input rows within each group. Comparison follows standard data type rules.

Inputs:

INTEGERFLOATNUMBERDATEDATETIMETIMESTAMPSTRING

Output:

Same as input type

Purpose:

metric

Example:

SELECT MIN(order_date) AS first_order_date BY customer_id;

AVG

Computes the average (arithmetic mean) of non-null numeric values within each group. Returns NULL if the group is empty or contains only NULL values. The result is typically a FLOAT or NUMBER.

Inputs:

INTEGERFLOATNUMBERNUMERIC

Output:

FLOAT or NUMBER

Purpose:

metric

Example:

SELECT AVG(product_price) AS avg_price BY category;

ARRAY_AGG

Aggregates values from a group into a single array. The order of elements in the array is not guaranteed.

Inputs:

Any

Output:

ARRAY

Purpose:

metric

Example:

SELECT ARRAY_AGG(product_name) by category;

ANY

Returns a single, non-deterministic value from each group. This is useful when you know all values in a group are the same for a given field.

Inputs:

Any

Output:

Same as input type

Purpose:

metric

Example:

SELECT ANY(customer_name) by customer_id;

BOOL_AND

Returns TRUE if every input value in the group is TRUE. Returns FALSE if any value is FALSE, and NULL if all values are NULL.

Inputs:

BOOL

Output:

BOOL

Purpose:

metric

Example:

SELECT BOOL_AND(is_in_stock) by product_category;

BOOL_OR

Returns TRUE if at least one input value in the group is TRUE. Returns FALSE if all values are FALSE, and NULL if all values are NULL.

Inputs:

BOOL

Output:

BOOL

Purpose:

metric

Example:

SELECT BOOL_OR(has_shipped) by order_id;