Wichtige Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

In der letzten Vorlesung haben wir verstanden, warum Window Functions überhaupt gebraucht werden. Jetzt schauen wir uns die konkreten Funktionen und deren Ergebnisse an. Die Details zum Syntax machen wir in der nächsten Vorlesung.

Funktion ROW_NUMBER()

Die Funktion ROW_NUMBER() gibt jeder Zeile innerhalb eines Fensters eine eindeutige Nummer. Das ist einfach eine fortlaufende Nummerierung der Zeilen in der Reihenfolge, die du mit ORDER BY bestimmst.

Syntax:

ROW_NUMBER() OVER ([PARTITION BY spalte] ORDER BY spalte)

Wo:

• PARTITION BY spalte (optional): Teilt die Daten in Gruppen auf. Wenn du das weglässt, wird global über das ganze Set nummeriert.
• ORDER BY spalte: Bestimmt die Reihenfolge der Zeilen für die Nummerierung.

Beispiel. Zeilennummerierung in einer Tabelle

Schauen wir uns die Tabelle students an, die Infos über Studierende und deren Noten enthält.

SELECT * FROM students;

Jetzt nummerieren wir die Zeilen absteigend nach ihren Noten (score):

SELECT
    name, 
    score, 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

Ergebnis:

Jede Zeile bekommt eine eindeutige laufende Nummer – und zwar nach der absteigenden Sortierung der Noten.

Das ist eine simple, aber ziemlich mächtige Operation: Du kannst einfach eine Zeilennummer zum Query-Result hinzufügen. Im klassischen SELECT geht das ohne Window Functions nicht.

Funktion RANK()

Die Funktion RANK() ist sehr ähnlich zu ROW_NUMBER(), aber sie berücksichtigt gleiche Werte. Wenn mehrere Zeilen denselben Wert im Sortierkriterium haben, bekommen sie denselben Rang, und der nächste Rang wird übersprungen.

Syntax:

RANK() OVER ([PARTITION BY spalte] ORDER BY spalte)

Beispiel. Ranking der Studierenden nach ihren Noten

Wir nutzen RANK() für die gleichen Daten:

SELECT
    name, 
    score, 
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

Ergebnis:

Hier bekommen Zeilen mit gleichen Werten (95 und 87) denselben Rang, und die nächsten Ränge werden übersprungen.

Funktion DENSE_RANK()

DENSE_RANK() ist ähnlich wie RANK(), aber überspringt keine Rangwerte. Das heißt, wenn es doppelte Werte gibt, ist der nächste Rang einfach eins höher als der vorherige.

Syntax:

DENSE_RANK() OVER ([PARTITION BY spalte] ORDER BY spalte)

Beispiel. Dichtes Ranking

Wir nutzen DENSE_RANK() für die gleichen Daten:

SELECT
    name, 
    score, 
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

Ergebnis:

Hier – im Unterschied zu RANK() – steigen die Rangwerte ohne Lücken.

Funktion NTILE()

Die Funktion NTILE() teilt die Zeilen in gleich große Gruppen (Quantile) und gibt jeder Zeile eine Gruppennummer.

Syntax:

NTILE(n) OVER ([PARTITION BY spalte] ORDER BY spalte)

• n: Anzahl der Gruppen, in die die Daten aufgeteilt werden sollen.

Beispiel. Studierende in 3 Gruppen aufteilen

Wir teilen die Studierenden in 3 Gruppen nach absteigender Note auf:

SELECT
    name, 
    score, 
    NTILE(3) OVER (ORDER BY score DESC) AS group_num
FROM students;

Ergebnis:

Achte darauf: Wenn die Zeilen nicht exakt gleichmäßig aufgeteilt werden können, kommen die "überzähligen" Zeilen in die ersten Gruppen. In diesem Beispiel haben die ersten beiden Gruppen je zwei Zeilen, die letzte nur eine.

Wann welche Funktion nutzen?

• ROW_NUMBER(): Für eindeutige Nummerierung der Zeilen nach Sortierung.
• RANK(): Für Ranking mit Berücksichtigung gleicher Werte und Lücken im Rang.
• DENSE_RANK(): Für Ranking mit Berücksichtigung gleicher Werte, aber ohne Lücken im Rang.
• NTILE(): Für gleichmäßige Aufteilung der Zeilen in Gruppen.

Alle diese Funktionen helfen dir, Daten auf einem ganz neuen Level zu analysieren. Nutze sie immer dann, wenn du flexibel Reihenfolgen berechnen oder Daten in Gruppen teilen willst.