Einfache Windowing Functions

Um zu verdeutlichen, wie man Windowing Functions verwenden kann, legen wir ein kleines Beispiel an:

test=# CREATE TABLE t_business (
	tstamp 		date, 
	revenue 	int4, 	
	country 	char(2)
);
CREATE TABLE

test=# INSERT INTO t_business VALUES ('2009-10-01', '20', 'DE');
INSERT 0 1
test=# INSERT INTO t_business VALUES ('2009-10-02', '18', 'DE');
INSERT 0 1
test=# INSERT INTO t_business VALUES ('2009-10-03', '23', 'DE');
INSERT 0 1
test=# INSERT INTO t_business VALUES ('2009-10-04', '26', 'DE');
INSERT 0 1
test=# INSERT INTO t_business VALUES ('2009-10-01', '9', 'AT');
INSERT 0 1
test=# INSERT INTO t_business VALUES ('2009-10-02', '12', 'AT');
INSERT 0 1
test=# INSERT INTO t_business VALUES ('2009-10-03', '15', 'AT');
INSERT 0 1
test=# INSERT INTO t_business VALUES ('2009-10-04', '15', 'AT');
INSERT 0 1

Die Idee hinter diesem Beispiel ist, dass wir für 2 Länder den Umsatz pro Tag speichern wollen. Klarerweise können wir auf diesem Datenbestand wie bereits gezeigt mit GROUP BY und co bearbeiten - was wir aber wollen ist, den Umsatz jedes einzelnen Datensatzes mit dem Gesamtumsatz im Bezug setzen.

Der Versuch, das wie folgt zu machen, wird scheitern:

test=# SELECT tstamp, revenue, sum(revenue) 
	FROM 	t_business 
	WHERE 	country = 'DE' 
	ORDER BY tstamp;
ERROR:  column "t_business.tstamp" must appear in the 
	GROUP BY clause 
	or be used in an aggregate function
LINE 1: SELECT tstamp, revenue, sum(revenue) 
	FROM 	t_business 
	WHERE 	c...

Ohne GROUP BY Clause ist an dieser Stelle kein Krieg zu gewinnen. Um den Bezug einer Zeile zum Gesamten herzustellen, müssen wir angeben, welches 'Windows' wir als Referenzgruppe betrachten möchte. Im nächsten Beispiel wollen wir die Grundgesamtheit der Daten als Referenzgruppe verwenden:

test=# SELECT tstamp, revenue, sum(revenue) OVER () AS total 
	FROM 	t_business 
	WHERE 	country = 'DE' 
	ORDER BY tstamp;
   tstamp   | revenue | total 
------------+---------+-------
 2009-10-01 |      20 |    87
 2009-10-02 |      18 |    87
 2009-10-03 |      23 |    87
 2009-10-04 |      26 |    87
(4 rows)

OVER gibt an, wie das Windows aufgespannt werden soll. Die OVER-Clause enthält keinerlei Restriktionen und wir gehen daher von der Gesamtheit aller Records aus. Das führt dazu, dass wir in der letzten Spalte die Gesamtsumme erhalten.

Oft ist es aber nicht genug, nur die Grundgesamtheit zu betrachten. In vielen Fällen kann es sinnvoll sein, das Window in einer bestimmten Reihefolge zu verarbeiten. Ein Beispiel dafür wäre ein 'Running Total':

test=# SELECT tstamp, revenue, sum(revenue) 
		OVER (ORDER BY tstamp) AS total 
	FROM 	t_business 
	WHERE 	country = 'DE' 
	ORDER BY tstamp;
   tstamp   | revenue | total 
------------+---------+-------
 2009-10-01 |      20 |    20
 2009-10-02 |      18 |    38
 2009-10-03 |      23 |    61
 2009-10-04 |      26 |    87
(4 rows)

Wir wollen erreichen, dass die letzte Spalte immer die Summe der vorangegangenen Zeilen enthält. Das erreichen wir, in dem wir die Daten nach Datum sortiert in die sum-Funktion stecken. Da sum für jede Zeile ausgegeben wird, erhalten wir schrittweise die entsprechenden Zwischenergebnisse - eben das Running Total. Das Sortieren des Inputs von sum ist wichtig, weil wir sonst zufällige Ergebnisse enthalten, die irgendwelche Tage summieren.

Wenn wir nun die WHERE-Clause weg lassen, erhalten wir ein Ergebnis, das wir so nicht wollen:

test=# SELECT tstamp, revenue, country, 
		sum(revenue) OVER (ORDER BY tstamp) AS total 
	FROM 	t_business 
	ORDER BY tstamp, country;
   tstamp   | revenue | country | total 
------------+---------+---------+-------
 2009-10-01 |       9 | AT      |    29
 2009-10-01 |      20 | DE      |    29
 2009-10-02 |      12 | AT      |    59
 2009-10-02 |      18 | DE      |    59
 2009-10-03 |      15 | AT      |    97
 2009-10-03 |      23 | DE      |    97
 2009-10-04 |      15 | AT      |   138
 2009-10-04 |      26 | DE      |   138
(8 rows)

Wieder wird das Running Total berechnet - die Datenbank summiert jedoch eiskalt über Ländergrenzen hinweg und gibt uns die Summe aller Länder anstelle des jeweiligen Running Totals pro Land.

Um diesen Fehler zu korrigieren, müssen wir eine PARTITION BY Clause einführen:

test=# SELECT tstamp, revenue, country, 
		sum(revenue) OVER (PARTITION BY country 
					ORDER BY tstamp) 
			AS total 
	FROM 	t_business 
	ORDER BY tstamp, country;
   tstamp   | revenue | country | total 
------------+---------+---------+-------
 2009-10-01 |       9 | AT      |     9
 2009-10-01 |      20 | DE      |    20
 2009-10-02 |      12 | AT      |    21
 2009-10-02 |      18 | DE      |    38
 2009-10-03 |      15 | AT      |    36
 2009-10-03 |      23 | DE      |    61
 2009-10-04 |      15 | AT      |    51
 2009-10-04 |      26 | DE      |    87
(8 rows)

Die Idee ist, dass wir ein Running Total 'pro Land' führen. Wir partitionieren also unser Fenster und teilen das jeweils in Betracht gezogene Window nach Land. Auf diese Weise erhalten wir jeweils die Zwischensumme für ein Land. Die PARTITION BY Clause ist im Zusammenhand mit Windowing von besonderer Bedeutung, weil uns nur so ermöglicht wird, das Window zu teilen, um nicht auf den gesamten Datenbestand zugreifen zu müssen.