​
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

All Supported FunctionsArray Functions