9.21. 聚集函数

聚集函数从一个输入值的集合计算出一个单一值。 内建的通用聚集函数在表 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_jsonto_jsonb将值转换为JSON。

No

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

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

将所有键/值对收集到一个JSON对象中。关键参数强制转换为文本;值参数按照to_jsonto_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_andbool_or 对应于标准SQL聚合 everyanysome. PostgreSQL 支持 every, 但不支持 anysome, 因为标准语法中存在模糊性:

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

计算由(XY)对决定的最小二乘拟合的线性方程的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 productssum(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)

在这里,前四行中的grouping0表明这些已经正常分组,在两个分组列上。 值1表示model没有在倒数两行中分组,值3表示无论是make还是model都没有在最后一行中分组(因此,这是所有输入行的聚合)。