Menu

PostgreSQL Window Functions: OVER, PARTITION BY, ROW_NUMBER

Window Functions berechnen für jede Zeile einen Wert aus einer Gruppe zusammengehöriger Zeilen, ohne sie zusammenzufassen: Rankings, laufende Summen, gleitende Durchschnitte und Vergleiche mit der vorigen Zeile.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

Eine Window Function berechnet für jede Zeile einen Wert aus einer Menge zusammengehöriger Zeilen, ihrem Fenster, ohne die Zeilen zusammenzufassen, wie GROUP BY es tut. Du schreibst sie als function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY teilt die Zeilen in Gruppen, und ORDER BY legt die Reihenfolge innerhalb jeder Gruppe fest.

Jede Zeile bleibt im Ergebnis und trägt die Summe ihrer Region: 1000 für north, 500 für south. Der Anteil jedes Vertriebsmitarbeiters ist dann ein einziger Ausdruck (ana hat 50 Prozent von north). Jedes Aggregat (sum, count, avg, min, max) wird zur Window Function, wenn du OVER ergänzt. Ein leeres OVER () macht das ganze Ergebnis zu einem Fenster.

Zeilen mit ROW_NUMBER, RANK und DENSE_RANK nummerieren

Die drei Ranking-Funktionen unterscheiden sich nur, wenn Zeilen beim ORDER BY Wert gleichauf liegen:

ben und cy haben beide 85. row_number gibt ihnen trotzdem 2 und 3; wer die 2 bekommt, ist nicht festgelegt, außer das ORDER BY löst den Gleichstand auf. rank gibt beiden 2 und springt für dee auf 4. dense_rank gibt beiden 2 und macht mit 3 weiter.

FunktionGleichständeLücken danach
row_number()unterschiedliche Nummernnein
rank()gleiche Nummerja
dense_rank()gleiche Nummernein

Die ersten N Zeilen pro Gruppe holen

Window Functions werden nach WHERE ausgewertet, du kannst also nicht direkt nach ihnen filtern. Nummeriere die Zeilen in einer Unterabfrage oder CTE und filtere dann außen:

Das liefert die zwei besten Vertriebsmitarbeiter jeder Region. Nimm rank() statt row_number(), um jede Zeile zu behalten, die auf Platz zwei gleichauf liegt. Nur für die erste Zeile pro Gruppe ist DISTINCT ON kürzer.

Laufende Summe oder gleitenden Durchschnitt berechnen

Mit ORDER BY in OVER läuft ein Aggregat über einen Frame. Standardmäßig reicht der Frame von der ersten Zeile der Partition bis zur aktuellen Zeile, das ergibt eine laufende Summe. Eine ROWS BETWEEN Klausel setzt einen anderen Frame, etwa die aktuelle Zeile und die beiden davor:

Die laufende Summe erreicht an Tag 5 den Wert 130, und der Dreitagesdurchschnitt an Tag 5 ist (30 + 20 + 50) / 3 = 33.3.

Der Standard-Frame ist RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Er enthält jede Zeile, die beim ORDER BY Wert mit der aktuellen gleichauf liegt, zwei Zeilen mit demselben Tag bekommen also dieselbe laufende Summe. Schreib ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, um sie einzeln zu addieren.

Mit der vorigen Zeile vergleichen: LAG und LEAD

lag(col) liest einen Wert aus der Zeile vor der aktuellen in der Reihenfolge des Fensters, lead(col) aus der Zeile danach. Beide liefern jenseits des Rands NULL, außer du übergibst als drittes Argument einen Standardwert.

Tag 1 hat keine vorige Zeile, previous und change sind also NULL. Die WINDOW Klausel definiert ein Fenster einmal unter einem Namen, damit mehrere Funktionen es teilen.

first_value funktioniert mit dem Standard-Frame, last_value aber nicht: Der Standard-Frame endet bei der aktuellen Zeile, also liefert es den Wert der aktuellen Zeile. Ergänze ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, damit der Frame die ganze Partition umfasst. Weitere Funktionen, die es nur als Window Function gibt, sind ntile(n), percent_rank(), cume_dist() und nth_value(); die Referenz der Window Functions von PostgreSQL listet alle auf.

Filtern, welche Zeilen ein Aggregat zählt

FILTER (WHERE ...) funktioniert auch bei Window-Aggregaten, für bedingte Summen in jeder Zeile:

SELECT day,
       count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;

Häufig gestellte Fragen

Was ist der Unterschied zwischen GROUP BY und PARTITION BY?

GROUP BY liefert eine Zeile pro Gruppe. PARTITION BY innerhalb von OVER behält jede Zeile und gibt jeder einen Wert, der über ihre Gruppe berechnet wird. So zeigst du den Betrag einer Zeile neben der Summe ihrer Gruppe.

Kann ich eine Window Function in WHERE verwenden?

Nein. Window Functions laufen nach WHERE, GROUP BY und HAVING und sind daher nur in der Select-Liste und in ORDER BY erlaubt. Berechne den Wert in einer Unterabfrage oder CTE und filtere in der äußeren Abfrage.

Warum liefert LAST_VALUE den Wert der aktuellen Zeile?

Mit ORDER BY in OVER endet der Standard-Frame bei der aktuellen Zeile (und ihren Gleichständen), die letzte Zeile des Frames ist also die aktuelle. Ergänze ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, damit der Frame die ganze Partition umfasst.

Was ist der Unterschied zwischen ROWS und RANGE in einem Frame?

ROWS zählt physische Zeilen: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ist die aktuelle Zeile und die beiden davor. RANGE arbeitet mit dem Wert aus ORDER BY, Zeilen mit gleichen Werten (Peers) sind also immer zusammen im Frame.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's