ROLLUP 与 CUBE 运算符的使用

    对于数据的汇总，是数据库经常用到的任务之一，除了我们通常使用的GROUP BY分组配合聚合函数对数据汇总，以及使用UNION ALL 对数据汇总之外，SQL还提供了 GROUP BY Col1,Col2.. WITH CUBE | ROLLUP，以及COMPUTE BY 等汇总方式，本文主要介绍了使用CUBE 与ROLLUP运算符来实现数据的分级汇总。

IF OBJECT_ID('tb') IS NOT NULL
 DROP TABLE tb;
GO  
CREATE TABLE tb
(
    Provider    VARCHAR(10)
    ,MaterialNo VARCHAR(3)
    ,Quantity INT
   
); 
INSERT INTO tb SELECT 'Canon',  '001',  500 
UNION ALL SELECT 'Canon',  '001',     200 
UNION ALL SELECT 'Canon',  '002',     100
UNION ALL SELECT 'Canon',  '002',     300
UNION ALL SELECT 'Sony',  '001',     200 
UNION ALL SELECT 'IBM',  '002',     100 
UNION ALL SELECT 'IBM',  '001',     600 
UNION ALL SELECT 'IBM',  '001',     200  

 

--1.使用ROLLUP来对数据进行汇总
SELECT Provider,MaterialNo,SUM(Quantity) AS Sum_Quantity FROM tb
GROUP BY Provider,MaterialNo
WITH ROLLUP
/*  --结果
Provider   MaterialNo Sum_Quantity
---------- ---------- ------------
Canon      001        700
Canon      002        400
Canon      NULL       1100
IBM        001        800
IBM        002        100
IBM        NULL       900
Sony       001        200
Sony       NULL       200
NULL       NULL       2200
*/
由结果可以得知，ROLLUP运算符实现了对Provider级别进行汇总，结果集中Provider字段不为空，MaterialNo字段为空的时候实现了对不同的Provider分类汇总，而当Provider与MaterialNo都为空时则是对所有的Provider来实现数据汇总。

 

--2.使用CUBE来对数据进行汇总
SELECT Provider,MaterialNo,SUM(Quantity) AS Sum_Quantity FROM tb
GROUP BY Provider,MaterialNo
WITH CUBE
/*  --结果
Provider   MaterialNo Sum_Quantity
---------- ---------- ------------
Canon      001        700
Canon      002        400
Canon      NULL       1100
IBM        001        800
IBM        002        100
IBM        NULL       900
Sony       001        200
Sony       NULL       200
NULL       NULL       2200
NULL       001        1700
NULL       002        500
*/
与ROLLUP相比，从结果可以看出CUBE的结果集在ROLLUP结果集的基础上增加了两行，
NULL       001        1700
NULL       002        200
即对不同MaterialNo也实现了汇总。

 

--3.ROLLUP与CUBE的差异
 . ROLLUP 生成的结果集为所选列中值的某一维度的聚合。如以上示例中实现了对Provider维度进行汇总。
 . CUBE  生成的结果集为所选列中值的所有维度的聚合。如以上示例中实现了对Provider和MaterialNo所有维度进行汇总。
 
--4.使用GROUPING函数来处理汇总产生的NULL值
     对于使用ROLLUP与CUBE汇总数据所产生的NULL值，容易引起与实际数据本身为NULL容易引起歧义，对此我们可以使用GROUPING函数的进行区分。
     当NULL为ROLLUP或CUBE所产生时，则GROUPING函数返回的值为1，当NULL来自实际数据本身的话，GROUPING函数返回的值为0。
SELECT
    CASE WHEN (GROUPING(Provider) = 1) THEN 'ALL'
         ELSE Provider END AS Provider,
    CASE WHEN (GROUPING(MaterialNo) = 1) THEN 'ALL'
         ELSE MaterialNo END AS MaterialNo,
    SUM(Quantity) AS Sum_Quantity FROM tb
GROUP BY Provider,MaterialNo
WITH CUBE 
/*  --结果
Provider   MaterialNo Sum_Quantity
---------- ---------- ------------
Canon      001        700
Canon      002        400
Canon      ALL        1100
IBM        001        800
IBM        002        100
IBM        ALL        900
Sony       001        200
Sony       ALL        200
ALL        ALL        2200
ALL        001        1700
ALL        002        500
*/

 

--5.使用Having子句来顾虑掉无用的数据，比如本例过滤掉了总计以及MaterialNo汇总的数据。
SELECT
    CASE WHEN (GROUPING(Provider) = 1) THEN 'ALL'
         ELSE Provider END AS Provider,
    CASE WHEN (GROUPING(MaterialNo) = 1) THEN 'ALL'
         ELSE MaterialNo END AS MaterialNo,
    SUM(Quantity) AS Sum_Quantity FROM tb
GROUP BY Provider,MaterialNo WITH CUBE
HAVING GROUPING(Provider) <> 1
/*  --结果
Provider   MaterialNo Sum_Quantity
---------- ---------- ------------
Canon      001        700
Canon      002        400
Canon      ALL        1100
IBM        001        800
IBM        002        100
IBM        ALL        900
Sony       001        200
Sony       ALL        200
*/

 

--过滤掉了相同Provider，不同的MaterialNo 的小计数据和最终的总计数据
SELECT
    CASE WHEN (GROUPING(Provider) = 1) THEN 'ALL'
         ELSE Provider END AS Provider,
    CASE WHEN (GROUPING(MaterialNo) = 1) THEN 'ALL'
         ELSE MaterialNo END AS MaterialNo,
    SUM(Quantity) AS Sum_Quantity FROM tb
GROUP BY Provider,MaterialNo WITH CUBE
HAVING GROUPING(MaterialNo) = 0

 

总结:CUBE，ROLLUP为多维数据集的汇总提供了可能，当需要对所有维度进行汇总，应当使用CUBE运算符，对某一维度进行汇总则使用ROLLUP运算法。
     需要注意的是，WITH CUBE | ROLLUP必须跟在GROUP BY Col1,Col2列之后，然后可以通过使用HAVING子句配合GROUPING函数来过滤不需要的结果集。