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函数来过滤不需要的结果集。