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

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);
9. /**
10. * Rechnungen
11. */
12. INSERT INTO rechnung VALUES
13. ( 1, 1, '2010-01-01', 100),
14. ( 2, 1, '2010-02-01', 500),
15. ( 3, 2, '2010-03-01', 200),
16. ( 4, 1, '2010-02-01', 100),
17. ( 5, 1, '2010-05-01', 400),
18. ( 6, 1, '2010-02-01', 500),
19. ( 7, 3, '2010-01-01', 200),
20. ( 8, 1, '2010-03-01', 200),
21. ( 9, 2, '2010-02-01', 100),
22. (10, 3, '2010-06-01', 500),
23. (11, 1, '2010-04-01', 500),
24. (12, 3, '2010-07-01', 200),
25. (13, 1, '2010-06-01', 200),
26. (14, 2, '2010-04-01', 100),
27. (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;
12. +----------+---------+---------+
13. | kunde_id | name | umsatz |
14. +----------+---------+---------+
15. | 1 | Kunde-1 | 2500.00 |
16. | 3 | Kunde-3 | 1400.00 |
17. | 2 | Kunde-2 | 400.00 |
18. +----------+---------+---------+
19. 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;
12. +----------+---------+---------+
13. | kunde_id | name | umsatz |
14. +----------+---------+---------+
15. | 1 | Kunde-1 | 2500.00 |
16. | 3 | Kunde-3 | 1400.00 |
17. | 2 | Kunde-2 | 400.00 |
18. +----------+---------+---------+
19. 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';
23. +-----------+-----------+-----------+-----------+
24. | 1.Quartal | 2.Quartal | 3.Quartal | 4.Quartal |
25. +-----------+-----------+-----------+-----------+
26. | 2400.00 | 1700.00 | 200.00 | NULL |
27. +-----------+-----------+-----------+-----------+
28. 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;
29. +-------+---------+-----------+-----------+-----------+-----------+
30. | plz | umsatz | 1.Quartal | 2.Quartal | 3.Quartal | 4.Quartal |
31. +-------+---------+-----------+-----------+-----------+-----------+
32. | 30000 | 400.00 | 300.00 | 100.00 | NULL | NULL |
33. | 40000 | 1400.00 | 700.00 | 500.00 | 200.00 | NULL |
34. | 20000 | 2500.00 | 1400.00 | 1100.00 | NULL | NULL |
35. +-------+---------+-----------+-----------+-----------+-----------+
36. 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.

https://twiedmann.de