PostgerSQL技术问答10 - 聚合函数 Aggregate Function
本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：
文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。
本文笔者想要来讨论一下在PostgreSQL中的聚合函数和计算。
什么是聚合函数
聚合函数(Aggregate Functions)就是分类聚合统计的方法。它可以将数据表或者数据集的内容，通过指定的字段进行分类(分组)，然后使用其他字段和特性在分组内部进行聚合计算，就是对每种分组的特性都进行某种形式的统计分析。常见的聚合计算方式包括集合的计数、求和、求平均数、求最大最小值等等。
聚合函数是一种简单、基础而常用的分类汇总统计的方式。在SQL语言中，它也是标准化的SQL数据库的功能特性和语句。
如何使用聚合函数
作为标准的SQL语句，聚合函数的标准形式是:
select field..., agg_fn(fieldvalue)... group by field...
下面我们通过一个简单的例子来进行说明：
// 数据表 D 的结构包括员工名称、部门代码和性别
name | dep | gender
--------+-----+--------
John | 1 | 1
Tom | 2 | 1
Marry | 1 | 2
Season | 2 | 2
Noah | 1 | 1
Smith | 2 | 1
Alice | 1 | 2
// 统计每个部门的员工人数
select dep, count(1) ecount from D group by dep;
dep | ecount
-----+--------
1 | 4
2 | 3
(2 rows)
这里使用聚合函数的要点是：
- 聚合函数其实是通用查询语句Select的一种应用模式
- 使用Group By关键字结合聚合函数，来表明这个语句是一个聚合查询
- 查询结果字段中，只能包括聚合函数和聚合引用的字段
- 聚合引用字段和聚合函数字段都可以省略，但省略后不利用后续数据使用(聚合数据无依托和意义了)
- 可以使用多个不同类型的聚合函数，如同时计算计数、求和和平均数等等
有什么需要注意的问题
有时候使用聚合函数，可能不是开发者想象的那样。它有一些限制，但也有一些灵活的地方：
- 可以使用多个聚合分类的字段，但在查询中，不能包括不是聚合字段或者函数的
比如，在上个例子中，除了dep作为聚合依据之外，如果还引用name，这个语句是可能会出错的：
select dep, name, count(1) ecount from D group by dep;
ERROR: column "d.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 10: select dep, name, count(1) ecount from D group by dep;
// 改进后的查询
select dep, max(name), count(1) ecount from D group by dep;
dep | max | ecount
-----+------+--------
1 | Noah | 4
2 | Tom | 3
这个报错的意思是，如果你要使用name字段，那么name就必须包括在group by子句当中作为聚合计算字段使用。显然，name作为聚合是没有意义的。但如果确实需要引用这个name(比如业务需求随便显示一个什么数据)，可以强行使用max或者min聚合函数。
- Group By子句其实是可以省略的，这时聚合的依据就是整个数据集，而且查询字段不能包括任何非聚合函数
select sum(3),avg(4), count(2) ecount from D;
sum | avg | ecount
-----+--------------------+--------
21 | 4.0000000000000000 | 7
- 可以使用多个聚合字段
可以同时进行多种形式的聚合计数，例如同时计算数量、求和和平均值(如上面的例子)。
- 聚合函数可以使用字段值，也可以使用外部数值
还是如上面的例子。但需要注意，必须是所支持的数据类型。
- PostgerSQL可以使用字段顺序作为逻辑字段名称
直接描述有点抽象，我们来看看下面的用例：
select dep, gender, count(1) ecount from D group by 1,2 order by 2, 1;
dep | gender | ecount
-----+--------+--------
1 | 1 | 2
2 | 1 | 2
1 | 2 | 2
2 | 2 | 1
(4 rows)
正常情况下，出现在group by 中的，应该是字段名称，但如果已经在select中声明了聚合分组字段，group by可以使用顺序编号作为逻辑字段，比如本例中的1，2等等。后续的排序方式也可以这么用。
- 聚合计算的是集合的某个特性，它无法和集合中的具体记录关联起来
这个其实是由聚合计算的特性决定的，对于初学者而言，表面上也是普通聚合函数最大的一个不足的地方。比如，聚合计算可以求最大值，但无法得知这个最大值对应的那条记录。
要实现这个目的，使用聚合函数本身是做不到的。即使通过将聚合结果记录再次关联查询原始记录集，看起来能够工作，但逻辑上也是不足的。比如，如何处理多个相同聚合结果的记录，如何处理排序中间值等等。最终的解决方案，其实是使用更强大的窗口函数Windows Function(笔者有机会另行著文探讨)，这就是另外一个课题了。
PostgreSQL支持那些聚合函数
这部分的主要内容来自PG官方技术文档的相关章节，笔者按照自己的理解进行了整理：
www.postgresql.org/docs/15/fun…
PostgreSQL支持的标准的用于统计聚合函数包括：
- max 聚合分组最大值，字段是可以比较的类型，包括数组、字符串等等
- min 聚合分组最小值，字段类型同max
- count 每个聚合分组的记录数量，字段类型不限
- avg 聚合分组求平均值，字段必须是数值类型
- sum 聚合分组求和，字段必须是数值类型
当然，PG也提供更加高级的统计学分析方法，包括：
- 总体方差var_pop、样本方差var_samp(原variance)
- 总体标准差stddev_pop(原stddev)、样本标准差stddev_samp
- 总体协方差covar_pop、样本协方差covar_samp
- 自变平均值regr_avgx、因变平均值regr_avgy
- 自变平方和regr_sxx、自变因变乘积和regr_sxy、因变平方和regr_syyy
- 相关系数corr、相关系数平方regr_r2
- 最小二乘方程斜率regr_slope、最小二乘方程截距regr_intercept
- ...
这部分内容，大多都是和更高级的统计学方法相关的，在日常应用中涉及的不多，这里就不详细展开叙述了，有兴趣的读者可以自行查阅相关技术文档。
除此之外，Postgres还提供一些扩展的聚合操作，它们虽然可能没有太多统计和数学意义，但是可以提供某些业务开发的方便性。
- array_agg(expression), string_agg,xmlagg
这些聚合方法，可以在分组内，对数据进行转换。如将分组内的记录字段值，转换为数组形式、字符串形式(按照指定的字符进行分隔)或者XML形式。
这种操作在业务系统中非常常用，下面是一个简单的例子：
with E(name,dep,gender) as ( values
('John', 1 ,1),
('Tom', 2 ,1),
('Marry', 1 ,2),
('Season', 2 ,2),
('Noah', 1 ,1),
('Smith', 2 ,1),
('Alice', 1 ,2)
)
select dep, array_agg(name),string_agg(name,',')
,xmlagg(XMLELEMENT(
name EMPLOYEE,
XMLATTRIBUTES(name, dep, gender)
))
from E group by 1;
dep | array_agg | string_agg | xmlagg
-----+-------------------------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | {John,Marry,Noah,Alice} | John,Marry,Noah,Alice | <employee name="John" dep="1" gender="1"/><employee name="Marry" dep="1" gender="2"/><employee name="Noah" dep="1" gender="1"/><employee name="Alice" dep="1" gender="2"/>
2 | {Tom,Season,Smith} | Tom,Season,Smith | <employee name="Tom" dep="2" gender="1"/><employee name="Season" dep="2" gender="2"/><employee name="Smith" dep="2" gender="1"/>
(2 rows)
- json_agg，jsonb_agg
可以将记录值转换成为JSON对象并聚合成为JSON数组。
- bool_and(expression)，bool_or(expression)，every
可以在分组内，做布尔运算。只支持布尔类型。其实，every和bool_and是等效的，更好理解而已。
- bit_and(expression)，bit_or
可以在分组内，做位运算，求分组内所有值的与值和或值。支持整数类型，但不支持空值。
- range_agg，range_intersect_agg
对范围进行聚合。返回并集或者交集。
聚合查询中，使用Count(* )和Count(field)有什么区别吗？
其实是有区别的。简单而言Count* 或者Count(1)，是对所有记录进行计数。而Count(field)，则只对计数字段中非 null 的记录进行计数。这里可能有一些业务方面的差异，所以在具体应用中，要特别注意这一点。
可不可以进行多维多重聚合计算
在前面的例子中，我们看到了可以在聚合计算中指定聚合计算的字段，然后数据库就可以将聚合计算细分到这些字段进行操作。但在实际的业务场景中，我们可能还需要对这些结果进行一个再次分组的小结，就是经常见到的“分类小计”。
这种数据分析功能，在Execl里面的名称叫“数据透视表”。它的本质就是可以将数据按照多个维度进行聚合计算，从而达到可以一次性的从多个角度对原始数据进行统计分析的效果。在PG中，作为更强大的数据库系统和分析工具，当然也提供了类似的功能和操作，名为“cube”(立方)，笔者理解应该是多维数据分析的意思。
这样的描述可能有点抽象，我们举一个非常简单的例子就容易理解了：
// 有数据(CTE定义)如下， 需要统计每个部门的员工总数、男员工和女员工数：
with E(name,dep,gender) as ( values
('John', 1 ,1),
('Tom', 2 ,1),
('Marry', 1 ,2),
('Season', 2 ,2),
('Noah', 1 ,1),
('Smith', 2 ,1),
('Alice', 1 ,2))
// 可以使用如下聚合计算方式，进行多维度的统计
select dep,gender,count(1) from E group by cube(1, 2) order by 1,2;
dep | gender | count
-----+--------+-------
1 | 1 | 2
1 | 2 | 2
1 | | 4
2 | 1 | 2
2 | 2 | 1
2 | | 3
| 1 | 4
| 2 | 3
| | 7
(9 rows)
例子中我们可以看到，Postgres提供了Cube函数，来进行多维聚合计算，它可以自动的将cube中指定的字段自动分解组合成为各种聚合方式，一次性的计算所有可能的聚合统计结果。非常简单方便。
可以使用聚合计算的结果，来对聚合查询进行筛选吗？
是可以的，但需要使用Having子句。笔者会在另外一篇博文中详细探讨。
可以对聚合查询项目，进行筛选吗？
也是可以的，具体而言是使用filter子句，同样笔者会在后续博文中进行探讨。
小结
本文探讨了PostgreSQL中，基本的统计和分析功能集：聚合函数和查询。包括其基本概念，使用场景，支持的聚合算法，一般SQL语法和结构，以及使用中需要注意的问题等等。
转载自:https://juejin.cn/post/7373891594234282047