Fenster-Frame einstellen mit ROWS und RANGE

Wenn du Window Functions benutzt, kommt die Frage auf: "Wie viele Zeilen im Fenster werden für die Berechnung des Werts der aktuellen Zeile verwendet?" Die Antwort darauf hängt vom Fenster-Frame ab.

Fenster-Frame ist der Bereich von Zeilen, der für das Ergebnis der Window Function genutzt wird. Dieser Bereich wird auf Basis der aktuellen Zeile und zusätzlicher Bedingungen gebaut, die du mit ROWS oder RANGE angibst.

Ein einfaches Beispiel: Wenn du eine kumulierte Summe berechnest, kannst du angeben:

• Nur die aktuelle Zeile berücksichtigen.
• Die aktuelle Zeile und alle Zeilen darüber berücksichtigen.
• Die aktuelle Zeile und eine feste Anzahl Zeilen darüber/darunter berücksichtigen.

Genau ROWS und RANGE steuern, welche Zeilen in den Fenster-Frame kommen.

Verwendung von ROWS

ROWS definiert den Fenster-Frame auf Basis der physischen Reihenfolge der Zeilen. Das heißt, es zählt die Zeilen von oben nach unten in ihrer Reihenfolge, egal welche Werte in diesen Zeilen stehen.

Syntax

fenster_funktion OVER (
    ORDER BY spalte
    ROWS BETWEEN start AND ende
)

Wichtige Ausdrücke:

• CURRENT ROW — aktuelle Zeile.
• zahl PRECEDING — bestimmte Anzahl Zeilen über der aktuellen.
• zahl FOLLOWING — bestimmte Anzahl Zeilen unter der aktuellen.
• UNBOUNDED PRECEDING — vom Anfang des Fensters.
• UNBOUNDED FOLLOWING — bis zum Ende des Fensters.

Beispiel: Kumulative Summe für aktuelle und 2 vorherige Zeilen

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

Erklärung:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW bedeutet: Nimm die aktuelle Zeile und zwei Zeilen darüber.
• Die kumulierte Summe wird nur für diese drei Zeilen berechnet.

Ergebnis:

Beispiel: "Rolling Window"-Analyse mit fester Zeilenzahl

Aufgabe: Berechne das Durchschnittsgehalt für die aktuelle Zeile und die zwei folgenden.

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

Ergebnis:

Verwendung von RANGE

RANGE baut den Fenster-Frame auf Basis von Werten, nicht auf der Position der Zeilen. Das heißt, Zeilen werden in den Frame aufgenommen, wenn ihre Werte in der ORDER BY-Spalte im angegebenen Bereich liegen.

Syntax

fenster_funktion OVER (
    ORDER BY spalte
    RANGE BETWEEN start AND ende
)

Beispiel: Kumulative Summe nach Wertebereich

Aufgabe: Berechne die kumulierte Summe für Zeilen, bei denen das Gehalt sich von der aktuellen Zeile um nicht mehr als 2000 unterscheidet.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

Erklärung:

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING bedeutet: Nimm die Zeilen, bei denen der Wert von salary im Bereich ±2000 von der aktuellen Zeile liegt.

Ergebnis:

Vergleich von ROWS und RANGE

• ROWS arbeitet mit echten Zeilen und deren Anzahl. Es hängt nicht von den Werten ab.
• RANGE arbeitet mit einem logischen Wertebereich, der für die Spalte aus ORDER BY angegeben wird.

Zum Vergleich ein Beispiel. Angenommen, wir haben eine Tabelle sales mit Daten:

Vergleichen wir die Abfragen:

ROWS:

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

Ergebnis:

Hier wird jede Zeile vom System zur Summe hinzugefügt, sobald sie wirklich erscheint.

RANGE:

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

Ergebnis:

Hier werden die Zeilen 1 und 2 zusammengefasst, weil ihr amount = 100 ist. RANGE berücksichtigt wiederholte Werte in der Spalte amount.

Beispiele für echte Aufgaben

1. Berechnung des Umsatzwachstums

Aufgabe: Berechne die Veränderung des Umsatzes im Vergleich zur vorherigen Zeile.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. Vergleich der aktuellen Zeile mit dem Durchschnitt in der Gruppe

Aufgabe: Für jede Abteilung die Differenz zwischen dem Gehalt des Mitarbeiters und dem Durchschnittsgehalt der Abteilung berechnen.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

Fehler bei der Verwendung von ROWS und RANGE

Falsch angegebene Zeilenreihenfolge (ORDER BY): Wenn du keine Sortierreihenfolge angibst, gibt PostgreSQL einen Fehler aus, weil es die aktuelle Zeile nicht bestimmen kann.

Vermischung von ROWS und RANGE in einer Aufgabe: Wähle den Ansatz je nach deinen Daten. ROWS ist für Aufgaben mit fester Zeilenzahl geeignet, RANGE — für Wertebereiche.

Überspringen von wiederholten Werten bei RANGE: Denk daran, dass RANGE alle wiederholten Werte berücksichtigt, was das Ergebnis stark verändern kann.