跳到主要内容

聚合函数

示例​

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

SELECT sum(amount)
FROM sales;

按每个唯一 region 生成一行，包含各分组 amount 的总和：

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

仅返回 amount 总和大于 100 的 region：

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

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

SELECT count(DISTINCT region)
FROM sales;

返回两个值：amount 的总和，以及通过 FILTER 子句 排除 region 为 north 后的 amount 总和：

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

按 amount 列顺序返回所有 region 的列表：

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

使用 first() 聚合函数返回第一笔销售的 amount：

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;
countdown
3, 2, 1

处理 NULL 值​

除 list（array_agg）、first（arbitrary）和 last 外，所有通用聚合函数都会忽略 NULL。 若要在 list 中排除 NULL，可使用 FILTER 子句。 若要在 first 中忽略 NULL，可使用 any_value 聚合函数。

除 count 外，所有通用聚合函数在空分组上都返回 NULL。 特别地，在这种情况下 list 不会 返回空列表，sum 不会 返回 0，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)返回给定表达式中所有位的按位 AND 结果。
bit_or(arg)返回给定表达式中所有位的按位 OR 结果。
bit_xor(arg)返回给定表达式中所有位的按位 XOR 结果。
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 Sum）计算平均值。 此函数受排序影响。
first(arg)返回 arg 中的第一个值（可为 null 或非 null）。 此函数受排序影响。
fsum(arg)使用更精确的浮点求和（Kahan Sum）计算总和。 此函数受排序影响。
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 行的 arg 值。
min(arg)返回 arg 中的最小值。 此函数不受去重影响。
min(arg, n)返回一个 LIST，包含按 arg 升序排列后“底部” n 行的 arg 值。
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 Sum）计算平均值。 此函数受排序影响。 | | 示例 | favg(A) |

first(arg)​

| 说明 | 返回 arg 中的第一个值（可为 null 或非 null）。 此函数受排序影响。 | | 示例 | first(A) | | 别名 | arbitrary(A) |

fsum(arg)​

| 说明 | 使用更精确的浮点求和（Kahan Sum）计算总和。 此函数受排序影响。 | | 示例 | 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 行的 arg 值。 | | 示例 | max(A, 2) |

min(arg)​

| 说明 | 返回 arg 中的最小值。 此函数不受去重影响。 | | 示例 | min(A) |

min(arg, n)​

| 说明 | 返回一个 LIST，包含按 arg 升序排列后“底部” n 行的 arg 值。 | | 示例 | 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)使用蓄水池抽样计算近似分位数，sample_size 为可选参数，默认值为 8192。reservoir_quantile(A, 0.5, 1024)

统计聚合函数​

下表展示可用的统计聚合函数。 这些函数都会忽略 NULL 值（单输入列 x 的情况），或忽略任一输入为 NULL 的输入对（双输入列 y 和 x 的情况）。

函数说明
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 在 -1 <= pos <= 1 范围内的插值 pos 分位数。返回 x 的第 pos * (n_nonnull_values - 1) 个值（从 0 开始计数，按指定顺序）；若索引不是整数，则返回相邻值之间的插值。-1 到 0 之间的 pos 等价于从 1 反向计数，更精确地说，quantile_cont(x, -y) = quantile_cont(x, 1 - y)。直观上，将 x 的值视为区间 [0, 1] 上等距的点，并返回位置 pos 处（可能插值后）的值。这对应 Hyndman & Fan (1996) 的 Type 7。若 pos 是 FLOAT 的 LIST，结果为对应插值分位数的 LIST。
quantile_disc(x, pos)x 在 0 <= pos <= 1 范围内的离散 pos 分位数。返回 x 的第 greatest(ceil(pos * n_nonnull_values) - 1, 0) 个值（从 0 开始计数，按指定顺序）。直观上，为 x 的每个值分配区间 [0, 1] 上等长的子区间（除首个区间外均为左开右闭），并选取包含 pos 的子区间对应的值。这对应 Hyndman & Fan (1996) 的 Type 1。若 pos 是 FLOAT 的 LIST，结果为对应离散分位数的 LIST。
regr_avgx(y, x)在非 NULL 成对值中，自变量 x 的平均值（其中 x 为自变量，y 为因变量）。
regr_avgy(y, x)在非 NULL 成对值中，因变量 y 的平均值（其中 x 为自变量，y 为因变量）。
regr_count(y, x)非 NULL 成对值的数量。
regr_intercept(y, x)一元线性回归直线的截距（其中 x 为自变量，y 为因变量）。
regr_r2(y, x)y 与 x 之间 Pearson 相关系数的平方；亦即线性回归中的决定系数（其中 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)偏度。
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(x*y) - 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(x*y) - 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 在 0 <= pos <= 1 范围内的插值 pos 分位数。返回 x 的第 pos * (n_nonnull_values - 1) 个值（从 0 开始计数，按指定顺序）；若索引不是整数，则返回相邻值之间的插值。直观上，将 x 的值视为区间 [0, 1] 上等距的点，并返回位置 pos 处（可能插值后）的值。这对应 Hyndman & Fan (1996) 的 Type 7。若 pos 是 FLOAT 的 LIST，结果为对应插值分位数的 LIST。 | | 公式 | - |

quantile_disc(x, pos)​

| 说明 | x 在 0 <= pos <= 1 范围内的离散 pos 分位数。返回 x 的第 greatest(ceil(pos * n_nonnull_values) - 1, 0) 个值（从 0 开始计数，按指定顺序）。直观上，为 x 的每个值分配区间 [0, 1] 上等长的子区间（除首个区间外均为左开右闭），并选取包含 pos 的子区间对应的值。这对应 Hyndman & Fan (1996) 的 Type 1。若 pos 是 FLOAT 的 LIST，结果为对应离散分位数的 LIST。 | | 公式 | - | | 别名 | quantile |

regr_avgx(y, x)​

| 说明 | 在非 NULL 成对值中，自变量 x 的平均值（其中 x 为自变量，y 为因变量）。 | | 公式 | - |

regr_avgy(y, x)​

| 说明 | 在非 NULL 成对值中，因变量 y 的平均值（其中 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 之间 Pearson 相关系数的平方；亦即线性回归中的决定系数（其中 x 为自变量，y 为因变量）。 | | 公式 | - |

regr_slope(y, x)​

| 说明 | 返回线性回归直线的斜率（其中 x 为自变量，y 为因变量）。 | | 公式 | regr_sxy(y, x) / regr_sxx(y, x) | | 别名 | - |

regr_sxx(y, x)​

| 说明 | 在非 NULL 成对值中，自变量的样本方差（包含贝塞尔偏差校正，其中 x 为自变量，y 为因变量）。 | | 公式 | - |

regr_sxy(y, x)​

| 说明 | 样本协方差，包含贝塞尔偏差校正。 | | 公式 | (sum(x*y) - 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 成对值中，因变量的样本方差（包含贝塞尔偏差校正，其中 x 为自变量，y 为因变量）。 | | 公式 | - |

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