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
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>
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
arbitrary
Returns an arbitrary non-null value of x, if one exists. Identical to any_value().
Supported Signatures
function arbitrary($1) returns $1
array_agg
Returns an array created from the input x elements.
Examples:
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>
avg
Returns the average (arithmetic mean) of all input values.
Examples:
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
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
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
bool_and
Returns TRUE if every input value is TRUE, otherwise FALSE.
Supported Signatures
function bool_and(boolean) returns boolean
bool_or
Returns TRUE if any input value is TRUE, otherwise FALSE.
Supported Signatures
function bool_or(boolean) returns boolean
checksum
Returns an order-insensitive checksum of the given values.
Supported Signatures
function checksum($1) returns varbinary
corr
Returns correlation coefficient of input values.
Supported Signatures
function corr(double, double) returns double
function corr(real, real) returns real
count
Returns the number of input rows.
Examples:
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
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
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
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
every
This is an alias for bool_and().
Supported Signatures
function every(boolean) returns boolean
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
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>
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
listagg
Returns the concatenated input values, separated by the separator string.
Supported Signatures
function listagg(varchar, varchar, boolean, varchar, boolean) returns varchar
map_agg
Returns a map created from the input key / value pairs.
Supported Signatures
function map_agg($4, $5) returns map<$4, $5>
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>
max
Returns the maximum value of all input values.
Supported Signatures
function max($3, bigint) returns array<$3>
function max($1, ...) returns $1
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
min
Returns the minimum value of all input values.
Supported Signatures
function min($3, bigint) returns array<$3>
function min($1, ...) returns $1
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
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>>
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>
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
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
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
stddev
This is an alias for stddev_samp().
Supported Signatures
function stddev(bigint) returns double
function stddev(double) returns double
stddev_pop
Returns the population standard deviation of all input values.
Examples:
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
stddev_samp
Returns the sample standard deviation of all input values.
Examples:
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
sum
Returns the sum of all input values.
Examples:
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
var_pop
Returns the population variance of all input values.
Supported Signatures
function var_pop(bigint) returns double
function var_pop(double) returns double
var_samp
Returns the sample variance of all input values.
Supported Signatures
function var_samp(bigint) returns double
function var_samp(double) returns double
variance
This is an alias for var_samp().
Supported Signatures
function variance(bigint) returns double
function variance(double) returns double