Aggregate Functions

​

any_value

Returns the first non-null value of a column Supported Signatures

function any_value($1) returns $1

🔗 Official Documentation

​

approx_count_distinct

approximate distinct row count Supported Signatures

function approx_count_distinct($1, ...) returns decimal(38, 0)

🔗 Official Documentation

​

approx_percentile

percentile approximation of inputs Supported Signatures

function approx_percentile(double, double) returns double

🔗 Official Documentation

​

approx_percentile_accumulate

tdigest state in JSON format Supported Signatures

function approx_percentile_accumulate(double) returns object

🔗 Official Documentation

​

approx_percentile_combine

A tdigest state in JSON format Supported Signatures

function approx_percentile_combine(object) returns object

🔗 Official Documentation

​

approx_percentile_estimate

ESTIMATED PERCENTILE VALUE Supported Signatures

function approx_percentile_estimate(object, double) returns double

🔗 Official Documentation

​

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

🔗 Official Documentation

​

approx_top_k_accumulate

An internal state Supported Signatures

function approx_top_k_accumulate($1, decimal(38, 0)) returns object

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

approximate_jaccard_index

Similarity approximation of inputs Supported Signatures

function approximate_jaccard_index(variant) returns double

🔗 Official Documentation

​

approximate_similarity

Similarity approximation of inputs Supported Signatures

function approximate_similarity(variant) returns double

🔗 Official Documentation

​

array_agg

output array Supported Signatures

function array_agg($1) returns array

🔗 Official Documentation

​

array_union_agg

output array Supported Signatures

function array_union_agg(array) returns array

🔗 Official Documentation

​

array_unique_agg

output array Supported Signatures

function array_unique_agg($1) returns array

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

bitmap_bit_position

relative position in bitmap Supported Signatures

function bitmap_bit_position(decimal(38, 0)) returns decimal(38, 0)

🔗 Official Documentation

​

bitmap_bucket_number

bucket index of the destination bitmap Supported Signatures

function bitmap_bucket_number(decimal(38, 0)) returns decimal(38, 0)

🔗 Official Documentation

​

bitmap_construct_agg

bitmap Supported Signatures

function bitmap_construct_agg(decimal(38, 0)) returns binary

🔗 Official Documentation

​

bitmap_count

number of set bits in the bitmap Supported Signatures

function bitmap_count(binary) returns decimal(38, 0)

🔗 Official Documentation

​

bitmap_or_agg

destination bitmap Supported Signatures

function bitmap_or_agg(binary) returns binary

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

corr

Returns the correlation coefficient for non-null pairs in a group. Supported Signatures

function corr(double, double) returns double

🔗 Official Documentation

​

count

Return number of non-null values of a given expression Supported Signatures

function count(...) returns decimal(38, 0)

🔗 Official Documentation

​

count_if

Returns a conditional count Supported Signatures

function count_if(boolean) returns decimal(38, 0)

🔗 Official Documentation

​

covar_pop

Returns the population covariance for non-null pairs in a group. Supported Signatures

function covar_pop(double, double) returns double

🔗 Official Documentation

​

covar_samp

Returns the sample covariance for non-null pairs in a group. Supported Signatures

function covar_samp(double, double) returns double

🔗 Official Documentation

​

grouping

Supported Signatures

function grouping(...) returns int

🔗 Official Documentation

​

grouping_id

Returns grouping bit-vector Supported Signatures

function grouping_id($1, ...) returns decimal(38, 0)

🔗 Official Documentation

​

hash_agg

aggregate hash value Supported Signatures

function hash_agg(...) returns decimal(19, 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)

🔗 Official Documentation

​

hll

approximate distinct row count Supported Signatures

function hll($1, ...) returns decimal(38, 0)

🔗 Official Documentation

​

hll_accumulate

HyperLogLog state Supported Signatures

function hll_accumulate($1, ...) returns binary

🔗 Official Documentation

​

hll_combine

combined HyperLogLog state Supported Signatures

function hll_combine(binary) returns binary

🔗 Official Documentation

​

hll_estimate

approximate distinct row count Supported Signatures

function hll_estimate(binary) returns decimal(38, 0)

🔗 Official Documentation

​

hll_export

HyperLogLog state as a compound OBJECT Supported Signatures

function hll_export(binary) returns object

🔗 Official Documentation

​

hll_import

HyperLogLog state in binary format Supported Signatures

function hll_import(object) returns binary

🔗 Official Documentation

​

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

🔗 Official Documentation

​

listagg

output string Supported Signatures

function listagg(varchar) returns varchar
function listagg(varchar, varchar) returns varchar

🔗 Official Documentation

​

max

Returns the maximum of a column Supported Signatures

function max($1) returns $1

🔗 Official Documentation

​

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

🔗 Official Documentation

​

median

The median of a column Supported Signatures

function median(decimal(p, s)) returns decimal(rp, rs)
function median(double) returns double

🔗 Official Documentation

​

min

Returns the minimum of a column Supported Signatures

function min($1) returns $1

🔗 Official Documentation

​

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

🔗 Official Documentation

​

minhash

Minhash state including an array of k (first input arg) minimum hash values Supported Signatures

function minhash(decimal(38, 0), $1, ...) returns variant

🔗 Official Documentation

​

minhash_combine

Combined result of two (or more) minhash states Supported Signatures

function minhash_combine(variant) returns variant

🔗 Official Documentation

​

mode

Returns the most frequent item of a column Supported Signatures

function mode($1) returns $1

🔗 Official Documentation

​

object_agg

output object Supported Signatures

function object_agg(varchar, variant) returns object

🔗 Official Documentation

​

percentile_cont

value of a sorted key at specified percentile in continuous distribution Supported Signatures

function percentile_cont(decimal(p,s), $1) returns $1

🔗 Official Documentation

​

percentile_disc

value of a sorted key at specified percentile in discrete distribution Supported Signatures

function percentile_disc(decimal(p,s), $1) returns $1

🔗 Official Documentation

​

regr_avgx

Returns the average of the independent variable (2nd parameter) for non-null pairs. Supported Signatures

function regr_avgx(double, double) returns double

🔗 Official Documentation

​

regr_avgy

Returns the average of the dependent variable (1st parameter) for non-null pairs. Supported Signatures

function regr_avgy(double, double) returns double

🔗 Official Documentation

​

regr_count

Returns the number of non-null number pairs. Supported Signatures

function regr_count(double, double) returns double

🔗 Official Documentation

​

regr_intercept

Returns the intercept of the univariate linear regression line for non-null pairs. Supported Signatures

function regr_intercept(double, double) returns double

🔗 Official Documentation

​

regr_r2

Returns the coefficient of determination for non-null pairs. Supported Signatures

function regr_r2(double, double) returns double

🔗 Official Documentation

​

regr_slope

Returns the slope of the linear regression line for non-null pairs. Supported Signatures

function regr_slope(double, double) returns double

🔗 Official Documentation

​

regr_sxx

Returns REGR_COUNT(expr1, expr2) * VAR_POP(expr2) for non-null pairs. Supported Signatures

function regr_sxx(double, double) returns double

🔗 Official Documentation

​

regr_sxy

Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs. Supported Signatures

function regr_sxy(double, double) returns double

🔗 Official Documentation

​

regr_syy

Returns REGR_COUNT(expr1, expr2) * VAR_POP(expr1) for non-null pairs. Supported Signatures

function regr_syy(double, double) returns double

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

sum

Returns a sum of a column Supported Signatures

function sum(decimal(p, s)) returns decimal(rp, s)
function sum(double) returns double

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation

​

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

🔗 Official Documentation