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來對資料進行彙總

WITH CUBE

NULL       001        1700

NULL       002        500

與ROLLUP相比，從結果可以看出CUBE的結果集在ROLLUP結果集的基礎上增加了兩行，

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

WITH CUBE 

Canon      ALL        1100

IBM        ALL        900

Sony       ALL        200

ALL        ALL        2200

ALL        001        1700

ALL        002        500

--5.使用Having子句來顧慮掉無用的資料，比如本例過濾掉了總計以及MaterialNo彙總的資料。

GROUP BY Provider,MaterialNo WITH CUBE

HAVING GROUPING(Provider) <> 1

--過濾掉了相同Provider，不同的MaterialNo 的小計資料和最終的總計資料

HAVING GROUPING(MaterialNo) = 0

總結:CUBE，ROLLUP為多元資料集的彙總提供了可能，當需要對所有次元進行彙總，應當使用CUBE運算符，對某一次元進行彙總則使用ROLLUP運算法。

     需要注意的是，WITH CUBE | ROLLUP必須跟在GROUP BY Col1,Col2列之後，然後可以通過使用HAVING子句配合GROUPING函數來過濾不需要的結果集。