Documentation
/ SQL
/ Functions
Aggregate Functions
生成包含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
子句时,在聚合计算中只考虑不同的值。这通常与count
聚合一起使用,以获取不同元素的数量;但它可以与系统中的任何聚合函数一起使用。
可以在函数调用的最后一个参数之后提供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;
除了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 中所有非空值的总和。 |
描述 |
返回arg 中的第一个非NULL 值。此函数受排序影响。 |
示例 |
any_value(A) |
别名 |
- |
描述 |
返回arg 中的第一个值(NULL 或非NULL )。此函数受排序影响。 |
示例 |
arbitrary(A) |
别名 |
first(A) |
描述 |
找到具有最大val 的行,并在该行计算arg 表达式。忽略arg 或val 表达式值为NULL 的行。此函数受排序影响。 |
示例 |
arg_max(A, B) |
别名 |
argMax(arg, val) , max_by(arg, val) |
描述 |
arg_max 的广义情况,适用于 n 个值:返回一个包含按 val 降序排列的前 n 行的 arg 表达式的 LIST 。此函数受排序影响。 |
示例 |
arg_max(A, B, 2) |
别名 |
argMax(arg, val, n) , max_by(arg, val, n) |
描述 |
找到具有最大val 的行,并在该行计算arg 表达式。忽略val 表达式评估为NULL 的行。此函数受排序影响。 |
示例 |
arg_max_null(A, B) |
Alias(es) |
- |
描述 |
找到具有最小val 的行,并在该行计算arg 表达式。忽略arg 或val 表达式值为NULL 的行。此函数受排序影响。 |
示例 |
arg_min(A, B) |
别名 |
argmin(arg, val) , min_by(arg, val) |
描述 |
arg_min 的广义情况,适用于 n 个值:返回一个包含按 val 降序排列的前 n 行的 arg 表达式的 LIST 。此函数受排序影响。 |
示例 |
arg_min(A, B, 2) |
别名 |
argmin(arg, val, n) , min_by(arg, val, n) |
描述 |
找到具有最小val 的行,并在该行计算arg 表达式。忽略val 表达式评估为NULL 的行。此函数受排序影响。 |
示例 |
arg_min_null(A, B) |
Alias(es) |
- |
描述 |
返回一个包含列中所有值的LIST 。此函数受排序影响。 |
示例 |
array_agg(A) |
别名 |
list |
描述 |
计算arg 中所有非空值的平均值。 |
示例 |
avg(A) |
别名 |
mean |
描述 |
返回给定表达式中所有位的按位AND 。 |
Example |
bit_and(A) |
Alias(es) |
- |
描述 |
返回给定表达式中所有位的按位OR 。 |
Example |
bit_or(A) |
Alias(es) |
- |
描述 |
返回给定表达式中所有位的按位XOR 。 |
Example |
bit_xor(A) |
Alias(es) |
- |
描述 |
返回一个长度对应于非空(整数)值范围的位串,每个(不同)值的位置都设置了位。 |
Example |
bitstring_agg(A) |
Alias(es) |
- |
描述 |
如果每个输入值都是 true ,则返回 true ,否则返回 false 。 |
示例 |
bool_and(A) |
Alias(es) |
- |
描述 |
如果任何输入值为true ,则返回true ,否则返回false 。 |
示例 |
bool_or(A) |
Alias(es) |
- |
描述 |
返回组中的行数。 |
示例 |
count() |
别名 |
count(*) |
描述 |
返回arg 中非空值的数量。 |
示例 |
count(A) |
Alias(es) |
- |
描述 |
使用更精确的浮点求和(Kahan Sum)计算平均值。 |
示例 |
favg(A) |
Alias(es) |
- |
描述 |
返回arg 中的第一个值(空值或非空值)。此函数受排序影响。 |
示例 |
first(A) |
别名 |
arbitrary(A) |
描述 |
使用更精确的浮点求和(Kahan Sum)计算总和。 |
示例 |
fsum(A) |
别名 |
sumKahan , kahan_sum |
描述 |
计算arg 中所有非空值的几何平均值。 |
示例 |
geomean(A) |
别名 |
geometric_mean(A) |
描述 |
返回一个表示桶和计数的键值对的MAP 。 |
示例 |
histogram(A) |
Alias(es) |
- |
描述 |
返回一个表示请求元素及其计数的键值对的MAP 。当其他元素出现时,会自动添加一个特定于数据类型的通用元素来计数,请参阅is_histogram_other_bin 。 |
示例 |
histogram_exact(A, [0, 1, 10]) |
Alias(es) |
- |
描述 |
返回列的最后一个值。此函数受排序影响。 |
示例 |
last(A) |
Alias(es) |
- |
Description |
Returns a LIST containing all the values of a column. This function is affected by ordering. |
示例 |
list(A) |
别名 |
array_agg |
描述 |
返回arg 中的最大值。 |
示例 |
max(A) |
Alias(es) |
- |
描述 |
返回一个包含按arg 降序排列的前n 行的arg 值的LIST 。 |
示例 |
max(A, 2) |
Alias(es) |
- |
描述 |
找到具有最大val 的行。在该行计算arg 表达式。此函数受排序影响。 |
示例 |
max_by(A, B) |
别名 |
argMax(arg, val) , arg_max(arg, val) |
描述 |
返回一个包含按val 降序排列的前n 行的arg 表达式的LIST 。 |
示例 |
max_by_n(A, B, 2) |
别名 |
argMax(arg, val, n) , arg_max(arg, val, n) |
描述 |
返回arg 中存在的最小值。 |
示例 |
min(A) |
Alias(es) |
- |
描述 |
返回一个包含按arg 升序排列的“底部”n 行的arg 值的LIST 。 |
示例 |
min(A, 2) |
Alias(es) |
- |
描述 |
找到具有最小val 的行。在该行计算arg 表达式。此函数受排序影响。 |
示例 |
min_by(A, B) |
别名 |
argMin(arg, val) , arg_min(arg, val) |
描述 |
返回一个包含按val 升序排列的“底部”n 行的arg 表达式的LIST 。 |
示例 |
min_by(A, B, 2) |
别名 |
argMin(arg, val, n) , arg_min(arg, val, n) |
描述 |
计算arg 中所有非空值的乘积。 |
示例 |
product(A) |
Alias(es) |
- |
描述 |
将列字符串值与分隔符连接起来。此函数受排序影响。 |
示例 |
string_agg(S, ',') |
别名 |
group_concat(arg, sep) , listagg(arg, sep) |
描述 |
计算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) |
样本方差,包括贝塞尔的偏差校正。 |
描述 |
相关系数。 |
公式 |
covar_pop(y, x) / (stddev_pop(x) * stddev_pop(y)) |
Alias(es) |
- |
描述 |
总体协方差,不包括偏差校正。 |
公式 |
(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) |
- |
描述 |
样本协方差,包括贝塞尔的偏差校正。 |
公式 |
(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) |
描述 |
对数-2熵。 |
公式 |
- |
Alias(es) |
- |
描述 |
无偏差校正的超额峰度(Fisher的定义)。 |
Formula |
- |
Alias(es) |
- |
描述 |
根据样本大小进行偏差校正的超额峰度(Fisher的定义)。 |
Formula |
- |
Alias(es) |
- |
描述 |
中位数绝对偏差。时间类型返回一个正的INTERVAL 。 |
公式 |
median(abs(x - median(x))) |
Alias(es) |
- |
描述 |
集合的中间值。对于偶数个值,定量值取平均值,序数值返回较小的值。 |
公式 |
quantile_cont(x, 0.5) |
Alias(es) |
- |
描述 |
最频繁的值。此函数受排序影响。 |
Formula |
- |
Alias(es) |
- |
描述 |
对于0 <= pos <= 1 ,x 的插值pos 分位数。返回x 的第pos * (n_nonnull_values - 1) 个(从零开始索引,按指定顺序)值,如果索引不是整数,则返回相邻值之间的插值。直观地说,将x 的值排列为一条线上等距的点,从0开始到1结束,并返回pos 处的(插值)值。如果pos 是FLOAT 的LIST ,则结果是相应插值分位数的LIST 。 |
Formula |
- |
Alias(es) |
- |
描述 |
对于0 <= pos <= 1 ,x 的离散pos 分位数。返回x 的第greatest(ceil(pos * n_nonnull_values) - 1, 0) 个(从零开始,按指定顺序)值。直观上,为x 的每个值分配一个等大小的子区间(除了初始区间外,左开右闭)在区间[0, 1] 中,并选择包含pos 的子区间的值。如果pos 是FLOAT 的LIST ,则结果是相应离散分位数的LIST 。 |
Formula |
- |
别名 |
quantile |
描述 |
非NULL 对的自变量的平均值,其中x是自变量,y是因变量。 |
Formula |
- |
Alias(es) |
- |
描述 |
非NULL 对的因变量的平均值,其中x是自变量,y是因变量。 |
Formula |
- |
Alias(es) |
- |
描述 |
非NULL 对的数量。 |
Formula |
- |
Alias(es) |
- |
描述 |
单变量线性回归线的截距,其中x是自变量,y是因变量。 |
公式 |
regr_avgy(y, x) - regr_slope(y, x) * regr_avgx(y, x) |
Alias(es) |
- |
描述 |
y 和 x 之间的平方皮尔逊相关系数。同样:线性回归中的决定系数,其中 x 是自变量,y 是因变量。 |
Formula |
- |
Alias(es) |
- |
描述 |
返回线性回归线的斜率,其中x是自变量,y是因变量。 |
公式 |
regr_sxy(y, x) / regr_sxx(y, x) |
Alias(es) |
- |
描述 |
非NULL 对的独立变量的总体方差,包括贝塞尔的偏差校正,其中x是独立变量,y是因变量。 |
Formula |
- |
Alias(es) |
- |
描述 |
总体协方差,包括贝塞尔的偏差校正。 |
Formula |
- |
Alias(es) |
- |
描述 |
非NULL 对的因变量的总体方差,包括贝塞尔的偏差校正,其中x是自变量,y是因变量。 |
Formula |
- |
Alias(es) |
- |
描述 |
偏度。 |
Formula |
- |
Alias(es) |
- |
描述 |
总体标准差。 |
公式 |
sqrt(var_pop(x)) |
Alias(es) |
- |
描述 |
样本标准差。 |
公式 |
sqrt(var_samp(x)) |
别名 |
stddev(x) |
描述 |
总体方差,不包括偏差校正。 |
公式 |
(sum(x^2) - sum(x)^2 / count(x)) / count(x) , var_samp(y, x) * (1 - 1 / count(x)) |
Alias(es) |
- |
描述 |
样本方差,包括贝塞尔的偏差校正。 |
公式 |
(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)]) |