SQLGROUPING运算符

Z时代
2024-01-10
分类：综合

database

一、同时得到合计行
二、ROLLUP——同时得出合计和小计
- 2.1 ROLLUP 的使用方法
- 2.2 将“登记日期”添加到聚合键当中
三、GROUPING 函数——让 NULL 更加容易分辨
四、CUBE——用数据来搭积木
五、GROUPING SETS——取得期望的积木
请参阅

学习重点
只使用 GROUP BY 子句和聚合函数是无法同时得出小计和合计的。如果想要同时得到，可以使用 GROUPING 运算符。
理解 GROUPING 运算符中 CUBE 的关键在于形成“积木搭建出的立方体”的印象。
虽然 GROUPING 运算符是标准 SQL 的功能，但还是有些 DBMS 尚未支持这一功能。

一、同时得到合计行

我们在对表进行分组中学习了 GROUP BY 子句和聚合函数的使用方法，可能有些读者会想，是否有办法能够通过 GROUP BY 子句得到表 1 那样的结果呢？

表 1　添加合计行

虽然这是按照商品种类计算销售单价的总额时得到的结果，但问题在于最上面多出了 1 行合计行。使用代码清单 10 中的 GROUP BY 子句的语法无法得到这一行。

代码清单 10　使用 GROUP BY 无法得到合计行

SELECT product_type, SUM(sale_price)
  FROM Product
 GROUP BY product_type;

执行结果

 product_type |  sum
--------------+------
 衣服         |  5000
 办公用品     |   600
 厨房用具     | 11180

因为 GROUP BY 子句是用来指定聚合键的场所，所以只会根据这里指定的键分割数据，当然不会出现合计行。而合计行是不指定聚合键时得到的汇总结果，因此与下面的 3 行通过聚合键得到的结果并不相同。按照通常的思路，想一次得到这两种结果是不可能的。

如果想要获得那样的结果，通常的做法是分别计算出合计行和按照商品种类进行汇总的结果，然后通过 UNION ALL^[1] 连接在一起（代码清单 11）。

KEYWORD
UNION ALL

代码清单 11　分别计算出合计行和汇总结果再通过 UNION ALL 进行连接

SELECT "合计" AS product_type, SUM(sale_price)
  FROM Product
UNION ALL
SELECT product_type, SUM(sale_price)
  FROM Product
GROUP BY product_type;

执行结果

 product_type |  sum
--------------+------
 合计         | 16780
 衣服         |  5000
 办公用品     |   600
 厨房用具     | 11180

这样一来，为了得到想要的结果，需要执行两次几乎相同的 SELECT 语句，再将其结果进行连接，不但看上去十分繁琐，而且 DBMS 内部的处理成本也非常高，难道没有更合适的实现方法了吗？

二、`ROLLUP`——同时得出合计和小计

为了满足用户的需求，标准 SQL 引入了 GROUPING 运算符，我们将在本节中着重介绍。使用该运算符就能通过非常简单的 SQL 得到之前那样的汇总单位不同的汇总结果了。

KEYWORD
GROUPING 运算符

GROUPING 运算符包含以下 3 种 ^[2]。

ROLLUP

CUBE

GROUPING SETS

2.1 `ROLLUP` 的使用方法

我们先从 ROLLUP 开始学习吧。使用 ROLLUP 就可以通过非常简单的 SELECT 语句同时计算出合计行了（代码清单 12）。

KEYWORD
ROLLUP 运算符

代码清单 12　使用 ROLLUP 同时得出合计和小计

Oracle SQL Server DB2 PostgreSQL

SELECT product_type, SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type);-----①

特定的 SQL
在 MySQL 中执行代码清单 12 时，请将 ① 中的 GROUP BY 子句改写为“GROUP BY product_type WITH ROLLUP;”。

执行结果（在 DB2 中执行）

product_type sum_price -------------- --------- 16780 厨房用具 11180 办公用品 600 衣服 5000

从语法上来说，就是将 GROUP BY 子句中的聚合键清单像 ROLLUP(<列 1>,<列 2>,...) 这样使用。该运算符的作用，一言以蔽之，就是“一次计算出不同聚合键组合的结果”。例如，在本例中就是一次计算出了如下两种组合的汇总结果。

① GROUP BY ()

② GROUP BY (product_type)

① 中的 GROUP BY () 表示没有聚合键，也就相当于没有 GROUP BY 子句（这时会得到全部数据的合计行的记录），该合计行记录称为超级分组记录（super group row）。虽然名字听上去很炫，但还是希望大家把它当作未使用 GROUP BY 的合计行来理解。超级分组记录的 product_type 列的键值（对 DBMS 来说）并不明确，因此会默认使用 NULL。之后会为大家讲解在此处插入恰当的字符串的方法。

KEYWORD
超级分组记录

法则 6
超级分组记录默认使用 NULL 作为聚合键。

2.2 将“登记日期”添加到聚合键当中

仅仅通过刚才一个例子大家的印象可能不够深刻，下面让我们再添加一个聚合键“登记日期（regist_date）”试试看吧。首先从不使用 ROLLUP 开始（代码清单 13）。

代码清单 13　在 GROUP BY 中添加“登记日期”（不使用 ROLLUP）

SELECT product_type, regist_date, SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY product_type, regist_date;

执行结果（在 DB2 中执行）

product_type regist_date sum_price -------------- ------------ ---------- 厨房用具 2008-04-28 880 厨房用具 2009-01-15 6800 厨房用具 2009-09-20 3500 办公用品 2009-09-11 500 办公用品 2009-11-11 100 衣服 2009-09-20 1000 衣服 4000

在上述 GROUP BY 子句中使用 ROLLUP 之后，结果会发生什么变化呢（代码清单 14）？

代码清单 14　在 GROUP BY 中添加“登记日期”（使用 ROLLUP）

Oracle SQL Server DB2 PostgreSQL

SELECT product_type, regist_date, SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date); ------①

特定的 SQL
在 MySQL 中执行代码清单 14 时，请将 ① 中的 GROUP BY 子句改写为“GROUP BY product_type, regist_date WITH ROLLUP;”

执行结果（在 DB2 中执行）

product_type regist_date sum_price -------------- ------------ ---------- 16780 ←合计厨房用具 11180 ←小计（厨房用具）厨房用具 2008-04-28 880 厨房用具 2009-01-15 6800 厨房用具 2009-09-20 3500 办公用品 600 ←小计（办公用品）办公用品 2009-09-11 500 办公用品 2009-11-11 100 衣服 5000 ←小计（衣服）衣服 2009-09-20 1000 衣服 4000

将上述两个结果进行比较后我们发现，使用 ROLLUP 时多出了最上方的合计行以及 3 条不同商品种类的小计行（也就是未使用登记日期作为聚合键的记录），这 4 行就是我们所说的超级分组记录。也就是说，该 SELECT 语句的结果相当于使用 UNION 对如下 3 种模式的聚合级的不同结果进行连接（图 5）。

① GROUP BY ()

② GROUP BY (product_type)

③ GROUP BY (product_type, regist_date)

图 5　3 种模式的聚合级

如果大家觉得上述结果不容易理解的话，可以参考表 2 中按照聚合级添加缩进和说明后的内容，理解起来就很容易了。

表 2　根据聚合级添加缩进后的结果

合计

16780

厨房用具

小计

11180

厨房用具

2008-04-28

880

厨房用具

2009-01-15

6800

厨房用具

2009-09-20

3500

办公用品

小计

600

办公用品

2009-09-11

500

办公用品

2009-11-11

100

衣服

小计

5000

衣服

2009-09-20

1000

衣服

4000

ROLLUP 是“卷起”的意思，比如卷起百叶窗、窗帘卷，等等。其名称也形象地说明了该操作能够得到像从小计到合计这样，从最小的聚合级开始，聚合单位逐渐扩大的结果。

法则 7
ROLLUP 可以同时得出合计和小计，是非常方便的工具。

专栏
GROUPING 运算符的支持情况
本节介绍的 GROUPING 运算符与窗口函数中介绍的窗口函数都是为了实现 OLAP 用途而添加的功能，是比较新的功能（是 SQL：1999 的标准 SQL 中添加的新功能）。因此，还有一些 DBMS 尚未支持这些功能。截止到 2016 年 5 月，Oracle、SQL Server、DB2、PostgreSQL 的最新版本都已经支持这些功能了，但 MySQL 的最新版本 5.7 还是不支持这些功能。
想要在不支持 GROUPING 运算符的 DBMS 中获得包含合计和小计的结果时，只能像本文一开始介绍的那样，使用 UNION 将多条 SELECT 语句连接起来。
此外，使用 MySQL 时的情况更加复杂一些，只有一个不合规则的 ROLLUP 能够使用。这里所说的“不合规则”指的是需要使用特定的语法。
-- MySQL专用
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
 FROM Product
GROUP BY product_type, regist_date WITH ROLLUP;
遗憾的是，MySQL 5.7 并不支持 CUBE 和 GROUPING SETS。希望之后的版本能够提供对它们的支持。

三、`GROUPING` 函数——让 `NULL` 更加容易分辨

可能有些读者会注意到，之前使用 ROLLUP 所得到的结果（代码清单 14 的执行结果）有些蹊跷，问题就出在“衣服”的分组之中，有两条记录的 regist_date 列为 NULL，但其原因却并不相同。

sum_price 为 4000 日元的记录，因为商品表中运动 T 恤的注册日期为 NULL，所以就把 NULL 作为聚合键了，这在之前的示例中我们也曾见到过。

相反，sum_price 为 5000 日元的记录，毫无疑问就是超级分组记录的 NULL 了（具体为 1000 日元 + 4000 日元 = 5000 日元）。但两者看上去都是“NULL”，实在是难以分辨。

product_type regist_date sum_price -------------- ------------ ---------- 衣服 5000 ←因为是超级分组记录，所以登记日期为NULL 衣服 2009-09-20 1000 衣服 4000 ←仅仅因为“运动T恤”的登记日期为NULL

为了避免混淆，SQL 提供了一个用来判断超级分组记录的 NULL 的特定函数 —— GROUPING 函数。该函数在其参数列的值为超级分组记录所产生的 NULL 时返回 1，其他情况返回 0（代码清单 15）。

KEYWORD
GROUPING 函数

代码清单 15　使用 GROUPING 函数来判断 NULL

Oracle SQL Server DB2 PostgreSQL

SELECT GROUPING(product_type) AS product_type,
            GROUPING(regist_date) AS regist_date, SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

执行结果（在 DB2 中执行）

product_type regist_date sum_price -------------- ------------ ---------- 1 1 16780 0 1 11180 0 0 880 0 0 6800 0 0 3500 0 1 600 0 0 500 0 0 100 0 1 5000 ←碰到超级分组记录中的NULL时返回1 0 0 1000 0 0 4000 ←原始数据为NULL时返回0

这样就能分辨超级分组记录中的 NULL 和原始数据本身的 NULL 了。使用 GROUPING 函数还能在超级分组记录的键值中插入字符串。也就是说，当 GROUPING 函数的返回值为 1 时，指定“合计”或者“小计”等字符串，其他情况返回通常的列的值（代码清单 16）。

代码清单 16　在超级分组记录的键值中插入恰当的字符串

Oracle SQL Server DB2 PostgreSQL

SELECT CASE WHEN GROUPING(product_type) = 1
            THEN "商品种类 合计"
            ELSE product_type END AS product_type,
       CASE WHEN GROUPING(regist_date) = 1
            THEN "登记日期 合计"
            ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
       SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

执行结果（在 DB2 中执行）

product_type regist_date sum_price -------------- ------------ ---------- 商品种类合计登记日期合计 16780 厨房用具登记日期合计 11180 厨房用具 2008-04-28 880 厨房用具 2009-01-15 6800 厨房用具 2009-09-20 3500 办公用品登记日期合计 600 办公用品 2009-09-11 500 办公用品 2009-11-11 100 衣服登记日期合计 5000 ←将超级分组记录中的NULL替换为“登记日期合计” 衣服 2009-09-20 1000 衣服 4000 ←原始数据中的NULL保持不变

在实际业务中需要获取包含合计或者小计的汇总结果（这种情况是最多的）时，就可以使用 ROLLUP 和 GROUPING 函数来实现了。

CAST(regist_date AS VARCHAR(16))

那为什么还要将 SELECT 子句中的 regist_date 列转换为 CAST(regist_date AS VARCHAR)16)) 形式的字符串呢？这是为了满足 CASE 表达式所有分支的返回值必须一致的条件。如果不这样的话，那么各个分支会分别返回日期类型和字符串类型的值，执行时就会发生语法错误。

法则 8
使用 GROUPING 函数能够简单地分辨出原始数据中的 NULL 和超级分组记录中的 NULL。

四、`CUBE`——用数据来搭积木

ROLLUP 之后我们来介绍另一个常用的 GROUPING 运算符 —— CUBE。CUBE 是“立方体”的意思，这个名字和 ROLLUP 一样，都能形象地说明函数的动作。那么究竟是什么样的动作呢？还是让我们通过一个列子来看一看吧。

KEYWORD
CUBE 运算符

CUBE 的语法和 ROLLUP 相同，只需要将 ROLLUP 替换为 CUBE 就可以了。下面我们就把代码清单 16 中的 SELECT 语句替换为 CUBE 试试看吧（代码清单 17）。

代码清单 17　使用 CUBE 取得全部组合的结果

Oracle SQL Server DB2 PostgreSQL

SELECT CASE WHEN GROUPING(product_type) = 1
            THEN "商品种类 合计"
            ELSE product_type END AS product_type,
       CASE WHEN GROUPING(regist_date) = 1
            THEN "登记日期 合计"
            ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
       SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY CUBE(product_type, regist_date);

执行结果（在 DB2 中执行）

product_type regist_date sum_price -------------- ------------ ---------- 商品种类合计登记日期合计 16780 商品种类合计 2008-04-28 880 ←追加商品种类合计 2009-01-15 6800 ←追加商品种类合计 2009-09-11 500 ←追加商品种类合计 2009-09-20 4500 ←追加商品种类合计 2009-11-11 100 ←追加商品种类合计 4000 ←追加厨房用具登记日期合计 11180 厨房用具 2008-04-28 880 厨房用具 2009-01-15 6800 厨房用具 2009-09-20 3500 办公用品登记日期合计 600 办公用品 2009-09-11 500 办公用品 2009-11-11 100 衣服登记日期合计 5000 衣服 2009-09-20 1000 衣服 4000

与 ROLLUP 的结果相比，CUBE 的结果中多出了几行记录。大家看一下应该就明白了，多出来的记录就是只把 regist_date 作为聚合键所得到的汇总结果。

① GROUP BY ()

② GROUP BY (product_type)

③ GROUP BY (regist_date) ←添加的组合

④ GROUP BY (product_type, regist_date)

所谓 CUBE，就是将 GROUP BY 子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此，组合的个数就是 2n（n 是聚合键的个数）。本例中聚合键有 2 个，所以 2² = 4。如果再添加 1 个变为 3 个聚合键的话，就是 2³ = 8 ^[3]。

读到这里，可能很多读者都会觉得奇怪，究竟 CUBE 运算符和立方体有什么关系呢？

众所周知，立方体由长、宽、高 3 个轴构成。对于 CUBE 来说，一个聚合键就相当于其中的一个轴，而结果就是将数据像积木那样堆积起来（图 6）。

图 6　CUBE 的执行图示

由于本例中只有商品种类（product_type）和登记日期（regist_date）2 个轴，所以我们看到的其实是一个正方形，请大家把它看作缺了 1 个轴的立方体。通过 CUBE 当然也可以指定 4 个以上的轴，但那已经属于 4 维空间的范畴了，是无法用图形来表示的。

法则 9
可以把 CUBE 理解为将使用聚合键进行切割的模块堆积成一个立方体。

五、`GROUPING SETS`——取得期望的积木

最后要介绍给大家的 GROUPING 运算符是 GROUPING SETS。该运算符可以用于从 ROLLUP 或者 CUBE 的结果中取出部分记录。

KEYWORD
GROUPING SETS 运算符

例如，之前的 CUBE 的结果就是根据聚合键的所有可能的组合计算而来的。如果希望从中选取出将“商品种类”和“登记日期”各自作为聚合键的结果，或者不想得到“合计记录和使用 2 个聚合键的记录”时，可以使用 GROUPING SETS（代码清单 18）。

代码清单 18　使用 GROUPING SETS 取得部分组合的结果

Oracle SQL Server DB2 PostgreSQL

SELECT CASE WHEN GROUPING(product_type) = 1
            THEN "商品种类 合计"
            ELSE product_type END AS product_type,
       CASE WHEN GROUPING(regist_date) = 1
            THEN "登记日期 合计"
            ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
       SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY GROUPING SETS (product_type, regist_date);

执行结果（在 DB2 中执行）

product_type regist_date sum_price -------------- ------------ ---------- 商品种类合计 2008-04-28 880 商品种类合计 2009-01-15 6800 商品种类合计 2009-09-11 500 商品种类合计 2009-09-20 4500 商品种类合计 2009-11-11 100 商品种类合计 4000 厨房用具登记日期合计 11180 办公用品登记日期合计 600 衣服登记日期合计 5000

上述结果中也没有全体的合计行（16780 日元）。与 ROLLUP 或者 CUBE 能够得到规定的结果相对，GROUPING SETS 用于从中取出个别条件对应的不固定的结果。然而，由于期望获得不固定结果的情况少之又少，因此与 ROLLUP 或者 CUBE 比起来，使用 GROUPING SETS 的机会也就很少了。

请参阅

窗口函数

GROUPING 运算符

（完）

虽然也可以使用 UNION 来代替 UNION ALL，但由于两条 SELECT 语句的聚合键不同，一定不会出现重复行，因此可以使用 UNION ALL。UNION ALL 和 UNION 的不同之处在于它不会对结果进行排序，因此比 UNION 的性能更好。 ↩︎

目前 PostgreSQL 和 MySQL 并不支持 GROUPING 运算符（MySQL 仅支持 ROLLUP）。具体内容请参考专栏“GROUPING 运算符的支持状况”。 ↩︎

使用 ROLLUP 时组合的个数是 n + 1。随着组合个数的增加，结果的行数也会增加，因此如果使用 CUBE 时不加以注意的话，往往会得到意想不到的巨大结果。顺带说一下，ROLLUP 的结果一定包含在 CUBE 的结果之中。 ↩︎

以上是 SQLGROUPING运算符的全部内容，来源链接： utcz.com/z/536131.html

SQLGROUPING运算符

一、同时得到合计行

二、ROLLUP——同时得出合计和小计

2.1 ROLLUP 的使用方法

2.2 将“登记日期”添加到聚合键当中

三、GROUPING 函数——让 NULL 更加容易分辨

四、CUBE——用数据来搭积木

五、GROUPING SETS——取得期望的积木

请参阅

其他人也看了：

二、`ROLLUP`——同时得出合计和小计

2.1 `ROLLUP` 的使用方法

三、`GROUPING` 函数——让 `NULL` 更加容易分辨

四、`CUBE`——用数据来搭积木

五、`GROUPING SETS`——取得期望的积木