聚合函数 – DuckDB - DuckDB 数据库

搜索快捷键 cmd + k | ctrl + k

文档 / SQL / 函数

聚合函数

示例

生成包含 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;

语法

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

聚合函数中的 `DISTINCT` 子句

当提供了 DISTINCT 子句时，计算聚合时仅考虑非重复值。这通常与 count 聚合结合使用以获取不同元素的数量；但它也可以与系统中的任何聚合函数一起使用。有些聚合函数对重复值不敏感（例如 min 和 max），对于这些函数，该子句会被解析但会被忽略。

聚合函数中的 `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 (array_agg)、first (arbitrary) 和 last 外，所有通用聚合函数都会忽略 NULL。要从 list 中排除 NULL，可以使用 FILTER 子句。要从 first 中忽略 NULL，可以使用 any_value 聚合。

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

通用聚合函数

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

函数	描述
`any_value(arg)`	从 `arg` 中返回第一个非空值。此函数受排序影响。
`arg_max(arg, val)`	查找具有最大 `val` 的行，并计算该行的 `arg` 表达式。如果 `arg` 或 `val` 表达式的值为 `NULL`，则忽略该行。此函数受排序影响。
`arg_max(arg, val, n)`	`arg_max` 的通用情况，适用于 `n` 个值：返回一个 `LIST`，包含按 `val` 降序排列的前 `n` 行的 `arg` 表达式。此函数受排序影响。
`arg_max_null(arg, val)`	查找具有最大 `val` 的行，并计算该行的 `arg` 表达式。如果 `val` 表达式计算结果为 `NULL`，则忽略该行。此函数受排序影响。
`arg_min(arg, val)`	查找具有最小 `val` 的行，并计算该行的 `arg` 表达式。如果 `arg` 或 `val` 表达式的值为 `NULL`，则忽略该行。此函数受排序影响。
`arg_min(arg, val, n)`	返回一个 `LIST`，包含按 `val` 升序排列的“最后” `n` 行的 `arg` 表达式。此函数受排序影响。
`arg_min_null(arg, val)`	查找具有最小 `val` 的行，并计算该行的 `arg` 表达式。如果 `val` 表达式计算结果为 `NULL`，则忽略该行。此函数受排序影响。
`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` 不为 `NULL` 的行数。
`countif(arg)`	返回 `arg` 为 `true` 的行数。
`favg(arg)`	使用更精确的浮点求和（Kahan 求和）计算平均值。此函数受排序影响。
`first(arg)`	返回 `arg` 中的第一个值（无论是否为空）。此函数受排序影响。
`fsum(arg)`	使用更精确的浮点求和（Kahan 求和）计算总和。此函数受排序影响。
`geometric_mean(arg)`	计算 `arg` 中所有非空值的几何平均值。此函数受排序影响。
`histogram(arg)`	返回一个表示桶和计数的键值对 `MAP`。
`histogram(arg, boundaries)`	返回一个 `MAP`，其中包含提供的上限 `boundaries` 以及对应数据类型区间（左开右闭分区）中元素的计数。当出现比所有提供的 `boundaries` 都大的元素时，会自动添加一个边界值作为该数据类型的最大值，参见 `is_histogram_other_bin`。边界可以通过例如 `equi_width_bins` 提供。
`histogram_exact(arg, elements)`	返回一个 `MAP`，包含请求的元素及其计数。自动添加一个特定于数据类型的“其他”元素，用于统计未明确包含的元素，参见 `is_histogram_other_bin`。
`histogram_values(source, boundaries)`	返回区间的上限边界及其计数。
`last(arg)`	返回列的最后一个值。此函数受排序影响。
`list(arg)`	返回一个 `LIST`，包含列中的所有值。此函数受排序影响。
`max(arg)`	返回 `arg` 中存在的最大值。此函数不受去重影响。
`max(arg, n)`	返回一个 `LIST`，包含按 `arg` 降序排列的“前” `n` 个值。
`min(arg)`	返回 `arg` 中存在的最小值。此函数不受去重影响。
`min(arg, n)`	返回一个 `LIST`，包含按 `arg` 升序排列的“后” `n` 个值。
`product(arg)`	计算 `arg` 中所有非空值的乘积。此函数受排序影响。
`string_agg(arg)`	使用逗号分隔符 (`,`) 连接列中的字符串值。此函数受排序影响。
`string_agg(arg, sep)`	使用指定分隔符连接列中的字符串值。此函数受排序影响。
`sum(arg)`	计算 `arg` 中所有非空值的总和 / 当 `arg` 为布尔值时统计 `true` 值的数量。此函数的浮点版本受排序影响。
`weighted_avg(arg, weight)`	计算 `arg` 中所有非空值的加权平均值，其中每个值按其对应的 `weight` 缩放。如果 `weight` 为 `NULL`，则跳过对应的 `arg` 值。此函数受排序影响。

`any_value(arg)`

描述	返回 `arg` 中的第一个非 `NULL` 值。此函数受排序影响。
示例	`any_value(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` 个值：返回一个 `LIST`，包含按 `val` 降序排列的前 `n` 行的 `arg` 表达式。此函数受排序影响。
示例	`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)`

`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` 个值：返回一个 `LIST`，包含按 `val` 降序排列的前 `n` 行的 `arg` 表达式。此函数受排序影响。
示例	`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)`

`avg(arg)`

描述	计算 `arg` 中所有非空值的平均值。此函数受排序影响。
示例	`avg(A)`
别名	`mean`

`bit_and(arg)`

描述	返回给定表达式中所有位的按位 `AND` 结果。
示例	`bit_and(A)`

`bit_or(arg)`

描述	返回给定表达式中所有位的按位 `OR` 结果。
示例	`bit_or(A)`

`bit_xor(arg)`

描述	返回给定表达式中所有位的按位 `XOR` 结果。
示例	`bit_xor(A)`

`bitstring_agg(arg)`

描述	返回一个位串，其长度对应于非空（整数）值的范围，并在每个（不同）值的位置设置位。
示例	`bitstring_agg(A)`

`bool_and(arg)`

描述	如果每个输入值均为 `true`，则返回 `true`，否则返回 `false`。
示例	`bool_and(A)`

`bool_or(arg)`

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

`count()`

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

`count(arg)`

描述	返回 `arg` 不为 `NULL` 的行数。
示例	`count(A)`

`countif(arg)`

描述	返回 `arg` 为 `true` 的行数。
示例	`countif(A)`

`favg(arg)`

描述	使用更精确的浮点求和（Kahan 求和）计算平均值。此函数受排序影响。
示例	`favg(A)`

`first(arg)`

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

`fsum(arg)`

描述	使用更精确的浮点求和（Kahan 求和）计算总和。此函数受排序影响。
示例	`fsum(A)`
别名	`sumkahan`, `kahan_sum`

`geometric_mean(arg)`

描述	计算 `arg` 中所有非空值的几何平均值。此函数受排序影响。
示例	`geometric_mean(A)`
别名	`geomean(A)`

`histogram(arg)`

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

`histogram(arg, boundaries)`

描述	返回一个 `MAP`，其中包含提供的上限 `boundaries` 以及对应数据类型区间（左开右闭分区）中元素的计数。当出现比所有提供的 `boundaries` 都大的元素时，会自动添加一个边界值作为该数据类型的最大值，参见 `is_histogram_other_bin`。边界可以通过例如 `equi_width_bins` 提供。
示例	`histogram(A, [0, 1, 10])`

`histogram_exact(arg, elements)`

描述	返回一个 `MAP`，包含请求的元素及其计数。自动添加一个特定于数据类型的“其他”元素，用于统计未明确包含的元素，参见 `is_histogram_other_bin`。
示例	`histogram_exact(A, ['a', 'b', 'c'])`

`histogram_values(source, col_name, technique, bin_count)`

描述	返回区间的上限边界及其计数。
示例	`histogram_values(integers, i, bin_count := 2)`

`last(arg)`

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

`list(arg)`

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

`max(arg)`

描述	返回 `arg` 中存在的最大值。此函数不受去重影响。
示例	`max(A)`

`max(arg, n)`

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

`min(arg)`

描述	返回 `arg` 中存在的最小值。此函数不受去重影响。
示例	`min(A)`

`min(arg, n)`

描述	返回一个 `LIST`，包含按 `arg` 升序排列的“后” `n` 个值。
示例	`min(A, 2)`

`product(arg)`

描述	计算 `arg` 中所有非空值的乘积。此函数受排序影响。
示例	`product(A)`

`string_agg(arg)`

描述	使用逗号分隔符 (`,`) 连接列中的字符串值。此函数受排序影响。
示例	`string_agg(S, ',')`
别名	`group_concat(arg)`, `listagg(arg)`

`string_agg(arg, sep)`

描述	使用指定分隔符连接列中的字符串值。此函数受排序影响。
示例	`string_agg(S, ',')`
别名	`group_concat(arg, sep)`, `listagg(arg, sep)`

`sum(arg)`

描述	计算 `arg` 中所有非空值的总和 / 当 `arg` 为布尔值时统计 `true` 值的数量。此函数的浮点版本受排序影响。
示例	`sum(A)`

`weighted_avg(arg, weight)`

描述	计算 `arg` 中所有非空值的加权平均值，其中每个值按其对应的 `weight` 缩放。如果 `weight` 为 `NULL`，则跳过该值。此函数受排序影响。
示例	`weighted_avg(A, W)`
别名	`wavg(arg, weight)`

近似聚合函数

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

函数	描述	示例
`approx_count_distinct(x)`	使用 HyperLogLog 计算不同元素的近似计数。	`approx_count_distinct(A)`
`approx_quantile(x, pos)`	使用 T-Digest 计算近似分位数。	`approx_quantile(A, 0.5)`
`approx_top_k(arg, k)`	使用 Filtered Space-Saving 算法计算 `arg` 中 `k` 个最频繁值的 `LIST`。
`reservoir_quantile(x, quantile, sample_size = 8192)`	使用蓄水池采样计算近似分位数，样本大小可选，默认为 8192。	`reservoir_quantile(A, 0.5, 1024)`

统计聚合函数

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

函数	描述
`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)`	`x` 的插值 `pos` 分位数，适用于 `-1 <= pos <= 1`。返回 `x` 中索引为 `pos * (n_nonnull_values - 1)`（从零开始，按指定顺序）的值，若索引非整数，则在相邻值间进行插值。若 `pos` 为 `FLOAT` 的 `LIST`，则结果为相应的插值分位数列表。这是 Hyndman & Fan (1996) 中的 Type 7 类型。
`quantile_disc(x, pos)`	`x` 的离散 `pos` 分位数，适用于 `0 <= pos <= 1`。返回 `x` 中索引为 `greatest(ceil(pos * n_nonnull_values) - 1, 0)`（从零开始，按指定顺序）的值。这是 Hyndman & Fan (1996) 中的 Type 1 类型。
`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 之间的皮尔逊相关系数平方。在线性回归中也称为确定系数。
`regr_slope(y, x)`	线性回归线的斜率（x 为自变量，y 为因变量）。
`regr_sxx(y, x)`	非 `NULL` 对的自变量样本方差（含贝塞尔校正，x 为自变量）。
`regr_sxy(y, x)`	样本协方差，包含贝塞尔偏差校正。
`regr_syy(y, x)`	非 `NULL` 对的因变量样本方差（含贝塞尔校正，y 为因变量）。
`skewness(x)`	偏度。
`sem(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))`

`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))`

`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 为底的熵。
公式	-

`kurtosis_pop(x)`

描述	不带偏差校正的峰度（Fisher 定义）。
公式	-

`kurtosis(x)`

描述	根据样本大小进行偏差校正的峰度（Fisher 定义）。
公式	-

`mad(x)`

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

`median(x)`

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

`mode(x)`

描述	最频繁出现的值。此函数受排序影响。
公式	-

`quantile_cont(x, pos)`

描述	`x` 的插值 `pos` 分位数，适用于 `0 <= pos <= 1`。如果 `pos` 是 `FLOAT` 的 `LIST`，结果为相应的插值分位数列表。
公式	-

`quantile_disc(x, pos)`

描述	`x` 的离散 `pos` 分位数，适用于 `0 <= pos <= 1`。返回 `x` 中索引为 `greatest(ceil(pos * n_nonnull_values) - 1, 0)`（从零开始，按指定顺序）的值。这是 Hyndman & Fan (1996) 中的 Type 1 类型。
公式	-
别名	`分位数`

`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_avgy(y, x) - regr_slope(y, x) * regr_avgx(y, x)`

`regr_r2(y, x)`

描述	y 与 x 之间的皮尔逊相关系数平方。在线性回归中也称为确定系数。
公式	-

`regr_slope(y, x)`

描述	返回线性回归线的斜率（x 为自变量，y 为因变量）。
公式	`regr_sxy(y, x) / regr_sxx(y, x)`
别名	-

`regr_sxx(y, x)`

描述	非 `NULL` 对的自变量样本方差（含贝塞尔校正，x 为自变量）。
公式	-

`regr_sxy(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))`
别名	`covar_samp(y, x)`

`regr_syy(y, x)`

描述	非 `NULL` 对的因变量样本方差，含贝塞尔校正。
公式	-

`sem(x)`

描述	平均值标准误。
公式	-

`skewness(x)`

描述	偏度。
公式	-

`stddev_pop(x)`

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

`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))`

`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()`

关于此页面

行为准则商标使用指南

关于此页面

本文中