Window Functions Cheat Sheet auf Deutsch

Fensterfunktionen

Fensterfunktionen berechnen ihr Ergebnis auf der Grundlage eines gleitenden Fensterrahmens, einer Reihe von Zeilen, die in irgendeiner Weise mit der aktuellen Zeile verbunden sind.

Aggregatfunktionen vs. Fensterfunktionen

Im Gegensatz zu Aggregatfunktionen werden bei Fensterfunktionen die Zeilen nicht zusammenfasst.

Syntax

SELECT Stadt, Monat, 
  SUM(Verkauft) OVER (
    PARTITION BY Stadt 
    ORDER BY Monat 
    RANGE UNBOUNDED PRECEDING) Gesamt
FROM Verkauf;

SELECT <Spalte_1>, <Spalte_2>,
  <Fensterfunktion> OVER (
    PARTITION BY <...>
    ORDER BY <...>
    <Fensterrahmen>) <Fenster_Spalte_Alias>
FROM <Tabellenname>;

Definition Benannter Fenster

SELECT Land, Stadt,
  RANK() OVER Durchschnitt_Land_Verkauft
FROM Verkauf
WHERE Monat BETWEEN 1 AND 6
GROUP BY Land, Stadt
HAVING sum(Verkauft) > 10000
WINDOW Durchschnitt_Land_Verkauft AS (
  PARTITION BY Land 
  ORDER BY avg(Verkauft) DESC)
ORDER BY Land, Stadt;

SELECT <Spalte_1>, <Spalte_2>,
  <Fensterfunktion>() OVER <Fenstername>
FROM <Tabellenname>
WHERE <...>
GROUP BY <...>
HAVING <...>
WINDOW <Fenstername> AS (
  PARTITION BY <...>
  ORDER BY <...>
  <window_frame>)
ORDER BY <...>;

PARTITION BY, ORDER BY und die Definition des Fensterrahmens sind alle optional.

Logische Reihenfolge der Operationen In SQL

1. FROM, JOIN
2. WHERE
3. GROUP BY
4. Aggregatfunktionen
5. HAVING
6. Fensterfunktionen
7. SELECT
8. DISTINCT
9. UNION/INTERSECT/EXCEPT
10. ORDER BY
11. OFFSET
12. LIMIT/FETCH/TOP

Sie können Fensterfunktionen in SELECT und ORDER BY verwenden. Sie können jedoch keine Fensterfunktionen in den Klauseln FROM, WHERE, GROUP BY oder HAVING einfügen.

PARTITION BY

PARTITION BY unterteilt Zeilen in mehrere Gruppen, die Partitionen genannt werden und auf die die Fensterfunktion angewendet wird.

ORDER BY

ORDER BY gibt die Reihenfolge der Zeilen in jeder Partition an, auf die die Fensterfunktion angewendet wird.

Standard ORDER BY: Ohne ORDER BY Klausel ist die Reihenfolge der Zeilen innerhalb jeder Partition willkürlich.

Fensterrahmen

Ein Fensterrahmen ist ein Satz von Zeilen, die in irgendeiner Weise mit der aktuellen Zeile zusammenhängen. Der Fensterrahmen wird in jeder Partition separat ausgewertet.

<ROWS | RANGE | GROUPS> BETWEEN Untergrenze AND Obergrenze

Die Grenzen können eine der fünf Optionen sein:

• UNBOUNDED PRECEDING
• n PRECEDING
• CURRENT ROW
• n FOLLOWING
• UNBOUNDED FOLLOWING

Die Untergrenze muss VOR der Obergrenze stehen.

1 Zeile vor der aktuellen Zeile und 1 Zeile nach der aktuellen Zeile

Stand 2024 wird GROUPS nur in PostgreSQL 11 und höher unterstützt.

Abkürzungen

Standard-Fensterrahmen

Wenn ORDER BY angegeben ist, lautet der Rahmen RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Ohne ORDER BY lautet die Rahmenangabe ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Liste der Fensterfunktionen

• Aggregat-Funktionen
• avg()
• count()
• max()
• min()
• sum()
• Ranking-Funktionen
• row_number()
• rank()
• dense_rank()
• Verteilungs-Funktionen
• percent_rank()
• cume_dist()
• Analytische Funktionen
• lead()
• lag()
• ntile()
• first_value()
• last_value()
• nth_value()

Aggregatfunktionen

• avg(Expr) – Durchschnittswert für Zeilen innerhalb des Fensterrahmens
• count(Expr) – Anzahl der Werte für Zeilen innerhalb des Fensterrahmens
• max(Expr) – Maximalwert innerhalb des Fensterrahmens
• min(Expr) – Mindestwert innerhalb des Fensterrahmens
• sum(Expr) – Summe der Werte innerhalb des Fensterrahmens

ORDER BY und Fensterrahmen: Für die Aggregatfunktionen ist kein ORDER BY erforderlich. Sie akzeptieren die Definition eines Fensterrahmens (ROWS, RANGE, GROUPS).

Ranking-Funktionen

• row_number() – eindeutige Nummer für jede Zeile innerhalb der Partition, mit unterschiedlichen Nummern für gleiche Werte
• rank() – Rangfolge innerhalb der Partition, mit Lücken und gleicher Rangfolge für gleiche Werte
• dense_rank() – Rangfolge innerhalb der Partition, ohne Lücken und mit gleicher Rangfolge für gleiche Werte

ORDER BY und Fensterrahmen: rank() und dense_rank() erfordern ORDER BY, aber row_number() erfordert kein ORDER BY. Ranking-Funktionen akzeptieren keine Fensterrahmen-Definition (ROWS, RANGE, GROUPS).

Verteilungsfunktionen

• percent_rank() – die Perzentil-Rangzahl einer Zeile – ein Wert im Intervall [0, 1]: (Rang-1) / (Gesamtzahl der Zeilen - 1)
• cume_dist() – die kumulative Verteilung eines Wertes innerhalb einer Gruppe von Werten, d.h. die Anzahl der Zeilen mit Werten, die kleiner oder gleich dem Wert der aktuellen Zeile sind, geteilt durch die Gesamtzahl der Zeilen; ein Wert im Intervall (0, 1]

ORDER BY und Fensterrahmen: Die Verteilungsfunktionen erfordern ORDER BY. Sie akzeptieren keine Fensterrahmen-Definition (ROWS, RANGE, GROUPS).

Analytische Funktionen

• lead(exr, offset, default) – der Wert für den Zeilen-Offset: Zeilen nach der aktuellen Zeile; offset und default sind optional; Default-Werte: offset = 1, default = NULL
• lag(expr, offset, default) – der Wert für den Zeilen-Offset: Zeilen vor der aktuellen Zeile; offset und default sind optional; Default-Werte: offset = 1, default = NULL

• ntile(n) – Zeilen innerhalb einer Partition so gleichmäßig wie möglich in n Gruppen aufteilen und jeder Zeile eine Gruppennummer zuweisen.

ORDER BY und Fensterrahmen: ntile(), lead() und lag() erfordern ein ORDER BY. Sie akzeptieren keine Fensterrahmen-Definition (ROWS, RANGE, GROUPS).

• first_value(Expr) – den Wert für die erste Zeile innerhalb des Fensterrahmens
• last_value(Expr) – der Wert für die letzte Zeile innerhalb des Fensterrahmens

Hinweis: Normalerweise sollten Sie RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING mit last_value() verwenden. Mit dem Standardfensterrahmen für ORDER BY, RANGE UNBOUNDED PRECEDING gibt last_value() den Wert für die aktuelle Zeile zurück.

• nth_value(Expr, n) – der Wert für die n-te Zeile innerhalb des Fensterrahmens; n muss eine ganze Zahl sein

ORDER BY und Fensterrahmen: first_value(), last_value() und nth_value() erfordern kein ORDER BY. Sie akzeptieren die Definition von Fensterrahmen (ROWS, RANGE, GROUPS).