⌘+k ctrl+k
1.1.3 (stable)
Search Shortcut cmd + k | ctrl + k
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

aggregate-name ( DISTINCT expr , ) FILTER ( WHERE filter_expr )

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

DISTINCT 聚合函数中的子句

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

ORDER BY 聚合函数中的子句

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

SELECT aggregate_function(arg, sep ORDER BY ordering_criteria);

此子句确保在应用函数之前对要聚合的值进行排序。 大多数聚合函数对顺序不敏感,对于它们来说,此子句被解析并丢弃。 然而,有一些对顺序敏感的聚合函数在没有排序的情况下可能会有非确定性的结果,例如 first, last, liststring_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

除了listfirst(以及它们的别名array_aggarbitrary)之外,所有通用的聚合函数都会忽略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表达式。忽略argval表达式值为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表达式。忽略argval表达式值为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表达式。忽略argval表达式值为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表达式。忽略argval表达式值为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的情况下),或者在两个输入列yx的情况下,忽略其中任一输入为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 处的(插值)值。如果 posFLOATLIST,则结果是相应插值分位数的 LIST
quantile_disc(x, pos) 离散的pos-分位数,其中0 <= pos <= 1。返回x的第greatest(ceil(pos * n_nonnull_values) - 1, 0)个(从零开始,按指定顺序)值。直观上,将x的每个值分配给区间[0, 1]的一个等大小的子区间(除了初始区间外,左开右闭),并选择包含pos的子区间的值。如果posFLOATLIST,则结果是相应离散分位数的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(x*y) - 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(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熵。
公式 -
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 <= 1x的插值pos分位数。返回x的第pos * (n_nonnull_values - 1)个(从零开始索引,按指定顺序)值,如果索引不是整数,则返回相邻值之间的插值。直观地说,将x的值排列为一条线上等距的,从0开始到1结束,并返回pos处的(插值)值。如果posFLOATLIST,则结果是相应插值分位数的LIST
Formula -
Alias(es) -

quantile_disc(x, pos)

描述 对于0 <= pos <= 1x的离散pos分位数。返回x的第greatest(ceil(pos * n_nonnull_values) - 1, 0)个(从零开始,按指定顺序)值。直观上,为x的每个值分配一个等大小的子区间(除了初始区间外,左开右闭)在区间[0, 1]中,并选择包含pos的子区间的值。如果posFLOATLIST,则结果是相应离散分位数的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 BYROLLUPGROUPING SETS 的查询:返回一个整数,标识用于创建当前超级聚合行的参数表达式。 grouping_id()