Eingebaute Funktionen

Nachdem wir uns die einfache Statements angesehen haben, die Windowing Functions enthalten, wenden wir uns jetzt einigen Beispielen zu, die zeigen, wie Onboard-Funktionen genutzt werden können, die PostgreSQL im Zusammenhang mit Windowing liefert.

Eine der wohl populärsten Funktionen ist die rank() Funktion:

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

Die Idee hinter rank ist, den Input zu klassifizieren. In unserem Beispiel haben wir jeder Position ihr Ranking innerhalb des Windows zugewiesen. Wir sehen beispielsweise, dass 18 den ersten Platz ergattert hat, weil es der kleinste Wert ist. In diesem Beispiel kommen die kleinsten Werte zuerst, weil die OVER-Clause nach der revenue Spalte sortiert.

Ein weitere Funktion, die oft verwendet wird, ist lag:

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

lag erlaubt es uns, im Window zu positionieren. Im konkreten Beispiel wollen wir den Input um eine Zeile verschieben. Wir stellen also dem Umsatz vom ersten Oktober neben den Umsatz vom zweiten Oktober. Auf diese Weise können wir sehr leicht Differenzen bilden und beliebige Offsets bilden.

Neben rank und lag gibt es noch eine Reihe weiterer Funktionen, die automatisch mitgeliefert werden:

• row_number(): Retourniert die aktuelle Zeilennummer
• rank(): Gibt die Position innerhalb einer zu erzeugenden Rangliste.
• dense_rank(): Funktioniert gleich wie rank - nur mit dem Unterschied dass der rank bei doppelten Einträgen nicht um die Anzahl der Duplikate sondern nur um eins erhöht wird (siehe Beispiel weiter unten).
• precent_rank(): Dividiert den rank durch die Gesamtanzahl der Positionen und erzeugt so ein prozentuelles Ranking.
• cume_dist(): Berechnet die Position relativ zur Position einer Gruppe von Werten.
• ntile(num_buckets integer): Partitioniert den Wertebereich in n Teile. Ist n = 10 erzeugt man auf diese Weise eine Dezilverteilung.
• lag(value any [, offset integer [, default any ]]): Ermöglicht das Verschieben der Position innerhalb des Windows.
• lead(value any [, offset integer [, default any ]]): Ermöglicht das Verschieben der Position innerhal des Windows. Im Prinzip tut lead das Selbe wie lag - die Verschiebung findet lediglich in die andere Richtung statt.
• first_value(value any): Gibt den ersten Wert innerhalb des Windows zurück.
• last_value(value any): Gibt den letzten Wert iinerhalb des Windows zurück.
• lnth_value_value(value any, nth integer): gibt den n-ten Wert innerhalb des Windows zurück.

Klarerweise ist es jederzeit möglich, eigene Funktionen zu PostgreSQL hinzu zu fügen und die Datenbank so entsprechend der eigenen Bedürfnisse ergänzen.