Aggregate Functions

`approx_distinct`

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. Supported Signatures

function approx_distinct(boolean) returns bigint
function approx_distinct(boolean, double) returns bigint
function approx_distinct($1) returns bigint
function approx_distinct($1, double) returns bigint
function approx_distinct(unknown) returns bigint
function approx_distinct(unknown, double) returns bigint

🔗 Official Documentation

`approx_most_frequent`

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. Supported Signatures

function approx_most_frequent(bigint, bigint, bigint) returns map<bigint, bigint>
function approx_most_frequent(bigint, varchar, bigint) returns map<varchar, bigint>

🔗 Official Documentation

`approx_percentile`

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. Supported Signatures

function approx_percentile(bigint, array<double>) returns array<bigint>
function approx_percentile(bigint, double, array<double>) returns array<bigint>
function approx_percentile(double, array<double>) returns array<double>
function approx_percentile(double, double, array<double>) returns array<double>
function approx_percentile(real, array<double>) returns array<real>
function approx_percentile(real, double, array<double>) returns array<real>
function approx_percentile(bigint, double) returns bigint
function approx_percentile(bigint, double, double) returns bigint
function approx_percentile(bigint, double, double, double) returns bigint
function approx_percentile(double, double) returns double
function approx_percentile(double, double, double) returns double
function approx_percentile(double, double, double, double) returns double
function approx_percentile(real, double) returns real
function approx_percentile(real, double, double) returns real
function approx_percentile(real, double, double, double) returns real

🔗 Official Documentation

`arbitrary`

Returns an arbitrary non-null value of x, if one exists. Identical to any_value(). Supported Signatures

function arbitrary($1) returns $1

🔗 Official Documentation

`array_agg`

Returns an array created from the input x elements. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9);  SELECT array_agg(column1) AS value FROM tbl;
 -- value '[1, 5, 9]'

Supported Signatures

function array_agg($1) returns array<$1>

🔗 Official Documentation

`avg`

Returns the average (arithmetic mean) of all input values. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9); 
 SELECT avg(tbl.column1) AS value FROM tbl; -- value '5.0'

Supported Signatures

function avg(double) returns double
function avg(decimal(p, s)) returns decimal(p, s)
function avg(interval day to second) returns interval day to second
function avg(interval year to month) returns interval year to month
function avg(real) returns real

🔗 Official Documentation

`bitwise_and_agg`

Returns the bitwise AND of all input non-NULL values in 2’s complement representation. If all records inside the group are NULL, or if the group is empty, the function returns NULL. Supported Signatures

function bitwise_and_agg(bigint) returns bigint

🔗 Official Documentation

`bitwise_or_agg`

Returns the bitwise OR of all input non-NULL values in 2’s complement representation. If all records inside the group are NULL, or if the group is empty, the function returns NULL. Supported Signatures

function bitwise_or_agg(bigint) returns bigint

🔗 Official Documentation

`bool_and`

Returns TRUE if every input value is TRUE, otherwise FALSE. Supported Signatures

function bool_and(boolean) returns boolean

🔗 Official Documentation

`bool_or`

Returns TRUE if any input value is TRUE, otherwise FALSE. Supported Signatures

function bool_or(boolean) returns boolean

🔗 Official Documentation

`checksum`

Returns an order-insensitive checksum of the given values. Supported Signatures

function checksum($1) returns varbinary

🔗 Official Documentation

`corr`

Returns correlation coefficient of input values. Supported Signatures

function corr(double, double) returns double
function corr(real, real) returns real

🔗 Official Documentation

`count`

Returns the number of input rows. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9); 
 SELECT count(tbl.column1) AS value FROM tbl; -- value '3'

Supported Signatures

function count() returns bigint
function count($1) returns bigint

🔗 Official Documentation

`count_if`

Returns the number of TRUE input values. This function is equivalent to count(CASE WHEN x THEN 1 END). Supported Signatures

function count_if(boolean) returns bigint

🔗 Official Documentation

`covar_pop`

Returns the population covariance of input values. Supported Signatures

function covar_pop(double, double) returns double
function covar_pop(real, real) returns real

🔗 Official Documentation

`covar_samp`

Returns the sample covariance of input values. Supported Signatures

function covar_samp(double, double) returns double
function covar_samp(real, real) returns real

🔗 Official Documentation

`every`

This is an alias for bool_and(). Supported Signatures

function every(boolean) returns boolean

🔗 Official Documentation

`geometric_mean`

Returns the geometric mean of all input values. Supported Signatures

function geometric_mean(bigint) returns double
function geometric_mean(double) returns double
function geometric_mean(real) returns real

🔗 Official Documentation

`histogram`

Returns a map containing the count of the number of times each input value occurs. Supported Signatures

function histogram($1) returns map<$1, bigint>

🔗 Official Documentation

`kurtosis`

Returns the excess kurtosis of all input values. Unbiased estimate using the following expression. Supported Signatures

function kurtosis(bigint) returns double
function kurtosis(double) returns double

🔗 Official Documentation

`listagg`

Returns the concatenated input values, separated by the separator string. Supported Signatures

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

🔗 Official Documentation

`map_agg`

Returns a map created from the input key / value pairs. Supported Signatures

function map_agg($4, $5) returns map<$4, $5>

🔗 Official Documentation

`map_union`

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. Supported Signatures

function map_union(map<$4, $5>) returns map<$4, $5>

🔗 Official Documentation

`max`

Returns the maximum value of all input values. Supported Signatures

function max($3, bigint) returns array<$3>
function max($1, ...) returns $1

🔗 Official Documentation

`max_by`

Returns the value of x associated with the maximum value of y over all input values. Supported Signatures

function max_by($5, $4, bigint) returns array<$5>
function max_by($5, $4) returns $5

🔗 Official Documentation

`min`

Returns the minimum value of all input values. Supported Signatures

function min($3, bigint) returns array<$3>
function min($1, ...) returns $1

🔗 Official Documentation

`min_by`

Returns the value of x associated with the minimum value of y over all input values. Supported Signatures

function min_by($5, $4, bigint) returns array<$5>
function min_by($5, $4) returns $5

🔗 Official Documentation

`multimap_agg`

Returns a multimap created from the input key / value pairs. Each key can be associated with multiple values. Supported Signatures

function multimap_agg($4, $5) returns map<$4, array<$5>>

🔗 Official Documentation

`numeric_histogram`

Computes an approximate histogram with up to buckets number of buckets for all values with a per-item weight of weight. The algorithm is based loosely on. Supported Signatures

function numeric_histogram(bigint, double) returns map<double, double>
function numeric_histogram(bigint, double, double) returns map<double, double>
function numeric_histogram(bigint, real) returns map<real, real>
function numeric_histogram(bigint, real, double) returns map<real, real>

🔗 Official Documentation

`regr_intercept`

Returns linear regression intercept of input values. y is the dependent value. x is the independent value. Supported Signatures

function regr_intercept(double, double) returns double
function regr_intercept(real, real) returns real

🔗 Official Documentation

`regr_slope`

Returns linear regression slope of input values. y is the dependent value. x is the independent value. Supported Signatures

function regr_slope(double, double) returns double
function regr_slope(real, real) returns real

🔗 Official Documentation

`skewness`

Returns the Fisher’s moment coefficient of skewness of all input values. Supported Signatures

function skewness(bigint) returns double
function skewness(double) returns double

🔗 Official Documentation

`stddev`

This is an alias for stddev_samp(). Supported Signatures

function stddev(bigint) returns double
function stddev(double) returns double

🔗 Official Documentation

`stddev_pop`

Returns the population standard deviation of all input values. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9);  SELECT stddev_pop(column1) as value from tbl;
 -- value '3.265986323710904'

Supported Signatures

function stddev_pop(bigint) returns double
function stddev_pop(double) returns double

🔗 Official Documentation

`stddev_samp`

Returns the sample standard deviation of all input values. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9);  SELECT stddev_samp(column1) as value from tbl;
 -- value '4.0'

Supported Signatures

function stddev_samp(bigint) returns double
function stddev_samp(double) returns double

🔗 Official Documentation

`sum`

Returns the sum of all input values. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9); 
 SELECT sum(tbl.column1) AS value FROM tbl; -- value '15'

Supported Signatures

function sum(bigint) returns bigint
function sum(double) returns double
function sum(decimal(p, s)) returns decimal(38, s)
function sum(real) returns real
function sum(interval day to second) returns interval day to second
function sum(interval year to month) returns interval year to month

🔗 Official Documentation

`var_pop`

Returns the population variance of all input values. Supported Signatures

function var_pop(bigint) returns double
function var_pop(double) returns double

🔗 Official Documentation

`var_samp`

Returns the sample variance of all input values. Supported Signatures

function var_samp(bigint) returns double
function var_samp(double) returns double

🔗 Official Documentation

`variance`

This is an alias for var_samp(). Supported Signatures

function variance(bigint) returns double
function variance(double) returns double

🔗 Official Documentation

​approx_distinct

​approx_most_frequent

​approx_percentile

​arbitrary

​array_agg

​avg

​bitwise_and_agg

​bitwise_or_agg

​bool_and

​bool_or

​checksum

​corr

​count

​count_if

​covar_pop

​covar_samp

​every

​geometric_mean

​histogram

​kurtosis

​listagg

​map_agg

​map_union

​max

​max_by

​min

​min_by

​multimap_agg

​numeric_histogram

​regr_intercept

​regr_slope

​skewness

​stddev

​stddev_pop

​stddev_samp

​sum

​var_pop

​var_samp

​variance

`approx_distinct`

`approx_most_frequent`

`approx_percentile`

`arbitrary`

`array_agg`

`avg`

`bitwise_and_agg`

`bitwise_or_agg`

`bool_and`

`bool_or`

`checksum`

`corr`

`count`

`count_if`

`covar_pop`

`covar_samp`

`every`

`geometric_mean`

`histogram`

`kurtosis`

`listagg`

`map_agg`

`map_union`

`max`

`max_by`

`min`

`min_by`

`multimap_agg`

`numeric_histogram`

`regr_intercept`

`regr_slope`

`skewness`

`stddev`

`stddev_pop`

`stddev_samp`

`sum`

`var_pop`

`var_samp`

`variance`