Thomas Wiedmann https://twiedmann.de
• Sie befinden sich hier: Startseite > SQL-Backstube > OLAP Auswertungen mit GROUP BY und ROLLUP

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

10.10.2010: OLAP Auswertungen mit GROUP BY und ROLLUP

Problemstellung

Aus dem Bereich OLAP (Online Analytical Processing) haben es in den letzten Jahren auch einige spezielle Reporting-Befehle in die Datenbankwelt geschafft. Beispielweise der Befehl ROLLUP. Damit lassen sich komplexere Gruppierungen mit "Zwischensummen" erzeugen, die wichtig für eine übersichtliche Zahlendarstellung im Reporting sind. Mit ROLLUP werden also sogenannte Superaggregate für ausgewählte Gruppierungsspalten erzeugt.


Getestet mit folgender Datenbank:

* IBM DB2 9.1
* MySQL 5.1.30
* ORACLE 10g 10.2 EE


Tabellen erzeugen

Listing 1:

  1. /**
  2. * DB2 und MySQL
  3. */
  4. CREATE TABLE kunde (
  5. id INT NOT NULL,
  6. name VARCHAR(30) NOT NULL,
  7. plz INT NOT NULL,
  8. PRIMARY KEY (id)
  9. );
  11. CREATE TABLE rechnung (
  12. id INT NOT NULL,
  13. kunde_id INT NOT NULL,
  14. datum DATE NOT NULL,
  15. betrag DEC(10,2) NOT NULL,
  16. PRIMARY KEY (id),
  17. CONSTRAINT fk_rechnung_01
  18. FOREIGN KEY (kunde_id)
  19. REFERENCES kunde (id)
  20. );

Listing 2:

  1. /**
  2. * ORACLE
  3. */
  4. CREATE TABLE kunde (
  5. id INT NOT NULL,
  6. name VARCHAR2(30) NOT NULL,
  7. plz INT NOT NULL,
  8. PRIMARY KEY (id)
  9. );
  11. CREATE TABLE rechnung (
  12. id INT NOT NULL,
  13. kunde_id INT NOT NULL,
  14. datum DATE NOT NULL,
  15. betrag DEC(10,2) NOT NULL,
  16. PRIMARY KEY (id),
  17. CONSTRAINT fk_rechnung_01
  18. FOREIGN KEY (kunde_id)
  19. REFERENCES kunde (id)
  20. );

Testdaten einfügen

Listing 3:

  1. INSERT INTO kunde VALUES ( 1, 'Kunde-1', 20000);
  2. INSERT INTO kunde VALUES ( 2, 'Kunde-2', 30000);
  3. INSERT INTO kunde VALUES ( 3, 'Kunde-3', 40000);

Listing 4:

  1. /**
  2. * DB2 und MySQL
  3. */
  4. INSERT INTO rechnung VALUES
  5. ( 1, 1, '2010-01-01', 100),
  6. ( 2, 1, '2010-02-01', 500),
  7. ( 3, 2, '2010-03-01', 200),
  8. ( 4, 1, '2010-02-01', 100),
  9. ( 5, 1, '2010-05-01', 400),
  10. ( 6, 1, '2010-02-01', 500),
  11. ( 7, 3, '2010-01-01', 200),
  12. ( 8, 1, '2010-03-01', 200),
  13. ( 9, 2, '2010-02-01', 100),
  14. (10, 3, '2010-06-01', 500),
  15. (11, 1, '2010-04-01', 500),
  16. (12, 3, '2010-07-01', 200),
  17. (13, 1, '2010-06-01', 200),
  18. (14, 2, '2010-04-01', 100),
  19. (15, 3, '2010-03-01', 500);

Listing 5:

  1. /**
  2. * ORACLE
  3. */
  4. INSERT INTO rechnung VALUES ( 1, 1, TO_DATE('2010-01-01','YYYY-MM-DD'), 100);
  5. INSERT INTO rechnung VALUES ( 2, 1, TO_DATE('2010-02-01','YYYY-MM-DD'), 500);
  6. INSERT INTO rechnung VALUES ( 3, 2, TO_DATE('2010-03-01','YYYY-MM-DD'), 200);
  7. INSERT INTO rechnung VALUES ( 4, 1, TO_DATE('2010-02-01','YYYY-MM-DD'), 100);
  8. INSERT INTO rechnung VALUES ( 5, 1, TO_DATE('2010-05-01','YYYY-MM-DD'), 400);
  9. INSERT INTO rechnung VALUES ( 6, 1, TO_DATE('2010-02-01','YYYY-MM-DD'), 500);
  10. INSERT INTO rechnung VALUES ( 7, 3, TO_DATE('2010-01-01','YYYY-MM-DD'), 200);
  11. INSERT INTO rechnung VALUES ( 8, 1, TO_DATE('2010-03-01','YYYY-MM-DD'), 200);
  12. INSERT INTO rechnung VALUES ( 9, 2, TO_DATE('2010-02-01','YYYY-MM-DD'), 100);
  13. INSERT INTO rechnung VALUES (10, 3, TO_DATE('2010-06-01','YYYY-MM-DD'), 500);
  14. INSERT INTO rechnung VALUES (11, 1, TO_DATE('2010-04-01','YYYY-MM-DD'), 500);
  15. INSERT INTO rechnung VALUES (12, 3, TO_DATE('2010-07-01','YYYY-MM-DD'), 200);
  16. INSERT INTO rechnung VALUES (13, 1, TO_DATE('2010-06-01','YYYY-MM-DD'), 200);
  17. INSERT INTO rechnung VALUES (14, 2, TO_DATE('2010-04-01','YYYY-MM-DD'), 100);
  18. INSERT INTO rechnung VALUES (15, 3, TO_DATE('2010-03-01','YYYY-MM-DD'), 500);

Ziel ist es nun mit ROLLUP eine Umsatzauswertung über Jahr und Monat zu erzeugen.


IBM DB2 ROLLUP

Listing 6:

  1. /**
  2. * ROLLUP über Jahr und Monat (DB2)
  3. */
  4. SELECT YEAR(r.datum) AS jahr, MONTH(r.datum) AS monat, SUM(r.betrag) AS umsatz
  5. FROM rechnung r
  6. JOIN kunde k
  7. ON k.id = r.kunde_id
  8. WHERE r.datum BETWEEN '2010-01-01' AND '2010-12-31'
  9. GROUP BY ROLLUP(YEAR(r.datum), MONTH(r.datum))
  10. ORDER BY YEAR(r.datum), MONTH(r.datum);
  12. +------+-------+---------+
  13. ! JAHR ! MONAT ! UMSATZ !
  14. +------+-------+---------+
  15. ! 2010 | 1 | 300,00 |
  16. ! 2010 | 2 | 1200,00 |
  17. ! 2010 | 3 | 900,00 |
  18. ! 2010 | 4 | 600,00 |
  19. ! 2010 | 5 | 400,00 |
  20. ! 2010 | 6 | 700,00 |
  21. ! 2010 | 7 | 200,00 | <== Umsatz 2010 im Monat 07
  22. ! 2010 | | 4300,00 | <== Umsatz 2010
  23. ! | | 4300,00 | <== Gesamtumsatz
  24. +------+-------+---------+

Folgende Ergebnisse werden ausgegeben:

a) Der Umsatz pro Jahr und Monat gruppiert ( Monat 01 - 07 )
b) Der Umsatz pro Jahr ( Jahr 2010 )
c) Der Gesamtumsatz unabhängig vom Jahr und Monat.

Alle diese Ergebnisse und Summen werden "auf einen Schlag" ermittelt. Das ist schon mehr als praktisch.


ORACLE ROLLUP

Listing 7:

  1. /**
  2. * ROLLUP über Jahr und Monat (ORACLE)
  3. */
  4. SELECT TO_CHAR(r.datum,'YYYY') AS jahr, TO_CHAR(r.datum,'MM') AS monat, SUM(r.betrag) AS umsatz
  5. FROM rechnung r
  6. JOIN kunde k
  7. ON k.id = r.kunde_id
  8. WHERE r.datum BETWEEN TO_DATE('2010-01-01','YYYY-MM-DD') AND TO_DATE('2010-12-31','YYYY-MM-DD')
  9. GROUP BY ROLLUP(TO_CHAR(r.datum,'YYYY'), TO_CHAR(r.datum,'MM'))
  10. ORDER BY TO_CHAR(r.datum,'YYYY'), TO_CHAR(r.datum,'MM');
  12. +------+-------+---------+
  13. ! JAHR ! MONAT ! UMSATZ !
  14. +------+-------+---------+
  15. ! 2010 | 1 | 300,00 |
  16. ! 2010 | 2 | 1200,00 |
  17. ! 2010 | 3 | 900,00 |
  18. ! 2010 | 4 | 600,00 |
  19. ! 2010 | 5 | 400,00 |
  20. ! 2010 | 6 | 700,00 |
  21. ! 2010 | 7 | 200,00 | <== Umsatz 2010 im Monat 07
  22. ! 2010 | | 4300,00 | <== Umsatz 2010
  23. ! | | 4300,00 | <== Gesamtumsatz
  24. +------+-------+---------+

Der SQL muss zwar an ORACLE angepaßt werden, aber die Ergebnisse sind identisch mit dem aus Listing 6:


MySQL ROLLUP?

Listing 8:

  1. /**
  2. * ROLLUP über Jahr und Monat (MySQL)
  3. */
  4. SELECT YEAR(r.datum) AS jahr, MONTH(r.datum) AS monat, SUM(r.betrag) AS umsatz
  5. FROM rechnung r
  6. JOIN kunde k
  7. ON k.id = r.kunde_id
  8. WHERE r.datum BETWEEN '2010-01-01' AND '2010-12-31'
  9. GROUP BY YEAR(r.datum), MONTH(r.datum) WITH ROLLUP;
  11. +------+-------+---------+
  12. ! JAHR ! MONAT ! UMSATZ !
  13. +------+-------+---------+
  14. ! 2010 | 1 | 300,00 |
  15. ! 2010 | 2 | 1200,00 |
  16. ! 2010 | 3 | 900,00 |
  17. ! 2010 | 4 | 600,00 |
  18. ! 2010 | 5 | 400,00 |
  19. ! 2010 | 6 | 700,00 |
  20. | 2010 | 7 | 200.00 | <== Umsatz 2010 im Monat 07
  21. ! 2010 | NULL | 4300,00 | <== Umsatz 2010
  22. | NULL | NULL | 4300.00 | <== Gesamtumsatz
  23. +------+-------+---------+
  24. 9 rows in set (0.01 sec)

Der SQL muss zwar wieder angepaßt werden (siehe WITH ROLLUP und ORDER BY). MySQL erlaubt leider keinen zusätzlichen ORDER BY bei einem ROLLUP. Probiert man es trotzdem, handelt man sich den Fehler "ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY" ein. Aber auch ohne ORDER BY liefert MySQL das korrekte Ergebnis.


ROLLUP ohne ROLLUP?

Eigentlich besteht ein ROLLUP nur aus mehreren einzelnen GROUP BY Abfragen, deren Einzelergebnisse mit HILFE von UNION ALL aneinander gesetzt werden.


Listing 9:

  1. SELECT YEAR(r.datum) AS jahr, MONTH(r.datum) AS monat, SUM(r.betrag) AS umsatz
  2. FROM rechnung r
  3. JOIN kunde k
  4. ON k.id = r.kunde_id
  5. WHERE r.datum BETWEEN '2010-01-01' AND '2010-12-31'
  6. GROUP BY YEAR(r.datum), MONTH(r.datum)
  8. UNION ALL
  10. SELECT YEAR(r.datum) AS jahr, NULL AS monat, SUM(r.betrag) AS umsatz
  11. FROM rechnung r
  12. JOIN kunde k
  13. ON k.id = r.kunde_id
  14. WHERE r.datum BETWEEN '2010-01-01' AND '2010-12-31'
  15. GROUP BY YEAR(r.datum)
  17. UNION ALL
  19. SELECT NULL AS jahr, NULL AS monat, SUM(r.betrag) AS umsatz
  20. FROM rechnung r
  21. JOIN kunde k
  22. ON k.id = r.kunde_id
  23. WHERE r.datum BETWEEN '2010-01-01' AND '2010-12-31';
  25. +------+-------+---------+
  26. ! JAHR ! MONAT ! UMSATZ !
  27. +------+-------+---------+
  28. ! 2010 | 1 | 300,00 |
  29. ! 2010 | 2 | 1200,00 |
  30. ! 2010 | 3 | 900,00 |
  31. ! 2010 | 4 | 600,00 |
  32. ! 2010 | 5 | 400,00 |
  33. ! 2010 | 6 | 700,00 |
  34. | 2010 | 7 | 200.00 | <== Umsatz 2010 im Monat 07
  35. ! 2010 | NULL | 4300,00 | <== Umsatz 2010
  36. | NULL | NULL | 4300.00 | <== Gesamtumsatz
  37. +------+-------+---------+
  38. 9 rows in set (0.01 sec)

MySQL - ROLLUP Bugs?

Bei den aktuellen MySQL-Versionen sind noch Bugs im Zusammenhang mit ROLLUP gemeldet. Mit der neuen Version 5.5.6 sollen diese behoben sein. Siehe http://bugs.mysql.com/bug.php?id=54477

Resumee

Was für Probleme es geben kann einen Report basieren auf ROLLUP bei verschiedenen Datenbanken zu realisieren, haben wir eben gesehen. Mal wieder wird der SQL-Standard nur teilweise eingehalten und jeder Datenbankhersteller hat so seine "L.. äh Probleme im Keller".


https://twiedmann.de


Sitemap - Inhaltsverzeichnis

© 2002-2015 by Thomas Wiedmann : (Stand : 11.01.2015).