IPC09 – PostgreSQL – Hans Jürgen Schönig

Heute klappt es hoffentlich mit dem Artikel 😉

Der Workshop von Hans war äußerst interessant, aber für mich etwas ärgerlich, denn die Features, die es seit der Version 8.4. gibt hätte ich schon früher benötigt. Dafür trägt aber Hans keine Schuld, lediglich die Entwickler, die sich dafür so lange Zeit gelassen haben 🙂

Seit der neuen Version 8.4 von Postgres gibt es nun auch Window-Functions wie in DB2, Oracle oder dem SQL Server. (MySQL Benutzer muss ich an dieser Stelle enttäuschen, denn diese Funktionalitäten gibt es hier nicht.) Window-Functions sind dazu da, um analytische Berechnungen performanter durchführen zu können. Jede Zeile hat die Möglichkeit auf ein Subset von Daten zurück zu greifen, Slices oder auch Windows genannt. Wenn ihr genauer wissen möchtet wie Window-Functions funktionieren empfehle ich euch bei David Fetter die Slides durchzugehen (http://fetter.org/).

Auf der Postgres Seite findet ihr eine Zusammenfassung aller Window-Functions http://www.postgresql.org/docs/8.4/static/functions-window.html

Ich erkläre euch ein paar dieser neuen Funktionen anhand nachfolgender Beispiele.

Vorweg wäre noch zu sagen, dass alle Window-Functions mit der der OVER Clause definiert werden müssen.

Unsere Testtabelle t_test beinhaltet nachfolgende Daten. Als data definieren wir den Gesamtumsatz eines Produktes innerhalb einer Warengruppe id.

test=# SELECT * FROM t_test;
id |       data       | sold | customer
—-+——————+——+———-
8 |  35.524331079796 |  701 |        1
24 | 19.5517973043025 |  660 |        0
34 | 13.6039890814573 |   80 |        1
1 | 23.9336279220879 |   55 |        2
1 | 21.8945774715394 |  761 |        2
24 | 87.5175111461431 |  704 |        2
24 | 49.7780737001449 |  819 |        0
(7 rows)

Ausgabe der Summe des Gesamtumsatzes aller Produkte.

test=# SELECT sum(data) FROM t_test;
sum
——————-
251.8039077054711
(1 row)

Der Sinn der OVER Clause sollte hiermit klar sein. Es wird für jede Zeile das Ergebnis zurück gegeben.

test=# SELECT id, data, sum(data) OVER() FROM t_test;
id |       data       |        sum
—-+——————+——————-
8 |  35.524331079796 | 251.8039077054711
24 | 19.5517973043025 | 251.8039077054711
34 | 13.6039890814573 | 251.8039077054711
1 | 23.9336279220879 | 251.8039077054711
1 | 21.8945774715394 | 251.8039077054711
24 | 87.5175111461431 | 251.8039077054711
24 | 49.7780737001449 | 251.8039077054711
(7 rows)

Prozentuale Anteil eines Artikels vom Gesamtumsatz.

test=# SELECT id, (data / sum) * 100 || ‚ %‘ AS percent FROM (SELECT id, data, sum(data) OVER() FROM t_test) AS x;
id |          percent
—-+—————————
8 | 14.10793478286601696800 %
24 | 7.76469177244531139900 %
34 | 5.40261237620249913000 %
1 | 9.50486755355777951300 %
1 | 8.69509042613784778400 %
24 | 34.75621643191900021600 %
24 | 19.76858665687154499000 %
(7 rows)

Hier wird die Window-Function dense_rank() verwendet, die die Reihenfolge eines jeden Datensatzes im Kontext zum Ergebnis zurück gibt. Der Unterschied zur normalen rank() Funktion ist, dass im Falle eines gleichen Ranges in der Reihenfolge dense_rank() 112 im Gegensatz zu 113 zurück gibt.

test=# SELECT * FROM (SELECT id, data, sum(data) OVER(), dense_rank() OVER (ORDER BY data) FROM t_test) AS x WHERE dense_rank = 3;
id |       data       |        sum        | dense_rank
—-+——————+——————-+————
1 | 21.8945774715394 | 251.8039077054711 |          3
(1 row)

PARTITION BY ist die Gruppierungsanweisung für die OVER Clause. Ausgabe der Summe des Gesamtumsatzes aller Produkte gruppiert nach der Warengruppe id.

test=# SELECT id, data, sum(data) OVER (PARTITION BY id) AS umsatz_gruppe, sum(data) OVER () AS umsatz_total FROM t_test;
id |       data       |   umsatz_gruppe   |   umsatz_total
—-+——————+——————-+——————-
1 | 23.9336279220879 |  45.8282053936273 | 251.8039077054711
1 | 21.8945774715394 |  45.8282053936273 | 251.8039077054711
8 |  35.524331079796 |   35.524331079796 | 251.8039077054711
24 | 49.7780737001449 | 156.8473821505905 | 251.8039077054711
24 | 87.5175111461431 | 156.8473821505905 | 251.8039077054711
24 | 19.5517973043025 | 156.8473821505905 | 251.8039077054711
34 | 13.6039890814573 |  13.6039890814573 | 251.8039077054711
(7 rows)

Ausgabe des prozentualen Anteils einer Warengruppe.

test=# SELECT id, data / umsatz_gruppe FROM (SELECT id, data, sum(data) OVER (PARTITION BY id) AS umsatz_gruppe, sum(data) OVER () AS umsatz_total FROM t_test) AS x ORDER BY 2 DESC LIMIT 3;
id |        ?column?
—-+————————
34 | 1.00000000000000000000
8 | 1.00000000000000000000
24 | 0.55797878132334268946
(3 rows)

Ausgabe der Gesamtsumme aller Käufe und Kunden, sowie gereiht nach Umsatz.

test=# SELECT id, customer, sum(data*sold) OVER () as umsatz, sum(data*sold) OVER (PARTITION BY id) AS cat_umsatz, dense_rank() OVER (PARTITION BY id ORDER BY data*sold ASC) FROM t_test ORDER BY 1 DESC;
id | customer |        umsatz        |      cat_umsatz      | dense_rank
—-+———-+———————-+———————-+————
34 |        1 | 159253.7546331529634 |   1088.3191265165840 |          1
24 |        0 | 159253.7546331529634 | 115284.7564281430655 |          1
24 |        0 | 159253.7546331529634 | 115284.7564281430655 |          2
24 |        2 | 159253.7546331529634 | 115284.7564281430655 |          3
8 |        1 | 159253.7546331529634 |   24902.556086936996 |          1
1 |        2 | 159253.7546331529634 |  17978.1229915563179 |          2
1 |        2 | 159253.7546331529634 |  17978.1229915563179 |          1
(7 rows)

Mit der Funktion lag() kann man den gleitenden Durchschnitt eines Wertes ausgeben.

test=# SELECT *, lag(sold, 1) OVER (ORDER BY sold) FROM t_test ORDER BY sold;
id |       data       | sold | customer | lag
—-+——————+——+———-+—–
1 | 23.9336279220879 |   55 |        2 |
34 | 13.6039890814573 |   80 |        1 |  55
24 | 19.5517973043025 |  660 |        0 |  80
8 |  35.524331079796 |  701 |        1 | 660
24 | 87.5175111461431 |  704 |        2 | 701
1 | 21.8945774715394 |  761 |        2 | 704
24 | 49.7780737001449 |  819 |        0 | 761
(7 rows)

test=# SELECT sold, CASE WHEN sold-lag IS NULL THEN 0 ELSE sold-lag END FROM (SELECT *, lag(sold, 1) OVER (ORDER BY sold) FROM t_test ORDER BY sold) AS x;
sold | case
——+——
55 |    0
80 |   25
660 |  580
701 |   41
704 |    3
761 |   57
819 |   58
(7 rows)

Und jetzt wünsche ich euch viel Spaß die gleichen Aufgabenstellungen mit MySQL zu lösen 🙂