聚集函数

聚集函数¶

聚集函数是指通过一个输入值的集合计算出一个单一值的函数。内建的聚集函数包括：通用聚集函数、统计性聚集函数、组内有序集聚集函数、组内假想集聚集函数。聚集函数与分组操作紧密相关。

通用聚集函数¶

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

支持部分模式的聚集函数可参与到各种优化中，例如并行聚集。

`array_agg()`¶

语法: array_agg(expression)
参数类型: 任何非数组类型或任意数组类型
返回类型: 当参数为非数组类型时，返回参数类型的数组; 参数为数组类型时，返回类型与参数数据类型相同
部分模式: No
描述: 当参数为非数组类型时，输入值（包括空）被连接到一个数组中并返回; 参数为数组类型时，输入数组被串接到一个更高维度的数组中（输入必须都具有相同的维度并且不能为空或者 NULL）

`avg()`¶

语法
： avg(expression)

参数类型
： smallint, int, bigint、real、double precision、numeric或interval

返回类型
：对于任何整数类型参数是numeric，对于一个浮点参数是double precision，否则和参数数据类型相同

部分模式
： Yes

描述
：所有输入值的平均值（算术平均）

`bit_and()`¶

语法: bit_and(expression)
参数类型: smallint、int、bigint或bit
返回类型: 与参数数据类型相同
部分模式: Yes
描述: 所有非空输入值的按位与，如果没有非空值则结果是空值

`bit_or()`¶

语法: bit_or(expression)
参数类型: smallint, int, bigint, or bit
返回类型: 与参数数据类型相同
部分模式: Yes
描述: 所有非空输入值的按位或，如果没有非空值则结果是空值

`bool_and()`¶

语法: bool_and(expression)
参数类型: bool
返回类型: bool
部分模式: Yes
描述: 如果所有输入值为真则结果为真，否则为假

`bool_or()`¶

语法: bool_or(expression)
参数类型: bool
返回类型: bool
部分模式: Yes
描述: 至少一个输入值为真时结果为真，否则为假

`count()`¶

语法: count([*,expression])
参数类型: any
返回类型: bigint
部分模式: Yes
描述: 参数为*时返回输入的行数，参数为expression时，返回expression值非空的输入行的数目

`every()`¶

语法: every(expression)
参数类型: bool
返回类型: bool
部分模式: Yes
描述: 等价于bool_and

`json_agg()`¶

语法: json_agg(expression)
参数类型: any
返回类型: json
部分模式: No
描述: 将值聚集成一个 JSON 数组

`jsonb_agg()`¶

语法: jsonb_agg(expression)
参数类型: any
返回类型: jsonb
部分模式: No
描述: 把值聚合成一个 JSON 数组

`json_object_agg()/jsonb_object_agg()`¶

语法: json_object_agg(name, value), jsonb_object_agg(name, value)
参数类型: (any, any)
返回类型: json
部分模式: No
描述: 将名字/值对聚集成一个 JSON 对象

`max()`¶

语法: max(expression)
参数类型: 任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组
返回类型: 与参数数据类型相同
部分模式: Yes
描述: 所有输入值中expression的最大值

`min()`¶

语法: min(expression)
参数类型: 任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组
返回类型: 与参数数据类型相同
部分模式: Yes
描述: 所有输入值中expression的最小值

`string_agg()`¶

语法: string_agg(expression,delimiter)
参数类型: (text, text) 或 (bytea, bytea)
返回类型: 与参数数据类型相同
部分模式: No
描述: 输入值连接成一个串，用定界符分隔

`sum()`¶

语法: sum(expression)
参数类型: smallint、int、 bigint、real、double precision、numeric、 interval或money
返回类型: 对smallint或int参数是bigint，对bigint参数是numeric，否则和参数数据类型相同
部分模式: Yes
描述: 所有输入值的expression的和

`xmlagg()`¶

语法: xmlagg(expression)
参数类型: xml
返回类型: xml
部分模式: No
描述: 连接 XML 值（参见XML 函数）

注意1:

布尔聚集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;

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

与相似的用户定义的聚集函数一样，聚集函数array_agg、json_agg、jsonb_agg、json_object_agg、jsonb_object_agg、string_agg和xmlagg会依赖输入值的顺序产生有意义的不同结果值。这个顺序默认是不用指定的，但是可以在聚集调用时使用ORDER BY子句进行控制，如聚集表达式中所示。作为一种选择，从一个排序号的子查询来提供输入值通常会有帮助。例如：

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

如果外面的查询层次包含额外的处理（例如连接），这种方法可能会失败，因为这可能导致子查询的输出在计算聚集之前被重新排序。

用于统计的聚集函数¶

本节描述通常被用在统计分析中的聚集函数。描述中提到的 N 表示对应于所有非空输入表达式的输入行的数目。在所有情况中，如果计算是无意义的，将会返回空值，例如当 N 为零。

`corr()`¶

语法: corr(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: 相关系数

`covar_pop()`¶

语法: covar_pop(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: 总体协方差

`covar_samp()`¶

语法: covar_samp(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: 样本协方差

`regr_avgx()`¶

语法: regr_avgx(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: 自变量的平均值（sum(X)/N）

`regr_avgy()`¶

语法: regr_avgy(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: 因变量的平均值（sum(Y)/N）

`regr_count()`¶

语法: regr_count(Y,X)
参数类型: double precision
返回类型: bigint
部分模式: Yes
描述: 两个表达式都不为空的输入行的数目

`regr_intercept()`¶

语法: regr_intercept(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: 由（X,Y）对决定的最小二乘拟合的线性方程的 y截距

`regr_r2()`¶

语法: regr_r2(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: 相关系数的平方

`regr_slope()`¶

语法: regr_slope(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: 由（X,Y）对决定的最小二乘拟合的线性方程的斜率

`regr_sxx()`¶

语法: regr_sxx(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: sum(X^2) - sum(X)^2/N（自变量的“平方和”）

`regr_sxy()`¶

语法: regr_sxy(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: sum(X * Y) - sum(X) * sum(Y)/N（自变量乘以因变量的“积之合”）

`regr_syy()`¶

语法: regr_syy(Y,X)
参数类型: double precision
返回类型: double precision
部分模式: Yes
描述: sum(Y^2) - sum(Y)^2/N（因变量的“平方和”）

`stddev()`¶

语法: stddev(expression)
参数类型: smallint、int、 bigint、real、double precision或numeric
返回类型: 浮点参数为double precision，否则为numeric
部分模式: Yes
描述: stddev_samp的历史别名

`stddev_pop()`¶

语法: stddev_pop(expression)
参数类型: smallint、int、 bigint、real、double precision或numeric
返回类型: 浮点参数为double precision，否则为numeric
部分模式: Yes
描述: 输入值的总体标准偏差

`stddev_samp()`¶

语法: stddev_samp(expression)
参数类型: smallint、int、 bigint、real、double precision或numeric
返回类型: 浮点参数为double precision，否则为numeric
部分模式: Yes
描述: 输入值的样本标准偏差

`variance()`¶

语法: variance(expression)
参数类型: smallint、int、 bigint、real、double precision或numeric
返回类型: 浮点参数为double precision，否则为numeric
部分模式: Yes
描述: var_samp的历史别名

`var_pop()`¶

语法: var_pop(expression)
参数类型: smallint、int、 bigint、real、double precision或numeric
返回类型: 浮点参数为double precision，否则为numeric
部分模式: Yes
描述: 输入值的总体方差（总体标准偏差的平方）

`var_samp()`¶

语法: var_samp(expression)
参数类型: smallint、int、 bigint、real、double precision或numeric
返回类型: 浮点参数为double precision，否则为numeric
部分模式: Yes
描述: 输入值的样本方差（样本标准偏差的平方）

有序集聚集函数¶

以下是一些使用有序集聚集语法的聚集函数。这些函数有时也被称为“逆分布”函数。

以下所有的聚集会忽略它们的已排序输入中的空值。对那些有一个 fraction 参数的聚集来说，该分数值必须位于 0 和 1 之间，否则会抛出错误。不过，一个空分数值会产生一个空结果。

`mode()`¶

语法: mode() WITHIN GROUP (ORDER BY sort_expression)
聚集参数类型: 任何可排序类型
返回类型: 与排序表达式相同
部分模式: No
描述: 返回最频繁的输入值（如果有多个频度相同的值就选第一个）

`percentile_cont()`¶

语法: percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)
直接参数类型: double precision或double precision[]
聚集参数类型: double precision或者interval
返回类型: 与排序表达式相同
部分模式: No
描述: 连续百分率：返回一个对应于排序中指定分数的值，如有必要就在相邻的输入项之间插值; 参数为数组时为多重连续百分率：返回一个匹配fractions参数形状的结果数组，其中每一个非空元素都用对应于那个百分率的值替换

`percentile_disc()`¶

语法: percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)
直接参数类型: double precision或double precision[]
聚集参数类型: 一种可排序类型
返回类型: 与排序表达式相同
部分模式: No
描述: 离散百分率：返回第一个在排序中位置等于或者超过指定分数的输入值; 参数为数组时为多重离散百分率：返回一个匹配fractions参数形状的结果数组，其中每一个非空元素都用对应于那个百分率的输入值替换

假想集聚集函数¶

以下的每个聚集都与一个定义在窗口函数中的同名窗口函数相关联。在每种情况中，聚集结果的计算方法是：假设根据 args 构建的“假想”行已经被增加到从 sorted_args 计算得到的已排序行分组中，然后用相关联的窗口函数针对该行返回的值就是聚集的结果。

对于这些假想集聚集的每一个，args 中给定的直接参数列表必须匹配 sorted_args 中给定的聚集参数的数量和类型。与大部分的内建聚集不同，这些聚集并不严格，即它们不会丢弃包含空值的输入行。空值的排序根据ORDER BY子句中指定的规则进行。

`rank()`¶

语法: rank(args) WITHIN GROUP (ORDER BY sorted_args)
直接参数类型: VARIADIC "any"
聚集参数类型: VARIADIC "any"
返回类型: bigint
部分模式: No
描述: 假想行的排名，为重复的行留下间隔

`dense_rank()`¶

语法: dense_rank(args) WITHIN GROUP (ORDER BY sorted_args)
直接参数类型: VARIADIC "any"
聚集参数类型: VARIADIC "any"
返回类型: bigint
部分模式: No
描述: 假想行的排名，不留间隔

`percent_rank()`¶

语法: percent_rank(args) WITHIN GROUP (ORDER BY sorted_args)
直接参数类型: VARIADIC "any"
聚集参数类型: VARIADIC "any"
返回类型: double precision
部分模式: No
描述: 假想行的相对排名，范围从 0 到 1

`cume_dist()`¶

语法: cume_dist(args) WITHIN GROUP (ORDER BY sorted_args)
直接参数类型: VARIADIC "any"
聚集参数类型: VARIADIC "any"
返回类型: double precision
部分模式: No
描述: 假想行的相对排名，范围从 1/N 到 1

分组操作¶

`GROUPING()`¶

语法: GROUPING(args…)
返回类型: integer
描述: 整数位掩码指示哪些参数不被包括在当前分组集合中

分组操作用来与分组集合（见GROUPING SETS、CUBE和ROLLUP）共同来区分结果行。GROUPING操作的参数并不会被实际计算，但是它们必须准确地匹配在相关查询层次的GROUP BY子句中给定的表达式。最右边参数指派的位是最低有效位，如果对应的表达式被包括在产生结果行的分组集合的分组条件中则每一位是 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)

聚集函数

聚集函数¶

通用聚集函数¶

array_agg()¶

avg()¶

bit_and()¶

bit_or()¶

bool_and()¶

bool_or()¶

count()¶

every()¶

json_agg()¶

jsonb_agg()¶

json_object_agg()/jsonb_object_agg()¶

max()¶

min()¶

string_agg()¶

sum()¶

xmlagg()¶

用于统计的聚集函数¶

corr()¶

covar_pop()¶

covar_samp()¶

regr_avgx()¶

regr_avgy()¶

regr_count()¶

regr_intercept()¶

regr_r2()¶

regr_slope()¶

regr_sxx()¶

regr_sxy()¶

regr_syy()¶

stddev()¶

stddev_pop()¶

stddev_samp()¶

variance()¶

var_pop()¶

var_samp()¶