Thomas Wiedmann http://www.twiedmann.de
• Sie befinden sich hier: Startseite > SQL-Backstube > Pivot-ähnliche Auswertungen

Die SQL-Backstube

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

14.08.2010: Pivot-ähnliche Auswertungen

Problemstellung

Top-Ten Listen und Statistische Auswertungen werden häufig benötigt. Zum Einen werden dafür Gruppierungen benötigt, zum anderen müssen "vertikale" Ergebnisse nebeneinander, also "horizontal" dargestellt werden. Diese Anforderung widerspricht eigentlich den üblichen Prinzipien von SQL, aber trotzdem will der Chef, der Controller und der VB (Vertriebsbeauftragte) seine gewohnte Auswertung. Also an's Werk...

Getestet mit folgenden Datenbanken:

* MySQL 5.1.30

Datenbank-Tabellen

Die Datenbank-Tabellen hierfür sehen wie folgt aus:

Listing 1:

  1. /**
  2. * Kunden
  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. );
  10. /**
  11. * Rechnungen
  12. */
  13. CREATE TABLE rechnung (
  14. id INT NOT NULL,
  15. kunde_id INT NOT NULL,
  16. datum DATE NOT NULL,
  17. betrag DEC(10,2) NOT NULL,
  18. PRIMARY KEY (id),
  19. CONSTRAINT fk_rechnung_01
  20. FOREIGN KEY (kunde_id)
  21. REFERENCES kunde (id)
  22. );

Testdaten

Nun noch die Testdaten für die nachfolgenden SQL-Abfragen.

Listing 2:

  1. /**
  2. * Kunden
  3. */
  4. INSERT INTO kunde VALUES
  5. ( 1, 'Kunde-1', 20000),
  6. ( 2, 'Kunde-2', 30000),
  7. ( 3, 'Kunde-3', 40000);
  8. /**
  9. * Rechnungen
  10. */
  11. INSERT INTO rechnung VALUES
  12. ( 1, 1, '2010-01-01', 100),
  13. ( 2, 1, '2010-02-01', 500),
  14. ( 3, 2, '2010-03-01', 200),
  15. ( 4, 1, '2010-02-01', 100),
  16. ( 5, 1, '2010-05-01', 400),
  17. ( 6, 1, '2010-02-01', 500),
  18. ( 7, 3, '2010-01-01', 200),
  19. ( 8, 1, '2010-03-01', 200),
  20. ( 9, 2, '2010-02-01', 100),
  21. (10, 3, '2010-06-01', 500),
  22. (11, 1, '2010-04-01', 500),
  23. (12, 3, '2010-07-01', 200),
  24. (13, 1, '2010-06-01', 200),
  25. (14, 2, '2010-04-01', 100),
  26. (15, 3, '2010-03-01', 500);

TOP Kunden - Abfrage

Der Chef möchte seine Top Kunden. Also diese mit dem höchsten Umsatz im laufenden Jahr. Der Umsatz pro Kunde wird errechnet mit SUM(r.betrag). Die Spalte erhält nun den Namen "umsatz". Die Sortierung ist absteigend (DESC), folglich steht der Kunde mit dem höchsten Umsatz oben.

Listing 3:

  1. /**
  2. * TOP Kunden
  3. */
  4. SELECT r.kunde_id, MAX(k.name) AS name, 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 r.kunde_id
  10. ORDER BY SUM(r.betrag) DESC;
  11. +----------+---------+---------+
  12. | kunde_id | name | umsatz |
  13. +----------+---------+---------+
  14. | 1 | Kunde-1 | 2500.00 |
  15. | 3 | Kunde-3 | 1400.00 |
  16. | 2 | Kunde-2 | 400.00 |
  17. +----------+---------+---------+
  18. 3 rows in set (0.00 sec)

Als kleine Variante zur obigen Abfrage, erfolgt hier die Sortierung direkt mit Hilfe der Spalte "umsatz" und nicht mehr nach der Summenberechnung "SUM(r.betrag)".

Listing 4:

  1. /**
  2. * TOP Kunden
  3. */
  4. SELECT r.kunde_id, MAX(k.name) AS name, 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 r.kunde_id
  10. ORDER BY umsatz DESC;
  11. +----------+---------+---------+
  12. | kunde_id | name | umsatz |
  13. +----------+---------+---------+
  14. | 1 | Kunde-1 | 2500.00 |
  15. | 3 | Kunde-3 | 1400.00 |
  16. | 2 | Kunde-2 | 400.00 |
  17. +----------+---------+---------+
  18. 3 rows in set (0.00 sec)

Quartalsauswertung

Der Controller möchte seine Quartalsauswertung, soll er bekommen. Für uns natürlich kein Problem. Mit Hilfe des CASE Befehls erzeugen wir vier Spalten mit der Quartalszuordnung und summieren die Beträge.

Listing 5:

  1. /**
  2. * Quartalauswertung des Umsatzes
  3. */
  4. SELECT SUM( CASE
  5. WHEN r.datum BETWEEN '2010-01-01' AND '2010-03-31' THEN r.betrag
  6. END
  7. ) AS '1.Quartal',
  8. SUM( CASE
  9. WHEN r.datum BETWEEN '2010-04-01' AND '2010-06-30' THEN r.betrag
  10. END
  11. ) AS '2.Quartal',
  12. SUM( CASE
  13. WHEN r.datum BETWEEN '2010-07-01' AND '2010-09-30' THEN r.betrag
  14. END
  15. ) AS '3.Quartal',
  16. SUM( CASE
  17. WHEN r.datum BETWEEN '2010-10-01' AND '2010-12-31' THEN r.betrag
  18. END
  19. ) AS '4.Quartal'
  20. FROM rechnung r
  21. WHERE r.datum BETWEEN '2010-01-01' AND '2010-12-31';
  22. +-----------+-----------+-----------+-----------+
  23. | 1.Quartal | 2.Quartal | 3.Quartal | 4.Quartal |
  24. +-----------+-----------+-----------+-----------+
  25. | 2400.00 | 1700.00 | 200.00 | NULL |
  26. +-----------+-----------+-----------+-----------+
  27. 1 row in set (0.00 sec)

Umsatzstärkste Regionen

Der VB benötigt eine Darstellung der umsatzstärksten Regionen nach Postleitzahl (plz) sortiert. Da wir nun richtig in Fahrt sind, ist auch dies keine echte Herausforderung mehr.

Listing 6:

  1. /**
  2. * Quartalauswertung der umsatzstärksten Regionen
  3. */
  4. SELECT k.plz,
  5. SUM(r.betrag) AS umsatz,
  6. SUM( CASE
  7. WHEN r.datum BETWEEN '2010-01-01' AND '2010-03-31' THEN r.betrag
  8. END
  9. ) AS '1.Quartal',
  10. SUM( CASE
  11. WHEN r.datum BETWEEN '2010-04-01' AND '2010-06-30' THEN r.betrag
  12. END
  13. ) AS '2.Quartal',
  14. SUM( CASE
  15. WHEN r.datum BETWEEN '2010-07-01' AND '2010-09-30' THEN r.betrag
  16. END
  17. ) AS '3.Quartal',
  18. SUM( CASE
  19. WHEN r.datum BETWEEN '2010-10-01' AND '2010-12-31' THEN r.betrag
  20. END
  21. ) AS '4.Quartal'
  22. FROM rechnung r
  23. JOIN kunde k
  24. ON k.id = r.kunde_id
  25. WHERE r.datum BETWEEN '2010-01-01' AND '2010-12-31'
  26. GROUP BY k.plz
  27. ORDER BY umsatz;
  28. +-------+---------+-----------+-----------+-----------+-----------+
  29. | plz | umsatz | 1.Quartal | 2.Quartal | 3.Quartal | 4.Quartal |
  30. +-------+---------+-----------+-----------+-----------+-----------+
  31. | 30000 | 400.00 | 300.00 | 100.00 | NULL | NULL |
  32. | 40000 | 1400.00 | 700.00 | 500.00 | 200.00 | NULL |
  33. | 20000 | 2500.00 | 1400.00 | 1100.00 | NULL | NULL |
  34. +-------+---------+-----------+-----------+-----------+-----------+
  35. 3 rows in set (0.00 sec)

Fazit

Mit Hilfe von GROUP BY, SUM und CASE lassen sich mit wenig Aufwand einfache, Pivot-ähnliche Auswertungen erzeugen. Damit können echte OLAP Funktionen nicht ersetzt werden, aber auf die schnelle so eine Liste zu erstellen, sollte nun kein Problem mehr sein.



Sitemap - Inhaltsverzeichnis

© 2002-2017 by Thomas Wiedmann : kontakt (at) twiedmann (punkt) de (Stand : 11.01.2015).