Aggregate Functions
any_value
Returns the first non-null value of a column Supported Signatures
function any_value($1) returns $1
approx_count_distinct
approximate distinct row count Supported Signatures
function approx_count_distinct($1, ...) returns decimal(38, 0)
approx_percentile
percentile approximation of inputs Supported Signatures
function approx_percentile(double, double) returns double
approx_percentile_accumulate
tdigest state in JSON format Supported Signatures
function approx_percentile_accumulate(double) returns object
approx_percentile_combine
A tdigest state in JSON format Supported Signatures
function approx_percentile_combine(object) returns object
approx_percentile_estimate
ESTIMATED PERCENTILE VALUE Supported Signatures
function approx_percentile_estimate(object, double) returns double
approx_top_k
Array containing top k items and their frequencies Supported Signatures
function approx_top_k($1) returns array
function approx_top_k($1, decimal(38, 0)) returns array
function approx_top_k($1, decimal(38, 0), decimal(38, 0)) returns array
approx_top_k_accumulate
An internal state Supported Signatures
function approx_top_k_accumulate($1, decimal(38, 0)) returns object
approx_top_k_combine
A state created by combining information from combining all input Supported Signatures
function approx_top_k_combine(object) returns object
function approx_top_k_combine(object, decimal(38, 0)) returns object
approx_top_k_estimate
Array containing most common k items and their frequencies Supported Signatures
function approx_top_k_estimate(object) returns array
function approx_top_k_estimate(object, decimal(38, 0)) returns array
approximate_jaccard_index
Similarity approximation of inputs Supported Signatures
function approximate_jaccard_index(variant) returns double
approximate_similarity
Similarity approximation of inputs Supported Signatures
function approximate_similarity(variant) returns double
array_agg
output array Supported Signatures
function array_agg($1) returns array
array_union_agg
output array Supported Signatures
function array_union_agg(array) returns array
function array_union_agg(array) returns array
array_unique_agg
output array Supported Signatures
function array_unique_agg($1) returns array
avg
Returns the average of the values in a group. Null values are ignored. Supported Signatures
function avg(decimal(38, 0)) returns decimal(38, 0)
function avg(double) returns double
function avg(string) returns string
bitand_agg
Returns the bitwise AND of a column. Null values are ignored. Supported Signatures
function bitand_agg(decimal(38, 0)) returns decimal(38, 0)
function bitand_agg(string) returns string
bitmap_bit_position
relative position in bitmap Supported Signatures
function bitmap_bit_position(decimal(38, 0)) returns decimal(38, 0)
bitmap_bucket_number
bucket index of the destination bitmap Supported Signatures
function bitmap_bucket_number(decimal(38, 0)) returns decimal(38, 0)
bitmap_construct_agg
bitmap Supported Signatures
function bitmap_construct_agg(decimal(38, 0)) returns binary
bitmap_count
number of set bits in the bitmap Supported Signatures
function bitmap_count(binary) returns decimal(38, 0)
bitmap_or_agg
destination bitmap Supported Signatures
function bitmap_or_agg(binary) returns binary
bitor_agg
Returns the bitwise OR of a column. Null values are ignored. Supported Signatures
function bitor_agg(decimal(38, 0)) returns decimal(38, 0)
function bitor_agg(string) returns string
bitxor_agg
Returns the bitwise XOR of a column. Null values are ignored. Supported Signatures
function bitxor_agg(decimal(38, 0)) returns decimal(38, 0)
function bitxor_agg(string) returns string
booland_agg
Returns the logical (boolean) AND of a column. Null values are ignored. Supported Signatures
function booland_agg(decimal(38, 0)) returns boolean
function booland_agg(boolean) returns boolean
boolor_agg
Returns the logical (boolean) OR of a column. Null values are ignored. Supported Signatures
function boolor_agg(decimal(38, 0)) returns boolean
function boolor_agg(boolean) returns boolean
boolxor_agg
Returns the logical (boolean) XOR of a column. Null values are ignored. Supported Signatures
function boolxor_agg(decimal(38, 0)) returns boolean
function boolxor_agg(boolean) returns boolean
corr
Returns the correlation coefficient for non-null pairs in a group. Supported Signatures
function corr(double, double) returns double
count
Return number of non-null values of a given expression Supported Signatures
function count($1, ...) returns decimal(38, 0)
count_if
Returns a conditional count Supported Signatures
function count_if(boolean) returns decimal(38, 0)
function count_if(boolean) returns double
covar_pop
Returns the population covariance for non-null pairs in a group. Supported Signatures
function covar_pop(double, double) returns double
covar_samp
Returns the sample covariance for non-null pairs in a group. Supported Signatures
function covar_samp(double, double) returns double
grouping
Supported Signatures
function grouping(...) returns int
grouping_id
Returns grouping bit-vector Supported Signatures
function grouping_id($1, ...) returns decimal(38, 0)
hash_agg
aggregate hash value Supported Signatures
function hash_agg(...) returns decimal(38, 0)
function hash_agg($1) returns decimal(38, 0)
function hash_agg($1, $2) returns decimal(38, 0)
function hash_agg($1, $2, $3) returns decimal(38, 0)
function hash_agg($1, $2, $3, $4) returns decimal(38, 0)
function hash_agg($1, $2, $3, $4, $5) returns decimal(38, 0)
hll
approximate distinct row count Supported Signatures
function hll($1, ...) returns decimal(38, 0)
hll_accumulate
HyperLogLog state Supported Signatures
function hll_accumulate($1, ...) returns binary
hll_combine
combined HyperLogLog state Supported Signatures
function hll_combine(binary) returns binary
hll_estimate
approximate distinct row count Supported Signatures
function hll_estimate(binary) returns decimal(38, 0)
hll_export
HyperLogLog state as a compound OBJECT Supported Signatures
function hll_export(binary) returns object
hll_import
HyperLogLog state in binary format Supported Signatures
function hll_import(object) returns binary
kurtosis
Returns the sample kurtosis of the values in a group. Null values are ignored. Supported Signatures
function kurtosis(decimal(38, 0)) returns double
function kurtosis(double) returns double
listagg
output string Supported Signatures
function listagg(varchar) returns varchar
function listagg(varchar, varchar) returns varchar
max
Returns the maximum of a column Supported Signatures
function max($1) returns $1
max_by
Returns value of first column with maximum second column val Supported Signatures
function max_by($1, $2) returns $1
function max_by($1, $2, decimal(38, 0)) returns array
median
value of sort key at specified percentile Supported Signatures
function median() returns decimal(38, 0)
function median(decimal(38, 0)) returns decimal(38, 0)
min
Returns the minimum of a column Supported Signatures
function min($1) returns $1
min_by
Returns value of first column with minimum second column val Supported Signatures
function min_by($1, $2) returns $1
function min_by($1, $2, decimal(38, 0)) returns array
minhash
Minhash state including an array of k (first input arg) minimum hash values Supported Signatures
function minhash(decimal(38, 0), $1, ...) returns variant
minhash_combine
Combined result of two (or more) minhash states Supported Signatures
function minhash_combine(variant) returns variant
mode
Returns the most frequent item of a column Supported Signatures
function mode($1) returns $1
object_agg
output object Supported Signatures
function object_agg(varchar, variant) returns object
percentile_cont
value of sort key at specified percentile Supported Signatures
function percentile_cont() returns double
function percentile_cont(decimal(38, 0)) returns double
percentile_disc
value of sort key at specified percentile Supported Signatures
function percentile_disc() returns decimal(38, 0)
function percentile_disc(decimal(38, 0)) returns decimal(38, 0)
regr_avgx
Returns the average of the independent variable (2nd parameter) for non-null pairs. Supported Signatures
function regr_avgx(double, double) returns double
regr_avgy
Returns the average of the dependent variable (1st parameter) for non-null pairs. Supported Signatures
function regr_avgy(double, double) returns double
regr_count
Returns the number of non-null number pairs. Supported Signatures
function regr_count(double, double) returns double
regr_intercept
Returns the intercept of the univariate linear regression line for non-null pairs. Supported Signatures
function regr_intercept(double, double) returns double
regr_r2
Returns the coefficient of determination for non-null pairs. Supported Signatures
function regr_r2(double, double) returns double
regr_slope
Returns the slope of the linear regression line for non-null pairs. Supported Signatures
function regr_slope(double, double) returns double
regr_sxx
Returns REGR_COUNT(expr1, expr2) * VAR_POP(expr2) for non-null pairs. Supported Signatures
function regr_sxx(double, double) returns double
regr_sxy
Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs. Supported Signatures
function regr_sxy(double, double) returns double
regr_syy
Returns REGR_COUNT(expr1, expr2) * VAR_POP(expr1) for non-null pairs. Supported Signatures
function regr_syy(double, double) returns double
skew
Returns the sample skew of the values in a group. Null values are ignored. Supported Signatures
function skew(decimal(38, 0)) returns double
function skew(double) returns double
stddev
Returns the sample standard deviation of the values in a group. Null values are ignored. Supported Signatures
function stddev(decimal(38, 0)) returns double
function stddev(double) returns double
stddev_pop
Returns the population standard deviation of the values in a group. Null values are ignored. Supported Signatures
function stddev_pop(decimal(38, 0)) returns double
function stddev_pop(double) returns double
stddev_samp
Returns the sample standard deviation of the values in a group. Null values are ignored. Supported Signatures
function stddev_samp(decimal(38, 0)) returns double
function stddev_samp(double) returns double
sum
Returns a sum of a column Supported Signatures
function sum(decimal(38, 0)) returns decimal(38, 0)
function sum(double) returns double
var_pop
Returns the population variance of the values in a group. Null values are ignored. Supported Signatures
function var_pop(decimal(38, 0)) returns double
function var_pop(double) returns double
var_samp
Returns the sample variance of the values in a group. Null values are ignored. Supported Signatures
function var_samp(decimal(38, 0)) returns double
function var_samp(double) returns double
variance
Returns the sample variance of the values in a group. Null values are ignored. Supported Signatures
function variance(decimal(38, 0)) returns double
function variance(double) returns double
variance_pop
Returns the population variance of the values in a group. Null values are ignored. Supported Signatures
function variance_pop(decimal(38, 0)) returns double
function variance_pop(double) returns double
variance_samp
Returns the sample variance of the values in a group. Null values are ignored. Supported Signatures
function variance_samp(decimal(38, 0)) returns double
function variance_samp(double) returns double