9.20. 聚集函数
聚集函数从一个输入值的集合计算一个单一结果。内建的普通聚集函数被列在表 9-51和表 9-52中。内建的顺序集聚集函数被列在表 9-53和 表 9-54中。分组操作，这与聚集函数有密切联系，被列在表 9-55中。聚集函数的特殊语法考虑在第 4.2.7 节中解释。更多介绍性信息请参考第 2.7 节。
表 9-51. 通用聚集函数
| 函数 | 参数类型 | 返回类型 | 局部模式 | 描述 |
|---|---|---|---|---|
array_agg(expression)
|
任何非数组类型 | 参数类型的数组 | No | 输入值（包括空）被连接到一个数组 |
array_agg(expression)
|
任何数组类型 | 和参数数据类型一样 | No | 级联到更高维数组的输入数组（输入必须都具有相同的维度，不能为空或NULL） |
avg(expression)
|
smallint, int, bigint、real、double precision、numeric或interval | 对于任何整数类型参数是numeric，对于一个浮点参数是double precision，否则和参数数据类型相同 | Yes | 所有输入值的平均值（算术平均） |
bit_and(expression)
|
smallint、int、bigint或bit | 与参数数据类型相同 | Yes | 所有非空输入值的按位与，如果没有非空值则结果是空值 |
bit_or(expression)
|
smallint, int, bigint, or bit | 与参数数据类型相同 | Yes | 所有非空输入值的按位或，如果没有非空值则结果是空值 |
bool_and(expression)
|
bool | bool | Yes | 如果所有输入值为真则结果为真，否则为假 |
bool_or(expression)
|
bool | bool | Yes | 至少一个输入值为真时结果为真，否则为假 |
count(*)
|
bigint | Yes | 输入的行数 | |
count(expression) |
any | bigint | Yes | expression值非空的输入行的数目 |
every(expression)
|
bool | bool | Yes | 等价于bool_and
|
json_agg(expression)
|
any | json | No | 将值聚集成一个 JSON 数组 |
jsonb_agg(expression)
|
any | jsonb | No | 将值聚集成一个JSON数组 |
json_object_agg(name, value)
|
(any, any) | json | No | 将名字/值对聚集成一个 JSON 对象 |
jsonb_object_agg(name, value)
|
(any, any) | jsonb | No | 将名字/值对聚集成一个JSON对象 |
max(expression)
|
任意数字、字符串，日期/时间，网络，或枚举类型或这些类型数组 | 与参数数据类型相同 | Yes | 所有输入值中expression的最大值 |
min(expression)
|
任意数字、字符串，日期/时间，网络，或枚举类型或这些类型数组 | 与参数数据类型相同 | Yes | 所有输入值中expression的最小值 |
string_agg(expression,
delimiter)
|
(text, text) 或 (bytea, bytea) | 与参数数据类型相同 | No | 输入值连接成一个串，用定界符分隔 |
sum(expression)
|
smallint、int、 bigint、real、double precision、numeric、 interval或money | 对smallint或int参数是bigint，对bigint参数是numeric，否则和参数数据类型相同 | Yes | 所有输入值的expression的和 |
xmlagg(expression)
|
xml | xml | No | 连接 XML 值（参见第 9.14.1.7 节） |
请注意，除了count以外，这些函数在没有行被选中时返回控制。尤其是sum函数在没有输入行时返回空值，而不是零，并且array_agg在这种情况返回空值而不是一个空数组。必要时可以用coalesce把空值替换成零或一个空数组。
支持局部模式的聚集函数有资格参与到各种优化中， 例如并行聚集。
注意: 布尔聚集
bool_and和bool_or对应于标准的 SQL 聚集every和any或some。而对于any和some，似乎在标准语法中有一个歧义：SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;如果子查询返回一行有一个布尔值的结果，这里的
ANY可以被认为是引入一个子查询，或者是作为一个聚集函数。因而标准的名称不能指定给这些聚集。
注意: 在把
count聚集应用到整个表上时，习惯于使用其他 SQL 数据管理系统的用户可能会对它的性能感到失望。一个如下的查询：SELECT count(*) FROM sometable;将会要求与整个表大小成比例的工作：PostgreSQL将需要扫描整个表或者整个包含表中所有行的索引。
与相似的用户定义的聚集函数一样，聚集函数array_agg、
json_agg、jsonb_agg、
json_object_agg、jsonb_object_agg、string_agg和xmlagg会依赖输入值的顺序产生有意义的不同结果值。这个顺序默认是不用指定的，但是可以在聚集调用时使用ORDER BY子句进行控制，如第 4.2.7 节中所示。作为一种选择，从一个排序号的子查询来提供输入值通常会有帮助。例如：
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
但是在 SQL 标准中不允许这种语法，并且不能被移植到其他数据库系统。
表 9-52展示了通常被用在统计分析中的聚集函数（这些被隔离出来是为了不和常用聚集混淆）。其中描述提到了N，它表示对应于所有非空输入表达式的输入行的数目。在所有情况中，如果计算是无意义的，将会返回空值，例如当N为零。
表 9-52. 用于统计的聚集函数
| 函数 | 参数类型 | 返回类型 | 局部模式 | 描述 |
|---|---|---|---|---|
corr(Y, X)
|
double precision | double precision | Yes | 相关系数 |
covar_pop(Y, X)
|
double precision | double precision | Yes | 总体协方差 |
covar_samp(Y, X)
|
double precision | double precision | Yes | 样本协方差 |
regr_avgx(Y, X)
|
double precision | double precision | Yes | 自变量的平均值 （sum(X)/N） |
regr_avgy(Y, X)
|
double precision | double precision | Yes | 因变量的平均值 （sum(Y)/N） |
regr_count(Y, X)
|
double precision | bigint | Yes | 两个表达式都不为空的输入行的数目 |
regr_intercept(Y, X)
|
double precision | double precision | Yes | 由（X, Y）对决定的最小二乘拟合的线性方程的 y截距 |
regr_r2(Y, X)
|
double precision | double precision | Yes | 相关系数的平方 |
regr_slope(Y, X)
|
double precision | double precision | Yes | 由（X, Y）对决定的最小二乘拟合的线性方程的斜率 |
regr_sxx(Y, X)
|
double precision | double precision | Yes | sum(X^2) - sum(X)^2/N（自变量的"平方和"） |
regr_sxy(Y, X)
|
double precision | double precision | Yes | sum(X*Y) - sum(X) * sum(Y)/N（自变量乘以因变量的"积之合"） |
regr_syy(Y, X)
|
double precision | double precision | Yes | sum(Y^2) - sum(Y)^2/N（因变量的"平方和"） |
stddev(expression)
|
smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes |
stddev_samp的历史别名 |
stddev_pop(expression)
|
smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes | 输入值的总体标准偏差 |
stddev_samp(expression)
|
smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes | 输入值的样本标准偏差 |
variance(expression)
|
smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes |
var_samp的历史别名 |
var_pop(expression)
|
smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes | 输入值的总体方差（总体标准偏差的平方） |
var_samp(expression)
|
smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes | 输入值的样本方差（样本标准偏差的平方） |
表 9-53展示了一些使用 有序集聚集语法的聚集函数。这些函数有时也被称为 "逆分布"函数。
表 9-53. 有序集聚集函数
| 函数 | 直接参数类型 | 聚集参数类型 | 返回类型 | 局部模式 | 描述 |
|---|---|---|---|---|---|
mode() WITHIN GROUP (ORDER BY sort_expression)
|
任何可排序类型 | 与排序表达式相同 | No | 返回最频繁的输入值（如果有多个频度相同的值就选第一个） | |
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)
|
double precision | double precision或者interval | 与排序表达式相同 | No | 连续百分率：返回一个对应于排序中指定分数的值，如有必要就在相邻的输入项之间插值 |
percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)
|
double precision[] | double precision或者interval | 排序表达式的类型的数组 | No | 多重连续百分率：返回一个匹配fractions参数形状的结果数组， 其中每一个非空元素都用对应于那个百分率的值替换 |
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)
|
double precision | 一种可排序类型 | 与排序表达式相同 | No | 离散百分率：返回第一个在排序中位置等于或者超过指定分数的输入值 |
percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression)
|
double precision[] | 任何可排序类型 | 排序表达式的类型的数组 | No | 多重离散百分率：返回一个匹配fractions参数形状的结果数组， 其中每一个非空元素都用对应于那个百分率的输入值替换 |
所有列在表 9-53中的聚集会忽略它们的已 排序输入中的空值。对那些有一个fraction参数的聚 集来说，该分数值必须位于 0 和 1 之间，否则会抛出错误。不过，一个空分数值会 产生一个空结果。
每个列在表 9-54中的聚集都与一个定义在 第 9.21 节中的同名窗口函数相关联。在每种情况中，聚集 结果的计算方法是：假设根据args构建的"假想"行已 经被增加到从sorted_args计算得到的已排序行分组中， 然后用相关联的窗口函数针对该行返回的值就是聚集的结果。
表 9-54. 假想集聚集函数
| 函数 | 直接参数类型 | 聚集参数类型 | 返回类型 | 局部模式 | 描述 |
|---|---|---|---|---|---|
rank(args) WITHIN GROUP (ORDER BY sorted_args)
|
VARIADIC "any" | VARIADIC "any" | bigint | No | 假想行的排名，为重复的行留下间隔 |
dense_rank(args) WITHIN GROUP (ORDER BY sorted_args)
|
VARIADIC "any" | VARIADIC "any" | bigint | No | 假想行的排名，不留间隔 |
percent_rank(args) WITHIN GROUP (ORDER BY sorted_args)
|
VARIADIC "any" | VARIADIC "any" | double precision | No | 假想行的相对排名，范围从 0 到 1 |
cume_dist(args) WITHIN GROUP (ORDER BY sorted_args)
|
VARIADIC "any" | VARIADIC "any" | double precision | No | 假想行的相对排名，范围从 1/N 到 1 |
对于这些假想集聚集的每一个，args中给定的直接参数 列表必须匹配sorted_args中给定的聚集参数的 数量和类型。与大部分的内建聚集不同，这些聚集并不严格，即它们不会丢弃包含空值 的输入行。空值的排序根据ORDER BY子句中指定的规则进行。
表 9-55. 分组操作
| 函数 | 返回类型 | 描述 |
|---|---|---|
GROUPING(args...)
|
integer | 整数位掩码表示当前分组集中不包含的参数 |
分组操作与分组集合一起（参见第 7.2.4 节）区分结果行。 这个GROUPING操作的参数实际上并没有进行评估， 但它们必须完全匹配关联查询级别的GROUP BY子句中的表达式。 Bit连同最右边参数是最不重要的一点； 如果对应的表达式被包含在分组集生成的结果行的分组条件中，那么每位是0， 如果不是，则为1。比如：
=> 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)