Die GROUP BY-Klausel erlaubt einen WITH ROLLUP-Modifikator, der bewirkt, dass zusätzliche Zeilen zur Ausgabe der Zusammenfassung hinzugefügt werden. Diese Zeilen stellen übergeordnete (oder super-aggregierte) Zusammenfassungsoperationen dar. Mit ROLLUP können Sie also mit einer einzigen Abfrage Fragen auf mehreren Analyseebenen beantworten. Es kann z. B. zur Unterstützung von OLAP-Operationen (Online Analytical Processing) verwendet werden.
Angenommen, eine Tabelle mit dem Namen "Umsatz" hat Spalten für Jahr, Land, Produkt und Gewinn, um die Umsatzrentabilität zu erfassen:
CREATE TABLE Verkäufe ( jahr INT NOT NULL, land VARCHAR(20) NOT NULL, produkt VARCHAR(32) NOT NULL, gewinn INT );
Der Inhalt der Tabelle kann pro Jahr mit einem einfachen GROUP BY wie folgt zusammengefasst werden:
mysql> SELECT Jahr, SUM(Gewinn) FROM Umsatz GROUP BY Jahr; +------+-------------+ | Jahr | SUM(Gewinn) | +------+-------------+ | 2000 | 4525 | | 2001 | 3010 | +------+-------------+
Diese Ausgabe zeigt den Gesamtgewinn für jedes Jahr, aber wenn Sie auch den Gesamtgewinn über alle Jahre summiert ermitteln wollen, müssen Sie die einzelnen Werte selbst addieren oder eine zusätzliche Abfrage durchführen.
Oder Sie können ROLLUP verwenden, das beide Analyseebenen mit einer einzigen Abfrage ermöglicht. Wenn Sie der GROUP BY-Klausel einen WITH ROLLUP-Modifikator hinzufügen, erzeugt die Abfrage eine weitere Zeile, die die Gesamtsumme aller Jahreswerte anzeigt:
mysql> SELECT Jahr, SUM(Gewinn) FROM Umsatz GROUP BY Jahr WITH ROLLUP; +------+-------------+ | Jahr | SUM(Gewinn) | +------+-------------+ | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | +------+-------------+
Die Zeile für das Superaggregat der Gesamtsumme ist durch den Wert NULL in der Spalte Jahr gekennzeichnet.
ROLLUP hat eine komplexere Wirkung, wenn es mehrere GROUP BY-Spalten gibt. In diesem Fall erzeugt die Abfrage jedes Mal, wenn es einen "Bruch" (Wertänderung) in einer der letzten Gruppierungsspalten gibt, eine zusätzliche Super-Aggregat-Zusammenfassungszeile.
Ohne ROLLUP könnte eine Zusammenfassung der Verkaufstabelle nach Jahr, Land und Produkt zum Beispiel so aussehen:
mysql> SELECT Jahr, Land, Produkt, SUM(Gewinn) -> FROM umsatz -> GROUP BY Jahr, Land, Produkt; +------+---------+------------+-------------+ | Jahr | Land | Produkt | SUM(Gewinn) | +------+---------+------------+-------------+ | 2000 | Finnland | Computer | 1500 | | 2000 | Finnland | Telefon | 100 | | 2000 | Indien | Rechner | 150 | | 2000 | Indien | Computer | 1200 | | 2000 | USA | Rechner | 75 | | 2000 | USA | Computer | 1500 | | 2001 | Finnland | Telefon | 10 | | 2001 | USA | Rechner | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | +------+---------+------------+-------------+
Die Ausgabe enthält zusammenfassende Werte nur auf der Analyseebene Jahr/Land/Produkt. Wenn ROLLUP hinzugefügt wird, erzeugt die Abfrage mehrere zusätzliche Zeilen:
mysql> SELECT Jahr, Land, Produkt, SUM(Gewinn) -> FROM umsatz -> GROUP BY Jahr, Land, Produkt WITH ROLLUP; +------+---------+------------+-------------+ | Jahr | Land | Produkt | SUM(Gewinn) | +------+---------+------------+-------------+ | 2000 | Finnland | Computer | 1500 | | 2000 | Finnland | Telefon | 100 | | 2000 | Finnland | NULL | 1600 | | 2000 | Indien | Rechner | 150 | | 2000 | Indien | Computer | 1200 | | 2000 | Indien | NULL | 1350 | | 2000 | USA | Rechner | 75 | | 2000 | USA | Computer | 1500 | | 2000 | USA | NULL | 1575 | | 2000 | NULL | NULL | 4525 | | 2001 | Finnland | Telefon | 10 | | 2001 | Finnland | NULL | 10 | | 2001 | USA | Rechner | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | | 2001 | USA | NULL | 3000 | | 2001 | NULL | NULL | 3010 | | NULL | NULL | NULL | 7535 | +------+---------+------------+-------------+
Bei dieser Abfrage führt das Hinzufügen von ROLLUP dazu, dass die Ausgabe Zusammenfassungsinformationen auf vier Analyseebenen enthält, nicht nur auf einer. Die ROLLUP-Ausgabe ist wie folgt zu interpretieren:
*
Following each set of product rows for a given year and country, an extra summary row is produced showing the total for all products. These rows have the product column set to NULL.
*
Following each set of rows for a given year, an extra summary row is produced showing the total for all countries and products. These rows have the country and products columns set to NULL.
*
Finally, following all other rows, an extra summary row is produced showing the grand total for all years, countries, and products. This row has the year, country, and products columns set to NULL.
Weitere Überlegungen bei der Verwendung von ROLLUP
Die folgenden Punkte führen einige Verhaltensweisen auf, die für die MySQL-Implementierung von ROLLUP spezifisch sind:
Wenn Sie ROLLUP verwenden, können Sie nicht gleichzeitig eine ORDER BY-Klausel verwenden, um die Ergebnisse zu sortieren. Mit anderen Worten: ROLLUP und ORDER BY schließen sich gegenseitig aus. Sie haben jedoch immer noch eine gewisse Kontrolle über die Sortierreihenfolge. GROUP BY in MySQL sortiert die Ergebnisse, und Sie können explizite ASC- und DESC-Schlüsselwörter mit in der GROUP BY-Liste genannten Spalten verwenden, um die Sortierreihenfolge für einzelne Spalten festzulegen. (Die übergeordneten Zusammenfassungszeilen, die durch ROLLUP hinzugefügt werden, erscheinen unabhängig von der Sortierreihenfolge immer noch nach den Zeilen, aus denen sie berechnet werden).
LIMIT kann verwendet werden, um die Anzahl der an den Client zurückgegebenen Zeilen zu begrenzen. LIMIT wird nach ROLLUP angewendet, so dass die Begrenzung für die zusätzlichen Zeilen gilt, die durch ROLLUP hinzugefügt werden. Zum Beispiel:
mysql> SELECT Jahr, Land, Produkt, SUM(Gewinn) -> FROM Verkäufe -> GROUP BY Jahr, Land, Produkt WITH ROLLUP -> LIMIT 5; +------+---------+------------+-------------+ | Jahr | Land | Produkt | SUM(Gewinn) | +------+---------+------------+-------------+ | 2000 | Finnland | Computer | 1500 | | 2000 | Finnland | Telefon | 100 | | 2000 | Finnland | NULL | 1600 | | 2000 | Indien | Rechner | 150 | | 2000 | Indien | Computer | 1200 | +------+---------+------------+-------------+
Die Verwendung von LIMIT mit ROLLUP kann zu Ergebnissen führen, die schwieriger zu interpretieren sind, weil Sie weniger Kontext zum Verständnis der Superaggregatzeilen haben.
Die NULL-Indikatoren in jeder Superaggregatzeile werden erzeugt, wenn die Zeile an den Client gesendet wird. Der Server prüft die in der GROUP BY-Klausel genannten Spalten, die auf die Spalte ganz links folgen, deren Wert sich geändert hat. Für jede Spalte in der Ergebnismenge mit einem Namen, der lexikalisch mit einem dieser Namen übereinstimmt, wird ihr Wert auf NULL gesetzt. (Wenn Sie die Gruppierung der Spalten nach Spaltennummer angeben, identifiziert der Server die auf NULL zu setzenden Spalten anhand der Nummer).
Da die NULL-Werte in den Superaggregat-Zeilen zu einem so späten Zeitpunkt der Abfrageverarbeitung in die Ergebnismenge eingefügt werden, können Sie sie nicht als NULL-Werte innerhalb der Abfrage selbst testen. Sie können zum Beispiel nicht HAVING product IS NULL zur Abfrage hinzufügen, um alle Zeilen außer den Superaggregat-Zeilen aus der Ausgabe zu entfernen.
Andererseits erscheinen die NULL-Werte auf der Client-Seite als NULL und können als solche mit jeder MySQL-Client-Programmierschnittstelle getestet werden.
