9.20. 聚集函数

聚集函数从一个输入值的集合计算一个单一结果。内建的聚集函数被列在Table 9-46Table 9-47中。聚集函数的特殊语法考虑在Section 4.2.7中解释。更多介绍性信息请参考Section 2.7

Table 9-46. 通用聚集函数

函数参数类型返回类型描述
array_agg(expression) any 参数类型的数组 输入值(包括空)被连接到一个数组
avg(expression) smallint, int, bigintrealdouble precisionnumericinterval 对于任何整数类型参数是numeric,对于一个浮点参数是double precision,否则和参数数据类型相同 所有输入值的平均值(算术平均)
bit_and(expression) smallintintbigintbit 与参数数据类型相同 所有非空输入值的按位与,如果没有非空值则结果是空值
bit_or(expression) smallint, int, bigint, or bit 与参数数据类型相同 所有非空输入值的按位或,如果没有非空值则结果是空值
bool_and(expression) bool bool 如果所有输入值为真则结果为真,否则为假
bool_or(expression) bool bool 至少一个输入值为真时结果为真,否则为假
count(*)  bigint输入的行数
count(expression)anybigint expression值非空的输入行的数目
every(expression) bool bool 等价于bool_and
json_agg(record) record json 将记录聚集成一个对象的 JSON 数组
max(expression) 任意数组、数字、串或日期/时间类型与参数数据类型相同 所有输入值中expression的最大值
min(expression) 任意数组、数字、串或日期/时间类型与参数数据类型相同 所有输入值中expression的最小值
string_agg(expression, delimiter) (text, text) 或 (bytea, bytea) 与参数数据类型相同 输入值连接成一个串,用定界符分隔
sum(expression) smallintintbigintrealdouble precisionnumericinterval smallintint参数是bigint,对bigint参数是numeric,对浮点参数是double precision,否则和参数数据类型相同 所有输入值的expression的和
xmlagg(expression) xml xml 连接 XML 值(参见Section 9.14.1.7

请注意,除了count以外,这些函数在没有行被选中时返回控制。尤其是sum函数在没有输入行时返回空值,而不是零,并且array_agg在这种情况返回空值而不是一个空数组。必要时可以用coalesce把空值替换成零或一个空数组。

Note: 布尔聚集bool_andbool_or对应于标准的 SQL 聚集everyanysome。而对于anysome,似乎在标准语法中有一个歧义:

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

如果子查询返回一行有一个布尔值的结果,这里的ANY可以被认为是引入一个子查询,或者是作为一个聚集函数。因而标准的名称不能指定给这些聚集。

Note: 在把count聚集应用到整个表上时,习惯于使用其他 SQL 数据管理系统的用户可能会对它的性能感到失望。一个如下的查询:

SELECT count(*) FROM sometable;

将会要求与整个表大小成比例的工作:PostgreSQL将需要扫描整个表或者整个包含表中所有行的索引。

与相似的用户定义的聚集函数一样,聚集函数array_aggjson_aggstring_aggxmlagg会依赖输入值的顺序产生有意义的不同结果值。这个顺序默认是不用指定的,但是可以在聚集调用时使用ORDER BY子句进行控制,如Section 4.2.7中所示。作为一种选择,从一个排序号的子查询来提供输入值通常会有帮助。例如:

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

但是在 SQL 标准中不允许这种语法,并且不能被移植到其他数据库系统。

Table 9-47展示了通常被用在统计分析中的聚集函数(这些被隔离出来是为了不和常用聚集混淆)。其中描述提到了N,它表示对应于所有非空输入表达式的输入行的数目。在所有情况中,如果计算是无意义的,将会返回空值,例如当N为零。

Table 9-47. 用于统计的聚集函数

函数参数类型返回类型描述
corr(Y, X) double precision double precision 相关系数
covar_pop(Y, X) double precision double precision 总体协方差
covar_samp(Y, X) double precision double precision 样本协方差
regr_avgx(Y, X) double precision double precision 自变量的平均值 (sum(X)/N
regr_avgy(Y, X) double precision double precision 因变量的平均值 (sum(Y)/N
regr_count(Y, X) double precision bigint 两个表达式都不为空的输入行的数目
regr_intercept(Y, X) double precision double precision 由(X, Y)对决定的最小二乘拟合的线性方程的 y截距
regr_r2(Y, X) double precision double precision 相关系数的平方
regr_slope(Y, X) double precision double precision 由(X, Y)对决定的最小二乘拟合的线性方程的斜率
regr_sxx(Y, X) double precision double precision sum(X^2) - sum(X)^2/N(自变量的"平方和"
regr_sxy(Y, X) double precision double precision sum(X*Y) - sum(X) * sum(Y)/N(自变量乘以因变量的"积之合"
regr_syy(Y, X) double precision double precision sum(Y^2) - sum(Y)^2/N(因变量的"平方和"
stddev(expression) smallintintbigintrealdouble precisionnumeric 浮点参数为double precision,否则为numeric stddev_samp的历史别名
stddev_pop(expression) smallintintbigintrealdouble precisionnumeric 浮点参数为double precision,否则为numeric 输入值的总体标准偏差
stddev_samp(expression) smallintintbigintrealdouble precisionnumeric 浮点参数为double precision,否则为numeric 输入值的样本标准偏差
variance(expression) smallintintbigintrealdouble precisionnumeric 浮点参数为double precision,否则为numeric var_samp的历史别名
var_pop(expression) smallintintbigintrealdouble precisionnumeric 浮点参数为double precision,否则为numeric 输入值的总体方差(总体标准偏差的平方)
var_samp(expression) smallintintbigintrealdouble precisionnumeric 浮点参数为double precision,否则为numeric 输入值的样本方差(样本标准偏差的平方)