Aggregate Functions

Search Shortcut cmd + k | ctrl + k

Documentation / SQL / Functions

Aggregate Functions

Examples

生成包含amount列总和的一行：

SELECT sum(amount)
FROM sales;

为每个唯一区域生成一行，包含每个组的amount的总和：

SELECT region, sum(amount)
FROM sales
GROUP BY region;

仅返回amount总和大于100的区域：

SELECT region
FROM sales
GROUP BY region
HAVING sum(amount) > 100;

返回region列中唯一值的数量：

SELECT count(DISTINCT region)
FROM sales;

返回两个值，amount 的总和以及使用 FILTER 子句减去地区为 north 的列的 amount 的总和：

SELECT sum(amount), sum(amount) FILTER (region != 'north')
FROM sales;

返回按amount列排序的所有区域的列表：

SELECT list(region ORDER BY amount DESC)
FROM sales;

使用first()聚合函数返回第一次销售的金额：

SELECT first(amount ORDER BY date ASC)
FROM sales;

Syntax

聚合函数是将多行组合成单个值的函数。聚合函数与标量函数和窗口函数不同，因为它们会改变结果的行数。因此，聚合函数只能在SQL查询的SELECT和HAVING子句中使用。

`DISTINCT` 聚合函数中的子句

当提供DISTINCT子句时，在聚合计算中只考虑不同的值。这通常与count聚合一起使用，以获取不同元素的数量；但它可以与系统中的任何聚合函数一起使用。

`ORDER BY` 聚合函数中的子句

可以在函数调用的最后一个参数之后提供ORDER BY子句。请注意在子句之前缺少逗号分隔符。

SELECT ⟨aggregate_function⟩(⟨arg⟩, ⟨sep⟩ ORDER BY ⟨ordering_criteria⟩);

此子句确保在应用函数之前对要聚合的值进行排序。大多数聚合函数对顺序不敏感，对于它们来说，此子句被解析并丢弃。然而，有一些对顺序敏感的聚合函数在没有排序的情况下可能会有非确定性的结果，例如 first, last, list 和 string_agg / group_concat / listagg。通过对参数进行排序，可以使这些函数具有确定性。

例如：

CREATE TABLE tbl AS
    SELECT s FROM range(1, 4) r(s);

SELECT string_agg(s, ', ' ORDER BY s DESC) AS countdown
FROM tbl;

倒计时
3, 2, 1

处理 `NULL` 值

除了list和first（以及它们的别名array_agg和arbitrary）之外，所有通用的聚合函数都会忽略NULL值。要从list中排除NULL，可以使用FILTER子句。要从first中忽略NULL，可以使用any_value聚合。

除了count之外的所有通用聚合函数在空组上返回NULL。特别是，list在这种情况下不会返回空列表，sum不会返回零，string_agg不会返回空字符串。

通用聚合函数

下表显示了可用的通用聚合函数。

Function	Description
`any_value(arg)`	返回`arg`中的第一个非空值。此函数受排序影响。
`arbitrary(arg)`	返回 `arg` 中的第一个值（空值或非空值）。此函数受排序影响。
`arg_max(arg, val)`	找到具有最大`val`的行，并在该行计算`arg`表达式。忽略`arg`或`val`表达式值为`NULL`的行。此函数受排序影响。
`arg_max(arg, val, n)`	`arg_max` 的通用情况，适用于 `n` 个值：返回一个包含按 `val` 降序排列的前 `n` 行的 `arg` 表达式的 `LIST`。此函数受排序影响。
`arg_max_null(arg, val)`	找到具有最大`val`的行，并在该行计算`arg`表达式。忽略`val`表达式评估为`NULL`的行。此函数受排序影响。
`arg_min(arg, val)`	找到具有最小`val`的行，并在该行计算`arg`表达式。忽略`arg`或`val`表达式值为`NULL`的行。此函数受排序影响。
`arg_min(arg, val, n)`	返回一个包含按`val`升序排列的“底部”`n`行的`arg`表达式的`LIST`。此函数受排序影响。
`arg_min_null(arg, val)`	找到具有最小`val`的行，并在该行计算`arg`表达式。忽略`val`表达式评估为`NULL`的行。此函数受排序影响。
`array_agg(arg)`	返回一个包含列中所有值的`LIST`。此函数受排序影响。
`avg(arg)`	计算`arg`中所有非空值的平均值。
`bit_and(arg)`	返回给定表达式中所有位的按位与。
`bit_or(arg)`	返回给定表达式中所有位的按位或运算结果。
`bit_xor(arg)`	返回给定表达式中所有位的按位异或。
`bitstring_agg(arg)`	返回一个位字符串，其长度对应于非空（整数）值的范围，每个（不同）值的位置设置位。
`bool_and(arg)`	如果每个输入值都是 `true`，则返回 `true`，否则返回 `false`。
`bool_or(arg)`	如果任何输入值为 `true`，则返回 `true`，否则返回 `false`。
`count()`	返回组中的行数。
`count(arg)`	返回 `arg` 中非空值的数量。
`favg(arg)`	使用更精确的浮点求和（Kahan Sum）计算平均值。
`first(arg)`	返回 `arg` 中的第一个值（空值或非空值）。此函数受排序影响。
`fsum(arg)`	使用更精确的浮点求和（Kahan求和）计算总和。
`geomean(arg)`	计算`arg`中所有非空值的几何平均值。
`histogram(arg)`	返回一个表示桶和计数的键值对的`MAP`。
`histogram(arg, boundaries)`	返回一个表示提供的上界`boundaries`和数据类型对应的左开右闭分区中元素计数的键值对`MAP`。当出现大于所有提供的`boundaries`的元素时，会自动添加数据类型最大值的边界，参见`is_histogram_other_bin`。可以通过例如`equi_width_bins`提供边界。
`histogram_exact(arg, elements)`	返回一个表示请求元素及其计数的`MAP`键值对。当其他元素出现时，会自动添加一个特定于数据类型的通用元素来计数，请参阅`is_histogram_other_bin`。
`last(arg)`	返回列的最后一个值。此函数受排序影响。
`list(arg)`	返回一个包含列所有值的`LIST`。此函数受排序影响。
`max(arg)`	返回 `arg` 中的最大值。
`max(arg, n)`	返回一个包含按`arg`降序排列的前`n`行的`arg`值的`LIST`。
`max_by(arg, val)`	找到具有最大`val`的行。在该行计算`arg`表达式。此函数受排序影响。
`max_by(arg, val, n)`	返回一个包含按`val`降序排列的前`n`行的`arg`表达式的`LIST`。
`min(arg)`	返回 `arg` 中存在的最小值。
`min(arg, n)`	返回一个包含按`arg`升序排列的“底部”`n`行的`arg`值的`LIST`。
`min_by(arg, val)`	找到具有最小`val`的行。在该行计算`arg`表达式。此函数受排序影响。
`min_by(arg, val, n)`	返回一个包含按`val`升序排列的“底部”`n`行的`arg`表达式的`LIST`。
`product(arg)`	计算`arg`中所有非空值的乘积。
`string_agg(arg, sep)`	将列字符串值与分隔符连接起来。此函数受排序影响。
`sum(arg)`	计算 `arg` 中所有非空值的总和。

`any_value(arg)`

描述	返回`arg`中的第一个非`NULL`值。此函数受排序影响。
示例	`any_value(A)`
别名	-

`arbitrary(arg)`

描述	返回`arg`中的第一个值（`NULL`或非`NULL`）。此函数受排序影响。
示例	`arbitrary(A)`
别名	`first(A)`

`arg_max(arg, val)`

描述	找到具有最大`val`的行，并在该行计算`arg`表达式。忽略`arg`或`val`表达式值为`NULL`的行。此函数受排序影响。
示例	`arg_max(A, B)`
别名	`argMax(arg, val)`, `max_by(arg, val)`

`arg_max(arg, val, n)`

描述	`arg_max` 的广义情况，适用于 `n` 个值：返回一个包含按 `val` 降序排列的前 `n` 行的 `arg` 表达式的 `LIST`。此函数受排序影响。
示例	`arg_max(A, B, 2)`
别名	`argMax(arg, val, n)`, `max_by(arg, val, n)`

`arg_max_null(arg, val)`

描述	找到具有最大`val`的行，并在该行计算`arg`表达式。忽略`val`表达式评估为`NULL`的行。此函数受排序影响。
示例	`arg_max_null(A, B)`
Alias(es)	-

`arg_min(arg, val)`

描述	找到具有最小`val`的行，并在该行计算`arg`表达式。忽略`arg`或`val`表达式值为`NULL`的行。此函数受排序影响。
示例	`arg_min(A, B)`
别名	`argmin(arg, val)`, `min_by(arg, val)`

`arg_min(arg, val, n)`

描述	`arg_min` 的广义情况，适用于 `n` 个值：返回一个包含按 `val` 降序排列的前 `n` 行的 `arg` 表达式的 `LIST`。此函数受排序影响。
示例	`arg_min(A, B, 2)`
别名	`argmin(arg, val, n)`, `min_by(arg, val, n)`

`arg_min_null(arg, val)`

描述	找到具有最小`val`的行，并在该行计算`arg`表达式。忽略`val`表达式评估为`NULL`的行。此函数受排序影响。
示例	`arg_min_null(A, B)`
Alias(es)	-

`array_agg(arg)`

描述	返回一个包含列中所有值的`LIST`。此函数受排序影响。
示例	`array_agg(A)`
别名	`list`

`avg(arg)`

描述	计算`arg`中所有非空值的平均值。
示例	`avg(A)`
别名	`mean`

`bit_and(arg)`

描述	返回给定表达式中所有位的按位`AND`。
Example	`bit_and(A)`
Alias(es)	-

`bit_or(arg)`

描述	返回给定表达式中所有位的按位`OR`。
Example	`bit_or(A)`
Alias(es)	-

`bit_xor(arg)`

描述	返回给定表达式中所有位的按位`XOR`。
Example	`bit_xor(A)`
Alias(es)	-

`bitstring_agg(arg)`

描述	返回一个长度对应于非空（整数）值范围的位串，每个（不同）值的位置都设置了位。
Example	`bitstring_agg(A)`
Alias(es)	-

`bool_and(arg)`

描述	如果每个输入值都是 `true`，则返回 `true`，否则返回 `false`。
示例	`bool_and(A)`
Alias(es)	-

`bool_or(arg)`

描述	如果任何输入值为`true`，则返回`true`，否则返回`false`。
示例	`bool_or(A)`
Alias(es)	-

`count()`

描述	返回组中的行数。
示例	`count()`
别名	`count(*)`

`count(arg)`

描述	返回`arg`中非空值的数量。
示例	`count(A)`
Alias(es)	-

`favg(arg)`

描述	使用更精确的浮点求和（Kahan Sum）计算平均值。
示例	`favg(A)`
Alias(es)	-

`first(arg)`

描述	返回`arg`中的第一个值（空值或非空值）。此函数受排序影响。
示例	`first(A)`
别名	`arbitrary(A)`

`fsum(arg)`

描述	使用更精确的浮点求和（Kahan Sum）计算总和。
示例	`fsum(A)`
别名	`sumKahan`, `kahan_sum`

`geomean(arg)`

描述	计算`arg`中所有非空值的几何平均值。
示例	`geomean(A)`
别名	`geometric_mean(A)`

`histogram(arg)`

描述	返回一个表示桶和计数的键值对的`MAP`。
示例	`histogram(A)`
Alias(es)	-

`histogram(arg, boundaries)`

描述	返回一个表示提供的上界`boundaries`和数据类型对应左开右闭分区中元素计数的`MAP`键值对。当出现大于所有提供的`boundaries`的元素时，会自动添加数据类型最大值的边界，参见`is_histogram_other_bin`。可以通过例如`equi_width_bins`提供边界。
示例	`histogram(A, [0, 1, 10])`
Alias(es)	-

`histogram_exact(arg, elements)`

描述	返回一个表示请求元素及其计数的键值对的`MAP`。当其他元素出现时，会自动添加一个特定于数据类型的通用元素来计数，请参阅`is_histogram_other_bin`。
示例	`histogram_exact(A, [0, 1, 10])`
Alias(es)	-

`last(arg)`

描述	返回列的最后一个值。此函数受排序影响。
示例	`last(A)`
Alias(es)	-

`list(arg)`

Description	Returns a `LIST` containing all the values of a column. This function is affected by ordering.
示例	`list(A)`
别名	`array_agg`

`max(arg)`

描述	返回`arg`中的最大值。
示例	`max(A)`
Alias(es)	-

`max(arg, n)`

描述	返回一个包含按`arg`降序排列的前`n`行的`arg`值的`LIST`。
示例	`max(A, 2)`
Alias(es)	-

`max_by(arg, val)`

描述	找到具有最大`val`的行。在该行计算`arg`表达式。此函数受排序影响。
示例	`max_by(A, B)`
别名	`argMax(arg, val)`, `arg_max(arg, val)`

`max_by(arg, val, n)`

描述	返回一个包含按`val`降序排列的前`n`行的`arg`表达式的`LIST`。
示例	`max_by_n(A, B, 2)`
别名	`argMax(arg, val, n)`, `arg_max(arg, val, n)`

`min(arg)`

描述	返回`arg`中存在的最小值。
示例	`min(A)`
Alias(es)	-

`min(arg, n)`

描述	返回一个包含按`arg`升序排列的“底部”`n`行的`arg`值的`LIST`。
示例	`min(A, 2)`
Alias(es)	-

`min_by(arg, val)`

描述	找到具有最小`val`的行。在该行计算`arg`表达式。此函数受排序影响。
示例	`min_by(A, B)`
别名	`argMin(arg, val)`, `arg_min(arg, val)`

`min_by(arg, val, n)`

描述	返回一个包含按`val`升序排列的“底部”`n`行的`arg`表达式的`LIST`。
示例	`min_by(A, B, 2)`
别名	`argMin(arg, val, n)`, `arg_min(arg, val, n)`

`product(arg)`

描述	计算`arg`中所有非空值的乘积。
示例	`product(A)`
Alias(es)	-

`string_agg(arg, sep)`

描述	将列字符串值与分隔符连接起来。此函数受排序影响。
示例	`string_agg(S, ',')`
别名	`group_concat(arg, sep)`, `listagg(arg, sep)`

`sum(arg)`

描述	计算`arg`中所有非空值的总和。
示例	`sum(A)`
Alias(es)	-

近似聚合

下表显示了可用的近似聚合函数。

Function	Description	Example
`approx_count_distinct(x)`	使用HyperLogLog给出不同元素的近似计数。	`approx_count_distinct(A)`
`approx_quantile(x, pos)`	使用T-Digest给出近似分位数。	`approx_quantile(A, 0.5)`
`reservoir_quantile(x, quantile, sample_size = 8192)`	使用水库采样给出近似分位数，样本大小是可选的，默认大小为8192。	`reservoir_quantile(A, 0.5, 1024)`

统计聚合

下表显示了可用的统计聚合函数。它们都忽略NULL值（在单个输入列x的情况下），或者在两个输入列y和x的情况下，忽略其中任一输入为NULL的对。

Function	Description
`corr(y, x)`	相关系数。
`covar_pop(y, x)`	总体协方差，不包括偏差校正。
`covar_samp(y, x)`	样本协方差，包括贝塞尔的偏差校正。
`entropy(x)`	对数2熵。
`kurtosis_pop(x)`	无偏校正的超额峰度（Fisher的定义）。
`kurtosis(x)`	根据样本大小进行偏差校正的超额峰度（Fisher的定义）。
`mad(x)`	中位数绝对偏差。时间类型返回一个正的`INTERVAL`。
`median(x)`	集合的中间值。对于偶数个值，定量值取平均值，序数值返回较低的值。
`mode(x)`	最频繁的值。此函数受排序影响。
`quantile_cont(x, pos)`	插值的 `pos`-分位数，其中 `0 <= pos <= 1`。返回 `x` 的第 `pos * (n_nonnull_values - 1)` 个（从零开始，按指定顺序）值，如果索引不是整数，则返回相邻值之间的插值。直观地说，将 `x` 的值排列为一条线上等距的点，从 0 开始到 1 结束，并返回在 `pos` 处的（插值）值。如果 `pos` 是 `FLOAT` 的 `LIST`，则结果是相应插值分位数的 `LIST`。
`quantile_disc(x, pos)`	离散的`pos`-分位数，其中`0 <= pos <= 1`。返回`x`的第`greatest(ceil(pos * n_nonnull_values) - 1, 0)`个（从零开始，按指定顺序）值。直观上，将`x`的每个值分配给区间`[0, 1]`的一个等大小的子区间（除了初始区间外，左开右闭），并选择包含`pos`的子区间的值。如果`pos`是`FLOAT`的`LIST`，则结果是相应离散分位数的`LIST`。
`regr_avgx(y, x)`	非`NULL`对中自变量的平均值，其中x是自变量，y是因变量。
`regr_avgy(y, x)`	非`NULL`对的因变量的平均值，其中x是自变量，y是因变量。
`regr_count(y, x)`	非`NULL`对的数量。
`regr_intercept(y, x)`	单变量线性回归线的截距，其中x是自变量，y是因变量。
`regr_r2(y, x)`	y和x之间的皮尔逊相关系数的平方。也称为线性回归中的决定系数，其中x是自变量，y是因变量。
`regr_slope(y, x)`	线性回归线的斜率，其中x是自变量，y是因变量。
`regr_sxx(y, x)`	自变量在非`NULL`对中的总体方差，包括贝塞尔偏差校正，其中x是自变量，y是因变量。
`regr_sxy(y, x)`	总体协方差，包括贝塞尔的偏差校正。
`regr_syy(y, x)`	总体方差，包括贝塞尔偏差校正，适用于非`NULL`对的因变量，其中x是自变量，y是因变量。
`skewness(x)`	偏度。
`stddev_pop(x)`	总体标准差。
`stddev_samp(x)`	样本标准差。
`var_pop(x)`	总体方差，不包括偏差校正。
`var_samp(x)`	样本方差，包括贝塞尔的偏差校正。

`corr(y, x)`

描述	相关系数。
公式	`covar_pop(y, x) / (stddev_pop(x) * stddev_pop(y))`
Alias(es)	-

`covar_pop(y, x)`

描述	总体协方差，不包括偏差校正。
公式	`(sum(xy) - sum(x) sum(y) / regr_count(y, x)) / regr_count(y, x)`, `covar_samp(y, x) * (1 - 1 / regr_count(y, x))`
Alias(es)	-

`covar_samp(y, x)`

描述	样本协方差，包括贝塞尔的偏差校正。
公式	`(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))`
别名	`regr_sxy(y, x)`

`entropy(x)`

描述	对数-2熵。
公式	-
Alias(es)	-

`kurtosis_pop(x)`

描述	无偏差校正的超额峰度（Fisher的定义）。
Formula	-
Alias(es)	-

`kurtosis(x)`

描述	根据样本大小进行偏差校正的超额峰度（Fisher的定义）。
Formula	-
Alias(es)	-

`mad(x)`

描述	中位数绝对偏差。时间类型返回一个正的`INTERVAL`。
公式	`median(abs(x - median(x)))`
Alias(es)	-

`median(x)`

描述	集合的中间值。对于偶数个值，定量值取平均值，序数值返回较小的值。
公式	`quantile_cont(x, 0.5)`
Alias(es)	-

`mode(x)`

描述	最频繁的值。此函数受排序影响。
Formula	-
Alias(es)	-

`quantile_cont(x, pos)`

描述	对于`0 <= pos <= 1`，`x`的插值`pos`分位数。返回`x`的第`pos * (n_nonnull_values - 1)`个（从零开始索引，按指定顺序）值，如果索引不是整数，则返回相邻值之间的插值。直观地说，将`x`的值排列为一条线上等距的点，从0开始到1结束，并返回`pos`处的（插值）值。如果`pos`是`FLOAT`的`LIST`，则结果是相应插值分位数的`LIST`。
Formula	-
Alias(es)	-

`quantile_disc(x, pos)`

描述	对于`0 <= pos <= 1`，`x`的离散`pos`分位数。返回`x`的第`greatest(ceil(pos * n_nonnull_values) - 1, 0)`个（从零开始，按指定顺序）值。直观上，为`x`的每个值分配一个等大小的子区间（除了初始区间外，左开右闭）在区间`[0, 1]`中，并选择包含`pos`的子区间的值。如果`pos`是`FLOAT`的`LIST`，则结果是相应离散分位数的`LIST`。
Formula	-
别名	`quantile`

`regr_avgx(y, x)`

描述	非`NULL`对的自变量的平均值，其中x是自变量，y是因变量。
Formula	-
Alias(es)	-

`regr_avgy(y, x)`

描述	非`NULL`对的因变量的平均值，其中x是自变量，y是因变量。
Formula	-
Alias(es)	-

`regr_count(y, x)`

描述	非`NULL`对的数量。
Formula	-
Alias(es)	-

`regr_intercept(y, x)`

描述	单变量线性回归线的截距，其中x是自变量，y是因变量。
公式	`regr_avgy(y, x) - regr_slope(y, x) * regr_avgx(y, x)`
Alias(es)	-

`regr_r2(y, x)`

描述	y 和 x 之间的平方皮尔逊相关系数。同样：线性回归中的决定系数，其中 x 是自变量，y 是因变量。
Formula	-
Alias(es)	-

`regr_slope(y, x)`

描述	返回线性回归线的斜率，其中x是自变量，y是因变量。
公式	`regr_sxy(y, x) / regr_sxx(y, x)`
Alias(es)	-

`regr_sxx(y, x)`

描述	非`NULL`对的独立变量的总体方差，包括贝塞尔的偏差校正，其中x是独立变量，y是因变量。
Formula	-
Alias(es)	-

`regr_sxy(y, x)`

描述	总体协方差，包括贝塞尔的偏差校正。
Formula	-
Alias(es)	-

`regr_syy(y, x)`

描述	非`NULL`对的因变量的总体方差，包括贝塞尔的偏差校正，其中x是自变量，y是因变量。
Formula	-
Alias(es)	-

`skewness(x)`

描述	偏度。
Formula	-
Alias(es)	-

`stddev_pop(x)`

描述	总体标准差。
公式	`sqrt(var_pop(x))`
Alias(es)	-

`stddev_samp(x)`

描述	样本标准差。
公式	`sqrt(var_samp(x))`
别名	`stddev(x)`

`var_pop(x)`

描述	总体方差，不包括偏差校正。
公式	`(sum(x^2) - sum(x)^2 / count(x)) / count(x)`, `var_samp(y, x) * (1 - 1 / count(x))`
Alias(es)	-

`var_samp(x)`

描述	样本方差，包括贝塞尔的偏差校正。
公式	`(sum(x^2) - sum(x)^2 / count(x)) / (count(x) - 1)`, `var_pop(y, x) / (1 - 1 / count(x))`
别名	`variance(arg, val)`

有序集合聚合函数

下表显示了可用的“有序集”聚合函数。这些函数使用WITHIN GROUP (ORDER BY sort_expression)语法指定，它们被转换为一个等效的聚合函数，该函数将排序表达式作为第一个参数。

函数	等价
`mode() WITHIN GROUP (ORDER BY column [(ASC\|DESC)])`	`mode(column ORDER BY column [(ASC\|DESC)])`
`percentile_cont(fraction) WITHIN GROUP (ORDER BY column [(ASC\|DESC)])`	`quantile_cont(column, fraction ORDER BY column [(ASC\|DESC)])`
`percentile_cont(fractions) WITHIN GROUP (ORDER BY column [(ASC\|DESC)])`	`quantile_cont(column, fractions ORDER BY column [(ASC\|DESC)])`
`percentile_disc(fraction) WITHIN GROUP (ORDER BY column [(ASC\|DESC)])`	`quantile_disc(column, fraction ORDER BY column [(ASC\|DESC)])`
`percentile_disc(fractions) WITHIN GROUP (ORDER BY column [(ASC\|DESC)])`	`quantile_disc(column, fractions ORDER BY column [(ASC\|DESC)])`

杂项聚合函数

函数	描述	别名
`grouping()`	对于带有 `GROUP BY` 和 `ROLLUP` 或 `GROUPING SETS` 的查询：返回一个整数，标识用于创建当前超级聚合行的参数表达式。	`grouping_id()`

关于此页面

本文内容