Kategorien:: Abfragesyntax

GROUP BY ROLLUP¶

GROUP BY ROLLUP ist eine Erweiterung der GROUP BY-Klausel, die aggregierte Zeilen auf mehreren Ebenen einer Hierarchie erzeugt (zusätzlich zu den detaillierten gruppierten Zeilen). Wenn Sie beispielsweise nach Ort und Bundesstaat gruppieren, erstellt ROLLUP Aggregationen für jede Ort/Bundesstaat-Kombination, für jeden Bundesstaat insgesamt sowie eine Gesamtsumme über alle Bundesstaaten hinweg. Diese Aggregationen werden mit den gleichen Aggregatfunktionen berechnet, die in der SELECT-Klausel angegeben sind.

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

Sie können sich ein Rollup als das Generieren mehrerer Resultsets vorstellen, von denen jedes (nach der ersten) das Aggregat des vorherigen Resultsets ist. Wenn Sie zum Beispiel eine Einzelhandelskette besitzen, möchten Sie sich vielleicht den Gewinn anzeigen für:

Jede Filiale.
Jede Stadt (große Städte verfügen möglicherweise über mehrere Filialen).
Jeden Bundesstaat.
Alle (alle Filialen in allen Bundesstaaten).

Sie können separate Berichte erstellen, um diese Informationen abzurufen. Effizienter ist es jedoch, die Daten einmal zu scannen.

Wenn Sie mit dem Konzept von Gruppierungssätzen vertraut sind, können Sie sich eine ROLLUP-Gruppierung als Entsprechung zu einer Reihe von Gruppierungssätzen vorstellen. Es handelt sich dabei aber im Wesentlichen um eine kürzere Spezifikation. Die N-Elemente einer ROLLUP-Spezifikation entsprechen N+1 GROUPING SETS.

Siehe auch:¶

GROUPING (Dienstprogrammfunktion, um zu ermitteln, welche Gruppierungsebene jede Zeile erzeugt hat)
GROUP BY GROUPING SETS
GROUP BY CUBE

Syntax¶

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] ROLLUP ( 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¶

Da die Abfrage auf immer höheren Ebenen aggregiert wird, werden in mehr Spalten der einzelnen Zeilen NULL-Werte angezeigt. Das ist angemessen. Im folgenden Beispiel lautet die city-Spalte für das Aggregat auf der Bundesstaatenebene NULL. Das liegt daran, dass der Wert in der profit-Spalte nicht einem Ort zugeordnet ist. Auf ähnliche Weise stammen in der Gesamtsumme, die Daten aus allen Bundesstaaten und allen Orten zusammenfasst, die Einnahmen nicht aus einem bestimmten Bundesland oder einem bestimmten Ort. Daher weist sowohl die state- als auch die``city``-Spalte in dieser Zeile den Wert NULL auf.
Die Abfrage sollte die „höchste Ebene“ in den Klammern nach ROLLUP zuerst auflisten. Beispiel: Da Bundesstaaten Orte enthalten, sollte die ROLLUP-Klausel beim Zusammenfassen der Daten von Bundesstaaten und Orten wie folgt aussehen: GROUP BY ROLLUP (state, city).

Wenn Sie die Reihenfolge der Spaltennamen umkehren, erhalten Sie ein Ergebnis, das wahrscheinlich nicht Ihren Erwartungen entspricht. Im folgenden Beispiel führt das Umkehren der Reihenfolge von city und state in der ROLLUP-Klausel zu einem – zumindest teilweise – falschen Ergebnis, da es sowohl in Kalifornien als auch in Puerto Rico eine Stadt mit dem Namen San José (SJ) gibt und Sie die Einnahmen aus den unterschiedlichen San Josés wahrscheinlich nicht zusammenfassen möchten, außer in der Endsumme aller Einnahmen. (Eine alternative Möglichkeit, die Kombination von Daten aus verschiedenen Städten mit demselben Namen zu vermeiden, besteht darin, für jede Stadt eine eindeutige ID zu erstellen und in der Abfrage anstelle des Namens die ID zu verwenden).
Die Dienstprogrammfunktion GROUPING kann helfen, zwischen NULL-Werten, die sich aus der Rollup-Aggregation ergeben, und den tatsächlichen NULL-Werten in den Daten zu unterscheiden. GROUPING gibt 0 für eine Zeile zurück, die nach einer bestimmten Spalte gruppiert ist, und 1 für eine Zeile, in der die Spalte aufgrund der Aggregation NULL anzeigt.

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 Rollup-Abfrage aus, die den Gewinn nach Ort und Bundesstaat sowie die Gesamtsumme für alle Bundesstaaten anzeigt. Die Abfrage erzeugt drei „Ebenen“ der Aggregation:

Die einzelnen Städte.
Jeden Bundesstaat.
Alle Einnahmen aus allen Bundesstaaten zusammen.

In dieser Abfrage wird ORDER BY state, city NULLS LAST verwendet, um sicherzustellen, dass die Zusammenfassung (Rollup) für jeden Bundesstaat sofort nach dem Rollup aller Orte 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 ROLLUP (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  | 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.