9.21. 聚合函数#

聚合函数根据一组输入值计算单个结果。内置通用聚合函数列在表 9.59中，而统计聚合函数列在表 9.60中。内置组内有序集聚合函数列在表 9.61中，而内置组内假设集聚合函数列在表 9.62中。与聚合函数密切相关的分组操作列在表 9.63中。聚合函数的特殊语法注意事项在第 4.2.7 节中进行了说明。有关其他介绍性信息，请参阅第 2.7 节。

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

表 9.59 通用聚合函数

函数 说明	部分模式
any_value ( anyelement ) → 与输入类型相同 从非空输入值中返回一个任意值。	是
array_agg ( anynonarray ) → anyarray 将所有输入值（包括空值）收集到一个数组中。	是
array_agg ( anyarray ) → anyarray 将所有输入数组连接成一个高一维度的数组。（所有输入必须具有相同的维度，并且不能是空或空值。）	是
avg ( smallint ) → numeric avg ( integer ) → numeric avg ( bigint ) → numeric avg ( numeric ) → numeric avg ( real ) → double precision avg ( double precision ) → double precision avg ( interval ) → interval 计算所有非空输入值的平均值（算术平均值）。	是
bit_and ( smallint ) → smallint bit_and ( integer ) → integer bit_and ( bigint ) → bigint bit_and ( bit ) → bit 计算所有非空输入值的按位 AND。	是
bit_or ( smallint ) → smallint bit_or ( integer ) → integer bit_or ( bigint ) → bigint bit_or ( bit ) → bit 计算所有非空输入值的按位或。	是
bit_xor ( smallint ) → smallint bit_xor ( integer ) → integer bit_xor ( bigint ) → bigint bit_xor ( bit ) → bit 计算所有非空输入值的按位异或。可作为无序值集的校验和。	是
bool_and ( boolean ) → boolean 如果所有非空输入值都为 true，则返回 true，否则返回 false。	是
bool_or ( boolean ) → boolean 如果任何非空输入值为 true，则返回 true，否则返回 false。	是
count ( * ) → bigint 计算输入行数。	是
count ( "any" ) → bigint 计算输入值不为 null 的输入行数。	是
every ( boolean ) → boolean 这是 SQL 标准中 bool_and 的等效项。	是
json_agg ( anyelement ) → json jsonb_agg ( anyelement ) → jsonb 将所有输入值（包括 null）收集到一个 JSON 数组中。值将根据 to_json 或 to_jsonb 转换为 JSON。	否
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" }	否
json_object_agg ( key "any", value "any" ) → json jsonb_object_agg ( key "any", value "any" ) → jsonb 将所有键/值对收集到一个 JSON 对象中。键参数强制转换为文本；值参数按照 to_json 或 to_jsonb 转换。值可以为 null，但键不能为 null。	否
json_object_agg_strict ( key "any", value "any" ) → json jsonb_object_agg_strict ( key "any", value "any" ) → jsonb 将所有键/值对收集到一个 JSON 对象中。键参数强制转换为文本；值参数按照 to_json 或 to_jsonb 转换。 key 不能为 null。如果 value 为 null，则跳过该条目，	否
json_object_agg_unique ( key "any", value "any" ) → json jsonb_object_agg_unique ( key "any", value "any" ) → jsonb 将所有键/值对收集到一个 JSON 对象中。键参数强制转换为文本；值参数按照 to_json 或 to_jsonb 进行转换。值可以为 null，但键不能。如果存在重复键，则会引发错误。	否
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]	否
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 不能为 null。如果 value 为 null，则会跳过该条目。如果存在重复键，则会引发错误。	否
max ( see text ) → same as input type 计算非空输入值的较大值。可用于任何数字、字符串、日期/时间或枚举类型，以及 inet、interval、money、oid、pg_lsn、tid、xid8 以及这些类型的数组。	是
min ( see text ) → same as input type 计算非空输入值的最小值。可用于任何数字、字符串、日期/时间或枚举类型，以及 inet、interval、money、oid、pg_lsn、tid、xid8 以及这些类型的数组。	是
range_agg ( value anyrange ) → anymultirange range_agg ( value anymultirange ) → anymultirange 计算非空输入值的并集。	否
range_intersect_agg ( value anyrange ) → anyrange range_intersect_agg ( value anymultirange ) → anymultirange 计算非空输入值的交集。	否
json_agg_strict ( anyelement ) → json jsonb_agg_strict ( anyelement ) → jsonb 收集所有输入值（跳过空值），并将其放入 JSON 数组中。值将根据 to_json 或 to_jsonb 转换为 JSON。	否
string_agg ( value text, delimiter text ) → text string_agg ( value bytea, delimiter bytea ) → bytea 将非空输入值连接成一个字符串。第一个值之后的每个值都将前置相应的 delimiter（如果它不为空）。	是
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 计算非空输入值的总和。	是
xmlagg ( xml ) → xml 连接非空 XML 输入值（参见 第 9.15.1.7 节）。	否

需要注意的是，除了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子句来控制，如第 4.2.7 节中所示。或者，通常会从排序的子查询中提供输入值。例如

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

请注意，如果外部查询级别包含其他处理（例如联接），则此方法可能会失败，因为这可能会导致在计算聚合之前重新排序子查询的输出。

注意

布尔聚合bool_and和bool_or对应于标准 SQL 聚合every和any或some。PostgreSQL支持every，但不支持any或some，因为标准语法中存在歧义此处ANY可以被视为引入子查询，或作为聚合函数，如果子查询返回一行布尔值。因此，无法将标准名称赋予这些聚合。

注意

习惯于使用其他 SQL 数据库管理系统的用户可能会对将count聚合应用于整个表时的性能感到失望。类似这样的查询需要与表大小成比例的精力：PostgreSQL需要扫描整个表或包含表中所有行的索引的全部内容。

表 9.60显示了统计分析中通常使用的聚合函数。（这些函数被单独列出，只是为了避免与更常用的聚合函数混淆。）显示为接受*numeric_type的函数可用于所有类型smallint、integer、bigint、numeric、real和double precision。如果描述中提到了N，则表示所有输入表达式都为非空值的输入行的数量。在所有情况下，如果计算没有意义（例如，当N*为零时），则返回 null。

表 9.60. 统计分析的聚合函数

函数 说明	部分模式
corr ( Y double precision, X double precision ) → double precision 计算相关系数。	是
covar_pop ( Y double precision, X double precision ) → double precision 计算总体协方差。	是
covar_samp ( Y double precision, X double precision ) → double precision 计算样本协方差。	是
regr_avgx ( Y double precision, X double precision ) → double precision 计算自变量的平均值，sum(X)/N。	是
regr_avgy ( Y double precision, X double precision ) → double precision 计算因变量的平均值，sum(Y)/N。	是
regr_count ( Y double precision, X double precision ) → bigint 计算两个输入均非空的行数。	是
regr_intercept ( Y double precision, X double precision ) → double precision 计算由 (X, Y) 对确定的最小二乘拟合线性方程的 y 截距。	是
regr_r2 ( Y double precision, X double precision ) → double precision 计算相关系数的平方。	是
regr_slope ( Y double precision, X double precision ) → double precision 计算由 (X, Y) 对确定的最小二乘拟合线性方程的斜率。	是
regr_sxx ( Y double precision, X double precision ) → double precision 计算自变量的“平方和”，sum(X^2) - sum(X)^2/N。	是
regr_sxy ( Y double precision, X double precision ) → double precision 计算自变量乘以因变量的“乘积和”，sum(X*Y) - sum(X) * sum(Y)/N。	是
regr_syy ( Y double precision, X double precision ) → double precision 计算因变量的“平方和”，sum(Y^2) - sum(Y)^2/N。	是
stddev ( numeric_type ) → double precision对于real或double precision，否则numeric 这是stddev_samp的历史别名。	是
stddev_pop ( numeric_type ) → double precision对于real或double precision，否则numeric 计算输入值的总体标准差。	是
stddev_samp ( numeric_type ) → double precision对于real或double precision，否则numeric 计算输入值的样本标准差。	是
variance ( numeric_type ) → double precision对于real或double precision，否则numeric 这是var_samp的历史别名。	是
var_pop ( numeric_type ) → double precision对于real或double precision，否则numeric 计算输入值的总体方差（总体标准差的平方）。	是
var_samp ( numeric_type ) → double precision 对于 real 或 double precision，否则为 numeric 计算输入值（样本标准差的平方）的样本方差。	是

表 9.61显示了一些使用有序集聚合语法的聚合函数。这些函数有时称为“逆分布”函数。它们的聚合输入由ORDER BY引入，它们还可以采用一个未聚合的直接参数，但仅计算一次。所有这些函数都忽略其聚合输入中的空值。对于那些采用*fraction参数的函数，分数值必须在 0 和 1 之间；如果不满足此条件，则会引发错误。但是，空fraction*值只会产生空结果。

表 9.61. 有序集聚合函数

函数 说明	部分模式
mode () WITHIN GROUP ( ORDER BY anyelement ) → anyelement 计算 众数，即聚合参数中最频繁的值（如果有多个频率相同的值，则任意选择第一个）。聚合参数必须为可排序类型。	否
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。如果需要，这将在相邻输入项之间进行插值。	否
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 参数具有相同的维度，其中每个非空元素都替换为对应于该百分位数的（可能插值）值。	否
percentile_disc ( fraction double precision ) WITHIN GROUP ( ORDER BY anyelement ) → anyelement 计算离散百分位数，即聚合参数值的有序集合中第一个值，其在排序中的位置等于或超过指定的fraction。聚合参数必须为可排序类型。	否
percentile_disc ( fractions double precision[] ) WITHIN GROUP ( ORDER BY anyelement ) → anyarray 计算多个离散百分位数。结果与fractions参数具有相同的维度，每个非空元素替换为对应于该百分位数的输入值。聚合参数必须为可排序类型。	否

表 9.62中列出的每个“假设集”聚合都与第 9.22 节中定义的同名窗口函数关联。在每种情况下，聚合的结果都是关联窗口函数将为从*args构建的“假设”行返回的值，如果已将该行添加到由sorted_args表示的有序行组中。对于这些函数中的每一个，args中给出的直接参数列表必须与sorted_args*中给出的聚合参数的数量和类型匹配。与大多数内置聚合不同，这些聚合不是严格的，即它们不会删除包含空值的输入行。空值根据ORDER BY子句中指定的规则进行排序。

表 9.62. 假设集聚合函数

函数 说明	部分模式
rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → bigint 计算假设行（含间隙）的秩，即其对等组中第一行的行号。	否
dense_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → bigint 计算假设行（不含间隙）的秩；此函数有效地计数对等组。	否
percent_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision 计算假设行的相对秩，即 (rank - 1) / (总行数 - 1)。因此，值范围为 0 到 1（含）。	否
cume_dist ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision 计算累积分布，即（假设行前或与之对等的行数）/（总行数）。因此，值范围为 1/N 到 1。	否

表 9.63 分组操作

函数 说明
GROUPING ( group_by_expression(s) ) → integer 返回一个位掩码，指示哪些 GROUP BY 表达式未包含在当前分组集中。位从右到左分配，最右边的参数对应最低有效位；如果相应的表达式包含在生成当前结果行的分组集的分组条件中，则每个位为 0，否则为 1。

表 9.63中所示的分组操作与分组集（请参阅第 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分组（因此是所有输入行的聚合）。