Kategorien:

Abfragesyntax

GROUP BY CUBE

GROUP BY CUBE ist eine Erweiterung der GROUP BY-Klausel und ähnelt GROUP BY ROLLUP. Ähnlich wie ROLLUP erzeugt CUBE aggregierte Zeilen auf mehreren Ebenen. Während ROLLUP allerdings Aggregationen erstellt, die einer natürlichen Hierarchie folgen (z. B. „city“ wird dem Bundesstaat und „state“ dem Land zugeordnet),erstellt CUBE Aggregationen für alle möglichen Kombinationen der angegebenen Spalten. Dazu gehören sowohl die hierarchischen Aggregationen, die ROLLUP erzeugen würde und zusätzliche „Kreuztabellen“-Zeilen, die unabhängig voneinander über jede einzelne Dimension aggregiert werden.

CUBE kann mit anderen GROUP BY-Ausdrücken kombiniert werden. Sie können zum Beispiel GROUP BY x, CUBE(y, z) schreiben, um nach Spalte x in Kombination mit Cube-Aggregationen für y und z zu gruppieren.

Eine CUBE-Gruppierung entspricht einer Reihe von Gruppierungssätzen und ist im Wesentlichen eine kürzere Spezifikation. Die N Elemente einer CUBE-Spezifikation entsprechen 2^N GROUPING SETS.

Siehe auch:

Syntax

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] CUBE ( groupItem [ , groupItem [ , ... ] ] )
[ ... ]
Copy

Wobei:

groupItem ::= { <column_alias> | <position> | <expr> }
Copy

Parameter

column_alias

Spaltenalias, der in der Liste SELECT des Abfrageblocks angezeigt wird.

position

Position eines Ausdrucks in der Liste SELECT.

expr

Jeder Ausdruck in Tabellen im aktuellen Bereich.

Nutzungshinweise

  • Snowflake erlaubt in jedem Cube bis zu 7 Elemente (entspricht 128 Gruppierungssätzen).

Beispiele

Beginnen Sie mit dem Erstellen und Laden einer Tabelle mit Informationen zu Umsätzen einer Einzelhandelskette, die Filialen in verschiedenen Orten und Bundesstaaten/Außengebiete hat.

-- Create some tables and insert some rows.
CREATE TABLE products (product_ID INTEGER, wholesale_price REAL);
INSERT INTO products (product_ID, wholesale_price) VALUES 
    (1, 1.00),
    (2, 2.00);

CREATE TABLE sales (product_ID INTEGER, retail_price REAL, 
    quantity INTEGER, city VARCHAR, state VARCHAR);
INSERT INTO sales (product_id, retail_price, quantity, city, state) VALUES 
    (1, 2.00,  1, 'SF', 'CA'),
    (1, 2.00,  2, 'SJ', 'CA'),
    (2, 5.00,  4, 'SF', 'CA'),
    (2, 5.00,  8, 'SJ', 'CA'),
    (2, 5.00, 16, 'Miami', 'FL'),
    (2, 5.00, 32, 'Orlando', 'FL'),
    (2, 5.00, 64, 'SJ', 'PR');
Copy

Führen Sie eine Cube-Abfrage aus, die die Gewinne nach Ort und Bundesstaat sowie die Gesamtsumme für alle Bundesstaaten anzeigt. Im folgenden Beispiel wird eine Abfrage mit drei „Ebenen“ gezeigt:

  • Alle Orte

  • Alle Bundesstaaten

  • Alle Umsätze zusammen

In diesem Beispiel wird ORDER BY state, city NULLS LAST verwendet, um sicherzustellen, dass die Zusammenfassung (Rollup) für jeden Bundesstaat sofort nach dem Rollup aller Städte in diesem Bundesstaat kommt und dass das Gesamt-Rollup am Ende der Ausgabe angezeigt wird.

SELECT state, city, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit 
 FROM products AS p, sales AS s
 WHERE s.product_ID = p.product_ID
 GROUP BY CUBE (state, city)
 ORDER BY state, city NULLS LAST
 ;
+-------+---------+--------+
| STATE | CITY    | PROFIT |
|-------+---------+--------|
| CA    | SF      |     13 |
| CA    | SJ      |     26 |
| CA    | NULL    |     39 |
| FL    | Miami   |     48 |
| FL    | Orlando |     96 |
| FL    | NULL    |    144 |
| PR    | SJ      |    192 |
| PR    | NULL    |    192 |
| NULL  | Miami   |     48 |
| NULL  | Orlando |     96 |
| NULL  | SF      |     13 |
| NULL  | SJ      |    218 |
| NULL  | NULL    |    375 |
+-------+---------+--------+
Copy

Einige Rollup-Zeilen enthalten NULL-Werte. Die letzte Zeile in der Tabelle enthält beispielsweise einen NULL-Wert für den Ort und einen NULL-Wert für den Bundesstaat, da die Daten für alle Orte und Bundesstaaten und nicht für einen bestimmten Ort und einen bestimmten Bundesstaat vorliegen.

Die Dienstprogrammfunktion GROUPING kann helfen, zwischen NULL-Werten zu unterscheiden, die sich aus der Cube-Aggregation ergeben, im Vergleich zu tatsächlichen NULL-Werten in den Daten.

Sowohl GROUP BY CUBE als auch GROUP BY ROLLUP generieren eine Zeile für jedes Ort/Bundesstaat-Paar, und beide GROUP BY-Klauseln generieren auch Rollup-Zeilen für jeden einzelnen Bundesstaat und für alle Bundesstaaten zusammen. Der Unterschied zwischen den beiden GROUP BY-Klauseln besteht darin, dass GROUP BY CUBE auch eine Ausgabezeile für jeden Ortsnamen (Miami, SJ usw.) generiert.

Seien Sie vorsichtig bei der Verwendung von GROUP BY CUBE bei hierarchischen Daten. In diesem Beispiel enthält die Zeile für SJ Summen sowohl für die Orte SJ im Bundesstaat CA als auch für den Ort SJ im Außengebiet PR, obwohl die einzige Beziehung zwischen diesen Orten darin besteht, dass sie denselben Namen haben. Verwenden Sie GROUP BY ROLLUP, um hierarchische Daten zu analysieren, und GROUP BY CUBE, um Daten über unabhängige Achsen zu analysieren.