Zusammenfassen von Daten mit CUBE
Der CUBE-Operator generiert als Resultset einen multidimensionalen Cube. Bei einem multidimensionalen Cube handelt es sich um eine Erweiterung von Faktendaten oder Daten, die einzelne Ereignisse aufzeichnen. Die Erweiterung basiert auf Spalten, die der Benutzer analysieren möchte. Diese Spalten werden als Dimensionen bezeichnet. Der Cube ist ein Resultset, das ein Kreuzprodukt als Tabellarisierung aller möglichen Kombinationen der Dimensionen enthält.
Der CUBE-Operator wird in der GROUP BY-Klausel einer SELECT-Anweisung angegeben. Die Auswahlliste enthält die Dimensionsspalten und Aggregatfunktionsausdrücke. Die GROUP BY-Klausel gibt die Dimensionsspalten und die WITH CUBE-Schlüsselwörter an. Das Resultset enthält alle möglichen Kombinationen der Werte in den Dimensionsspalten zusammen mit den Aggregatwerten der zugrunde liegenden Zeilen, die mit der jeweiligen Kombination aus Dimensionswerten übereinstimmen.
So enthält eine einfache Inventory-Tabelle z. B. Folgendes:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
Die folgende Abfrage gibt ein Resultset zurück, das die Quantity-Teilergebnisse für alle möglichen Kombinationen aus Item und Color enthält:
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Dies ist das Resultset.
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
Die folgenden Zeilen aus dem Resultset sind von besonderem Interesse:
Chair (null) 311.00
Diese Zeile berichtet ein Teilergebnis für alle Zeilen, die den Wert Chair in der Item-Dimension aufweisen. Für die Color-Dimension wird null zurückgegeben, um anzuzeigen, dass Aggregate, die von der Zeile berichtet werden, Zeilen mit beliebigen Werten in der Color-Dimension umfassen.
Table (null) 347.00
Diese Zeile ist vergleichbar, berichtet jedoch das Teilergebnis für alle Zeilen, die den Wert Table in der Item-Dimension aufweisen.
(null) (null) 658.00
Diese Zeile berichtet das Gesamtergebnis für den Cube. Sowohl die Item- als auch die Color-Dimension weisen den Wert null auf. Dies zeigt, dass alle Werte der beiden Dimensionen in der Zeile zusammengefasst sind.
(null) Blue 225.00
(null) Red 433.00
Diese zwei Zeilen berichten die Teilergebnisse für die Color-Dimension. Beide weisen null in der Item-Dimension auf, um anzuzeigen, dass die Aggregatdaten aus Zeilen stammen, die beliebige Werte in der Item-Dimension enthalten.
Verwenden von GROUPING zur Unterscheidung von NULL-Werten
Die von der CUBE-Operation generierten NULL-Werte bringen ein Problem mit sich: Wie kann ein von der CUBE-Operation generierter NULL-Wert von einem NULL-Wert, der in den tatsächlichen Daten zurückgegeben wird, unterschieden werden? Dieses Problem wird mithilfe der GROUPING-Funktion behoben. Die GROUPING-Funktion gibt 0 zurück, wenn der Spaltenwert aus den Faktendaten stammt; sie gibt 1 zurück, wenn der Spaltenwert ein NULL-Wert ist, der von der CUBE-Operation generiert wurde. In einer CUBE-Operation stellt ein generierter NULL-Wert alle Werte dar. Die SELECT-Anweisung kann so geschrieben werden, dass die GROUPING-Funktion alle generierten NULL-Werte durch die ALL-Zeichenfolge ersetzt. Da ein NULL-Wert aus den Faktendaten anzeigt, dass der Datenwert unbekannt ist, kann die SELECT-Anweisung auch so codiert werden, dass die UNKNOWN-Zeichenfolge statt eines NULL-Wertes aus den Faktendaten zurückgegeben wird. Beispiel:
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
Multidimensionale Cubes
Der CUBE-Operator kann zum Generieren von n-dimensionalen Cubes oder Cubes mit einer beliebigen Anzahl an Dimensionen verwendet werden. Ein eindimensionaler Cube kann wie im folgenden Beispiel zum Generieren eines Gesamtwertes verwendet werden:
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
Diese SELECT-Anweisung gibt ein Resultset zurück, das sowohl die Teilergebnisse für jeden Wert von Item als auch das Gesamtergebnis für alle Werte von Item zurückgibt:
Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00
SELECT-Anweisungen, die einen CUBE-Operator mit mehreren Dimensionen enthalten, können umfangreiche Resultsets generieren, da diese Anweisungen Zeilen für alle Kombinationen der Werte in allen Dimensionen generieren. Diese umfangreichen Resultsets enthalten möglicherweise zu viele Daten, um noch einfach gelesen oder verstanden werden zu können. Eine Lösung für dieses Problem besteht darin, die SELECT-Anweisung in eine Sicht zu übertragen:
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
Die Sicht kann dann verwendet werden, um ausschließlich die gewünschten Dimensionswerte abzufragen:
SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'
Item Color QtySum
-------------------- -------------------- --------------------------
Chair ALL 311.00
(1 row(s) affected)
Siehe auch
Konzepte
Zusammenfassen von Daten mit ROLLUP