Kategorien:

Abfragesyntax

GROUP BY ROLLUP

GROUP BY ROLLUP ist eine Erweiterung der GROUP BY-Klausel, die (zusätzlich zu den gruppierten Zeilen) Zeilen mit Zwischensummen erzeugt. Zwischensummenzeilen sind Zeilen, die weiter aggregieren, wessen Werte durch Berechnung derselben Aggregatfunktionen abgeleitet werden, die zur Erstellung der gruppierten Zeilen verwendet wurden.

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 (GROUP BY GROUPING SETS) vertraut sind, können Sie sich eine ROLLUP-Gruppierung als Entsprechung zu einer Reihe von Gruppierungssätzen vorstellen. Es handelt sich dabei im Wesentlichen um eine kürzere Spezifikation. Die N-Elemente einer ROLLUP-Spezifikation entsprechen N+1 GROUPING SETS.

Siehe auch:

GROUP BY GROUPING SETS

Syntax

SELECT ...
FROM ...
[ ... ]
GROUP BY ROLLUP ( groupRollup [ , groupRollup [ , ... ] ] )
[ ... ]
Copy

Wobei:

groupRollup ::= { <column_alias> | <position> | <expr> }
Copy
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 Ortsspalte für das Aggregat auf der Bundesstaatenebene NULL. Das liegt daran, dass der Wert in der Gewinnspalte nicht einer Stadt zugeordnet ist. Auf ähnliche Weise stammen in der Gesamtsumme, die Daten aus allen Bundesstaaten und allen Städten zusammenfasst, die Einnahmen nicht aus einem bestimmten Bundesstaat oder einer bestimmten Stadt. Daher weist sowohl die Bundesstaaten- als auch die Ortsspalte in dieser Zeile den Wert NULL auf.

  • Die Abfrage sollte die „höchste Ebene“ in den Klammern nach ROLLUP zuerst auflisten. Beispiel: Da Bundesstaaten Städte enthalten, sollte die ROLLUP-Klausel beim Zusammenfassen der Daten von Bundesstaaten und Städten wie folgt aussehen:

    ...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 Städten und Bundesstaaten 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).

Beispiele

Beginnen Sie mit dem Erstellen und Laden einer Tabelle mit Informationen zu Umsätzen einer Einzelhandelskette, die Filialen in verschiedenen Städten und Bundesstaaten/Regionen 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 Stadt und Bundesstaat sowie die Gesamtsumme für alle Bundesstaaten anzeigt.

Im folgenden Beispiel wird eine Abfrage mit drei „Ebenen“ gezeigt:

  • Die einzelnen Städte.

  • Jeden Bundesstaat.

  • 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 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.

Sprache: Deutsch