Window Functions: Aggregate ohne Zusammenfassung von Zeilen

Worum es geht

Das PostgreSQL-Tutorial definiert eine Window Function als eine, die eine Berechnung über eine Menge von Tabellenzeilen ausführt, die irgendwie mit der aktuellen Zeile zusammenhängen, vergleichbar mit einem Aggregat, aber ohne Zeilen zu einer einzigen Ausgabezeile zusammenzufassen. Die OVER-Klausel definiert das Window: PARTITION BY teilt Zeilen in Gruppen auf, ORDER BY ordnet Zeilen innerhalb einer Partition, und ein optionaler Frame (ROWS, RANGE oder GROUPS BETWEEN ... AND ...) begrenzt, welche Zeilen der Partition die Funktion sieht. Die Referenzseite listet row_number, rank, dense_rank, percent_rank, ntile, lag, lead, first_value, last_value und nth_value auf; jedes Aggregat wie sum oder avg lässt sich ebenfalls mit OVER verwenden.

Warum es wichtig ist

Ohne Window Functions brauchen Fälle wie „das Gehalt jedes Teammitglieds neben dem Abteilungsdurchschnitt“, „die letzten drei Bestellungen je Kunde“ oder „ein laufender Saldo“ Self-Joins oder korrelierte Unterabfragen, die schwerer zu lesen sind. Eine Window Function drückt jeden dieser Fälle direkt in der SELECT-Liste aus.

So wird es angewendet

• Laufende Summe: sum(amount) OVER (PARTITION BY account_id ORDER BY created_at, id). Ein Tiebreaker im ORDER BY einschliessen: Das Tutorial hält fest, dass der Standard-Frame mit ORDER BY vom Partitionsanfang bis zur aktuellen Zeile plus allen nachfolgenden, ihr gleichen Zeilen reicht, sodass gleiche Sortierschlüssel zusammen summiert werden.
• Top-N je Gruppe: row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn in einer Unterabfrage oder einem CTE, dann aussen WHERE rn <= 3; Window Functions können nicht direkt in WHERE auftreten.
• Vergleich mit der vorherigen Zeile: lag(value) OVER (ORDER BY ts) für Deltas, lead für den nächsten Wert.
• Ranking: rank lässt nach Gleichständen Lücken, dense_rank nicht, row_number ist bei Gleichständen beliebig, sofern das ORDER BY nicht vollständig ist.
• Ein Window mit WINDOW w AS (PARTITION BY ... ORDER BY ...) teilen und OVER w für mehrere Funktionen schreiben.

Stolpersteine

Window Functions werden nach WHERE, GROUP BY und HAVING ausgewertet, sodass das Filtern auf ihr Ergebnis eine äussere Abfrage braucht. last_value liefert mit dem Standard-Frame das letzte Peer der aktuellen Zeile (die Zeile selbst, wenn das ORDER BY keine Gleichstände hat), nicht die letzte Zeile der Partition; die Referenzseite nennt das voraussichtlich wenig hilfreich, weshalb ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING gesetzt werden sollte. Grosse Partitionen sortieren im Speicher oder auf der Platte; in EXPLAIN nach WindowAgg- und Sort-Knoten Ausschau halten.