Zurück zur Artikelliste Artikel
5 Leseminuten

Window Functions Cheat Sheet auf Deutsch

Lade diesen zweiseitigen SQL-Fensterfunktionen Spickzettel im PDF- oder PNG-Format herunter, drucke ihn aus und befestige ihn auf deinem Schreibtisch.

Fensterfunktionen sind erweiterte SQL-Funktionen, die Berechnungen über ein definiertes Fenster von Zeilen ermöglichen. Sie sind besonders nützlich für komplexe Datenanalysen.

Der SQL-Fensterfunktionen Spickzettel auf Deutsch bietet dir die Syntax aller grundlegenden Klauseln, zeigt dir, wie man verschiedene Bedingungen schreibt und enthält Beispiele.

Optionen zum Herunterladen:

Erste Seite von SQL-Fensterfunktionen Spickzettel Zweite Seite von SQL-Fensterfunktionen Spickzettel

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.

Fensterfunktionen

Aggregatfunktionen vs. Fensterfunktionen

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

Aggregatfunktionen vs. Fensterfunktionen

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.

Beispiel für die PARTITION BY-Klausel in Fensterfunktionen Standard-Partition: Ohne PARTITION BY-Klausel ist die gesamte Ergebnismenge die Partition.

ORDER BY

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

Beispiel für die ORDER BY-Klausel in Fensterfunktionen

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.

Grenzen in der Definition des Fensterrahmens
<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.

Beispiel für ROWS-, RANGE- und GROUPS-Klauseln in der Definition des Fensterrahmens

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

AbkürzungBedeutung
UNBOUNDED PRECEDINGBETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
n PRECEDINGBETWEEN n PRECEDING AND CURRENT ROW
CURRENT ROWBETWEEN CURRENT ROW AND CURRENT ROW
n FOLLOWINGBETWEEN AND CURRENT ROW AND n FOLLOWING
UNBOUNDED FOLLOWINGBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

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
Ranking Funktionen

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]
Verteilungsfunktionen

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
LEAD- und LAG-Funktionen
  • ntile(n) – Zeilen innerhalb einer Partition so gleichmäßig wie möglich in n Gruppen aufteilen und jeder Zeile eine Gruppennummer zuweisen.
Beispiel für die NTILE-Funktion

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
Beispiele für die FIRST_VALUE- und LAST_VALUE-Funktionen

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
Beispiel für die NTH_VALUE-Funktion

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