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
この行は、1 つ前の行と似ていますが、Item ディメンションの値として Table を持つすべての行の小計を示しています。
(null) (null) 658.00
この行は、このキューブの総計を示しています。Item ディメンションと Color ディメンションの両方に値 null が含まれています。これは、両方のディメンションのすべての値がこの行に集約されていることを示しています。
(null) Blue 225.00
(null) Red 433.00
これら 2 つの行は、Color ディメンションの小計を示しています。どちらの行も、Item ディメンションの値は null です。これは、この集計データが Item ディメンションの値に基づいていないことを示しています。
GROUPING による NULL 値の区別
CUBE 操作によって生成された NULL 値に関する問題があります。それは、CUBE 操作によって生成された NULL 値と実際のデータで返された NULL 値をどのようにして区別するかということです。これらの NULL 値は、GROUPING 関数を使用して区別できます。GROUPING 関数では、列の値がファクト データから抽出されたものである場合は 0、CUBE 操作によって生成された NULL 値である場合は 1 が返されます。CUBE 操作では、生成された NULL 値は "すべての値" を表します。NULL 値が生成された場合は、GROUPING 関数を使用して文字列 ALL で置き換えられるように SELECT ステートメントを記述することができます。また、ファクト データから抽出された NULL 値はデータ値が不明であることを示すので、ファクト データから NULL 値が抽出された場合は代わりに文字列 UNKNOWN を返すように SELECT ステートメントを記述することもできます。次に例を示します。
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 操作を使用すると、n 個のディメンションを持つキューブを生成できます。たとえば、次のようにディメンションを 1 つしか持たないキューブを使用すれば、合計を生成できます。
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)