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 [ , ... ] ] )
[ ... ]

Wobei:

groupRollup ::= { <column_alias> | <position> | <expr> }
Spaltenalias

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

Position

Position eines Ausdrucks in der Liste SELECT.

Ausdruck

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. Da Bundesstaaten beispielsweise Städte enthalten, sollte die Klausel ...ROLLUP (State, City) lauten, wenn Sie Daten für Bundesstaaten und Städte zusammenfassen. Wenn Sie die Reihenfolge der Spaltennamen umkehren, erhalten Sie ein Ergebnis, das wahrscheinlich nicht Ihren Wünschen entspricht. Im folgenden Beispiel ist das Ergebnis falsch, zumindest teilweise weil es sowohl in Kalifornien als auch 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. (Das Problem, Daten aus verschiedenen Städten zu kombinieren, die denselben Namen tragen, hätte vermieden werden können, indem für jede Stadt eine eindeutige ID erstellt und in der Abfrage anstelle des Namens die ID verwendet wird.)

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');

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.

    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)
     ;
    

    Ausgabe:

    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)
     ;
    +-------+---------+--------+
    | STATE | CITY    | PROFIT |
    |-------+---------+--------|
    | CA    | SF      |     13 |
    | CA    | SJ      |     26 |
    | FL    | Miami   |     48 |
    | FL    | Orlando |     96 |
    | PR    | SJ      |    192 |
    | CA    | NULL    |     39 |
    | FL    | NULL    |    144 |
    | PR    | NULL    |    192 |
    | NULL  | NULL    |    375 |
    +-------+---------+--------+