使用 CUBE 來摘要資料
CUBE 運算子會產生多維度 Cube 的結果集。多維度 Cube 是事實資料的擴充,或是記錄個別事件的資料。這種擴充以使用者所要分析的資料行為基礎,而這些資料行稱為維度。Cube 是一個結果集,其中包含維度的所有可能組合的交叉列表。
CUBE 運算子是在 SELECT 陳述式的 GROUP BY 子句中指定。選取清單中包含維度資料行與彙總函數運算式。GROUP BY 可指定維度資料行與關鍵字 WITH CUBE。結果集包含維度資料行值的所有可能組合,以及符合維度值組合之基礎資料列的彙總值。
例如,一個簡單的 Inventory 資料表包含了:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
下列查詢傳回的結果集包含 Item
和 Color
所有可能組合的 Quantity
小計:
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
以下為結果集:
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair (null) 311.00
Table Blue 124.00
Table Red 223.00
Table (null) 347.00
(null) (null) 658.00
(null) Blue 225.00
(null) Red 433.00
來自結果集中的下列資料列需要一點說明:
Chair (null) 311.00
此資料列報告了 Item
維度中擁有 Chair
值之所有資料列的小計。Color
維度將傳回 null
值,以顯示資料列所報告的彙總包括了具有任何 Color
維度值的資料列。
Table (null) 347.00
此資料列類似,但報告了 Item
維度中具有 Table
的所有資料列小計。
(null) (null) 658.00
此資料列報告了 Cube 的總計。Item
和 Color
維度都有 null
值。這顯示這兩個維度的所有值都摘要於資料列中。
(null) Blue 225.00
(null) Red 433.00
這兩個資料列報告了 Color
維度的小計。這兩者的 Item
維度均為 null
值,顯示出彙總資料來自具有任何 Item
維度值的資料列。
使用 GROUPING 來區分 Null 值
CUBE 作業所產生的 null 值會出現問題:如何區分 CUBE 作業所產生的 NULL 與實際資料中傳回的 NULL?答案是使用 GROUPING 函數來區分。如果此資料行值來自事實資料,GROUPING 函數會傳回 0,如果資料行值為 CUBE 作業所產生 的 NULL,則傳回 1。在 CUBE 作業中,產生的 NULL 表示所有值。您可以撰寫 SELECT 陳述式來使用 GROUPING 函數,以將字串 ALL 取代任何產生的 NULL。因為來自事實資料的 NULL 表示資料值未知,您也可以將 SELECT 編碼,對來自事實資料的任何 NULL 值,傳回字串 UNKNOWN。例如:
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
多維度 Cube
CUBE 運算子可用來產生 n-維度的 Cube,或是具有任何維度數值的 Cube。單一維度的 Cube 可用來產生總計,例如:
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO
此 SELECT
陳述式會傳回結果集,顯示 Item
每個值的小計,以及 Item
所有值的總計:
Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00
包含具有許多維度之 CUBE 的 SELECT 陳述式可產生大型的結果集,因為這些陳述式將產生所有維度之所有數值組合的資料列。這些大型結果集可能會包含太多資料,以致於無法輕鬆地讀取與瞭解。此問題的解決方式之一是將 SELECT
陳述式放入檢視:
CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
接著您可使用檢視來查詢您感興趣的維度值:
SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'
Item Color QtySum
-------------------- -------------------- --------------------------
Chair ALL 311.00
(1 row(s) affected)