Aggregate and analytic functions

Applies to VAST Cluster 5.5.

Aggregates that summarise the rows of a query, the FILTER clause, and the window functions the VAST Query Engine can run.

Each function has its own page: syntax, what it does, NULLs and edge cases, examples with their output, how it runs in VAST, how to make it faster, limitations and errors, and similar functions. Names that the VAST Query Engine doesn't have are in all functions A-Z.

Counting

Function	What it does
approx_count_distinct	Estimate the number of distinct non-NULL values of any type, where count(DISTINCT x) isn't available.
count_if (also `countif`)	Count the rows where a BOOLEAN condition is true; countif is the same function.
count (also `count()`, `count_star()`, `count(*)`, `count(ALL x)`, `count(x)`)	Count rows, or the rows where a value isn't NULL; count(), count_star() and count(*) are the same.

Sums and averages

Function	What it does
avg (also `mean`)	Compute the arithmetic mean of the non-NULL values of a number column or expression; mean is the same function.
geomean (also `geometric_mean`)	Compute the geometric mean of positive values, the n-th root of their product; geometric_mean is the same function.
product	Multiply the non-NULL values of a number column or expression together, as a DOUBLE.
sum	Add up numbers, or count the true values of a BOOLEAN, with the result types and overflow behaviour of each input type.
wavg (also `weighted_avg`)	Compute a weighted mean, where each value counts in proportion to its weight; weighted_avg is the same function.

Smallest, largest and any value

Function	What it does
any_value	Return one non-NULL value of an expression from the rows of a query, typically to show a column that's the same on every row next to other aggregates.
max	Return the largest value of a number, BOOLEAN, DATE, TIME, TIMESTAMP or INTERVAL column across the rows of a query.
min	Return the smallest value of a number, BOOLEAN, DATE, TIME, TIMESTAMP or INTERVAL column across the rows of a query.

True or false

Function	What it does
bool_and	Test whether a BOOLEAN expression is true on every row of a query, skipping NULLs.
bool_or	Test whether a BOOLEAN expression is true on at least one row of a query, skipping NULLs.

Spread

Function	What it does
stddev_pop	Compute the population standard deviation of a numeric column across the rows of a query.
stddev_samp (also `stddev`)	Compute the sample standard deviation of a numeric column across the rows of a query; stddev is the same function.
var_samp (also `variance`)	Compute the sample variance of a numeric column across the rows of a query; variance is the same function.
var_pop	Compute the population variance of a numeric column: the mean squared distance from the mean, dividing by n.

Restricting an aggregate

Function	What it does
FILTER (WHERE ...)	Restrict one aggregate to the rows that meet a condition with FILTER (WHERE ...), so one query can aggregate several subsets.

Window functions

Function	What it does
row_number	Number the rows of a result from 1 with row_number() OVER (), and number them in a chosen order with a sorted subquery.
Window functions	Which window functions and OVER (...) forms the VAST Query Engine runs: lag, lead, first_value, row_number and running aggregates over the order rows arrive in, and what to use for the rest.