aggregate.md

Aggregate functions

Functions used in GROUP BY queries and OVER clauses. SQE inherits ~40 aggregates from DataFusion plus 12 Trino UDAFs from sqe-trino-functions.

Standard aggregates

Function	Origin	Notes	Trino	Snowflake	Spark SQL	DuckDB
count(expr)	datafusion-builtin	Counts non-NULL rows.	count	count	count	count
count(*)	datafusion-builtin	Counts all rows.	count	count	count	count
count(distinct expr)	datafusion-builtin	Distinct non-NULL count.	count(distinct ...)	count(distinct ...)	count(distinct ...)	count(distinct ...)
sum(expr)	datafusion-builtin	Sum. NULL-skipping.	sum	sum	sum	sum
sum(distinct expr)	datafusion-builtin	Distinct sum.	sum(distinct ...)	sum(distinct ...)	sum(distinct ...)	sum(distinct ...)
avg(expr) / mean(expr)	datafusion-builtin	Arithmetic mean. NULL-skipping.	avg	avg	avg / mean	avg / mean
min(expr)	datafusion-builtin	Minimum.	min	min	min	min
max(expr)	datafusion-builtin	Maximum.	max	max	max	max
median(expr)	datafusion-builtin	Exact median. Slower than approx_median on big inputs.	-	median	median	median

Statistical / regression

Function	Origin	Notes	Trino	Snowflake	Spark SQL	DuckDB
variance(x) / var_samp(x)	datafusion-builtin	Sample variance.	variance / var_samp	variance_samp	variance / var_samp	variance / var_samp
var_pop(x)	datafusion-builtin	Population variance.	var_pop	variance_pop	var_pop	var_pop
stddev(x) / stddev_samp(x)	datafusion-builtin	Sample stddev.	stddev / stddev_samp	stddev_samp	stddev / stddev_samp	stddev
stddev_pop(x)	datafusion-builtin	Population stddev.	stddev_pop	stddev_pop	stddev_pop	stddev_pop
corr(y, x)	datafusion-builtin	Pearson correlation.	corr	corr	corr	corr
covar_samp(y, x) / covar_pop(y, x)	datafusion-builtin	Sample / population covariance.	covar_samp / covar_pop	covar_samp / covar_pop	covar_samp / covar_pop	covar_samp / covar_pop
regr_slope(y, x)	datafusion-builtin	Linear regression slope.	regr_slope	regr_slope	-	regr_slope
regr_intercept(y, x)	datafusion-builtin	y-intercept.	regr_intercept	regr_intercept	-	regr_intercept
regr_r2(y, x)	datafusion-builtin	R-squared.	regr_r2	regr_r2	-	regr_r2
regr_count, regr_sxx, regr_syy, regr_sxy, regr_avgx, regr_avgy	datafusion-builtin	Regression sums and counts.	yes	yes	-	yes

Distinct and approximation

Function	Origin	Notes	Trino	Snowflake	Spark SQL	DuckDB
approx_distinct(expr)	datafusion-builtin	HyperLogLog distinct count. ~1% error.	approx_distinct	approx_count_distinct	approx_count_distinct	approx_count_distinct
approx_median(expr)	datafusion-builtin	Median estimate via t-digest.	-	-	approx_percentile(0.5)	approx_quantile(0.5)
approx_percentile_cont(expr, p)	datafusion-builtin	Percentile estimate via t-digest. p in [0, 1].	approx_percentile	approx_percentile	approx_percentile	approx_quantile
approx_percentile(expr, p)	sqe-trino-functions	Trino-named alias of approx_percentile_cont. trino_functions.rs:164	approx_percentile	-	-	-

Boolean and bitwise

Function	Origin	Notes	Trino	Snowflake	Spark SQL	DuckDB
bool_and(x)	datafusion-builtin	True only if every row is true.	bool_and	booland_agg	bool_and / every	bool_and
bool_or(x)	datafusion-builtin	True if any row is true.	bool_or	boolor_agg	bool_or / any	bool_or
every(x)	sqe-trino-functions	Trino-named alias of bool_and. trino_functions.rs:170	every	-	every	-
bit_and(x)	datafusion-builtin	Bitwise AND of all values.	bitwise_and_agg	bitand_agg	bit_and	-
bit_or(x)	datafusion-builtin	Bitwise OR.	bitwise_or_agg	bitor_agg	bit_or	-
bit_xor(x)	datafusion-builtin	Bitwise XOR.	bitwise_xor_agg	bitxor_agg	bit_xor	-
bitwise_and_agg(x)	sqe-trino-functions	Trino name for bit_and. trino_functions.rs:144	bitwise_and_agg	-	-	-
bitwise_or_agg(x)	sqe-trino-functions	Trino name for bit_or. trino_functions.rs:150	bitwise_or_agg	-	-	-
bitwise_xor_agg(x)	sqe-trino-functions	Trino name for bit_xor. trino_functions.rs:158	bitwise_xor_agg	-	-	-

Positional

Function	Origin	Notes	Trino	Snowflake	Spark SQL	DuckDB
first_value(expr [order by ...])	datafusion-builtin	First row's value. Most useful with OVER ordering.	first_value	first_value	first_value	first_value
last_value(expr [order by ...])	datafusion-builtin	Last row's value.	last_value	last_value	last_value	last_value
nth_value(expr, n [order by ...])	datafusion-builtin	Nth row's value.	nth_value	nth_value	nth_value	nth_value
max_by(value, key)	sqe-trino-functions	value from the row with the max key. trino_functions.rs:177	max_by	max_by	-	arg_max
min_by(value, key)	sqe-trino-functions	value from the row with the min key. trino_functions.rs:178	min_by	min_by	-	arg_min
arbitrary(expr)	sqe-trino-functions (ext)	Any one non-NULL value. Trino-named alias of any_value. trino_functions_ext.rs:68	arbitrary	any_value	any_value	any_value

Collection-building

Function	Origin	Notes	Trino	Snowflake	Spark SQL	DuckDB
array_agg(expr)	datafusion-builtin	Collect into an array. NULLs included.	array_agg	array_agg	collect_list	array_agg / list
array_agg(distinct expr)	datafusion-builtin	Distinct array.	array_agg(distinct)	array_agg(distinct)	collect_set	list_distinct
string_agg(expr, sep)	datafusion-builtin	Concatenate with separator. SQL standard.	array_join/listagg	listagg	-	string_agg
listagg(expr, sep)	sqe-trino-functions	Same as string_agg; Snowflake / Trino name. trino_functions.rs:138	listagg	listagg	-	-
histogram(expr)	sqe-trino-functions	Map of value -> count. trino_functions.rs:188	histogram	-	-	histogram
map_agg(key, value)	sqe-trino-functions	Build a map by aggregating key-value pairs. Last write wins. trino_functions.rs:189	map_agg	object_agg	map_from_arrays	map
multimap_agg(key, value)	sqe-trino-functions	Map where each value is an array (collects duplicates). trino_functions.rs:190	multimap_agg	-	-	-
map_union(map_col)	sqe-trino-functions	Aggregate already-built maps into one. trino_functions.rs:191	map_union	-	-	-

Modifiers

Modifier	Notes
agg_func(expr) FILTER (WHERE pred)	Filter rows before aggregation. Cleaner than agg_func(CASE WHEN pred THEN expr END).
agg_func(distinct expr)	Distinct values only.
agg_func(expr) OVER (...)	Window form. Uses PARTITION BY, ORDER BY, frame clauses. See Window functions.
agg_func(expr) WITHIN GROUP (ORDER BY ...)	Ordered aggregate (e.g. listagg).

Example using FILTER:

SELECT
    region,
    count(*) AS total_orders,
    count(*) FILTER (WHERE status = 'cancelled') AS cancelled,
    sum(amount) FILTER (WHERE status = 'shipped') AS shipped_revenue
FROM orders
GROUP BY region;

GROUP BY extensions

Construct	Origin	Notes	Trino	Snowflake	Spark SQL	DuckDB
GROUP BY GROUPING SETS ((a, b), (a), ())	datafusion-builtin	Multiple grouping levels in one query.	yes	yes	yes	yes
GROUP BY CUBE (a, b, c)	datafusion-builtin	All 2^N grouping combinations.	yes	yes	yes	yes
GROUP BY ROLLUP (a, b, c)	datafusion-builtin	Hierarchical: (), (a), (a, b), (a, b, c).	yes	yes	yes	yes
GROUPING(col)	datafusion-builtin	Returns 1 if col was rolled up in this row, else 0.	yes	yes	yes	yes

SELECT
    region,
    product,
    sum(amount) AS revenue,
    GROUPING(region) AS region_rolled_up,
    GROUPING(product) AS product_rolled_up
FROM orders
GROUP BY ROLLUP (region, product)
ORDER BY region, product;

Approximation vs exact: when to choose

• count(distinct) exact. sub-second on millions of rows; avoid above ~1B distinct values.
• approx_distinct HyperLogLog. order of magnitude faster on huge inputs. ~1% relative error.
• median exact. sorts the entire group; expensive on big partitions.
• approx_median / approx_percentile_cont t-digest. sub-percent error, much cheaper memory profile.

For dashboards over multi-billion-row tables, default to approximations. For audit queries that need exact counts, default to exact.