用 CUBE 匯總數(shù)據(jù)

CUBE 運算符生成的結(jié)果集是多維數(shù)據(jù)集。多維數(shù)據(jù)集是事實數(shù)據(jù)的擴展，事實數(shù)據(jù)即記錄個別事件的數(shù)據(jù)。擴展建立在用戶打算分析的列上。這些列被稱為維。多維數(shù)據(jù)集是一個結(jié)果集，其中包含了各維度的所有可能組合的交叉表格。

CUBE 運算符在 SELECT 語句的 GROUP BY 子句中指定。該語句的選擇列表應(yīng)包含維度列和聚合函數(shù)表達式。GROUP BY 應(yīng)指定維度列和關(guān)鍵字 WITH CUBE。結(jié)果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎(chǔ)行中的聚合值。

例如，一個簡單的表 Inventory 中包含：

Item???????????????? Color??????????????? Quantity??????????????????
-------------------- -------------------- --------------------------
Table??????????????? Blue???????????????? 124???????????????????????
Table??????????????? Red????????????????? 223???????????????????????
Chair??????????????? Blue???????????????? 101???????????????????????
Chair??????????????? Red????????????????? 210???????????????????????

下列查詢返回的結(jié)果集中，將包含 Item 和 Color 的所有可能組合的 Quantity 小計：

SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

下面是結(jié)果集：

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????????????????????

這一行報告了多維數(shù)據(jù)集的總計。Item 和 Color 維度的值都是 NULL，表示兩個維度中的所有值都匯總在該行中。

(null)?????????????? Blue???????????????? 225.00????????????????????
(null)?????????????? Red????????????????? 433.00????????????????????

這兩行報告了 Color 維度的小計。兩行中的 Item 維度值都是 NULL，表示聚合數(shù)據(jù)來自 Item 維度為任意值的行。

使用 GROUPING 區(qū)分空值
CUBE 操作所生成的空值帶來一個問題：如何區(qū)分 CUBE 操作所生成的 NULL 值和從實際數(shù)據(jù)中返回的 NULL 值？這個問題可用 GROUPING 函數(shù)解決。如果列中的值來自事實數(shù)據(jù)，則 GROUPING 函數(shù)返回 0；如果列中的值是 CUBE 操作所生成的 NULL，則返回 1。在 CUBE 操作中，所生成的 NULL 代表全體值。可將 SELECT 語句寫成使用 GROUPING 函數(shù)將所生成的 NULL 替換為字符串 ALL。因為事實數(shù)據(jù)中的 NULL 表明數(shù)據(jù)值未知，所以 SELECT 語句還可譯碼為返回字符串 UNKNOWN 替代來自事實數(shù)據(jù)的 NULL。例如：

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

多維數(shù)據(jù)集
CUBE 運算符可用于生成 n 維的多維數(shù)據(jù)集，即具有任意數(shù)目維度的多維數(shù)據(jù)集。只有一個維度的多維數(shù)據(jù)集可用于生成合計，例如：

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 語句返回的結(jié)果集既顯示了 Item 中每個值的小計，也顯示了 Item 中所有值的總計：

Item???????????????? QtySum????????????????????
-------------------- --------------------------
Chair??????????????? 311.00????????????????????
Table??????????????? 347.00????????????????????
ALL????????????????? 658.00????????????????????

包含帶有許多維度的 CUBE 的 SELECT 語句可能生成很大的結(jié)果集，因為這些語句會為所有維度中值的所有組合生成行。這些大結(jié)果集包含的數(shù)據(jù)可能過多而不易于閱讀和理解。這個問題有一種解決辦法是將 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)

用 ROLLUP 匯總數(shù)據(jù)
在生成包含小計和合計的報表時，ROLLUP 運算符很有用。ROLLUP 運算符生成的結(jié)果集類似于 CUBE 運算符所生成的結(jié)果集。有關(guān)更多信息，請參見用 CUBE 匯總數(shù)據(jù)。

CUBE 和 ROLLUP 之間的區(qū)別在于：

CUBE 生成的結(jié)果集顯示了所選列中值的所有組合的聚合。

ROLLUP 生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合。
例如，簡單表 Inventory 中包含：

Item???????????????? Color??????????????? Quantity??????????????????
-------------------- -------------------- --------------------------
Table??????????????? Blue???????????????? 124???????????????????????
Table??????????????? Red????????????????? 223???????????????????????
Chair??????????????? Blue???????????????? 101???????????????????????
Chair??????????????? Red????????????????? 210???????????????????????

下列查詢將生成小計報表：

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 ROLLUP

Item???????????????? Color??????????????? QtySum????????????????????
-------------------- -------------------- --------------------------
Chair??????????????? Blue???????????????? 101.00????????????????????
Chair??????????????? Red????????????????? 210.00????????????????????
Chair??????????????? ALL????????????????? 311.00????????????????????
Table??????????????? Blue???????????????? 124.00????????????????????
Table??????????????? Red????????????????? 223.00????????????????????
Table??????????????? ALL????????????????? 347.00????????????????????
ALL????????????????? ALL????????????????? 658.00????????????????????

(7 row(s) affected)

如果查詢中的 ROLLUP 關(guān)鍵字更改為 CUBE，那么 CUBE 結(jié)果集與上述結(jié)果相同，只是在結(jié)果集的末尾還會返回下列兩行：

ALL????????????????? Blue???????????????? 225.00????????????????????
ALL????????????????? Red????????????????? 433.00????????????????????

CUBE 操作為 Item 和 Color 中值的可能組合生成行。例如，CUBE 不僅報告與 Item 值 Chair 相組合的 Color 值的所有可能組合（Red、Blue 和 Red + Blue），而且報告與 Color 值 Red 相組合的 Item 值的所有可能組合（Chair、Table 和 Chair + Table）。

對于 GROUP BY 子句中右邊的列中的每個值，ROLLUP 操作并不報告左邊一列（或左邊各列）中值的所有可能組合。例如，ROLLUP 并不對每個 Color 值報告 Item 值的所有可能組合。

ROLLUP 操作的結(jié)果集具有類似于 COMPUTE BY 所返回結(jié)果集的功能；然而，ROLLUP 具有下列優(yōu)點：

ROLLUP 返回單個結(jié)果集；COMPUTE BY 返回多個結(jié)果集，而多個結(jié)果集會增加應(yīng)用程序代碼的復(fù)雜性。

ROLLUP 可以在服務(wù)器游標(biāo)中使用；COMPUTE BY 不可以。

有時，查詢優(yōu)化器為 ROLLUP 生成的執(zhí)行計劃比為 COMPUTE BY 生成的更為高效。

用 COMPUTE 和 COMPUTE BY 匯總數(shù)據(jù)
提供 COMPUTE 和 COMPUTE BY 是為了向后兼容。請改為使用下列組件：

Microsoft? SQL Server? 2000 Analysis Services 和用于 Analysis Services 的 OLE DB 或 Microsoft ActiveX? 數(shù)據(jù)對象（多維）(ADO MD) 一起使用。有關(guān)更多信息，請參見 Microsoft SQL Server? 2000 Analysis Services。

ROLLUP 運算符。有關(guān)更多信息，請參見用 ROLLUP 匯總數(shù)據(jù)。
COMPUTE BY 子句使您得以用同一 SELECT 語句既查看明細(xì)行，又查看匯總行。可以計算子組的匯總值，也可以計算整個結(jié)果集的匯總值。

COMPUTE 子句需要下列信息：

可選的 BY 關(guān)鍵字，該關(guān)鍵字可按對一列計算指定的行聚合。

行聚合函數(shù)名稱；例如，SUM、AVG、MIN、MAX 或 COUNT。

要對其執(zhí)行行聚合函數(shù)的列。
COMPUTE 生成的結(jié)果集
COMPUTE 所生成的匯總值在查詢結(jié)果中顯示為分離的結(jié)果集。包括 COMPUTE 子句的查詢的結(jié)果類似于控制中斷報表，即匯總值由指定的組（或稱中斷）控制的報表。可以為各組生成匯總值，也可以對同一組計算多個聚合函數(shù)。

當(dāng) COMPUTE 帶有可選的 BY 子句時，符合 SELECT 條件的每個組都有兩個結(jié)果集：

每個組的第一個結(jié)果集是明細(xì)行集，其中包含該組的選擇列表信息。

每個組的第二個結(jié)果集有一行，其中包含該組的 COMPUTE 子句中所指定的聚合函數(shù)的小計。
當(dāng) COMPUTE 不帶可選的 BY 子句時，SELECT 語句有兩個結(jié)果集：

每個組的第一個結(jié)果集是包含選擇列表信息的所有明細(xì)行。

第二個結(jié)果集有一行，其中包含 COMPUTE 子句中所指定的聚合函數(shù)的合計。
COMPUTE 用法示例
下列 SELECT 語句使用簡單 COMPUTE 子句生成 titles 表中 price 及 advance 的求和總計：

USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance)

下列查詢在 COMPUTE 子句中加入可選的 BY 關(guān)鍵字，以生成每個組的小計：

USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type

此 SELECT 語句的結(jié)果用 12 個結(jié)果集返回，六個組中的每個組都有兩個結(jié)果集。每個組的第一個結(jié)果集是一個行集，其中包含選擇列表中所請求的信息。每個組的第二個結(jié)果集包含 COMPUTE 子句中兩個 SUM 函數(shù)的小計。

?

說明? 一些實用工具（如 osql）顯示多個小計或合計聚合匯總的方式可能會使用戶以為每個小計都是結(jié)果集中的單獨一行。這是由于該實用工具設(shè)置輸出格式的方式；小計或合計聚合返回時單獨占用一行。其它應(yīng)用程序（如 SQL 查詢分析器）將多個聚合設(shè)置在同一行。

比較 COMPUTE 和 GROUP BY
COMPUTE 和 GROUP BY 之間的區(qū)別匯總?cè)缦拢?

GROUP BY 生成單個結(jié)果集。每個組都有一個只包含分組依據(jù)列和顯示該組子聚合的聚合函數(shù)的行。選擇列表只能包含分組依據(jù)列和聚合函數(shù)。

COMPUTE 生成多個結(jié)果集。一類結(jié)果集包含每個組的明細(xì)行，其中包含選擇列表中的表達式。另一類結(jié)果集包含組的子聚合，或 SELECT 語句的總聚合。選擇列表可包含除分組依據(jù)列或聚合函數(shù)之外的其它表達式。聚合函數(shù)在 COMPUTE 子句中指定，而不是在選擇列表中。
下列查詢使用 GROUP BY 和聚合函數(shù)；該查詢將返回一個結(jié)果集，其中每個組有一行，該行中包含該組的聚合小計：

USE pubs
SELECT type, SUM(price), SUM(advance)
FROM titles
GROUP BY type

?

說明? 在 COMPUTE 或 COMPUTE BY 子句中，不能包含 ntext、text 或 image 數(shù)據(jù)類型。

SQL Server 2000中的數(shù)據(jù)匯總