PostgreSQL 10.1 手册_部分 II. SQL 语言_第 9 章 函数和操作符_9.20. 聚集函数

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 9.20. 聚集函数 聚集函数从一个输入值的集合计算一个单一结果。内建的通用聚集函数被列在表 9.52中,统计信息聚合函数列在表 9.53中。内建的组内顺序集聚集函数被列在表 9.54中, 而内置的组内假设集函数列在表 9.55中。

9.20. 聚集函数

聚集函数从一个输入值的集合计算一个单一结果。内建的通用聚集函数被列在表 9.52中,统计信息聚合函数列在表 9.53中。内建的组内顺序集聚集函数被列在表 9.54中, 而内置的组内假设集函数列在表 9.55中。分组操作,这与聚集函数有密切联系,被列在表 9.56中。聚集函数的特殊语法考虑在第 4.2.7 节中解释。更多介绍性信息请参考第 2.7 节

表 9.52. 通用聚集函数

函数 参数类型 返回类型 局部模式 描述
array_agg(expression) 任何非数组类型 参数类型的数组 No 输入值(包括空)被连接到一个数组
array_agg(expression) 任何数组类型 和参数数据类型一样 No 级联到更高维数组的输入数组(输入必须都具有相同的维度,不能为空或NULL)
avg(expression) smallintintbigintrealdouble precisionnumericinterval 对于任何整数类型参数是numeric,对于一个浮点参数是double precision,否则和参数数据类型相同 Yes 所有输入值的平均值(算术平均)
bit_and(expression) smallintintbigintbit 与参数数据类型相同 Yes 所有非空输入值的按位与,如果没有非空值则结果是空值
bit_or(expression) smallintintbigint, 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(namevalue) (any, any) json No 将名字/值对聚集成一个 JSON 对象
jsonb_object_agg(namevalue) (any, any) jsonb No 将名字/值对聚集成一个JSON对象
max(expression) 任意数字、字符串,日期/时间,网络,或枚举类型或这些类型数组 与参数数据类型相同 Yes 所有输入值中expression的最大值
min(expression) 任意数字、字符串,日期/时间,网络,或枚举类型或这些类型数组 与参数数据类型相同 Yes 所有输入值中expression的最小值
string_agg(expression,delimiter) (texttext) 或 (byteabytea) 与参数数据类型相同 No 输入值连接成一个串,用定界符分隔
sum(expression) smallintint、 bigintrealdouble precisionnumeric、 intervalmoney smallintint参数是bigint,对bigint参数是numeric,否则和参数数据类型相同 Yes 所有输入值的expression的和
xmlagg(expression) xml xml No 连接 XML 值(参见第 9.14.1.7 节

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

支持局部模式的聚集函数有资格参与到各种优化中, 例如并行聚集。

注意

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

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

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

注意

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

SELECT count(*) FROM sometable;

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

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

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

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

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

表 9.53. 用于统计的聚集函数

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

表 9.54展示了一些使用 有序集聚集语法的聚集函数。这些函数有时也被称为 逆分布函数。

表 9.54. 有序集聚集函数

函数 直接参数类型 聚集参数类型 返回类型 局部模式 描述
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.54中的聚集会忽略它们的已 排序输入中的空值。对那些有一个fraction参数的聚 集来说,该分数值必须位于 0 和 1 之间,否则会抛出错误。不过,一个空分数值会 产生一个空结果。

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

表 9.55. 假想集聚集函数

函数 直接参数类型 聚集参数类型 返回类型 局部模式 描述
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.56. 分组操作

函数 返回类型 描述
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)

本文转自PostgreSQL中文社区,原文链接:9.20. 聚集函数

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
57 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
54 0
|
23天前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
36 0
|
1天前
|
关系型数据库 PostgreSQL
postgresql日程排程函数的编写实例
postgresql日程排程函数的编写实例
|
10天前
|
SQL 存储
SQL Server基本函数
SQL Server基本函数
|
10天前
|
SQL 关系型数据库 分布式数据库
在PolarDB for PostgreSQL中,你可以使用LIKE运算符来实现类似的查询功能,而不是使用IF函数
在PolarDB for PostgreSQL中,你可以使用LIKE运算符来实现类似的查询功能,而不是使用IF函数
27 7
|
23天前
|
SQL 安全 关系型数据库
MySQL技能完整学习列表3、SQL语言基础——3、SQL运算符和函数
MySQL技能完整学习列表3、SQL语言基础——3、SQL运算符和函数
24 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据更新
【数据库SQL server】关系数据库标准语言SQL之数据更新
23 0
|
1月前
|
SQL 数据库 数据库管理
【数据库SQL server】关系数据库标准语言SQL的基本知识
【数据库SQL server】关系数据库标准语言SQL的基本知识
36 0
|
2月前
|
SQL Perl
PL/SQL的函数和包
PL/SQL的函数和包
18 1