Window Functions: ROW_NUMBER, RANK, PARTITION BY

Window Functions (deutsch oft „Fensterfunktionen" oder „analytische Funktionen") sind eines der mächtigsten Features modernen SQLs. Sie machen Dinge möglich, die ohne sie nur mit umständlichen Self-JOINs oder Subqueries gingen: fortlaufende Nummerierungen, Ranglisten innerhalb von Gruppen, laufende Summen, Vergleiche mit der Vorzeile, Top-N pro Kategorie.

Der entscheidende Unterschied zu normalen Aggregaten (GROUP BY): Window Functions reduzieren nicht die Zeilenanzahl. Jede Zeile bleibt erhalten und bekommt zusätzlich einen berechneten Wert, der sich auf eine „Fenster"-Auswahl der anderen Zeilen bezieht.

1) Was ist eine Window Function?

Stell dir vor, du hast eine Liste mit Bestellungen und willst zu jeder Zeile wissen, welchen Rang diese Bestellung im Vergleich zu allen anderen hat. Mit GROUP BY bekommst du nur eine Zeile pro Gruppe – du verlierst Details. Window Functions lösen das:

SELECT
  id, kunde_id, summe,
  RANK() OVER (ORDER BY summe DESC) AS rang
FROM bestellungen;

Jede Bestellung bleibt erhalten, bekommt aber zusätzlich eine Spalte rang. Das Schlüsselwort dafür ist OVER: es definiert das „Fenster", über das die Funktion arbeitet.

2) Beispiel-Daten

Wir nutzen eine erweiterte Bestellungs-Tabelle, weil viele Window-Function-Beispiele Vergleiche brauchen:

3) Anatomie einer Window Function

Eine Window Function hat immer denselben Aufbau:

4) ROW_NUMBER() – einfache Durchnummerierung

ROW_NUMBER() vergibt eine fortlaufende Nummer 1, 2, 3, …:

SELECT
  id, summe,
  ROW_NUMBER() OVER (ORDER BY summe DESC) AS nr
FROM bestellungen;

5) RANK() und DENSE_RANK() – mit Gleichständen

Bei sportlichen Wettkämpfen gibt es bei Gleichstand zwei zweite Plätze und dann den vierten – nicht den dritten. Genau das macht RANK():

SELECT
  id, summe,
  RANK()       OVER (ORDER BY summe DESC) AS rang,
  DENSE_RANK() OVER (ORDER BY summe DESC) AS drang,
  ROW_NUMBER() OVER (ORDER BY summe DESC) AS nr
FROM bestellungen;

Wann was?

• ROW_NUMBER: eindeutige Reihenfolge, etwa für Pagination („Zeilen 21–40")
• RANK: klassischer Sportrang („zwei zweite Plätze, dann vierter")
• DENSE_RANK: lückenlose Rangfolge („zwei zweite Plätze, dann dritter")

6) PARTITION BY – Ranglisten pro Gruppe

Bisher haben wir die Funktion über alle Zeilen laufen lassen. Mit PARTITION BY teilst du die Daten in Gruppen und die Funktion startet pro Gruppe von vorne:

SELECT
  id, kunde_id, summe,
  ROW_NUMBER() OVER (
    PARTITION BY kunde_id
    ORDER BY summe DESC
  ) AS nr_pro_kunde
FROM bestellungen;

7) Top-N pro Gruppe

Klassisches Muster: „Zeige die teuerste Bestellung pro Kunde." Ohne Window Functions kompliziert, mit ihnen elegant:

WITH ranked AS (
  SELECT id, kunde_id, summe,
    ROW_NUMBER() OVER (
      PARTITION BY kunde_id ORDER BY summe DESC
    ) AS nr
  FROM bestellungen
)
SELECT id, kunde_id, summe
FROM ranked
WHERE nr = 1;

Das funktioniert immer, auch wenn ein Kunde mehrere Bestellungen mit demselben Spitzenbetrag hat – dann nimmst du eine davon (durch ROW_NUMBER). Brauchst du alle Top-Ergebnisse, nutze RANK und filtere WHERE rang = 1.

Beachte: das ist ein typisches Beispiel für die Kombination CTE + Window Function. Die Window Function muss erst berechnet werden, bevor man darauf filtern kann – ein WHERE direkt auf der Window Function geht nicht.

8) Window Functions als Aggregate

Du kannst auch normale Aggregate (SUM, AVG, COUNT, MIN, MAX) mit OVER kombinieren. Vorteil: jede Zeile bleibt erhalten und bekommt den Aggregatwert als zusätzliche Spalte:

SELECT
  id, kunde_id, summe,
  SUM(summe) OVER (PARTITION BY kunde_id) AS kunde_total,
  AVG(summe) OVER ()                          AS overall_avg
FROM bestellungen;

9) Laufende Summen (Running Total)

Wenn du ORDER BY in der Window Function ergänzt, bekommst du eine laufende Berechnung – jede Zeile berücksichtigt sich selbst und alle vorherigen:

SELECT
  id, summe,
  SUM(summe) OVER (ORDER BY id) AS laufende_summe
FROM bestellungen;

10) LAG und LEAD – auf Nachbar-Zeilen zugreifen

Manchmal will man pro Zeile wissen: „Was war der Wert in der vorherigen Zeile?" Genau das macht LAG(); das Gegenstück LEAD() guckt nach vorne:

SELECT
  id, summe,
  LAG(summe)  OVER (ORDER BY id) AS vorherige,
  summe - LAG(summe) OVER (ORDER BY id) AS differenz
FROM bestellungen;

11) Die wichtigsten Window Functions im Überblick

12) Window Functions in der Praxis – typische Aufgaben

• Pagination: WHERE row_num BETWEEN 21 AND 40 für „Seite 2 mit 20 Einträgen"
• Top-3 pro Kategorie: WHERE rank <= 3 nach PARTITION BY kategorie
• Veränderungen pro Tag: LAG/LEAD mit Datum-Ordering
• Anteil am Gesamtumsatz: summe / SUM(summe) OVER () pro Zeile
• Quartile/Dezile: NTILE(4) oder NTILE(10) für Statistiken
• Duplikate finden: ROW_NUMBER() mit PARTITION BY (alle Spalten, die gleich sein müssten)

13) Wo darf eine Window Function stehen?

Eine wichtige Einschränkung: Window Functions werden nach WHERE, GROUP BY und HAVING ausgewertet, aber vor ORDER BY und LIMIT. Konsequenzen:

• In SELECT und ORDER BY: erlaubt
• In WHERE oder HAVING: nicht erlaubt – das gibt einen Syntaxfehler
• Lösung: Window Function in einer CTE oder Subquery berechnen, danach filtern

14) Häufige Fehler

• Window Function in WHERE: gibt Fehler. Erst in CTE/Subquery wickeln
• ORDER BY vergessen bei rangabhängigen Funktionen: ROW_NUMBER(), RANK, LAG ohne ORDER BY liefert unvorhersehbare Ergebnisse
• PARTITION BY mit GROUP BY verwechselt: GROUP BY reduziert Zeilen, PARTITION BY nicht
• FIRST_VALUE / LAST_VALUE Überraschung: ohne Frame-Definition gibt LAST_VALUE oft die aktuelle statt der wirklich letzten Zeile zurück. Workaround: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
• Datenbank-Kompatibilität: Window Functions gibt es in MySQL ab 8.0, PostgreSQL ab 8.4, SQL Server ab 2005, Oracle schon länger. Bei alten MySQL-Versionen funktioniert das nicht

15) Window vs. GROUP BY vs. Subquery

Zusammenfassung

Window Functions berechnen pro Zeile einen Wert, der sich auf andere Zeilen bezieht – ohne die Zeilenanzahl zu reduzieren (anders als GROUP BY). Aufbau: funktion() OVER (PARTITION BY … ORDER BY …). Die wichtigsten Funktionen sind ROW_NUMBER (eindeutige Nummerierung), RANK (Rang mit Lücken bei Gleichstand), DENSE_RANK (Rang ohne Lücken), LAG/LEAD (Zugriff auf Nachbar-Zeilen) und Aggregate (SUM, AVG) mit OVER für laufende Summen. Wichtig: in WHERE nicht erlaubt – stattdessen in eine CTE wickeln und danach filtern. Verfügbar in MySQL ab 8.0, sonst überall.