PostgreSQL 中文参考文档
🌐 英文原版 官方 ↗
9.21. 聚合函数
上一页 上一级Chapter 9. 函数与操作符首页 下一页

9.21. 聚合函数 #

聚集函数 从一组输入值计算出一个单一的结果。 内置的通用聚集函数列在 Table 9.62 中， 而统计聚集列在 Table 9.63 中。 内置的分组内有序集聚集函数列在 Table 9.64 中， 而内置的分组内假想集聚集列在 Table 9.65 中。 与聚集函数密切相关的分组操作列在 Table 9.66 中。 聚集函数的特殊语法考量在 Section 4.2.7 中解释。 更多入门信息请参见 Section 2.7。

支持 部分模式 的聚集函数有资格参与各种优化， 例如并行聚集。

虽然下面所有的聚集都接受一个可选的 ORDER BY 子句 （如 Section 4.2.7 中概括的那样）， 但这个子句只被加到那些其输出受排序影响的聚集上。

Table 9.62. 通用聚集函数

函数

说明

部分模式

any_value ( anyelement ) → same as input type

从非空输入值中返回一个任意的值。

Yes

array_agg ( anynonarray ORDER BY input_sort_columns ) → anyarray

把所有输入值 （包括空值） 收集到一个数组中。

Yes

array_agg ( anyarray ORDER BY input_sort_columns ) → anyarray

把所有输入数组连接成一个高一维的数组。 （这些输入必须都具有相同的维数， 并且不能为空或空值。）

Yes

avg ( smallint ) → numeric

avg ( integer ) → numeric

avg ( bigint ) → numeric

avg ( numeric ) → numeric

avg ( real ) → double precision

avg ( double precision ) → double precision

avg ( interval ) → interval

计算所有非空输入值的平均 （算术平均）。

Yes

bit_and ( smallint ) → smallint

bit_and ( integer ) → integer

bit_and ( bigint ) → bigint

bit_and ( bit ) → bit

计算所有非空输入值的按位与。

Yes

bit_or ( smallint ) → smallint

bit_or ( integer ) → integer

bit_or ( bigint ) → bigint

bit_or ( bit ) → bit

计算所有非空输入值的按位或。

Yes

bit_xor ( smallint ) → smallint

bit_xor ( integer ) → integer

bit_xor ( bigint ) → bigint

bit_xor ( bit ) → bit

计算所有非空输入值的按位异或。 它可以作为一组无序值的校验和。

Yes

bool_and ( boolean ) → boolean

如果所有非空输入值都为真则返回真， 否则返回假。

Yes

bool_or ( boolean ) → boolean

如果任何非空输入值为真则返回真， 否则返回假。

Yes

count ( * ) → bigint

计算输入行的数量。

Yes

count ( "any" ) → bigint

计算输入值非空的输入行的数量。

Yes

every ( boolean ) → boolean

这是 SQL 标准中与 bool_and 等价的函数。

Yes

json_agg ( anyelement ORDER BY input_sort_columns ) → json

jsonb_agg ( anyelement ORDER BY input_sort_columns ) → jsonb

把所有输入值 （包括空值） 收集到一个 JSON 数组中。 值按照 to_json 或 to_jsonb 被转换为 JSON。

No

json_agg_strict ( anyelement ) → json

jsonb_agg_strict ( anyelement ) → jsonb

把所有输入值 （跳过空值） 收集到一个 JSON 数组中。 值按照 to_json 或 to_jsonb 被转换为 JSON。

No

json_arrayagg ( [ value_expression ] [ ORDER BY sort_expression ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])

行为与 json_array 相同， 但作为一个聚集函数， 它只接受一个 value_expression 参数。 如果指定了 ABSENT ON NULL， 任何 NULL 值都会被省略。 如果指定了 ORDER BY， 元素将按该顺序而非输入顺序出现在数组中。

SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v) → [2, 1]

No

json_objectagg ( [ { key_expression { VALUE | ':' } value_expression } ] [ { NULL | ABSENT } ON NULL ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])

行为类似于 json_object， 但作为一个聚集函数， 它只接受一个 key_expression 和一个 value_expression 参数。

SELECT json_objectagg(k:v) FROM (VALUES ('a'::text,current_date),('b',current_date + 1)) AS t(k,v) → { "a" : "2022-05-10", "b" : "2022-05-11" }

No

json_object_agg ( key "any", value "any" ORDER BY input_sort_columns ) → json

jsonb_object_agg ( key "any", value "any" ORDER BY input_sort_columns ) → jsonb

把所有键/值对收集到一个 JSON 对象中。 键参数被强制转换为文本； 值参数按照 to_json 或 to_jsonb 被转换。 值可以为空， 但键不可以。

No

json_object_agg_strict ( key "any", value "any" ) → json

jsonb_object_agg_strict ( key "any", value "any" ) → jsonb

把所有键/值对收集到一个 JSON 对象中。 键参数被强制转换为文本； 值参数按照 to_json 或 to_jsonb 被转换。 key 不能为空。 如果 value 为空， 那么该条目会被跳过，

No

json_object_agg_unique ( key "any", value "any" ) → json

jsonb_object_agg_unique ( key "any", value "any" ) → jsonb

把所有键/值对收集到一个 JSON 对象中。 键参数被强制转换为文本； 值参数按照 to_json 或 to_jsonb 被转换。 值可以为空， 但键不可以。 如果存在重复的键， 会抛出一个错误。

No

json_object_agg_unique_strict ( key "any", value "any" ) → json

jsonb_object_agg_unique_strict ( key "any", value "any" ) → jsonb

把所有键/值对收集到一个 JSON 对象中。 键参数被强制转换为文本； 值参数按照 to_json 或 to_jsonb 被转换。 key 不能为空。 如果 value 为空， 那么该条目会被跳过。 如果存在重复的键， 会抛出一个错误。

No

max ( see text ) → same as input type

计算非空输入值的最大值。 可用于任何数值、 字符串、 日期/时间或枚举类型， 以及 bytea、 inet、 interval、 money、 oid、 pg_lsn、 tid、 xid8， 还有包含可排序数据类型的数组和组合类型。

Yes

min ( see text ) → same as input type

计算非空输入值的最小值。 可用于任何数值、 字符串、 日期/时间或枚举类型， 以及 bytea、 inet、 interval、 money、 oid、 pg_lsn、 tid、 xid8， 还有包含可排序数据类型的数组和组合类型。

Yes

range_agg ( value anyrange ) → anymultirange

range_agg ( value anymultirange ) → anymultirange

计算非空输入值的并集。

No

range_intersect_agg ( value anyrange ) → anyrange

range_intersect_agg ( value anymultirange ) → anymultirange

计算非空输入值的交集。

No

string_agg ( value text, delimiter text ) → text

string_agg ( value bytea, delimiter bytea ORDER BY input_sort_columns ) → bytea

把非空输入值串接成一个字符串。 第一个值之后的每个值前面都会加上相应的 delimiter （如果它不为空）。

Yes

sum ( smallint ) → bigint

sum ( integer ) → bigint

sum ( bigint ) → numeric

sum ( numeric ) → numeric

sum ( real ) → real

sum ( double precision ) → double precision

sum ( interval ) → interval

sum ( money ) → money

计算非空输入值的总和。

Yes

xmlagg ( xml ORDER BY input_sort_columns ) → xml

串接非空的 XML 输入值 （参见 Section 9.15.1.8）。

No

应当注意， 除了 count 之外， 这些函数在没有选中任何行时都会返回一个空值。 特别是， 对于零行， sum 返回的是空而非人们可能期望的零， 而当没有输入行时 array_agg 返回的是空而非一个空数组。 必要时可以使用 coalesce 函数把零或空数组替换掉空值。

聚集函数 array_agg、 json_agg、 jsonb_agg、 json_agg_strict、 jsonb_agg_strict、 json_object_agg、 jsonb_object_agg、 json_object_agg_strict、 jsonb_object_agg_strict、 json_object_agg_unique、 jsonb_object_agg_unique、 json_object_agg_unique_strict、 jsonb_object_agg_unique_strict、 string_agg 和 xmlagg， 以及类似的用户定义聚集函数， 产生的结果值会根据输入值的顺序而有意义地不同。 这种顺序默认是不确定的， 但可以通过在聚集调用内写一个 ORDER BY 子句来控制， 如 Section 4.2.7 所示。 或者， 从一个已排序的子查询中提供输入值通常也能奏效。 例如： 

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

注意， 如果外层查询层级包含额外的处理 （例如一个连接）， 这种方法可能失败， 因为那可能导致子查询的输出在聚集被计算之前被重新排序。

Note

布尔聚集 bool_and 和 bool_or 对应于标准 SQL 的聚集 every 和 any 或 some。 PostgreSQL 支持 every， 但不支持 any 或 some， 因为标准语法中内建了一种歧义： 

SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

在这里， 如果子查询返回带布尔值的一行， 那么 ANY 既可以被理解为引入一个子查询， 也可以被理解为是一个聚集函数。 因此这些聚集不能被赋予标准的名字。

Note

习惯于使用其他 SQL 数据库管理系统的用户， 可能会对把 count 聚集应用到整张表上的性能感到失望。 一条这样的查询： 

SELECT count(*) FROM sometable;

将需要与表的尺寸成比例的工作量： PostgreSQL 将需要扫描整张表， 或者扫描一个包含该表中所有行的索引的全部内容。

Table 9.63 展示了通常用于统计分析的聚集函数。 （它们被单独列出只是为了避免把更常用的聚集的列表弄乱。） 展示为接受 numeric_type 的函数可用于 smallint、 integer、 bigint、 numeric、 real 和 double precision 这些类型中的全部。 描述中提到 N 的地方， 它表示所有输入表达式都非空的输入行的数量。 在所有情况下， 如果计算是没有意义的， 例如 N 为零时， 都会返回空。

Table 9.63. 用于统计的聚集函数

函数

说明

部分模式

corr ( Y double precision, X double precision ) → double precision

计算相关系数。

Yes

covar_pop ( Y double precision, X double precision ) → double precision

计算总体协方差。

Yes

covar_samp ( Y double precision, X double precision ) → double precision

计算样本协方差。

Yes

regr_avgx ( Y double precision, X double precision ) → double precision

计算自变量的平均值， 即 sum(X)/N。

Yes

regr_avgy ( Y double precision, X double precision ) → double precision

计算因变量的平均值， 即 sum(Y)/N。

Yes

regr_count ( Y double precision, X double precision ) → bigint

计算两个输入都非空的行的数量。

Yes

regr_intercept ( Y double precision, X double precision ) → double precision

计算由 (X, Y) 对所确定的最小二乘拟合线性方程的 y 轴截距。

Yes

regr_r2 ( Y double precision, X double precision ) → double precision

计算相关系数的平方。

Yes

regr_slope ( Y double precision, X double precision ) → double precision

计算由 (X, Y) 对所确定的最小二乘拟合线性方程的斜率。

Yes

regr_sxx ( Y double precision, X double precision ) → double precision

计算自变量的 “平方和”， 即 sum(X^2) - sum(X)^2/N。

Yes

regr_sxy ( Y double precision, X double precision ) → double precision

计算自变量乘以因变量的 “乘积和”， 即 sum(X*Y) - sum(X) * sum(Y)/N。

Yes

regr_syy ( Y double precision, X double precision ) → double precision

计算因变量的 “平方和”， 即 sum(Y^2) - sum(Y)^2/N。

Yes

stddev ( numeric_type ) → double precision for real or double precision, otherwise numeric

这是 stddev_samp 的一个历史别名。

Yes

stddev_pop ( numeric_type ) → double precision for real or double precision, otherwise numeric

计算输入值的总体标准差。

Yes

stddev_samp ( numeric_type ) → double precision for real or double precision, otherwise numeric

计算输入值的样本标准差。

Yes

variance ( numeric_type ) → double precision for real or double precision, otherwise numeric

这是 var_samp 的一个历史别名。

Yes

var_pop ( numeric_type ) → double precision for real or double precision, otherwise numeric

计算输入值的总体方差 （总体标准差的平方）。

Yes

var_samp ( numeric_type ) → double precision for real or double precision, otherwise numeric

计算输入值的样本方差 （样本标准差的平方）。

Yes

Table 9.64 展示了一些使用 有序集聚集 语法的聚集函数。 这些函数有时被称为 “逆分布”函数。 它们的聚合输入由 ORDER BY 引入， 而且它们还可以接受一个不被聚合、 只被计算一次的 直接参数。 所有这些函数都会忽略其聚合输入中的空值。 对于那些接受 fraction 参数的函数， 分数值必须介于 0 和 1 之间； 否则会抛出一个错误。 然而， 一个空的 fraction 值只会产生一个空结果。

Table 9.64. 有序集聚集函数

函数

说明

部分模式

mode () WITHIN GROUP ( ORDER BY anyelement ) → anyelement

计算 众数， 即聚合参数中出现最频繁的值 （如果存在多个同等频繁的值， 则任意选择第一个）。 聚合参数必须是一个可排序的类型。

No

percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY double precision ) → double precision

percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY interval ) → interval

计算 连续百分位数， 即在聚合参数值的有序集中与指定的 fraction 相对应的值。 如果需要， 它会在相邻的输入项之间做插值。

No

percentile_cont ( fractions double precision[] ) WITHIN GROUP ( ORDER BY double precision ) → double precision[]

percentile_cont ( fractions double precision[] ) WITHIN GROUP ( ORDER BY interval ) → interval[]

计算多个连续百分位数。 结果是一个与 fractions 参数具有相同维度的数组， 其中每个非空元素都被替换为与该百分位数相对应的 （可能是插值过的） 值。

No

percentile_disc ( fraction double precision ) WITHIN GROUP ( ORDER BY anyelement ) → anyelement

计算 离散百分位数， 即聚合参数值的有序集中、 其在排序中的位置等于或超过指定的 fraction 的第一个值。 聚合参数必须是一个可排序的类型。

No

percentile_disc ( fractions double precision[] ) WITHIN GROUP ( ORDER BY anyelement ) → anyarray

计算多个离散百分位数。 结果是一个与 fractions 参数具有相同维度的数组， 其中每个非空元素都被替换为与该百分位数相对应的输入值。 聚合参数必须是一个可排序的类型。

No

Table 9.65 中列出的每一个 “假想集”聚集， 都与 Section 9.22 中定义的一个同名窗口函数相关联。 在每种情况下， 聚集的结果就是相关的窗口函数， 对于由 args 构造的 “假想”行， 本来会返回的值 （前提是这样的行被加入到了由 sorted_args 表示的已排序行组中）。 对于这些函数中的每一个， 在 args 中给出的直接参数列表， 都必须与 sorted_args 中给出的聚合参数在数量和类型上相匹配。 与大多数内置聚集不同， 这些聚集不是严格的， 也就是说， 它们不会丢弃包含空值的输入行。 空值按照 ORDER BY 子句中指定的规则排序。

Table 9.65. 假想集聚集函数

函数

说明

部分模式

rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → bigint

计算假想行的排名 （带空隙）； 也就是说， 为它的对等组中第一行的行号。

No

dense_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → bigint

计算假想行的排名 （不带空隙）； 这个函数有效地统计对等组的数量。

No

percent_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision

计算假想行的相对排名， 即 (rank - 1) / (total rows - 1)。 该值的范围因此是从 0 到 1（含）。

No

cume_dist ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision

计算累积分布， 即 （位于假想行之前或与之同级的行数）/ (总行数)。 该值的范围因此是从 1/N 到 1。

No

Table 9.66. 分组操作

函数

说明

GROUPING ( group_by_expression(s) ) → integer

返回一个位掩码， 指示哪些 GROUP BY 表达式没有被包括在当前分组集中。 位从最右边的参数对应最低有效位的方式分配； 如果相应的表达式被包括在产生当前结果行的分组集的分组条件中， 则每个位为 0， 否则为 1。


Table 9.66 中展示的分组操作与分组集一起使用 （参见 Section 7.2.4） 来区分结果行。 GROUPING 函数的参数实际上不会被求值， 但它们必须与相关查询层级的 GROUP BY 子句中给出的表达式精确匹配。 例如： 

=> SELECT * FROM items_sold;
 make  | model | sales
-------+-------+-------
 Foo   | GT    |  10
 Foo   | Tour  |  20
 Bar   | City  |  15
 Bar   | Sport |  5
(4 rows)

=> SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);
 make  | model | grouping | sum
-------+-------+----------+-----
 Foo   | GT    |        0 | 10
 Foo   | Tour  |        0 | 20
 Bar   | City  |        0 | 15
 Bar   | Sport |        0 | 5
 Foo   |       |        1 | 30
 Bar   |       |        1 | 20
       |       |        3 | 50
(7 rows)

这里， 前四行中的 grouping 值 0 表明它们是在两个分组列上都正常分组的。 值 1 表明在倒数第二和第三行中 model 没有被分组， 而值 3 表明在最后一行中 make 和 model 都没有被分组 （因此最后一行是对所有输入行的一个聚合）。

上一页 上一级 下一页
9.20. 范围与多重范围函数及操作符 首页 9.22. 窗口函数