Aggregate functions operate on a set of values to compute a single result.
Except for count
, count_if
, max_by
, min_by
and
approx_distinct
, all of these aggregate functions ignore null values
and return null for no input rows or when all values are null. For example,
sum
returns null rather than zero and avg
does not include null
values in the count. The coalesce
function can be used to convert null into
zero.
Some aggregate functions such as array_agg
produce different results
depending on the order of input values. This ordering can be specified by writing
an order by clause within the aggregate function:
The FILTER
keyword can be used to remove rows from aggregation processing
with a condition expressed using a WHERE
clause. This is evaluated for each
row before it is used in the aggregation and is supported for all aggregate
functions.
A common and very useful example is to use FILTER
to remove nulls from
consideration when using array_agg
As another example, imagine you want to add a condition on the count for Iris flowers, modifying the following query:
If you just use a normal WHERE
statement you lose information:
Using a filter you retain all information:
Returns an arbitrary non-null value x
, if one exists. x
can be any
valid expression. This allows you to return values from columns that are not
directly part of the aggregation, inluding expressions using these columns,
in a query.
For example, the following query returns the customer name from the name
column, and returns the sum of all total prices as customer spend. The
aggregation however uses the rows grouped by the customer identifier
custkey
a required, since only that column is guaranteed to be unique:
Returns an arbitrary non-null value of x
, if one exists. Identical to any_value.
Returns an array created from the input x
elements.
Returns the average (arithmetic mean) of all input values.
Returns the average interval length of all input values.
Returns TRUE
if every input value is TRUE
, otherwise FALSE
.
Returns TRUE
if any input value is TRUE
, otherwise FALSE
.
Returns an order-insensitive checksum of the given values.
Returns the number of input rows.
Returns the number of non-null input values.
Returns the number of TRUE
input values.
This function is equivalent to count(CASE WHEN x THEN 1 END)
.
This is an alias for bool_and
.
Returns the geometric mean of all input values.
Returns the concatenated input values, separated by the separator
string.
Synopsis:
If separator
is not specified, the empty string will be used as separator
.
In its simplest form the function looks like:
and results in:
The overflow behaviour is by default to throw an error in case that the length of the output
of the function exceeds 1048576
bytes:
There exists also the possibility to truncate the output WITH COUNT
or WITHOUT COUNT
of omitted non-null values in case that the length of the output of the
function exceeds 1048576
bytes:
If not specified, the truncation filler string is by default '...'
.
This aggregation function can be also used in a scenario involving grouping:
results in:
This aggregation function supports filtering during aggregation for scenarios where the aggregation for the data not matching the filter condition still needs to show up in the output:
results in:
The current implementation of listagg
function does not support window frames.
Returns the maximum value of all input values.
Returns n
largest values of all input values of x
.
Returns the value of x
associated with the maximum value of y
over all input values.
Returns n
values of x
associated with the n
largest of all input values of y
in descending order of y
.
Returns the minimum value of all input values.
Returns n
smallest values of all input values of x
.
Returns the value of x
associated with the minimum value of y
over all input values.
Returns n
values of x
associated with the n
smallest of all input values of y
in ascending order of y
.
Returns the sum of all input values.
Returns the bitwise AND of all input values in 2’s complement representation.
Returns a map containing the count of the number of times each input value occurs.
Returns a map created from the input key
/ value
pairs.
Returns the union of all the input maps. If a key is found in multiple input maps, that key’s value in the resulting map comes from an arbitrary input map.
For example, take the following histogram function that creates multiple maps from the Iris dataset:
You can combine these maps using map_union
:
Returns a multimap created from the input key
/ value
pairs.
Each key can be associated with multiple values.
Returns the approximate number of distinct input values.
This function provides an approximation of count(DISTINCT x)
.
Zero is returned if all input values are null.
This function should produce a standard error of 2.3%, which is the standard deviation of the (approximately normal) error distribution over all possible sets. It does not guarantee an upper bound on the error for any specific input set.
Returns the approximate number of distinct input values.
This function provides an approximation of count(DISTINCT x)
.
Zero is returned if all input values are null.
This function should produce a standard error of no more than e
, which
is the standard deviation of the (approximately normal) error distribution
over all possible sets. It does not guarantee an upper bound on the error
for any specific input set. The current implementation of this function
requires that e
be in the range of [0.0040625, 0.26000]
.
Computes the top frequent values up to buckets
elements approximately.
Approximate estimation of the function enables us to pick up the frequent
values with less memory. Larger capacity
improves the accuracy of
underlying algorithm with sacrificing the memory capacity. The returned
value is a map containing the top elements with corresponding estimated
frequency.
The error of the function depends on the permutation of the values and its cardinality. We can set the capacity same as the cardinality of the underlying data to achieve the least error.
buckets
and capacity
must be bigint
. value
can be numeric
or string type.
The function uses the stream summary data structure proposed in the paper Efficient Computation of Frequent and Top-k Elements in Data Streams by A. Metwalley, D. Agrawl and A. Abbadi.
Returns the approximate percentile for all input values of x
at the
given percentage
. The value of percentage
must be between zero and
one and must be constant for all input rows.
Returns the approximate percentile for all input values of x
at each of
the specified percentages. Each element of the percentages
array must be
between zero and one, and the array must be constant for all input rows.
Returns the approximate weighed percentile for all input values of x
using the per-item weight w
at the percentage percentage
. Weights must be
greater or equal to 1. Integer-value weights can be thought of as a replication
count for the value x
in the percentile set. The value of percentage
must be
between zero and one and must be constant for all input rows.
Returns the approximate weighed percentile for all input values of x
using the per-item weight w
at each of the given percentages specified
in the array. Weights must be greater or equal to 1. Integer-value weights can
be thought of as a replication count for the value x
in the percentile
set. Each element of the percentages
array must be between zero and one, and the array
must be constant for all input rows.
See Quantile digest functions.
Computes an approximate histogram with up to buckets
number of buckets
for all value
s. This function is equivalent to the variant of
numeric_histogram
that takes a weight
, with a per-item weight of 1
.
Computes an approximate histogram with up to buckets
number of buckets
for all value
s with a per-item weight of weight
. The algorithm
is based loosely on:
buckets
must be a bigint
. value
and weight
must be numeric.
Returns correlation coefficient of input values.
Returns the population covariance of input values.
Returns the sample covariance of input values.
Returns the excess kurtosis of all input values. Unbiased estimate using the following expression:
Returns linear regression intercept of input values. y
is the dependent
value. x
is the independent value.
Returns linear regression slope of input values. y
is the dependent
value. x
is the independent value.
Returns the Fisher’s moment coefficient of skewness of all input values.
This is an alias for stddev_samp
.
Returns the population standard deviation of all input values.
Returns the sample standard deviation of all input values.
This is an alias for var_samp
.
Returns the population variance of all input values.
Returns the sample variance of all input values.
Reduces all input values into a single value. inputFunction
will be invoked
for each non-null input value. In addition to taking the input value, inputFunction
takes the current state, initially initialState
, and returns the new state.
combineFunction
will be invoked to combine two states into a new state.
The final state is returned:
The state type must be a boolean, integer, floating-point, char, varchar or date/time/interval.