SQL Window Functions

6 Kernkonzepte

Die wichtigsten SQL Window Functions für erweiterte Analysen: ROW_NUMBER · RANK · DENSE_RANK · LAG · LEAD · NTILE Window Functions ermöglichen Berechnungen über eine Gruppe von Zeilen, ohne die Zeilen zu einer einzigen Ausgabe zu aggregieren. Sie sind unverzichtbar für Ranglisten, Zeitreihenanalysen und komplexe Berichte.

ROW_NUMBER – Fortlaufende Nummerierung

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

SELECT name, preis, ROW_NUMBER() OVER (ORDER BY preis DESC) AS "platz" FROM produkte;

ROW_NUMBER weist jeder Zeile eine eindeutige, fortlaufende Nummer innerhalb ihrer Partition zu. Die Reihenfolge wird durch ORDER BY bestimmt.

Beispiele

-- Jeder Zeile eine Nummer zuweisen

SELECT name, preis,

ROW_NUMBER() OVER (ORDER BY preis DESC) AS "rang"

FROM produkte;

-- Mit PARTITION BY: Nummerierung pro Kategorie

SELECT kategorie, name, preis,

ROW_NUMBER() OVER (PARTITION BY kategorie ORDER BY preis DESC) AS "rang_pro_kategorie"

FROM produkte;

-- Die 3 teuersten Produkte pro Kategorie

WITH ranked AS (

SELECT kategorie, name, preis,

ROW_NUMBER() OVER (PARTITION BY kategorie ORDER BY preis DESC) AS "rn"

FROM produkte

)

SELECT * FROM ranked WHERE rn <= 3;

Tipp: ROW_NUMBER ist ideal für Paginierung innerhalb von Gruppen (z.B. "Top 3 pro Kategorie") oder für das Entfernen von Duplikaten in Kombination mit WHERE rn = 1.

RANK & DENSE_RANK – Ranglisten mit Bindungen

RANK() · DENSE_RANK() OVER (...)

SELECT name, punktzahl, RANK() OVER (ORDER BY punktzahl DESC) AS "rank", DENSE_RANK() OVER (ORDER BY punktzahl DESC) AS "dense_rank" FROM spieler;

RANK und DENSE_RANK erstellen Ranglisten. Bei gleichen Werten (Bindungen) erhalten sie den gleichen Rang. RANK überspringt Ränge, DENSE_RANK nicht.

Unterschied: RANK vs DENSE_RANK

Wert	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
100	2	1	1
90	3	3	2
80	4	4	3

Beispiele

-- Rangliste mit RANK und DENSE_RANK

SELECT name, punktzahl,

RANK() OVER (ORDER BY punktzahl DESC) AS "rank",

DENSE_RANK() OVER (ORDER BY punktzahl DESC) AS "dense"

FROM spieler

ORDER BY punktzahl DESC;

-- Mit PARTITION BY: Rang pro Kategorie

SELECT kategorie, name, preis,

RANK() OVER (PARTITION BY kategorie ORDER BY preis DESC) AS "rang_pro_kategorie"

FROM produkte;

Typische Ausgabe (RANK vs DENSE_RANK):
name | punktzahl | rank | dense
Anna | 100 | 1 | 1
Ben | 100 | 1 | 1
Clara | 90 | 3 | 2
David | 80 | 4 | 3

Tipp: Verwenden Sie RANK für Wettbewerbe ("1. Platz, 2. Platz"), wenn bei Gleichstand mehrere den gleichen Rang erhalten. Verwenden Sie DENSE_RANK, wenn Sie lückenlose Ränge wünschen.

LAG & LEAD – Vorherige / nächste Zeilen

LAG(spalte, offset, default) · LEAD(...)

SELECT datum, umsatz, LAG(umsatz, 1) OVER (ORDER BY datum) AS "vorheriger_umsatz", LEAD(umsatz, 1) OVER (ORDER BY datum) AS "naechster_umsatz" FROM umsaetze;

LAG greift auf Werte aus vorherigen Zeilen zu, LEAD auf Werte aus folgenden Zeilen. Ideal für Zeitreihenanalysen und Differenzberechnungen.

Beispiele

-- Tagesdifferenz zum Vortag berechnen

SELECT datum, umsatz,

umsatz - LAG(umsatz, 1) OVER (ORDER BY datum) AS "differenz"

FROM umsaetze;

-- Mit PARTITION BY (pro Kategorie)

SELECT kategorie, datum, umsatz,

LAG(umsatz, 1, 0) OVER (PARTITION BY kategorie ORDER BY datum) AS "vorher"

FROM umsaetze;

-- LEAD: Nächsten Wert anzeigen

SELECT datum, umsatz,

LEAD(umsatz, 1) OVER (ORDER BY datum) AS "naechster_tag"

FROM umsaetze;

-- 7-Tage-Differenz mit OFFSET

SELECT datum, umsatz,

umsatz - LAG(umsatz, 7) OVER (ORDER BY datum) AS "woche_zu_woche"

FROM umsaetze;

Typische Ausgabe (LAG/LEAD):
datum | umsatz | vorheriger_umsatz | naechster_umsatz
2024-01-01 | 100 | NULL | 120
2024-01-02 | 120 | 100 | 110
2024-01-03 | 110 | 120 | NULL

Tipp: LAG und LEAD sind unverzichtbar für Zeitreihenanalysen, Trends und Vergleichsberechnungen (z.B. "Umsatz im Vergleich zum Vormonat").

NTILE – Daten in Gruppen aufteilen

NTILE(n) OVER (...)

SELECT name, punktzahl, NTILE(4) OVER (ORDER BY punktzahl DESC) AS "quartil" FROM spieler;

NTILE teilt die Ergebnismenge in eine bestimmte Anzahl von Gruppen (Töpfen) auf. Jede Gruppe erhält eine Nummer von 1 bis n. Ideal für Perzentile und Quintile.

Beispiele

-- Quartile (4 Gruppen)

SELECT name, punktzahl,

NTILE(4) OVER (ORDER BY punktzahl DESC) AS "quartil"

FROM spieler;

-- Dezile (10 Gruppen)

SELECT name, umsatz,

NTILE(10) OVER (ORDER BY umsatz DESC) AS "dezil"

FROM kunden;

-- Mit PARTITION BY: Per Gruppe

SELECT kategorie, name, preis,

NTILE(3) OVER (PARTITION BY kategorie ORDER BY preis DESC) AS "preisgruppe"

FROM produkte;

Typische Ausgabe (NTILE 4):
name | punktzahl | quartil
Anna | 100 | 1
Ben | 90 | 1
Clara | 80 | 2
David | 70 | 2

Tipp: NTILE ist ideal für die Einteilung in Leistungsklassen, Preissegmente oder für statistische Analysen wie Quartilsberechnungen.

PARTITION BY – Gruppierte Fenster

PARTITION BY spalte1, spalte2 ...

SELECT kategorie, name, preis, ROW_NUMBER() OVER (PARTITION BY kategorie ORDER BY preis DESC) AS "rn" FROM produkte;

PARTITION BY teilt die Daten in logische Gruppen (Partitionen) auf. Die Window Function wird separat für jede Partition berechnet – wie ein "GROUP BY" ohne Zusammenfassung.

Beispiele

-- ROW_NUMBER pro Kategorie

SELECT kategorie, name, preis,

ROW_NUMBER() OVER (PARTITION BY kategorie ORDER BY preis DESC) AS "rn"

FROM produkte;

-- LAG pro Kategorie

SELECT kategorie, datum, umsatz,

LAG(umsatz, 1) OVER (PARTITION BY kategorie ORDER BY datum) AS "vorher"

FROM umsaetze;

-- Mehrere Partitionierungsspalten

SELECT jahr, monat, kategorie, umsatz,

SUM(umsatz) OVER (PARTITION BY jahr, monat) AS "monatsumsatz"

FROM umsaetze;

Tipp: PARTITION BY ist besonders nützlich für "Top-N pro Gruppe"-Abfragen oder für Vergleiche innerhalb von Gruppen (z.B. Umsatz pro Kategorie im Vergleich zum Vormonat).

Frame-Spezifikation – Fenster definieren

ROWS BETWEEN · RANGE BETWEEN

SELECT datum, umsatz, SUM(umsatz) OVER (ORDER BY datum ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS "gleitender_durchschnitt" FROM umsaetze;

Frame-Spezifikation definiert, welche Zeilen in der Berechnung der Window Function berücksichtigt werden – z.B. für gleitende Durchschnitte oder kumulative Summen.

Häufige Frame-Optionen

Option	Beschreibung
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Kumulative Summe (Standard für ORDER BY ohne Frame)
ROWS BETWEEN n PRECEDING AND n FOLLOWING	Gleitender Durchschnitt über n Zeilen vor und nach der aktuellen
ROWS BETWEEN n PRECEDING AND CURRENT ROW	Laufender Durchschnitt über die letzten n Zeilen
ROWS UNBOUNDED PRECEDING	Alle vorherigen Zeilen (kumulativ)
RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW	Letzte 7 Tage (PostgreSQL)

Beispiele

-- Gleitender 3-Tages-Durchschnitt

SELECT datum, umsatz,

AVG(umsatz) OVER (ORDER BY datum

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS "gleitender_durchschnitt"

FROM umsaetze;

-- Kumulative Summe (laufende Summe)

SELECT datum, umsatz,

SUM(umsatz) OVER (ORDER BY datum

ROWS UNBOUNDED PRECEDING) AS "kumulativ"

FROM umsaetze;

-- Laufendes 3-Monats-Maximum

SELECT monat, umsatz,

MAX(umsatz) OVER (ORDER BY monat

ROWS BETWEEN 2 PRECEDING AND 0 FOLLOWING) AS "max_3_monate"

FROM monatliche_umsaetze;

Typische Ausgabe (gleitender Durchschnitt):
datum | umsatz | gleitender_durchschnitt
2024-01-01 | 100 | 110.00 (100+120)/2
2024-01-02 | 120 | 110.00 (100+120+110)/3
2024-01-03 | 110 | 115.00 (120+110+115)/3

Tipp: Die Frame-Spezifikation ist das Herzstück von Window Functions für gleitende Berechnungen. Experimentieren Sie mit ROWS und RANGE, um die gewünschte Fenstergröße zu erreichen.

Window Functions im Überblick

ROW_NUMBER Fortlaufende Nummer
Eindeutige Zeilennummer

RANK Rang mit Lücken
Bei Gleichstand: gleicher Rang

DENSE_RANK Rang ohne Lücken
Lückenlos fortlaufend

LAG/LEAD Vorherige/nächste Zeile
Zeitreihenvergleiche

NTILE Gruppenaufteilung
Quartile, Dezile

Frame Fenster definieren
Gleitende Berechnungen

Quick Summary

ROW_NUMBER

Nummerierung

RANK

Rangliste

LAG/LEAD

Zeitreihen

NTILE

Gruppen

PARTITION BY

Gruppierung

ROWS

Fenster

ROW_NUMBER() OVER (PARTITION BY kategorie ORDER BY preis DESC) · LAG(umsatz, 1) OVER (ORDER BY datum)