PostgreSQL 分组汇总（二）

PostgreSQL除了支持基本的GROUP BY分组操作之外，还支持3种高级的分组选项：GROUPING SETS、ROLLUP以及CUBE。

GROUPING SETS选项

GROUPING SETS是GROUP BY的扩展选项，用于指定自定义的分组集。举例来说，以下是一个销售数据表：

CREATE TABLE sales
(
    item     VARCHAR(10),
    year     VARCHAR(4),
    quantity INT
);
INSERT INTO sales
VALUES ('apple', '2018', 800);
INSERT INTO sales
VALUES ('apple', '2018', 1000);
INSERT INTO sales
VALUES ('banana', '2018', 500);
INSERT INTO sales
VALUES ('banana', '2018', 600);
INSERT INTO sales
VALUES ('apple', '2019', 1200);
INSERT INTO sales
VALUES ('banana', '2019', 1800);
select * from sales;

按照产品（item）和年度（year）进行分组汇总时，所有可能的4种分组集包括：

• 按照产品和年度的组合进行分组；
• 按照产品进行分组；
• 按照年度进行分组；
• 所有数据分为一组；

可以通过以下多个查询获取所有分组集的分组结果：

--按照产品和年度的组合进行分组
SELECT item, year, SUM(quantity) sum_quantity
FROM sales
GROUP BY item, year
union all
--按照产品进行分组
SELECT item, null as year, SUM(quantity) sum_quantity
FROM sales
GROUP BY item
union all
--按照年度进行分组
SELECT null as item, year, SUM(quantity) sum_quantity
FROM sales
GROUP BY year
union all
--所有数据分为一组
SELECT null as item, null as year, SUM(quantity) sum_quantity
FROM sales;

这种方法存在一些问题：首先，查询语句比较冗长，查询的次数随着分组字段的增加呈指数增长；其次，多次查询意味着需要多次扫描同一张表，存在性能上的问题。GROUPING SETS是GROUP BY的扩展选项，能够为这种查询需求提供更加简单有效的解决方法。我们使用分组集改写上面的示例：

SELECT item, year, SUM(quantity) sum_quantity
FROM sales
GROUP BY grouping sets ((item,year),(item),(year),());

GROUPING SETS选项用于定义分组集，每个分组集都需要包含在单独的括号中，空白的括号（()）表示将所有数据当作一个组处理。查询的结果等于前文4个查询的合并结果，但是语句更少，可读性更强；而且PostgreSQL执行时只需要扫描一次销售表，性能更加优化。另外，默认的GROUP BY使用由所有分组字段构成的一个分组集，本示例中为((item, year))。

CUBE选项

随着分组字段的增加，即使通过GROUPING SETS列出所有可能的分组方式也会显得比较麻烦。设想一下使用4个字段进行分组统计的场景，所有可能的分组集共计有16个。这种情况下编写查询语句仍然很复杂，为此PostgreSQL提供了简写形式的GROUPING SETS：CUBE和ROLLUP。CUBE表示所有可能的分组集，例如：

CUBE ( c1, c2, c3 )

等价于：

GROUPING SETS (( c1, c2, c3 ),( c1, c2 ),( c1, c3 ),( c2, c3 ),( c1),( c2 ),( c3 ),( ))

因此，可以进一步将上面的示例改写如下：

SELECT item, year, SUM(quantity) sum_quantity
FROM sales
GROUP BY cube (item, year);

ROLLUP选项

GROUPING SETS第二种简写形式就是ROLLUP，用于生成按照层级进行汇总的结果，类似于财务报表中的小计、合计和总计。例如：

ROLLUP ( c1, c2, c3 )

等价于：

GROUPING SETS (( c1, c2, c3 ),( e1, e2 ),( e1 ),( ))

以下查询返回按照产品和年度组合进行统计的销量小计，加上按照产品进行统计的销量合计，再加上所有销量的总计：

SELECT item, year, SUM(quantity) sum_quantity
FROM sales
GROUP BY rollup (item, year);

查看结果时，需要根据每个字段上的空值进行判断。比如第一行的产品和年度都为空，因此它是所有销量的总计。为了便于查看，可以将空值进行转换显示：

SELECT coalesce(item, '所有产品') ·产品·,
       coalesce(year, '所有年度') 年度,
       SUM(quantity)          销量
FROM sales
GROUP BY rollup (item, year);

COALESCE函数返回第一个非空的参数值。可以根据需要返回按照某些组合进行统计的结果，以下查询返回按照产品和年度组合进行统计的销量小计，加上按照产品进行统计的销量合计：

SELECT coalesce(item, '所有产品') ·产品·,
       coalesce(year, '所有年度') 年度,
       SUM(quantity)          销量
FROM sales
GROUP BY item, rollup (year);

对于CUBE和ROLLUP而言，每个元素可以是单独的字段或表达式，也可以是使用括号包含的列表。如果是括号中的列表，产生分组集时它们必须作为一个整体。例如：

CUBE ( (c1, c2), (c3, c4) )

等价于：

GROUPING SETS (( c1, c2, c3, c4 ),( c1, c2 ),( c3, c4 ),( ))

因为c1和c2是一个整体，c3和c4是一个整体。同样：

ROLLUP ( c1, (c2, c3), c4 )

等价于：

GROUPING SETS (( c1, c2, c3, c4 ),( c1, c2, c3 ),( c1 ),( ))

GROUPING函数

虽然有时候可以通过空值来判断数据是不是某个字段上的汇总，比如说按照年度进行统计的结果在字段year上的值为空。但是情况并非总是如此，考虑以下示例：

--未知产品在2018 年的销量为5000
INSERT INTO sales
VALUES (NULL, '2018', 5000);
SELECT coalesce(item, '所有产品') AS "产品", coalesce(year, '所有年度') AS "年度", SUM(quantity) AS "销量"
FROM sales
GROUP BY ROLLUP (item, year);

其中第5行和第7行的显示存在问题，它们分别应该是未知产品在2018年的销量小计和所有年度的销量合计。问题的关键在于无法区分是分组产生的NULL还是源数据中的NULL。为了解决这个问题，PostgreSQL提供了一个分组函数：GROUPING。

以下查询显示了GROUPING函数的结果：

select item          产品,
       year          年度,
       sum(quantity) 销量,
       grouping(item),
       grouping(year),
       grouping(item, year)
from sales
group by rollup (item, year);

GROUPING函数如果只有一个参数，返回整数0或者1。如果某个统计结果使用的分组集包含了函数中的参数字段，该函数返回0，否则返回1。比如说，第1行数据是所有产品所有年度的统计（分组集为空），所以GROUPING(item)和GROUPING(year)结果都是1；第7行数据是未知产品所有年度的统计（分组集为(item, )），所以GROUPING(item)结果为0，GROUPING(year)结果为1。GROUPING函数如果包含多个参数，针对每个参数返回整数0或者1，然后将它们按照二进制数值连接到一起。比如说，第1行数据中的GROUPING(item, year)结果等于GROUPING(item)和GROUPING(year)结果的二进制数值连接，也就是3（二进制的11）。通过使用GROUPING函数，我们可以正确显示分组中的NULL值和源数据中的NULL值：

select case when grouping(item) = 1 then '所有产品' else item end 产品,
       case when grouping(year) = 1 then '所有年度' else year end 年度,
       sum(quantity)                                          销量
from sales
group by rollup (item, year);