Aggregate functions

Aggregate functions are functions that combine values from multiple rows into one.

Aggregate functions differ from scalar functions and window functions in that they change the cardinality of the result. That is why they can be used in queries only in expressions SELECT and HAVING.

`DISTINCT` expression in aggregate functions

When the expression DISTINCT is used, only unique values are considered when calculating the value of an aggregate function. Often this expression is used in conjunction with an aggregate function count() to get the number of unique items, but it can be used with other aggregate functions as well.

Example

CREATE TABLE cities(city_name VARCHAR);

INSERT INTO cities VALUES
    ('Moscow'),
    ('Moscow'),
    ('Paris'),
    ('Madrid');

SELECT
    count(DISTINCT city_name) AS distinct_cities_num,
    count(city_name) AS cities_num
FROM cities;

+---------------------+------------+
| distinct_cities_num | cities_num |
+---------------------+------------+
| 3                   | 4          |
+---------------------+------------+

Some aggregate functions are insensitive to repeated values (e.g, min() и max()), and for them the DISTINCT expression is ignored.

`any_value()`

Description	Returns the first value from `argument` other than `NULL`.
Usage	`any_value(argument)`

The result of this function is affected by the sort order in the table.

See example

CREATE TABLE numbers(number_asc BIGINT, number_desc BIGINT);

INSERT INTO numbers VALUES
    (NULL,  3),
    (1,     2),
    (2,     1),
    (3,     NULL);

SELECT
    any_value(number_asc) AS "any value from number asc",
    any_value(number_desc) AS "any value from number desc"
FROM numbers;

+---------------------------+----------------------------+
| any value from number asc | any value from number desc |
+---------------------------+----------------------------+
| 1                         | 3                          |
+---------------------------+----------------------------+

`approx_count_distinct()`

Description	Calculates the approximate number of unique values.
Usage	`approx_count_distinct(argument)`

Calculates the approximate number of unique values in a column using the HyperLogLog probabilistic algorithm.

Useful for quickly counting the number of unique values. On large tables, it runs faster than COUNT(DISTINCT).

See example

WITH test AS (
    SELECT
        (generate_series % 1000000) AS col1
    FROM generate_series(1, 100000000)
)
SELECT
    approx_count_distinct(col1) AS result
FROM test;

+--------+
| result |
+--------+
| 962761 |
+--------+

`approx_quantile()`

Description	Calculates an approximate quantile.
Usage	`approx_quantile(argument)`

Calculates an approximate quantile of numeric values using the T-Digest algorithm.

See example

WITH test AS (
    SELECT
        (generate_series % 500) + (generate_series % 10) AS col1
    FROM generate_series(1, 10000000)
)
SELECT
    approx_quantile(col1, 0.95) AS result
FROM test;

+--------+
| result |
+--------+
| 479    |
+--------+

`approx_top_k()`

Description	Calculates an approximate list of the `k` most frequently occurring values.
Usage	`approx_top_k(argument)`

Calculates an approximate list of the k most frequently occurring values using the Filtered Space-Saving method.

See example

WITH test AS (
    SELECT
        CASE
            WHEN generate_series % 10 = 0 THEN 'A'
            WHEN generate_series % 5 = 0  THEN 'B'
            WHEN generate_series % 3 = 0  THEN 'C'
            ELSE 'D'
        END AS col1
    FROM generate_series(1, 10000000)
)
SELECT
    approx_top_k(col1, 3) AS result
FROM test;

+---------+
| result  |
+---------+
| {D,C,B} |
+---------+

`arg_max()`

Description	Finds the row with the maximum value in one column and returns the value of the other column in that row.
Usage	`arg_max(argument1, argument2[, n])`
Aliases	`argmax()`, `max_by()`

Finds the row with the maximum value in column argument2 and returns the value of column argument1 in that row. Rows with a NULL value are ignored.

If a third argument is specified, a list of length n containing the values of column argument1 corresponding to the top n values of column argument2 is returned.

The result of this function is affected by the sort order in the table.

See examples

CREATE TABLE demo (num BIGINT, letter VARCHAR);

INSERT INTO demo VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (3, NULL),
    (4, NULL);

SELECT
    arg_max(letter, num) AS result
FROM demo;

+--------+
| result |
+--------+
| C      |
+--------+

CREATE TABLE demo (num BIGINT, letter VARCHAR);

INSERT INTO demo VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (3, NULL),
    (4, NULL);

SELECT
    arg_max(letter, num, 2) AS result
FROM demo;

+--------+
| result |
+--------+
| {C,B}  |
+--------+

`arg_max_null()`

Description	Finds the row with the maximum value in one column and returns the value of the other column in that row (even if it is empty).
Usage	`arg_max_null(argument1, argument2)`

Finds the row with the maximum value in the argument2 column and returns the value of the argument1 column in that row. If the argument1 column contains a NULL value, that is returned.

The result of this function is affected by the sort order in the table.

See example

CREATE TABLE demo (num BIGINT, letter VARCHAR);

INSERT INTO demo VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (3, NULL),
    (4, NULL);

SELECT
    arg_max_null(letter, num) AS result
FROM demo;

+--------+
| result |
+--------+
| null   |
+--------+

`arg_min()`

Description	Finds the row with the minimum value in one column and returns the value of the other column in that row.
Usage	`arg_min(argument1, argument2[, n])`
Aliases	`argmin()`, `min_by()`

Finds the row with the minimum value in column argument2 and returns the value of column argument1 in that row. Rows with a NULL value are ignored.

If a third argument is specified, a list of length n containing the values of column argument1 corresponding to the top n values of column argument2, sorted in ascending order, is returned.

The result of this function is affected by the sort order in the table.

See examples

CREATE TABLE demo (num BIGINT, letter VARCHAR);

INSERT INTO demo VALUES
    (0, NULL),
    (1, NULL),
    (1, 'A'),
    (2, 'B'),
    (3, 'C');

SELECT
    arg_min(letter, num) AS result
FROM demo;

+--------+
| result |
+--------+
| A      |
+--------+

CREATE TABLE demo (num BIGINT, letter VARCHAR);

INSERT INTO demo VALUES
    (0, NULL),
    (1, NULL),
    (1, 'A'),
    (2, 'B'),
    (3, 'C');

SELECT
    arg_min(letter, num, 2) AS result
FROM demo;

+--------+
| result |
+--------+
| {A,B}  |
+--------+

`arg_min_null()`

Description	Finds the row with the minimum value in one column and returns the value of the other column in that row (even if it is empty).
Usage	`arg_min_null(argument1, argument2)`

Finds the row with the minimum value in the argument2 column and returns the value of the argument1 column in that row. If the argument1 column contains a NULL value, that is returned.

The result of this function is affected by the sort order in the table.

See example

CREATE TABLE demo (num BIGINT, letter VARCHAR);

INSERT INTO demo VALUES
    (0, NULL),
    (1, NULL),
    (1, 'A'),
    (2, 'B'),
    (3, 'C');

SELECT
    arg_min_null(letter, num) AS result
FROM demo;

+--------+
| result |
+--------+
| null   |
+--------+

`array_agg()`

Description	Returns a list containing all the values of a column.
Usage	`array_agg(argument)`
Aliases	`list()`

The result of this function is affected by the sort order in the table.

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (NULL);

SELECT
    array_agg(number) AS array_agg,
    list(number) AS list
FROM numbers;

+--------------+--------------+
|   array_agg  |     list     |
+--------------+--------------+
| {1,2,3,None} | {1,2,3,None} |
+--------------+--------------+

`avg()`

Description	Calculates the average of all non-empty values in a group.
Usage	`avg(argument)`
Aliases	`mean()`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (NULL);

SELECT
    avg(number) AS average,
    mean(number) AS mean
FROM numbers;

+---------+------+
| average | mean |
+---------+------+
| 2       | 2    |
+---------+------+

`bit_and()`

Description	Returns the bitwise `AND` of all bits in the group.
Usage	`bit_and(argument)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (3),
    (5),
    (7),
    (NULL);

SELECT
    bit_and(number) AS result
FROM numbers;

+--------+
| result |
+--------+
| 1      |
+--------+

`bit_or()`

Description	Returns a bitwise `OR` for all bits in the group.
Usage	`bit_or(argument)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (3),
    (5),
    (7),
    (NULL);

SELECT
    bit_or(number) AS result
FROM numbers;

+--------+
| result |
+--------+
| 7      |
+--------+

`bit_xor()`

Description	Returns the bitwise `XOR` for all bits in the group.
Usage	`bit_xor(argument)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (1),
    (2),
    (2),
    (3),
    (NULL);

SELECT
    bit_xor(number) AS result
FROM numbers;

+--------+
| result |
+--------+
| 3      |
+--------+

`bitstring_agg()`

Description	Returns a bit string whose length corresponds to the range of non-zero (integer) values.
Usage	`bitstring_agg(argument)`

Returns a bit string whose length corresponds to the range of non-zero (integer) values. The bits are set at the position of each (unique) value.

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (1),
    (2),
    (2),
    (3),
    (NULL);

SELECT
    bit_count(bitstring_agg(number)) AS result
FROM numbers;

+--------+
| result |
+--------+
| 3      |
+--------+

`bool_and()`

Description	Returns `TRUE` if all non-empty values in the group are true; otherwise, `FALSE`.
Usage	`bool_and(argument)`

See example

CREATE TABLE demo(col1 BOOL, col2 BOOL, col3 BOOL);

INSERT INTO demo VALUES
    (true, true, true),
    (true, true, true),
    (false, true, true),
    (true, true, true),
    (NULL, true, NULL);

SELECT
    bool_and(col1) AS result_1,
    bool_and(col2) AS result_2,
    bool_and(col3) AS result_3,
FROM demo;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| false    | true     | true     |
+----------+----------+----------+

`bool_or()`

Description	Returns `TRUE` if at least one value in the group is true, otherwise `FALSE`.
Usage	`bool_or(argument)`

See example

CREATE TABLE demo(col1 BOOL, col2 BOOL, col3 BOOL);

INSERT INTO demo VALUES
    (true, false, false),
    (true, false, false),
    (false, false, false),
    (true, false, false),
    (NULL, false, NULL);

SELECT
    bool_or(col1) AS result_1,
    bool_or(col2) AS result_2,
    bool_or(col3) AS result_3,
FROM demo;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| true     | false    | false    |
+----------+----------+----------+

`count()`

Description	Counts the number of rows in a group.
Usage	`count()`
Aliases	`count(*)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (NULL);

SELECT
    count() AS rows_count,
    count(*) AS rows_count_star
FROM numbers;

+------------+-----------------+
| rows_count | rows_count_star |
+------------+-----------------+
| 4          | 4               |
+------------+-----------------+

`count(argument)`

Description	Counts the number of non-empty values in a group.
Usage	`count(argument)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (NULL);

SELECT
    count() AS rows_count,
    count(number) AS values_count
FROM numbers;

+------------+--------------+
| rows_count | values_count |
+------------+--------------+
| 4          | 3            |
+------------+--------------+

`corr()`

Description	Calculates the correlation coefficient.
Usage	`corr(argument1, argument2)`
Formula	`covar_pop(y, x) / (stddev_pop(x) * stddev_pop(y))`

See example

CREATE TABLE numbers(number_1 BIGINT, number_2 BIGINT);

INSERT INTO numbers VALUES
    (1, 1),
    (2, 2),
    (3, 30),
    (NULL, NULL);

SELECT
    corr(number_1, number_2) AS result
FROM numbers;

+--------------------+
| result             |
+--------------------+
| 0.8808122718846411 |
+--------------------+

`covar_pop()`

Description	Calculates the population covariance (does not include bias correction).
Usage	`covar_pop(argument1, argument2)`
Formula	`(sum(xy) - sum(x) sum(y) / regr_count(y, x)) / regr_count(y, x), covar_samp(y, x) * (1 - 1 / regr_count(y, x))`

See example

CREATE TABLE numbers(number_1 BIGINT, number_2 BIGINT);

INSERT INTO numbers VALUES
    (1, 1),
    (2, 2),
    (3, 30),
    (NULL, NULL);

SELECT
    covar_pop(number_1, number_2) AS result
FROM numbers;

+-------------------+
| result            |
+-------------------+
| 9.666666666666666 |
+-------------------+

`covar_samp()`

Description	Calculates the sample covariance, including the Bessel bias correction.
Usage	`covar_samp(argument1, argument2)`
Formula	`(sum(xy) - sum(x) sum(y) / regr_count(y, x)) / (regr_count(y, x) - 1), covar_pop(y, x) / (1 - 1 / regr_count(y, x))`
Aliases	`regr_sxy()`

See example

CREATE TABLE numbers(number_1 BIGINT, number_2 BIGINT);

INSERT INTO numbers VALUES
    (1, 1),
    (2, 2),
    (3, 30),
    (NULL, NULL);

SELECT
    covar_samp(number_1, number_2) AS result
FROM numbers;

+--------+
| result |
+--------+
| 14.5   |
+--------+

`count_if()`

Description	Returns the number of records that satisfy the condition, or `NULL` if no records satisfy the condition.
Usage	`count_if(<condition>)`
Alias	`countif()`

See example

CREATE TABLE text_table(text_data VARCHAR);

INSERT INTO text_table VALUES
('Tengri'),
('Tengri'),
('TNGRi'),
(NULL);

SELECT
    COUNT_IF(TRUE) AS row_number,
    COUNT_IF(text_data = 'Tengri') AS tengri_number
FROM text_table;

+------------+---------------+
| row_number | tengri_number |
+------------+---------------+
| 4          | 2             |
+------------+---------------+

`entropy()`

Description	Calculates the log-2 entropy.
Usage	`entropy(argument)`

See example

CREATE TABLE numbers(number_1 BIGINT, number_2 BIGINT);

INSERT INTO numbers VALUES
    (1, 1),
    (2, 2),
    (3, 2),
    (NULL, NULL);

SELECT
    entropy(number_1) AS result_1,
    entropy(number_2) AS result_2
FROM numbers;

+--------------------+--------------------+
| result_1           | result_2           |
+--------------------+--------------------+
| 1.5849625007211559 | 0.9182958340544893 |
+--------------------+--------------------+

`favg()`

Description	Calculates the average using the Kahan Sum algorithm.
Usage	`favg(argument)`

Calculates the average using the https://en.wikipedia.org/wiki/Kahan_summation_algorithm algorithm[Kahan Sum].

The result of this function is affected by the sort order in the table.

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (NULL);

SELECT
    favg(number) AS result
FROM numbers;

+--------+
| result |
+--------+
| 2      |
+--------+

`first()`

Description	Returns the first value from a group.
Usage	`first(argument)`
Aliases	`arbitrary()`

Returns the first value from a group.

The result of this function is affected by the sort order in the table.

See example

CREATE TABLE weekdays (name VARCHAR, weekend BOOL);

INSERT INTO weekdays VALUES
('Monday', False),
('Tuesday', False),
('Wednesday', False),
('Thursday', False),
('Friday', False),
('Saturday', True),
('Sunday', True);

SELECT
    first(name) AS first_day,
    weekend
FROM weekdays
GROUP BY weekend;

+-----------+---------+
| first_day | weekend |
+-----------+---------+
| Monday    | false   |
+-----------+---------+
| Saturday  | true    |
+-----------+---------+

`fsum()`

Description	Calculates the sum of non-empty values in a group using the Kahan Sum algorithm.
Usage	`fsum(argument)`
Aliases	`sumkahan()`, `kahan_sum()`

Calculates the sum of non-empty values in a group using the https://en.wikipedia.org/wiki/Kahan_summation_algorithm [Kahan Sum] algorithm.

The result of this function is affected by the sort order in the table.

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (NULL);

SELECT
    fsum(number) AS result
FROM numbers;

+--------+
| result |
+--------+
| 6      |
+--------+

`geometric_mean()`

Description	Calculates the geometric mean of non-empty values in a group.
Usage	`geometric_mean(argument)`
Aliases	`geomean()`

Calculates the geometric mean of non-empty values in a group.

The result of this function is affected by the sort order in the table.

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (NULL);

SELECT
    geometric_mean(number) AS result
FROM numbers;

+--------------------+
| result             |
+--------------------+
| 1.8171205928321397 |
+--------------------+

`histogram()`

Description	Returns a dictionary of unique non-empty values in a group, along with the number of times each occurs.
Usage	`histogram(argument[, boundaries_list])`

Returns a dictionary of unique non-empty values in a group, along with the number of times each occurs.

If the boundaries_list argument is specified, the range of all values is split according to the boundaries specified in this list, and the number of values in each interval is returned.

See examples

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (1),
    (1),
    (2),
    (2),
    (3),
    (NULL);

SELECT
    histogram(number) AS result
FROM numbers;

+--------------------------------------------------------------------------+
| result                                                                   |
+--------------------------------------------------------------------------+
| [{"key": 1, "value": 3}, {"key": 2, "value": 2}, {"key": 3, "value": 1}] |
+--------------------------------------------------------------------------+

WITH numbers AS (
    SELECT unnest(generate_series(1, 10)) as number
    )
SELECT
    histogram(number, [1,5,10]) AS result
FROM numbers;

+---------------------------------------------------------------------------+
| result                                                                    |
+---------------------------------------------------------------------------+
| [{"key": 1, "value": 1}, {"key": 5, "value": 4}, {"key": 10, "value": 5}] |
+---------------------------------------------------------------------------+

`histogram_exact()`

Description	Returns a dictionary of the specified values in the group, along with the number of occurrences of each.
Usage	`histogram_exact(argument, elements_list)`

Returns a dictionary of the values specified in elements_list, grouped by their frequency.

Any values not present in the elements_list are placed in a separate group.

Empty values are ignored.

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (1),
    (1),
    (2),
    (2),
    (3),
    (NULL);

SELECT
    histogram_exact(number, [2, 3]) AS result
FROM numbers;

+--------------------------------------------------------------------------------------------+
| result                                                                                     |
+--------------------------------------------------------------------------------------------+
| [{"key": 2, "value": 2}, {"key": 3, "value": 1}, {"key": 9223372036854775807, "value": 3}] |
+--------------------------------------------------------------------------------------------+

`kurtosis()`

Description	Calculates kurtosis (as defined by Fisher) with bias correction according to the sample size..
Usage	`kurtosis(argument)`

See example

CREATE TABLE numbers(number_1 BIGINT, number_2 BIGINT);

INSERT INTO numbers VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 400),
    (NULL, NULL);

SELECT
    kurtosis(number_1) AS result_1,
    kurtosis(number_2) AS result_2
FROM numbers;

+--------------------+--------------------+
| result_1           | result_2           |
+--------------------+--------------------+
| -1.200000000000001 | 3.9996633187929866 |
+--------------------+--------------------+

`kurtosis_pop()`

Description	Calculates kurtosis (as defined by Fisher) without bias correction.
Usage	`kurtosis_pop(argument)`

See example

CREATE TABLE numbers(number_1 BIGINT, number_2 BIGINT);

INSERT INTO numbers VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 400),
    (NULL, NULL);

SELECT
    kurtosis_pop(number_1) AS result_1,
    kurtosis_pop(number_2) AS result_2
FROM numbers;

+----------+---------------------+
| result_1 | result_2            |
+----------+---------------------+
| -1.36    | -0.6667115574942684 |
+----------+---------------------+

`last()`

Description	Returns the last value from a group.
Usage	`last(argument)`

Returns the last value from a group.

The result of this function is affected by the sort order in the table.

See example

CREATE TABLE weekdays (name VARCHAR, weekend BOOL);

INSERT INTO weekdays VALUES
('Monday', False),
('Tuesday', False),
('Wednesday', False),
('Thursday', False),
('Friday', False),
('Saturday', True),
('Sunday', True);

SELECT
    last(name) AS last_day,
    weekend
FROM weekdays
GROUP BY weekend;

+----------+---------+
| last_day | weekend |
+----------+---------+
| Friday   | false   |
+----------+---------+
| Sunday   | true    |
+----------+---------+

`mad()`

Description	Calculates the median absolute deviation.
Usage	`mad(argument)`
Formula	`median(abs(x - median(x)))`

Calculates the median absolute deviation. Date and time types return a positive interval.

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (4),
    (400),
    (NULL);

SELECT
    stddev_samp(number),
    mad(number)
FROM numbers;

+--------------------+-----+
| stddev_samp        | mad |
+--------------------+-----+
| 177.77092000661975 | 1   |
+--------------------+-----+

`max()`

Description	Returns the maximum value in the group.
Usage	`max(argument)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (NULL);

SELECT
    max(number),
    min(number)
FROM numbers;

+-----+-----+
| max | min |
+-----+-----+
| 3   | 1   |
+-----+-----+

`min()`

Description	Returns the minimum value in the group.
Usage	`min(argument)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (NULL);

SELECT
    max(number),
    min(number)
FROM numbers;

+-----+-----+
| max | min |
+-----+-----+
| 3   | 1   |
+-----+-----+

`median()`

Description	Returns the median of all non-empty values in the group.
Usage	`median(argument)`

If there is an even number of values, the average of the two central values is taken.

See example

Let’s show the difference between the median value and the mean value (avg()) on examples of even (_even) and odd (_odd) number sets containing empty values.

CREATE TABLE numbers(
    number_even BIGINT,
    number_odd BIGINT);

INSERT INTO numbers VALUES
    (1,    1),
    (2,    2),
    (3,    10),
    (10,   NULL),
    (NULL, NULL);

SELECT
    median(number_even) AS median_even,
    avg(number_even) AS avg_even,
    median(number_odd) AS median_odd,
    avg(number_odd) AS avg_odd
FROM numbers;

+-------------+----------+------------+-------------------+
| median_even | avg_even | median_odd |      avg_odd      |
+-------------+----------+------------+-------------------+
| 2.5         | 4        | 2          | 4.333333333333333 |
+-------------+----------+------------+-------------------+

`mode()`

Description	Returns the most frequent value in a group.
Usage	`mode(argument)`

The result of this function is affected by the sort order in the table.

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (2),
    (3),
    (NULL);

SELECT
    mode(number) AS result,
FROM numbers;

+--------+
| result |
+--------+
| 2      |
+--------+

`product()`

Description	Multiplies the non-empty values in a group.
Usage	`product(argument)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (NULL);

SELECT
    product(number) AS result,
FROM numbers;

+--------+
| result |
+--------+
| 6      |
+--------+

`quantile_cont()`

Description	Calculates an interpolated positive quantile.
Usage	`quantile_cont(argument, pos)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (4),
    (NULL);

SELECT
    quantile_cont(number, 0.5)               AS result_1,
    quantile_cont(number, [0.25, 0.5, 0.75]) AS result_2
FROM numbers;

+----------+-----------------+
| result_1 | result_2        |
+----------+-----------------+
| 2.5      | {1.75,2.5,3.25} |
+----------+-----------------+

`quantile_disc()`

Description	Calculates a discrete positive quantile.
Usage	`quantile_disc(argument, pos)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (4),
    (NULL);

SELECT
    quantile_disc(number, 0.5)               AS result_1,
    quantile_disc(number, [0.25, 0.5, 0.75]) AS result_2
FROM numbers;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| 2        | {1,2,3}  |
+----------+----------+

`reservoir_quantile()`

Description	Calculates an approximate quantile using reservoir splatting.
Usage	`reservoir_quantile(argument, quantile[, sample_size])`

Computes an approximate quantile using reservoir splatting.

The sample_size parameter can be used to specify the sample size. The default value is 8192.

See example

WITH numbers AS (
    SELECT unnest(generate_series(1000000)) as number
    ORDER BY random()
)
SELECT
    reservoir_quantile(number, 0.5),
    quantile_cont(number, 0.5)
FROM numbers;

+--------------------+---------------+
| reservoir_quantile | quantile_cont |
+--------------------+---------------+
| 500201             | 500000        |
+--------------------+---------------+

`sem()`

Description	Calculates the standard error of the mean.
Usage	`sem(argument)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (4),
    (NULL);

SELECT
    mean(number) AS sample_mean,
    sem(number) AS standard_error
FROM numbers;

+-------------+--------------------+
| sample_mean | standard_error     |
+-------------+--------------------+
| 2.5         | 0.5590169943749475 |
+-------------+--------------------+

`skewness()`

Description	Calculates the skewness coefficient.
Usage	`skewness(argument)`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (40),
    (NULL);

SELECT
    skewness(number)
FROM numbers;

+-------------------+
| skewness          |
+-------------------+
| 1.988947740397821 |
+-------------------+

`stddev_pop()`

Description	Calculates the standard deviation of the population.
Usage	`stddev_pop(argument)`
Formula	`sqrt(var_pop(x))`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (4),
    (NULL);

SELECT
    stddev_pop(number),
    stddev_samp(number)
FROM numbers;

+-------------------+--------------------+
| stddev_pop        | stddev_samp        |
+-------------------+--------------------+
| 1.118033988749895 | 1.2909944487358056 |
+-------------------+--------------------+

`stddev_samp()`

Description	Calculates the sample standard deviation.
Usage	`stddev_samp(argument)`
Formula	`sqrt(var_samp(x))`
Aliases	`stddev()`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (4),
    (NULL);

SELECT
    stddev_pop(number),
    stddev_samp(number)
FROM numbers;

+-------------------+--------------------+
| stddev_pop        | stddev_samp        |
+-------------------+--------------------+
| 1.118033988749895 | 1.2909944487358056 |
+-------------------+--------------------+

`string_agg()`

Description	Concatenates values into a single text string using a specified separator.
Usage	`string_agg(argument, separator)`
Aliases	`group_concat()`, `listagg()`

If the values are not text, they are converted to text.

See example

CREATE TABLE weekdays (name VARCHAR, weekend BOOL);

INSERT INTO weekdays VALUES
('Monday', False),
('Tuesday', False),
('Wednesday', False),
('Thursday', False),
('Friday', False),
('Saturday', True),
('Sunday', True);

SELECT
    string_agg(name, ', ') AS week_days,
    weekend AS are_weekend
FROM weekdays
GROUP BY weekend;

+----------------------------------------------+-------------+
| week_days                                    | are_weekend |
+----------------------------------------------+-------------+
| Monday, Tuesday, Wednesday, Thursday, Friday | false       |
+----------------------------------------------+-------------+
| Saturday, Sunday                             | true        |
+----------------------------------------------+-------------+

`sum()`

Description	Calculates the sum of all non-empty values in a group.
Usage	`sum(argument)`

In case of boolean values, counts the number of True values.

See example

CREATE TABLE numbers(
    number BIGINT,
    boolean BOOL);

INSERT INTO numbers VALUES
    (1,    True),
    (2,    False),
    (3,    False),
    (NULL, NULL);

SELECT
    sum(number) AS sum_number,
    sum(boolean) AS sum_boolean
FROM numbers;

+------------+-------------+
| sum_number | sum_boolean |
+------------+-------------+
| 6          | 1           |
+------------+-------------+

`var_pop()`

Description	Calculates the variance of the population without bias correction.
Usage	`var_pop(argument)`
Formula	`(sum(x^2) - sum(x)^2 / count(x)) / count(x), var_samp(y, x) * (1 - 1 / count(x))`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (4),
    (NULL);

SELECT
    var_pop(number),
    var_samp(number)
FROM numbers;

+---------+--------------------+
| var_pop | var_samp           |
+---------+--------------------+
| 1.25    | 1.6666666666666667 |
+---------+--------------------+

`var_samp()`

Description	Calculates the sample variance, including the Bessel correction for bias.
Usage	`var_samp(argument)`
Formula	`(sum(x^2) - sum(x)^2 / count(x)) / (count(x) - 1), var_pop(y, x) / (1 - 1 / count(x))`
Aliases	`variance()`

See example

CREATE TABLE numbers(number BIGINT);

INSERT INTO numbers VALUES
    (1),
    (2),
    (3),
    (4),
    (NULL);

SELECT
    var_pop(number),
    var_samp(number)
FROM numbers;

+---------+--------------------+
| var_pop | var_samp           |
+---------+--------------------+
| 1.25    | 1.6666666666666667 |
+---------+--------------------+

`weighted_avg()`

Description	Calculates the weighted average of all non-empty values in a group.
Usage	`weighted_avg(argument, weight)`
Aliases	`wavg()`

See example

CREATE TABLE numbers(number BIGINT, weight BIGINT);

INSERT INTO numbers VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (NULL, NULL);

SELECT
    avg(number),
    weighted_avg(number, weight)
FROM numbers;

+-----+--------------------+
| avg | weighted_avg       |
+-----+--------------------+
| 2   | 2.3333333333333335 |
+-----+--------------------+

Aggregate functions

DISTINCT expression in aggregate functions

Example

any_value()

approx_count_distinct()

approx_quantile()

approx_top_k()

arg_max()

arg_max_null()

arg_min()

arg_min_null()

array_agg()

avg()

bit_and()

bit_or()

bit_xor()

bitstring_agg()

bool_and()

bool_or()

count()

count(argument)

corr()

covar_pop()

covar_samp()

count_if()

entropy()

favg()

first()

fsum()

geometric_mean()

histogram()

histogram_exact()

kurtosis()

kurtosis_pop()

last()

mad()

max()

min()

median()

mode()

product()

quantile_cont()

quantile_disc()

reservoir_quantile()

sem()

skewness()

stddev_pop()

stddev_samp()

string_agg()

sum()

var_pop()

var_samp()

weighted_avg()

`DISTINCT` expression in aggregate functions

`any_value()`

`approx_count_distinct()`

`approx_quantile()`

`approx_top_k()`

`arg_max()`

`arg_max_null()`

`arg_min()`

`arg_min_null()`

`array_agg()`

`avg()`

`bit_and()`

`bit_or()`

`bit_xor()`

`bitstring_agg()`

`bool_and()`

`bool_or()`

`count()`

`count(argument)`

`corr()`

`covar_pop()`

`covar_samp()`

`count_if()`

`entropy()`

`favg()`

`first()`

`fsum()`

`geometric_mean()`

`histogram()`

`histogram_exact()`

`kurtosis()`

`kurtosis_pop()`

`last()`

`mad()`

`max()`

`min()`

`median()`

`mode()`

`product()`

`quantile_cont()`

`quantile_disc()`

`reservoir_quantile()`

`sem()`

`skewness()`

`stddev_pop()`

`stddev_samp()`

`string_agg()`

`sum()`

`var_pop()`

`var_samp()`

`weighted_avg()`