先前文章曾介紹使用GROUP
BY指令可針對特定群組進行資料總計，若在多群組條件時需計算各群組小計或全部總計時，則需使用多組SQL再配合UNION ALL指令才得以實現(方法1)。
本文將介紹GROUP
BY指令的延伸功能ROLLUP及CUBE，透過此指令將在同一結果集中呈現不同層級的彙總資料，產生包含小計與總計資訊(方法2)。
以下將列出產生如下表所示之彙總結果。分別以一般SQL及ROLLUP指令說明。
|
希望呈現結果
|
|
彙總參考資訊
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
滾動彙總（Roll Up)
|
|
Order_Date
|
Cust_Name
|
群組
|
說明
|
|
0
|
Yes
|
Yes
|
明細
|
以訂單日期及客戶姓名為分群條件
|
|
1
|
Yes
|
NULL
|
小計
|
以訂單日期為分群條件，不限客戶(NULL)
|
|
3
|
NULL
|
NULL
|
合計
|
全部彙總，不限日期及客戶(均為NULL)
|
方法1: UNION ALL
應用基本SQL指令分別產生明細、小計及合計等資料，再以UNION ALL指令將三者資料合併成一個資料集，為清楚區分，在此增加Level欄位以定義三群資料，分別為定義1（明細）、2（小計）及3（合計）。但對ORACLE而言，Level是保留字，因此欄位名稱上需加上雙引號（"）。
|
SELECT Order_Date, Cust_Name
, Qty,
"Level"
,
"Grp(Order_Date)"
,
"Grp(Cust_Name)"
,
"Grp(Order_Date)" * 2+"Grp(Cust_Name)" Grp_Id
FROM
(
--Level 1: (明細)計算每天、每個客戶的訂購數量
SELECT FORMAT(Order_Date, 'yyyy-MM-dd') Order_Date, Cust_Name
, SUM(Qty) Qty
, 1
"Level" --Level對ORALE是保留字
, 0 "Grp(Order_Date)"
, 0 "Grp(Cust_Name)"
FROM Orders
GROUP BY FORMAT(Order_Date, 'yyyy-MM-dd')
, Cust_Name
UNION ALL
-- Level 2: (小計)計算每天、全部客戶的訂購數量
SELECT FORMAT(Order_Date, 'yyyy-MM-dd') , NULL
, SUM(Qty)
, 2
, 0
"Grp(Order_Date)"
, 1
"Grp(Cust_Name)"
FROM Orders
GROUP BY FORMAT(Order_Date, 'yyyy-MM-dd')
UNION ALL
-- Level 3: (合計)計算期間內、全部客戶的訂購數量
SELECT NULL, NULL
, SUM(Qty)
, 3
, 1
"Grp(Order_Date)"
, 1
"Grp(Cust_Name)"
FROM Orders
) A
WHERE 1=1
ORDER BY
"Grp(Order_Date)", Order_Date --方法1: 以虛擬欄位
,
"Grp(Cust_Name)",
Cust_Name
|
註: FORMAT函數為MSSQL2012指令，舊版則用CONVERT(CHAR(10),Order_Date, 120)，ORACLE只需改成TO_CHAR函數即可(參數相同)。
l 各層次彙總：
由前述應用基本SQL指令，分別產生明細、小計及合計等資料，為將此三者組透過UNION ALL合成同一資料集。
|
群組
|
Order_Date
|
Cust_Name
|
說明
|
|
明細
|
Yes
|
Yes
|
以訂單日期及客戶姓名為分群條件
|
|
小計
|
Yes
|
NULL
|
以訂單日期為分群條件，不限客戶(NULL)
|
|
合計
|
NULL
|
NULL
|
全部彙總，不限日期及客戶(均為NULL)
|
明細資料針對每位客戶、訂單日期計算其訂購數量，UNION ALL結合原則為欄位數量相等、型態相容，因此第二、三個子資料集中在部分合計欄位中，將以NULL值補齊欄位數量，而小計則是以訂單日期為彙總依據，將所有客戶進行彙總計算，客戶姓名欄位將以NULL值為替代，此NULL值即代表超彙總（Super
Aggregate，數個彙總資料的彙總），結果集中將增加Grp(Order_Date)及Grp(Cust_Name)兩個欄位，分別代表以訂單日期或客戶姓名是否為超彙總，在此小計Grp(Order_Date)將為0，而Grp(Cust_Name)為1。
l UNION
ALL：
前述三種資料集是依不同群組條件所產生的，資料特性均不相同，因此，應直接採用UNION
ALL將三個資料集進行聯集。聯集運算的特性是欄位數量相同資料型態需相容，傳回結果欄位名稱將以第一個資料集的欄位名稱為主，因第二、三個子資料集中在部分合計欄位中將以NULL值補齊欄位數量。
l 排序：
排序條件依序為訂單日期及客戶姓名，合計資訊置於明細下方兩項重點進行排序。在此將探討兩種方法。
|
#
|
方法
|
SQL
|
|
1
|
虛擬欄位
（超彙總）
|
ORDER BY "Grp(Order_Date)", Order_Date
, "Grp(Cust_Name)",
Cust_Name
|
|
2
|
群組NULL值
|
ORDER BY
CASE WHEN
Order_Date IS NULL
THEN 1 ELSE 0
END,
Order_Date
, CASE WHEN Cust_Name IS NULL THEN 1 ELSE 0 END,
Cust_Name
|
(1)
虛擬欄位（超彙總）
可以採用超彙總虛擬欄位為優先判斷條件，若為超彙總值為1，反之為0。
(2) 群組NULL值
為將三種不同層級彙總資料能以聯集指令進行合併，在小計、總計部分欄位將以NULL值補足欄位數量，但NULL值的排序上將涉及優先順序的問題。MSSQL及ORALCE對NULL值的定義不同。
|
資料庫
|
NULL值
|
|
MSSQL
|
最小
|
|
ORACLE
|
最大
|
ORACLE視NULL值最大值，MSSQL為最小值，因此ORACLE在群組條件欄位值中若無NULL值，將可直接以訂單日期、客戶姓名排序，也可使用NULLS FIRST/ LAST等關鍵字以強制將NULL置於最前或最後。MSSQL則需將NULL值以CASE WHEN指令進行轉換。
方法2：ROLLUP、CUBE、GROUPING SETS指令
ORALE或MSSQL均提供ROLLUP、CUBE及GROUPING SETS運算子。
1.
ROLLUP
以MSSQL 2005（含）以下是將ROLLUP置於GROUP BY條件式後方，以WITH ROLLUP命令方式指定。
MSSQL2008對GROUPING SETS指令有大幅改進，MSSQL2008、ORACLE則在ROLLUP函數中以參數方式指定。以下以ROLLUP指令為範例，SQL語法整理如下：
|
資料庫
|
語法
|
|
MSSQL2005(含)
以下
|
SELECT FORMAT(Order_Date, 'yyyy-MM-dd') Order_Date
, Cust_Name
, SUM(Qty) Qty
, GROUPING(FORMAT(Order_Date, 'yyyy-MM-dd'))
"Grp(Order_Date)"
, GROUPING(Cust_Name)
"Grp(Cust_Name)"
, GROUPING(FORMAT(Order_Date, 'yyyy-MM-dd'))*2
+ GROUPING(Cust_Name) Grp_DC
, GROUPING(Cust_Name) *2
+ GROUPING(FORMAT(Order_Date, 'yyyy-MM-dd')) Grp_CD
FROM Orders
GROUP BY FORMAT(Order_Date, 'yyyy-MM-dd')
, Cust_Name
WITH ROLLUP
|
|
ORACLE
MSSQL2008(含)
|
SELECT TRUNC(Order_Date) Order_Date
,Cust_Name
,SUM(Qty) Qty
, GROUPING(TRUNC(Order_Date)) "Grp(Order_Date)"
, GROUPING(Cust_Name) "Grp(Cust_Name)"
, GROUPING_ID(TRUNC(Order_Date), Cust_Name) Grp_DC
, GROUPING_ID(Cust_Name, TRUNC(Order_Date)) Grp_CD
FROM Orders
GROUP BY ROLLUP(TRUNC(Order_Date)
, Cust_Name)
--ORDER BY GROUPING_ID(TRUNC(Order_Date),
Cust_Name)
--排序中GROUPING_ID函數參數不得有函數，否則發生ORA-00979錯誤
|
註：MSSQL2008（含）提供與ORACLE語法相同的ROLLUP()函數。
|
ORDER_DATE
|
CUST_NAME
|
QTY
|
Grp(Order_Date)
|
Grp(Cust_Name)
|
GRP_DC
|
GRP_CD
|
|
|
1
|
2009/8/1
|
張先生
|
7
|
0
|
0
|
0
|
0
|
|
2
|
2009/8/1
|
李先生
|
16
|
0
|
0
|
0
|
0
|
|
3
|
2009/8/1
|
陳先生
|
26
|
0
|
0
|
0
|
0
|
|
4
|
2009/8/1
|
NULL
|
49
|
0
|
1
|
1
|
2
|
|
5
|
2009/8/2
|
曹先生
|
14
|
0
|
0
|
0
|
0
|
|
6
|
2009/8/2
|
李先生
|
23
|
0
|
0
|
0
|
0
|
|
7
|
2009/8/2
|
NULL
|
37
|
0
|
1
|
1
|
2
|
|
8
|
2009/8/3
|
張先生
|
30
|
0
|
0
|
0
|
0
|
|
9
|
2009/8/3
|
曹先生
|
13
|
0
|
0
|
0
|
0
|
|
10
|
2009/8/3
|
李先生
|
23
|
0
|
0
|
0
|
0
|
|
11
|
2009/8/3
|
NULL
|
66
|
0
|
1
|
1
|
2
|
|
12
|
NULL
|
NULL
|
152
|
1
|
1
|
3
|
3
|
Partial Rollup
前述範例中，因GROUP BY條件式與ROLLUP的條件相同，兩種語法執行上並無差異；但如部分群組條件為必要條件(ex: expr1)，則可可採用部分小計（Partial Rollup），使用上即有所差異，整理如下：
|
SQL
|
輸出
|
組數
|
|
GROUP BY expr1, ROLLUP(expr2, expr3)
|
(expr1, expr2, expr3)、(expr1,
expr2)及(expr1) |
3
|
|
GROUP BY ROLLUP(expr1, expr2, expr3)
GROUP BY expr1, expr2, expr3 WITH ROLLUP
|
(expr1, expr2, expr3)、(expr1,
expr2)、(expr1)及() |
4
|
GROUPING: 判斷資料為彙總資料或原始資料列(單一欄位)
由結果中可發現小計及總計資料，在群組條件的欄位均為NULL值（反白部分）。若GROUP BY條件欄位資料值中即含有NULL值，因系統會把所有NULL值視為同一群組，此種情況下則無法由NULL值判斷是否為彙總，ORACLE及SQL SERVER均提供GROUPING函數用以識別。當GROUPING傳回1時，表示為彙總資料列，0
則否。因兩種資料庫均提供相同語法，以下將以MSSQL 2005為範例：
|
SQL
|
|
結果
|
||||||||||||||||
|
SELECT Cate
, SUM(VAL) Val
, GROUPING(Cate) Grp_Val
FROM
(
SELECT NULL Cate,
1 VAL
UNION ALL
SELECT 1 Cate, 2
VAL
) A
GROUP BY Cate WITH ROLLUP
|
|
第1、2筆為原始資料，第3筆為彙總資料。
1、3均為NULL，可使用GROUPING函數判斷為原始資料或彙總結果。
|
在結果表格中，第1、2筆為原始資料，第3筆為彙總資料，因第1及第3筆資料的Cate欄位均為NULL，因此，無法由此欄位判定是否為彙總資料，將用GROUPING函數判定。類似方法1的Grp(Order_Date)及Grp(Cust_Name)兩個虛擬欄位。
GROUPING_ID: 判斷資料為彙總資料或原始資料列(多欄位)
本例是以訂單日期及客戶姓名為群組條件，總計資料部分需同時判定兩個欄位，對於ORACLE及MSSQL2008提供GROUPING_ID函數，此函數可提供多欄位判斷。
GROUPING_ID是將GROUPING各欄位的結果以二進位計算所產生結果，整理如下表。
|
GROUPING (a)
|
GROUPING (b)
|
GROUPING_ID(a,b)
= GROUPING(a)
+ GROUPING(b)
|
GROUPING_ID(a,b)
|
|
0
|
0
|
00
|
0
|
|
0
|
1
|
01
|
1
|
|
1
|
0
|
10
|
2
|
|
1
|
1
|
11
|
3
|
如前面ROLLUP結果欄位中Grp_CD值為2的部分，以客戶姓名及訂單日期為判斷，因小計部分中GROUPING(Cust_Name)值為1，此將產生GROUPING_ID(Cust_Name, Order_Name)為10（二進制）的組合。
將前述GROUPING及GROUPING_ID整理如後。
GROUPING函數指出是否為GROUP BY條件中指定的欄位運算式的彙總結果；GROUPING傳回1時，表示在結果集中彙總，0則否。
GROUPING_ID為計算群組層級的函數，將指定參數中欄位以GROUPING判定，用二進位表示此彙總資料列是由那些欄位的合計。
對於GROUPING_ID的使用，除可應用於SELECT清單、HAVING或ORDER BY條件中使用，應用於ORDER BY使用時，ORACLE不支援GROUPING_ID函數中的參數為函數，將發生ORA-00979錯誤，SQL SERVER 2008參數則可以使用函數值。命令如下：
|
ORDER BY GROUPING_ID(TRUNC(Order_Date), Cust_Name)
|
2.
CUBE
ROLLUP指令將依據GROUP BY後所指定條件，由群組內最低階層到最高階層依序滾動彙總，但無法提供指定條件中每一種可能的組合，ORACLE及MSSQL供另一項指令CUBE，此指令可提供條件中各種可能組合，因CUBE與ROLLUP的使用方法相同，將以李先生、張先生於2009-08-01及08-02兩天為例，分別以ROLLUP及CUBE執行，結果如下，反白部分為CUBE與ROLLUP的差異，CUBE提供條件中各種可能組合，因此，將比ROLLUP指令產生更多的彙總組合，SQL語法及結果如下：
|
SELECT
Order_Date
, Cust_Name
, SUM(Qty)
Qty
, GROUPING_ID(Order_Date,
Cust_Name) Grp_DC
, GROUPING_ID(Cust_Name,
Order_Date) Order_Date
FROM
(
SELECT
TRUNC(Order_Date)
Order_Date
, Cust_Name
, Qty
FROM
Orders
WHERE
1=1
AND Cust_Name IN ('李先生','張先生')
AND Order_Date >=DATE'2009-08-01'
AND Order_Date <
DATE'2009-08-03'
)
GROUP BY CUBE(Order_Date,
Cust_Name)
|
|
ROLLUP執行結果
|
CUBE執行結果（整理過）
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
註:依序產生彙總結果。
|
註: 產生各種彙總組合。
|
以上結果將呈現ROLLUP與CUBE指令的差異，ROLLUP將產生依序的彙總結果，而CUBE指令則可產生所有組合。前述是以ORALCE的SQL語法為例，在排序GROUPING_ID使用函數為參數時將發生錯誤，在此將以子查詢將日期先行處理，再以處理後的日期進行CUBE及GROUPING_ID排序，執行結果如預期，GROUPING_ID函數若應用於ORDER BY條件中時，此版本（11gR1）測試並不支援函數參數。
3.
GROUPING SETS
ORACLE 9i及MSSQL2008提供符合ANSI SQL 2006規範的GROUPING SETS指令，用以產生與CUBE和ROLLUP所產生的類似結果，但GROUPING SETS更具彈性。
GROUPING SETS可使GROUP BY子句在單一結果集中產生多個群組的彙總。將前述範例進行簡化僅產生明細及總計資料，如下表所示。
|
Order_Date
|
Cust_Name
|
Qty
|
Grp_Id
|
|
2009-08-01
|
李先生
|
16
|
0
|
|
2009-08-02
|
李先生
|
23
|
0
|
|
2009-08-01
|
張先生
|
7
|
0
|
|
46
|
3
|
|
SELECT TO_CHAR(Order_Date,'yyyy-MM-dd') Order_Date
, Cust_Name
, SUM(Qty) Qty
, GROUPING_ID(TO_CHAR(Order_Date,'yyyy-MM-dd'), Cust_Name) Grp_Id
FROM Orders
WHERE 1=1
AND Cust_Name IN('李先生','張先生')
AND Order_Date >=DATE'2009-08-01'
AND Order_Date <DATE'2009-08-03'
GROUP BY GROUPING SETS((TO_CHAR(Order_Date,'yyyy-MM-dd'), Cust_Name)
, ()
)
|
前述雖可採用ROLLUP指令再剔除小計部分，GROUPING SETS指令可直接指定此需求，SQL語法如下：
綜合前述，將ROLLUP、CUBE及GROUPING SETS指令重點整理如下。
l
ROLLUP：
指定在結果集內不僅包含由GROUP BY所產的資料列，另外還包含彙總資料列。按層次結構順序，從組內的最低到最高層級產生彙總結果。
l
CUBE：
CUBUE指令則在使用ROLLUP參數產生結果的基礎上，再將每個可能組合、子組合在結果集內傳回。
l GROUPING SETS：
使用GROUPING SETS命令所產生的結果，也可應用基本GROUP
BY、ROLLUP或CUBE等指令達到。當不需要獲得完整ROLLUP或CUBE指令產生的所有群組時，則可使用GROUPING SETS指定所需的群組。
|
版本
|
指令
|
產生結果集
|
組數
|
|
ORACLE 8i
MSSQL 2008
|
GROUP BY ROLLUP(a,
b, c)
|
abc、ab、a、{}
|
N+1
|
|
ORACLE 8i
MSSQL 2008
|
GROUP BY CUBE(a, b, c)
|
abc、ab、a、{}、bc、b、ac、c
|
2N
|
|
ORACLE 9i
MSSQL2008
|
GROUP BY GROUPING
SETS((a, b, c), ())
|
abc、{}
|
2 (註)
|
註: 在此GROUPING SETS僅在於概念呈現，GROUPING SETS可與ROLLUP、CUBE同時搭配使用，無法詳列可能結果組數，請參閱資料庫官方文件。
如何在同一結果集中呈現不同層級的彙總資料，產生包含小計與總計資訊，提供以一般SQL指令產生特定群組彙總，及產生各階層的小計及總計，再透過UNION ALL將結果集合併，也提供資料庫內建函數ROLLUP、CUBE及GROUPING SETS等指令，產生各種可能的結果。
沒有留言:
張貼留言