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.
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:
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.
FILTER
to remove nulls from
consideration when using array_agg
:
WHERE
statement you lose information:
arbitrary(x)
→ [same as input]
Returns an arbitrary non-null value of x
, if one exists.
any_value(x)
→ [same as input]
Returns an arbitrary non-null value of x
, if one exists. This is an alias for arbitrary
.
array_agg(x)
→ [same as input]
Returns an array created from the input x
elements.
avg(x)
→ double
Returns the average (arithmetic mean) of all input values.
avg(time interval type)
→ time interval type
Returns the average interval length of all input values.
bool_and(boolean)
→ boolean
Returns TRUE
if every input value is TRUE
, otherwise FALSE
.
bool_or(boolean)
→ boolean
Returns TRUE
if any input value is TRUE
, otherwise FALSE
.
checksum(x)
→ varbinary
Returns an order-insensitive checksum of the given values.
count(*)
→ bigint
Returns the number of input rows.
count(x)
→ bigint
Returns the number of non-null input values.
count_if(x)
→ bigint
Returns the number of TRUE
input values. This function is equivalent to count(CASE WHEN x THEN 1 END)
.
every(boolean)
→ boolean
This is an alias for bool_and
.
geometric_mean(x)
→ double
Returns the geometric mean of all input values.
listagg(x, separator)
→ varchar
Returns the concatenated input values, separated by the separator
string.
Synopsis:
separator
is not specified, the empty string will be used as
separator
.
In its simplest form the function looks like:
1048576
bytes:
LISTAGG
function does not support window
frames.
max(x)
→ [same as input]
Returns the maximum value of all input values.
max(x, n)
→ array<[same as x]>
Returns n
largest values of all input values of x
.
max_by(x, y)
→ [same as x]
Returns the value of x
associated with the maximum value of y
over all input values.
max_by(x, y, n)
→ array<[same as x]>
Returns n
values of x
associated with the n
largest of all input values of y
in descending order of y
.
min(x)
→ [same as input]
Returns the minimum value of all input values.
min(x, n)
→ array<[same as x]>
Returns n
smallest values of all input values of x
.
min_by(x, y)
→ [same as x]
Returns the value of x
associated with the minimum value of y
over all input values.
min_by(x, y, n)
→ array<[same as x]>
Returns n
values of x
associated with the n
smallest of all input values of y
in ascending order of y
.
sum(x)
→ [same as input]
Returns the sum of all input values.
try_sum(uint256)
→ uint256
Returns the sum of all input values, or null if an addition overflow occurred.
bitwise_and_agg(x)
→ bigint
Returns the bitwise AND of all input values in 2’s complement representation.
bitwise_or_agg(x)
→ bigint
Returns the bitwise OR of all input values in 2’s complement representation.
histogram(x)
→ map<K,bigint>
Returns a map containing the count of the number of times each input value occurs.
map_agg(key, value)
→ map<K,V>
Returns a map created from the input key
/ value
pairs.
map_union(x(K,V))
→ map<K,V>
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.
map_union_agg(x(K,V))
→ map<K,V>
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 the last input map.
multimap_agg(key, value)
→ multimap<K,V>
Returns a multimap created from the input key
/ value
pairs.
multimap_union(x(K,V))
→ multimap<K,V>
Returns the union of all the input multimaps. If a key is found in multiple input multimaps, that key’s values in the resulting multimap are the union of all the values from the input multimaps.
approx_distinct(x)
→ bigint
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.
approx_distinct(x, e)
→ bigint
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 to 0.26000.
approx_most_frequent(x, k)
→ map<[same as x], bigint>
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.
approx_percentile(x, percentage)
→ [same as x]
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.
approx_percentile(x, percentages)
→ array<[same as x]>
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.
approx_percentile(x, w, percentage)
→ [same as x]
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.
approx_percentile(x, w, percentages)
→ array<[same as x]>
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.
approx_set(x)
→ HyperLogLog
See hyperloglog
.
merge(x)
→ HyperLogLog
See hyperloglog
.
merge(qdigest(T))
→ qdigest(T)
See qdigest
.
merge(tdigest)
→ tdigest
See tdigest
.
numeric_histogram(buckets, value)
→ map<double, double>
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
.
numeric_histogram(buckets, value, weight)
→ map<double, double>
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.
qdigest_agg(x)
→ qdigest
See Quantile digest functions.
qdigest_agg(x, w)
→ qdigest
See Quantile digest functions.
tdigest_agg(x)
→ tdigest
See T-Digest functions.
tdigest_agg(x, w)
→ tdigest
See T-Digest functions.
corr(x, y)
→ double
Returns correlation coefficient of input values.
covar_pop(y, x)
→ double
Returns the population covariance of input values.
covar_samp(y, x)
→ double
Returns the sample covariance of input values.
kurtosis(x)
→ double
Returns the excess kurtosis of all input values. Unbiased estimate using the following expression:
regr_intercept(y, x)
→ double
Returns linear regression intercept of input values. y
is the dependent value and x
is the independent value.
regr_slope(y, x)
→ double
Returns linear regression slope of input values. y
is the dependent value and x
is the independent value.
skewness(x)
→ double
Returns the skewness of all input values. Returns the Fisher’s moment coefficient of skewness of all input values.
stddev(x)
→ double
Returns the standard deviation of all input values.
stddev_pop(x)
→ double
Returns the population standard deviation of all input values.
stddev_samp(x)
→ double
Returns the sample standard deviation of all input values.
variance(x)
→ double
Returns the variance of all input values.
var_pop(x)
→ double
Returns the population variance of all input values.
var_samp(x)
→ double
Returns the sample variance of all input values.
reduce_agg(inputValue T, initialState S, inputFunction(S, T, S), combineFunction(S, S, S))
→ S
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: