Die Gruppierung von Daten und die Bildung entsprechender Summen gehört zu den grundlegenden Möglichkeiten von SQL. Manchmal ist es dann jedoch so, dass die einfache Summenbildung nicht ausreicht, speziell dann, wenn neben den Einzelsummen auch noch eine Gesamt- oder gar Zwischensummen benötigt werden. Läßt sich Letzteres mit dem, seit Version 4.1.1 des MySQL-Servers verfügbaren, GROUP BY-Modifikator „WITH ROLLUP” noch relativ einfach realisieren, so stellt die Bildung einer fortlaufenden Summe oftmals ein Problem dar.
Es seien die Tagesverdienste eines Shops in einem bestimmten Zeitraum gegeben. Gesucht werden die Verdienste je Monat, der Gesamtverdienst im Zeitraum und die fortlaufende Aufsummierung der Monatsverdienste. Das klingt erstmal nicht kompliziert, wird es aber wenn man an die konkrete Umsetzung innerhalb einer SQL-Abfrage geht. Wie eine mögliche Lösung für MySQL aussehen kann, möchte ich hier vorstellen.
Zuerst werden ein paar Beispieldaten benötigt:
CREATE TABLE bsp_daten (
creation_date datetime,
profit float
);
INSERT INTO bsp_daten VALUES
('2009-01-10', 128.17),
('2009-02-11', 150),
('2009-05-04', 80.5),
('2009-01-21', 12.23),
('2009-02-22', 1.48),
('2009-04-14', 18.75),
('2009-01-28', 24.8),
('2009-04-23', 109.34),
('2009-07-07', 24),
('2009-06-12', 75.25);
Die Ermittlung der Verdienste je Monat ist die leichteste Übung, da eine einfache Gruppierung mit Verwendung der Aggregatfunktion SUM() ausreicht.
SELECT month(d.creation_date) "Monat", format(sum(d.profit), 2) "Verdienst"
FROM bsp_daten d
GROUP BY month(d.creation_date);
Ergebnis:
| Monat | Verdienst |
|---|
| 1 | 165.20 |
| 2 | 151.48 |
| 4 | 128.09 |
| 5 | 80.50 |
| 6 | 75.25 |
| 7 | 24.00 |
Der zweite Teil, die zusätzliche Bildung der Gesamtsumme ist Dank des GROUP BY-Modifikators „WITH ROLLUP” auch kein Problem.
SELECT month(d.creation_date) "Monat", format(sum(d.profit), 2) "Verdienst"
FROM bsp_daten d
GROUP BY month(d.creation_date) WITH rollup;
Ergebnis:
| Monat | Verdienst |
|---|
| 1 | 165.20 |
| 2 | 151.48 |
| 4 | 128.09 |
| 5 | 80.50 |
| 6 | 75.25 |
| 7 | 24.00 |
| NULL | 624.52 |
Fehlt jetzt nur noch die fortlaufende Summe je Monat und damit die Zwischensummen. Hierzu lässt sich eine Servervariable quasi als Zwischenspeicher verwenden. In einer ersten Abfrage bilden wir damit die fortlaufende Summe zu jeder Datenzeile.
SET @sum_profit = 0;
SELECT d. creation_date, d.profit, @sum_profit:=@sum_profit+d.profit sum_profit
FROM bsp_daten d
ORDER BY d. creation_date;
Diese „Zwischenlösung” brauchen wir jetzt nur noch über eine äußere Abfrage wie gefordert zu gruppieren.
SET @sum_profit = 0;
SELECT month(i.creation_date) "Monat",
format(sum(i.profit), 2) "Verdienst",
format(max(i.sum_profit), 2) "fortlaufender Verdienst"
FROM (
SELECT d. creation_date, d.profit, @sum_profit:=@sum_profit+d.profit sum_profit
FROM bsp_daten d
ORDER BY d. creation_date) i
GROUP BY month(i.creation_date) WITH rollup;
Ergebnis:
| Monat | Verdienst | fortlaufender Verdienst |
|---|
| 1 | 165.20 | 165.20 |
| 2 | 151.48 | 316.68 |
| 4 | 128.09 | 444.77 |
| 5 | 80.50 | 525.27 |
| 6 | 75.25 | 600.52 |
| 7 | 24.00 | 624.52 |
| NULL | 624.52 | 624.52 |
Da es sich bei den Verdiensten um integrale Werte handelt und damit der Größte gleichzeitig auch der Letzte ist, lässt sich in diesem Beispiel die Aggregatfunktion MAX() verwenden. Für alternierende Werte funktioniert dies nicht (der größte Wert ist dann u.U. nicht gleichzeitig der letzte Wert) und man müsste stattdessen eine zusätzliche Subquery verwenden.
