聚集函数从一个输入值的集合计算出一个单一值。
内建的通用聚集函数在表 9.55中列出，而统计性聚集是在表 9.56中列出。
内建的组内有序集聚集函数在表 9.57中列出，而内建的组内假想集聚集在表 9.58中列出。
与聚集函数紧密相关的分组操作在表 9.59中列出。
第 4.2.7 节中会解释针对聚集函数的特殊语法考虑。额外的介绍信息请参考第 2.7 节。
支持部分模式的聚合函数具备参与各种优化的条件，例如并行聚合。
表 9.55. 通用聚集函数
函数
描述
| 部分模式 |
|---|
array_agg ( anynonarray )
→ anyarray
将所有输入值，包括空值，收集到一个数组中。
| No |
array_agg ( anyarray )
→ anyarray
将所有输入数组连接到一个更高维度的数组中。(输入必须都具有相同的维度，并且不能为空的(empty)或空值(null)。)
| No |
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
计算所有非空输入值的逐位AND。
| Yes |
bit_or ( smallint )
→ smallint
bit_or ( integer )
→ integer
bit_or ( bigint )
→ bigint
bit_or ( bit )
→ bit
计算所有非空输入值的逐位OR。
| Yes |
bool_and ( boolean )
→ boolean
如果全部非空输入值都为真则返回真，否则返回假。
| Yes |
bool_or ( boolean )
→ boolean
如果任何非空输入值为真则返回真，否则返回假。
| Yes |
count ( * )
→ bigint
计算输入行的数量。
| Yes |
count ( "any" )
→ bigint
计算输入值不为空的输入行的数量。
| Yes |
every ( boolean )
→ boolean
这是对应bool_and的SQL标准的等效物。
| Yes |
json_agg ( anyelement )
→ json
jsonb_agg ( anyelement )
→ jsonb
收集所有输入值，包括空值，到一个JSON数组。根据to_json或to_jsonb将值转换为JSON。
| No |
json_object_agg ( key
"any", value
"any" )
→ json
jsonb_object_agg ( key
"any", value
"any" )
→ jsonb
将所有键/值对收集到一个JSON对象中。关键参数强制转换为文本；值参数按照to_json或to_jsonb进行转换。
值可以为空，但键不能（为空）。
| No |
max ( see text )
→ same as input type
计算非空输入值的最大值。适用于任何数字、字符串、日期/时间或enum类型，
以及inet, interval, money, oid, pg_lsn,tid和任何这些类型的数组。
| Yes |
min ( see text )
→ same as input type
计算非空输入值的最小值。可用于任何数字、字符串、日期/时间或enum类型，
以及inet, interval,money, oid, pg_lsn,tid和任何这些类型的数组。
| Yes |
string_agg ( value
text, delimiter text )
→ text
string_agg ( value
bytea, delimiter bytea )
→ bytea
连接非空输入值到字符串中。第一个值之后的每个值前面都有相应的分隔符(delimiter)(如果它不为空)。
| No |
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 )
→ xml
连接非空的XML输入值(参见第 9.15.1.7 节)。
| No |
应该注意的是，除了count之外，这些函数在没有选择行时返回空值。
特别地，行数的sum返回空(null)，而不是预期的零，array_agg在没有输入行时返回空(null)而不是空数组。
coalesce函数可以在必要时用零或空数组代替空(null)。
聚合函数 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;
注意，如果外部查询级别包含其他处理，例如关联，则此方法可能会失败，因为这可能导致子查询的输出在计算聚合之前重新排序。
注意
布尔聚合 bool_and 和 bool_or 对应于标准SQL聚合 every 和 any 或 some.
PostgreSQL 支持 every, 但不支持 any 或 some, 因为标准语法中存在模糊性:
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
这里ANY可以被认为是引入子查询，或者是聚合函数，如果子查询返回一行布尔值。因此，不能为这些聚合提供标准名称。
注意
习惯使用其他SQL数据库管理系统的用户可能会对count聚合应用于整个表时的性能感到失望。一个类似下面的查询:
SELECT count(*) FROM sometable;
将需要与表大小成比例的工作:PostgreSQL将需要扫描整个表或包含表中所有行的索引。
表 9.56显示了统计分析中常用的聚合函数。
(这些被分离出来仅仅是为了避免使更常用的聚合列表混乱。)
显示为接受numeric_type的函数可用于所有类型smallint, integer,bigint, numeric, real, 和 double precision。
在描述中提及N时，它意味着所有输入表达式都非空的输入行数。在所有情况下，如果计算没有意义，则返回null，例如当N为0时。
表 9.56. 用于统计的聚集函数
函数
描述
| 部分模式 |
|---|
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 of products”，
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 |
表 9.57显示了一些使用ordered-set aggregate语法的聚合函数。
这些函数有时被称为“inverse distribution”函数。
它们的聚合输入是通过ORDER BY引入的，它们还可以接受未聚合的direct argument，但只计算一次。
所有这些函数在其聚合的输入中都忽略空(null)值。
对于使用fraction(fraction)参数的函数，分数值必须在0到1之间;否则将抛出一个错误。但是，空分数值简单地产生一个空结果。
表 9.57. 有序集聚集函数
函数
描述
| 部分模式 |
|---|
mode () WITHIN GROUP ( ORDER BY anyelement )
→ anyelement
计算mode，即聚合参数最频繁的值(如果有多个相同频繁的值，第一个可以任意选择)。聚合参数必须是可排序类型。
| 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
计算continuous percentile，该值对应于聚合参数值的有序集合中的指定分数(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
计算离散百分比(discrete percentile)，即聚合参数值的有序集合中的第一个值，该值在排序中的位置等于或超过指定的fraction。
聚合参数必须是可排序类型。
| No |
percentile_disc ( fractions double precision[] ) WITHIN GROUP ( ORDER BY anyelement )
→ anyarray
计算多个离散百分位数。
结果是一个与fractions参数具有相同维数的数组，每个非空元素都被对应于该百分位的输入值替换。
聚合参数必须是可排序类型。
| No |
列在表 9.58中的每个“hypothetical-set”聚合都与第 9.22 节中定义的同名窗口函数相关联。
在每种情况下，聚合的结果都是相关的窗口函数将为由args构造的“hypothetical”行返回的值，如果将这样的行添加到sorted_args表示的已排序行组中。
对于这些函数中的每一个，args中给出的直接参数列表必须与sorted_args中给出的聚合参数的数量和类型匹配。
与大多数内置聚合不同，这些聚合不是严格的，也就是说它们不会删除包含空值的输入行。空值根据ORDER BY子句中指定的规则排序。
表 9.58. 假想集聚集函数
函数
描述
| 部分模式 |
|---|
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 |
表 9.59. 分组操作
函数
描述
|
|---|
GROUPING ( group_by_expression(s) )
→ integer
返回一个位掩码以指示哪个GROUP BY表达式没有包含在当前分组集中。
比特位被分配给最右边的参数对应于最低有效位;如果对应的表达式包含在生成当前结果行的分组集的分组条件中，则每个位为0，如果不包含则为1。
|
表 9.59所示的分组操作与分组集(参见第 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都没有在最后一行中分组(因此，这是所有输入行的聚合)。
