Syntax von OVER() und seine wichtigsten Besonderheiten

OVER() ist ein Statement, das SQL sagt, auf welchen Datensatzbereich eine Window Function angewendet werden soll. Man kann sagen, es ist die Art und Weise, wie das "Fenster" der Daten für die Anwendung der Window Function definiert wird. Stell dir vor, wir haben einen Raum voller Leute und wollen zählen, wie viele Leute auf jedem Quadratmeter Boden stehen. OVER() gibt an, auf welchen Teil des Raums wir uns konzentrieren. Anders gesagt: Es legt fest, auf welchen Datensatzbereich die Funktion arbeitet.

Der OVER()-Operator wird ausschließlich mit Window Functions verwendet, um Operationen über Zeilen einer oder mehrerer Tabellen auszuführen, ohne die Daten zu gruppieren.

Syntax:

window_function() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS/RANGE ...]
)

Wo:

• PARTITION BY — teilt den Datensatz in logische Gruppen
• ORDER BY — legt die Reihenfolge der Zeilen innerhalb jeder Gruppe fest
• ROWS/RANGE — präzisiert die Größe des "Fensters" (z.B. aktuelle Zeile + 1 nächste)

Beispiel: OVER() ohne Parameter

Wenn OVER() ohne zusätzliche Parameter verwendet wird, bedeutet das, dass die Funktion davor auf den gesamten Datensatz angewendet wird.

SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER () AS row_num -- ROW_NUMBER() wird auf alle Ergebniszeilen angewendet
FROM employees;

Was passiert hier?

1. ROW_NUMBER() weist jeder Zeile eine eindeutige Nummer zu.
2. Da in OVER() keine Parameter angegeben sind, werden alle Zeilen aus der Tabelle employees als Ganzes verarbeitet.

Ergebnis:

Verwendung von PARTITION BY für Gruppierung

Okay, stell dir jetzt vor, uns interessiert die Nummerierung der Mitarbeiter nicht für die ganze Firma, sondern innerhalb jeder Abteilung. Hier kommt PARTITION BY ins Spiel.

PARTITION BY innerhalb von OVER() teilt die Daten in Gruppen (oder "Partitionen"). Für jede Gruppe berechnet die Funktion den Wert separat. Wenn ROW_NUMBER() ein Kellner wäre, würde er für jeden "Tisch" (Partition) neu mit dem Zählen anfangen.

Beispiel: PARTITION BY verwenden

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id) AS row_num
FROM employees;

Was passiert?

1. Die Daten aus der Tabelle employees werden nach department_id in Gruppen geteilt.
2. In jeder Gruppe werden die Zeilen mit ROW_NUMBER() durchnummeriert.

Ergebnis:

Verwendung von ORDER BY für Reihenfolge

Jetzt bringen wir ein bisschen Struktur rein. Stell dir vor, wir wollen die Zeilen nicht einfach nur nummerieren, sondern das in einer bestimmten Reihenfolge tun, zum Beispiel beginnend mit dem höchsten Gehalt. Das geht mit ORDER BY.

ORDER BY legt fest, in welcher Reihenfolge die Zeilen von der Window Function verarbeitet werden.

Beispiel: ORDER BY innerhalb von OVER() verwenden

SELECT
    department_id,
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

Was passiert?

1. Die Daten werden in Gruppen geteilt (PARTITION BY department_id).
2. Innerhalb jeder Gruppe werden die Zeilen nach Gehalt absteigend sortiert (ORDER BY salary DESC).
3. Jede Zeile bekommt einen Rang entsprechend der Sortierung.

Ergebnis:

Kombinieren von Window Functions

SQL erlaubt es, mehrere Window Functions in einer Query zu verwenden, wobei jede nach ihren eigenen Regeln arbeitet. Das ist so, als ob in einem Raum gleichzeitig Musik läuft und Leute gezählt werden – jeder Prozess ist unabhängig!

Beispiel: mehrere Window Functions

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

Was passiert?

1. ROW_NUMBER() nummeriert die Zeilen in jeder Gruppe nach absteigendem Gehalt.
2. AVG() berechnet das Durchschnittsgehalt in jeder Gruppe.

Ergebnis:

Beispiele aus dem echten Leben

Window Functions mit OVER() werden in vielen realen Szenarien genutzt. Hier ein paar Beispiele:

• Sales Analytics: Produkte nach Verkaufszahlen in jeder Kategorie ranken.
• Rankings: Positionen von Studierenden in jeder Gruppe nach Durchschnittsnote bestimmen.
• Zeitreihen: Kumulierte Verkaufssumme über die Zeit.

Beispiel aus der Sales Analytics:

SELECT
    category_id,
    product_id,
    product_name,
    SUM(sales) OVER (PARTITION BY category_id ORDER BY sales DESC) AS cumulative_sales
FROM products;

Typische Fehler bei der Arbeit mit Window Functions

1. Fehlendes PARTITION BY

Wenn du PARTITION BY nicht verwendest, wird die Window Function auf die ganze Tabelle angewendet. Das kann zu unerwarteten Ergebnissen führen, besonders wenn du eigentlich eine Gruppierung erwartet hast.

---

2. Falsche Datentypen in ORDER BY

ORDER BY innerhalb einer Window Function ist sensibel gegenüber Datentypen. Wenn du nach einem Feld sortierst, das ein Datum als Text (VARCHAR) speichert, kann die Sortierung alphabetisch statt chronologisch sein.

3. Falsche Verwendung von ROWS BETWEEN

Standardmäßig arbeiten Window Functions mit Frames, die durch ROWS BETWEEN definiert werden. Wenn du den Frame nicht explizit angibst, kann das Verhalten von RANGE greifen, das sich anders verhält und mehr Zeilen zurückgeben kann, als du erwartest.

4. Falscher Umgang mit NULL

Window Functions können NULL unterschiedlich behandeln. Zum Beispiel zählen RANK() und DENSE_RANK() NULL als Wert und geben ihm einen eigenen Rang.

5. Window Aggregatfunktionen statt normaler Aggregatfunktionen verwenden

Manchmal werden Window Aggregatfunktionen (SUM() OVER(...)) genutzt, wo normale Aggregatfunktionen mit GROUP BY reichen würden. Das macht die Query unnötig kompliziert und langsamer.