【SQL基础教程】GROUPING运算符

Posted on | In

同时得到合计行

如果想要获得同时得出小计和合计的结果，通常的做法是分别计算出合计行和按照商品种类进行汇总的结果，然后通过UNION ALL连接在一起。

1
2
3
4
5
6
SELECT '合计' AS product_type, SUM(sale_price)
FROM Product
UNION ALL
SELECT product_type, SUM(sale_price)
FROM Product
GROUP BY product_type;

eNhiuD.png

这样一来，为了得到想要的结果，需要执行两次几乎相同的SELECT语句，再将其结果进行连接，不但看上去十分繁琐，而且DBMS内部的处理成本也非常高。

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

标准SQL引入了GROUPING运算符。使用该运算符就能通过非常简单的SQL得到之前那样的汇总单位不同的汇总结果了。

GROUPING运算符包含以下3种：

  • ROLLUP
  • CUBE
  • GROUPING SETS

ROLLUP的使用方法

使用ROLLUP就可以通过非常简单的SELECT语句同时计算出合计行了。

1
2
3
SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type);

eNIaxP.png

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

  1. GROUP BY ()
  2. GROUP BY (product_type)

1中的GROUP BY()表示没有聚合键，也就相当于没有GROUP BY子句（这时会得到全部数据的合计行的记录），该合计行记录称为超级分组记录。超级分组记录的product_type列的键值（对DBMS来说）并不明确，因此会默认使用NULL。

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

下面再添加一个聚合键“登记日期(regist_date)”试试看吧。

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

eNoCid.png

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

  1. GROUP BY ()
  2. GROUP BY (product_type)
  3. GROUP BY (product_type, regist_date)

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

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

之前使用ROLLUP所得到的结果有些蹊跷，问题就出在“衣服”的分组之中，有两条记录的regist_date为NULL，但其原因却并不相同。
sum_price为4000日元的记录，因为商品表中运动T恤的注册日期为NULL，所以就把NULL作为聚合键了。
相反，sum_price为5000日元的记录毫无疑问就是超级分组记录的NULL了。但两者看上去都是“NULL“，实在是难以分辨。

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

1
2
3
4
5
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);

eNTaB8.png

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

1
2
3
4
5
6
7
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);

eN7GaF.png

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

CUBE——用数据来搭积木

ROLLUP之后来介绍另一个常用的GROUPING运算符——CUBE。CUBE的语法和ROLLUP相同，只需要将ROLLUP替换为CUBE就可以了。

1
2
3
4
5
6
7
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);

eU1Q4P.png

与ROLLUP的结果相比，CUBE的结果中多出了几行记录。多出来的记录就是只把regist_date作为聚合键所得到的汇总结果。

  1. GROUP BY ()
  2. GROUP BY (product_type)
  3. GROUP BY (regist_date) <- 添加的组合
  4. GROUP BY (product_type, regist_date)

所谓CUBE，就是将GROUP BY子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此，组合的个数就是2^n（n是聚合键的个数）。本例中聚合键有2个，所以2^2=4。

究竟CUBE运算符和立方体有什么关系呢？
众所周知，立方体由长、宽、高3个轴构成。对于CUBE来说，一个聚合键就相当于其中的一个轴，而结果就是将数据像积木那样堆积起来。

eUJtzV.png

GROUPING SETS取得期望的积木

最后要介绍的GROUPING运算符是GROUPING SETS。该运算符可以用于从ROLLUP或者CUBE的结果中取出部分记录。
例如，之前的CUBE的结果就是根据聚合键的所有可能的组合计算而来的。如果希望从中选取出将“商品种类”和“登记日期”各自作为聚合键的结果，或者不想得到“合计记录和使用2个聚合键的记录”时，可以使用GROUPING SETS。

1
2
3
4
5
6
7
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);

eUYHAJ.png

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