Window Functions: Grundlagen der OVER-Klausel

1. Die Grundidee: Window Functions vs. GROUP BY

Diese grundlegende Unterscheidung zwischen "reduzieren" und "erweitern" ist der rote Faden, der sich durch alle Anwendungen von Window Functions zieht, egal ob es um einfache Durchschnitte oder komplexe Rangfolgen geht.

Eine Window Function berechnet einen Wert uber eine Menge von Zeilen, die zur aktuellen Zeile in Beziehung stehen, ohne diese Zeilen dabei zu einer einzigen Ergebniszeile zusammenzufassen. Das ist der zentrale Unterschied zu GROUP BY: Eine GROUP-BY-Abfrage reduziert zehn Zeilen einer Gruppe auf eine einzige Zeile mit dem Aggregatwert. Eine Window Function behalt alle zehn Zeilen und stellt jeder von ihnen den berechneten Wert zusatzlich zur Seite. Das Ergebnis hat exakt so viele Zeilen wie die Ausgangsabfrage, nur mit einer oder mehreren zusatzlichen Spalten.

Diese Eigenschaft macht Window Functions zum Werkzeug der Wahl, wann immer Detail und Aggregat gleichzeitig gebraucht werden. Ein typisches Beispiel: Man mochte pro Mitarbeiter das eigene Gehalt sehen und gleichzeitig den Durchschnitt der Abteilung, um die Abweichung zu berechnen. Mit reinem GROUP BY musste man zwei Abfragen schreiben und die Ergebnisse per JOIN wieder zusammenfuhren. Eine Window Function liefert beides in einer einzigen, lesbaren Abfrage.

Der Name Window Function kommt genau von diesem Konzept: Fur jede Zeile offnet sich ein Fenster auf eine Menge verwandter Zeilen, und die Funktion berechnet ihren Wert innerhalb dieses Fensters. Das Fenster kann die komplette Tabelle sein, eine per PARTITION BY definierte Gruppe, oder ein per ORDER BY und Frame eingeschrankter Bereich davon. Genau diese Flexibilitat unterscheidet Window Functions grundlegend von klassischen Aggregatfunktionen in GROUP BY.

2. Die OVER-Klausel: Syntax und Grundbausteine

Jede Window Function wird durch die OVER-Klausel zu einer solchen, statt eine normale Aggregatfunktion zu bleiben. Ohne OVER ist SUM() eine Aggregatfunktion, die eine Fehlermeldung wirft, sobald sie mit nicht aggregierten Spalten gemischt wird. Mit OVER(...) direkt hinter dem Funktionsaufruf wird aus derselben Funktion eine Window Function, die pro Zeile ausgewertet wird und dabei problemlos neben anderen Spalten stehen darf. Die OVER-Klausel kann leer sein, dann bezieht sich die Funktion auf die gesamte Ergebnismenge als ein einziges Fenster.

Innerhalb der Klammern der OVER-Klausel stehen bis zu drei optionale Bestandteile: PARTITION BY zur Definition von Gruppen, ORDER BY zur Definition einer Reihenfolge innerhalb der Gruppe, und eine Frame-Klausel wie ROWS BETWEEN zur weiteren Einschrankung des Fensters. Alle drei sind optional und konnen unabhangig voneinander kombiniert werden. Diese Modularitat ist der Grund, warum Window Functions so vielseitig einsetzbar sind, von einfachen Durchschnittswerten bis zu komplexen Rangfolgen und laufenden Summen.

-- Basic Window Function: department average next to each row
-- Rows are preserved, unlike a GROUP BY aggregation
SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

-- Result (excerpt)
-- employee_name | department | salary | dept_avg_salary | diff_from_avg
-- Anna Weber    | Sales      |  62000 |         54666.67 |       7333.33
-- Tom Krause    | Sales      |  52000 |         54666.67 |      -2666.67
-- Lisa Vogt     | Sales      |  50000 |         54666.67 |      -4666.67

3. PARTITION BY: Gruppen definieren ohne zu aggregieren

PARTITION BY innerhalb der OVER-Klausel funktioniert konzeptionell wie GROUP BY, mit einem entscheidenden Unterschied: Es teilt die Ergebnismenge in logische Gruppen auf, ohne die Zeilen dieser Gruppen zusammenzufassen. Jede Gruppe, die PARTITION BY bildet, wird intern separat behandelt, die Window Function berechnet ihren Wert also nur aus den Zeilen der jeweiligen Partition. Fehlt PARTITION BY, gilt die gesamte Ergebnismenge als eine einzige Partition.

Man kann nach einer einzelnen Spalte partitionieren, wie im Beispiel nach der Abteilung, oder nach mehreren Spalten gleichzeitig, etwa nach Abteilung und Kalenderjahr, um Jahresvergleiche pro Abteilung zu erhalten. Jede zusatzliche Spalte in PARTITION BY verfeinert die Gruppierung weiter. Wichtig ist: PARTITION BY beeinflusst nur, welche Zeilen fur die Berechnung der Window Function herangezogen werden, es filtert oder sortiert die Ausgabe der Abfrage selbst nicht. Fur die Sortierung der Ausgabe bleibt die normale ORDER BY-Klausel am Ende der Abfrage zustandig.

-- PARTITION BY without ORDER BY: constant value per group
SELECT
    order_id,
    status,
    order_total,
    COUNT(*) OVER (PARTITION BY status) AS orders_with_same_status,
    ROUND(
        100.0 * order_total / SUM(order_total) OVER (PARTITION BY status),
        2
    ) AS pct_of_status_total
FROM orders
ORDER BY status, order_total DESC;

4. ORDER BY innerhalb OVER: Reihenfolge und Frame

Das ORDER BY innerhalb der OVER-Klausel hat eine andere Aufgabe als das ORDER BY am Ende der Abfrage. Es legt fest, in welcher Reihenfolge die Zeilen innerhalb einer Partition fur die Berechnung der Window Function betrachtet werden. Diese Reihenfolge ist entscheidend fur Funktionen, die von der Position einer Zeile abhangen, wie ROW_NUMBER, RANK oder laufende Summen mit SUM() OVER. Ohne ORDER BY innerhalb von OVER gibt es fur solche Funktionen keine wohldefinierte Reihenfolge, das Ergebnis ware unvorhersehbar.

Sobald ORDER BY innerhalb von OVER verwendet wird, andert sich standardmassig auch das implizite Fenster: Statt der gesamten Partition wird das Fenster auf alle Zeilen von Beginn der Partition bis zur aktuellen Zeile eingeschrankt. Das ist der Grund, warum SUM(betrag) OVER (ORDER BY datum) eine laufende Summe erzeugt, statt einfach die Gesamtsumme jeder Zeile anzuheften. Dieses Standardverhalten ist einer der haufigsten Stolpersteine fur Einsteiger, die eine Gesamtsumme erwarten und stattdessen eine kumulative Summe erhalten.

-- ORDER BY inside OVER changes the implicit frame
-- Without ORDER BY: total per partition on every row
SELECT order_id, customer_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

-- With ORDER BY: running total from partition start to current row
SELECT order_id, customer_id, order_date, amount,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date
       ) AS running_total
FROM orders;

5. Aggregatfunktionen als Window Functions

Praktisch jede Aggregatfunktion, die man aus GROUP BY kennt, funktioniert auch als Window Function: SUM, AVG, COUNT, MIN und MAX. Der einzige syntaktische Unterschied ist das angehangte OVER(...). Semantisch andert sich dagegen einiges: Statt eines einzelnen Ergebniswerts pro Gruppe liefert die Funktion einen Wert pro Zeile, berechnet uber die Partition und den Frame, die durch OVER definiert sind. COUNT(*) OVER (PARTITION BY status) etwa liefert auf jeder Zeile die Anzahl aller Zeilen mit demselben Status, was sich hervorragend fur Prozent- und Anteilsberechnungen eignet.

Neben den klassischen Aggregatfunktionen gibt es echte Window-only-Funktionen, die ausserhalb einer OVER-Klausel gar nicht existieren: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG und LEAD. Diese Funktionen haben keine sinnvolle Bedeutung ohne ein Fenster, weil sie sich per Definition auf die Position einer Zeile relativ zu anderen Zeilen beziehen. Sie erganzen die klassischen Aggregatfunktionen um Ranking- und Navigationslogik, die mit GROUP BY grundsatzlich nicht abbildbar ist.

-- Combining a classic aggregate with a window-only function
SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    SUM(salary) OVER (PARTITION BY department) AS dept_total_salary
FROM employees
ORDER BY department, salary DESC;

6. Window Frame: ROWS vs. RANGE

Der Window Frame ist der dritte und am wenigsten bekannte Baustein der OVER-Klausel. Er schrankt das durch PARTITION BY und ORDER BY definierte Fenster weiter ein, etwa auf die drei vorherigen Zeilen oder auf alle Zeilen bis zur aktuellen. Die Frame-Klausel wird mit ROWS oder RANGE eingeleitet, gefolgt von BETWEEN und zwei Grenzen wie UNBOUNDED PRECEDING, CURRENT ROW oder N FOLLOWING. ROWS zahlt physische Zeilen, RANGE bezieht sich auf logische Werte in der ORDER-BY-Spalte, was bei gleichen Werten zu unterschiedlichen Ergebnissen fuhren kann.

Der Unterschied zwischen ROWS und RANGE zeigt sich vor allem bei Bindungen, also wenn mehrere Zeilen denselben ORDER-BY-Wert haben. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW nimmt immer exakt drei physische Zeilen, unabhangig vom Inhalt. RANGE BETWEEN 2 PRECEDING AND CURRENT ROW dagegen bezieht sich auf den Wertebereich und kann bei Duplikaten mehr oder weniger Zeilen einschliessen, als man erwartet. Fur gleitende Durchschnitte und Moving-Sum-Berechnungen ist ROWS deshalb in der Praxis fast immer die richtige Wahl, weil das Verhalten vorhersehbar bleibt.

-- Window Frame with ROWS: moving average over three physical rows
SELECT
    reading_date,
    sensor_value,
    AVG(sensor_value) OVER (
        ORDER BY reading_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM sensor_readings
ORDER BY reading_date;

-- Default frame when ORDER BY is present but no frame is specified:
-- RANGE UNBOUNDED PRECEDING AND CURRENT ROW

7. Typische Anwendungsfalle fur Window Functions

Window Functions losen eine ganze Klasse von Auswertungsproblemen, die mit reinem GROUP BY nur umstandlich oder gar nicht losbar sind. Rangfolgen wie "die drei besten Verkaufer pro Region" benotigen RANK oder ROW_NUMBER in Kombination mit PARTITION BY. Laufende Summen fur Kontoauszuge oder kumulative Umsatze pro Monat sind eine klassische Domane von SUM() OVER mit ORDER BY. Vergleiche zur Vorperiode, etwa Umsatz diesen Monat gegenuber dem Vormonat, lassen sich elegant mit LAG losen, ohne einen Self-Join zu benotigen.

Ein weiterer haufiger Anwendungsfall ist die Deduplizierung: Mit ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) lasst sich pro E-Mail-Adresse die neueste Zeile identifizieren, und alle Zeilen mit einer Nummer grosser als eins konnen als Duplikate geloscht werden. Auch Perzentil-Berechnungen, Anteils-am-Total-Auswertungen und die Identifikation von Ausreissern innerhalb einer Gruppe gehoren zum Standardrepertoire, das erst durch Window Functions in reinem SQL praktikabel wird, ohne auf die Anwendungsschicht auszuweichen.

Neben den bereits genannten Beispielen lohnt sich ein Blick auf zusammengesetzte Kennzahlen, die mehrere Window Functions in einer einzigen Abfrage kombinieren. Ein Dashboard, das gleichzeitig den Rang eines Produkts, seinen Anteil am Kategorieumsatz und die Abweichung vom Vormonat zeigen soll, benotigt drei unterschiedliche Window Functions in derselben SELECT-Klausel, jede mit ihrer eigenen OVER-Definition. Diese Kombinierbarkeit ist einer der grossten praktischen Vorteile von Window Functions gegenuber Ansatzen, die mehrere separate Abfragen erfordern und deren Ergebnisse anschliessend in der Anwendungsschicht zusammengefuhrt werden mussten.

8. Performance-Aspekte von Window Functions

Window Functions werden von modernen Datenbanksystemen typischerweise effizient ausgefuhrt, weil der Optimizer die Daten fur mehrere Window Functions mit derselben PARTITION BY- und ORDER BY-Klausel nur einmal sortieren muss. Trotzdem ist die Sortierung selbst nicht kostenlos: Bei grossen Tabellen ohne passenden Index auf den PARTITION-BY- und ORDER-BY-Spalten muss die Datenbank einen vollstandigen Sort-Schritt durchfuhren, was bei Millionen von Zeilen spurbar Zeit kostet. Ein zusammengesetzter Index, der die PARTITION-BY-Spalten gefolgt von den ORDER-BY-Spalten abdeckt, kann diesen Sortierschritt in vielen Fallen vermeiden.

Ein zweiter wichtiger Punkt: Window Functions werden nach WHERE, GROUP BY und HAVING, aber vor der finalen ORDER BY-Klausel und vor LIMIT ausgewertet. Das bedeutet, eine Window Function kann nicht direkt in einer WHERE-Klausel gefiltert werden, weil zu diesem Zeitpunkt ihr Wert noch nicht existiert. Wer das Ergebnis einer Window Function filtern mochte, muss die Abfrage in eine Subquery oder Common Table Expression einbetten und im aussersten SELECT filtern. Dieser zusatzliche Schritt ist kein Performance-Problem an sich, aber ein haufiger Grund fur Fehlermeldungen bei Einsteigern.

9. Window Functions in verschiedenen Datenbanksystemen

Window Functions sind seit SQL:2003 Teil des ANSI-SQL-Standards, und die Grundsyntax mit OVER, PARTITION BY und ORDER BY ist zwischen PostgreSQL, MySQL ab Version 8.0, SQL Server, Oracle und SQLite ab Version 3.25 weitgehend identisch. Wer die Syntax aus diesem Artikel einmal gelernt hat, kann sie mit minimalen Anpassungen auf praktisch jedes moderne relationale Datenbanksystem ubertragen. Das macht Window Functions zu einer der portabelsten fortgeschrittenen SQL-Techniken uberhaupt.

Unterschiede zeigen sich vor allem in Details: Manche Systeme unterstutzen benannte Fenster uber die WINDOW-Klausel, mit der man eine OVER-Definition einmal deklariert und in mehreren Funktionen wiederverwendet, was Wiederholungen im Code reduziert. Auch die Standardwerte fur den Frame konnen leicht variieren, und nicht jedes System unterstutzt jede Kombination aus RANGE und komplexen Grenzen wie N FOLLOWING gleichermassen gut. Vor dem produktiven Einsatz lohnt sich deshalb immer ein Blick in die Dokumentation des jeweiligen Systems, insbesondere bei alteren MySQL-Versionen unter 8.0, die Window Functions gar nicht kennen und stattdessen auf Subqueries oder Variablen-Tricks angewiesen sind.

Wer aus der Anwendungsentwicklung kommt und Window Functions zum ersten Mal einsetzt, sollte sich klarmachen, dass die Datenbank hier eine Berechnung ubernimmt, die sonst in einer Schleife uber das Ergebnis-Set in der Anwendungsschicht stattgefunden hatte. Diese Verlagerung in die Datenbank spart nicht nur Netzwerk-Overhead durch weniger Rundtrips, sondern nutzt auch die fur solche Berechnungen optimierten internen Datenstrukturen der Datenbank, die typischerweise deutlich effizienter sind als eine vergleichbare Iteration in PHP, Python oder Java uber dieselbe Datenmenge.

Wer sich intensiver mit Window Functions beschaftigt, wird schnell feststellen, dass sie sich nicht nur fur Reporting eignen, sondern auch fur Datenqualitatsprufungen und ETL-Prozesse wertvoll sind. Die Kombination aus PARTITION BY und einer Rangfunktion identifiziert zuverlassig Duplikate, wahrend LAG-basierte Prufungen Lucken oder Sprunge in fortlaufenden Sequenzen aufdecken konnen, lange bevor solche Probleme in einem nachgelagerten System sichtbar werden.

10. Zusammenfassung

Window Functions losen ein Grundproblem klassischer Aggregation: Sie erlauben es, Aggregatwerte zu berechnen, ohne die Detailzeilen zu verlieren. Die OVER-Klausel macht aus einer normalen Aggregatfunktion eine Window Function, PARTITION BY definiert die Gruppen innerhalb derer gerechnet wird, und ORDER BY zusammen mit dem Frame legt fest, welche Zeilen innerhalb der Gruppe tatsachlich einbezogen werden. Dieses Zusammenspiel ist flexibler als GROUP BY und deckt Anwendungsfalle ab, die sonst nur mit Self-Joins oder Anwendungslogik losbar waren.

Der praktische Nutzen zeigt sich in Rangfolgen, laufenden Summen, Vorwert-Vergleichen und Anteilsberechnungen, die alle in einer einzigen, lesbaren Abfrage moglich werden. Wer Window Functions einmal verinnerlicht hat, schreibt spurbar weniger Subqueries und Self-Joins fur Reporting-Anfragen. Da die Syntax uber die meisten modernen Datenbanksysteme hinweg fast identisch ist, lohnt sich die Investition in dieses Konzept unabhangig davon, welches konkrete Datenbanksystem gerade im Einsatz ist.

11. FAQ: Window Functions und die OVER-Klausel