Window-Funktionen in SQL

Window-Funktionen sind ein mächtiges Werkzeug der Sprache SQL, mit dem sich komplexe Berechnungen über Gruppen von Zeilen durchführen lassen, die mit der aktuellen Zeile in Beziehung stehen.

Funktionsweise

Vielleicht fragst du dich: „Was bedeutet eigentlich Window?“.

In einer Standard-SQL-Abfrage werden alle Zeilenmengen als ein einziger zusammenhängender Datenblock betrachtet, für den die Aggregatwerte berechnet werden.

Wenn aber Window-Funktionen verwendet werden, wird die Abfrage in Gruppen von Zeilen segmentiert (sogenannte „Fenster“), und für jedes dieser Segmente werden individuelle Aggregatwerte berechnet.

Dieses Fenster, das an die Window-Funktion übergeben wird, kann sein:

• die gesamte Tabelle
• einzelne Partitionen der Tabelle, also eine Gruppe von Zeilen auf Basis eines oder mehrerer Felder
• oder sogar ein konkreter Zeilenbereich innerhalb einer Tabelle oder Partition. Wir können zum Beispiel ein Fenster definieren, das an die Window-Funktion übergeben wird, als die vorherige + die aktuelle Zeile der Tabelle. Dann wird für jede Zeile der Wert der Aggregatfunktion auf eine eigene Weise berechnet, weil sich die Daten, die in die Funktion fließen, dynamisch von Zeile zu Zeile ändern. Das Fenster „gleitet“ dann sozusagen über die Tabelle.

Visualisierung

Window-Funktionen erhalten als Eingabe immer ein Datenfenster, das der Nutzer angibt, und geben das Ergebnis in einer eigenen Spalte zurück.

Schauen wir uns an, wie das aussehen kann. Dafür nehmen wir die Window-Funktion AVG zur Berechnung des Durchschnittswerts und folgende kleine Tabelle:

Und jetzt schauen wir uns an, wie die Window-Funktion bei verschiedenen übergebenen Fenstern arbeitet:

• Wenn als Fenster die gesamte Tabelle angegeben wird, ist das Fenster für alle Zeilen identisch und der Funktion AVG wird derselbe Datensatz übergeben, entsprechend ist das Ergebnis gleich.

  

• Wenn als Fenster eine Partition nach dem Feld home_type angegeben wird, wird der Funktion AVG eine Menge von Unterkünften mit demselben Typ übergeben, und entsprechend wird in der neuen Spalte der durchschnittliche Preis für Unterkünfte angezeigt, deren Typ mit dem Typ in der aktuellen Zeile der Tabelle übereinstimmt.

  

• Als Fenster kann auch eine spezifischere Menge von Zeilen angegeben werden. Zum Beispiel kann das Fenster als "vorherige + aktuelle Zeile" der Tabelle definiert werden. Dann sieht es folgendermaßen aus:

  

  Anzumerken ist, dass das Fenster für die erste Zeile nur aus 1 Datensatz besteht, weil es keine vorherige Zeile gibt.

Syntax einer Window-Funktion

MySQL 8.1
SELECT <window_funktion>(<tabellenfeld>)
OVER (
      [PARTITION BY <spalten_zum_partitionieren>]
      [ORDER BY <spalten_zum_sortieren>]
      [ROWS|RANGE <definition_des_zeilenbereichs>]
)

Wobei:

• <window_funktion>(<tabellenfeld>) — die verwendete Window-Funktion. Zum Beispiel AVG(price).
• Danach folgt OVER, das das Fenster (die Gruppe von Zeilen) definiert, das an die Window-Funktion übergeben wird. Bleibt die Konstruktion OVER () ohne Parameter, dient die gesamte Tabelle als Fenster.

Innerhalb von OVER folgen dann 3 optionale Parameter, mit denen sich das Fenster flexibel konfigurieren lässt:

• mit PARTITION BY <spalten_zum_partitionieren> wird die Ergebnismenge in nicht überlappende Teilmengen aufgeteilt, wobei jede Teilmenge Zeilen mit gleichen Werten in einer oder mehreren Spalten enthält. So entstehen Partitionen.
• mit ORDER BY <spalten_zum_sortieren> wird die Reihenfolge der Zeilen innerhalb des Fensters festgelegt. Das spielt besonders bei Rang-Window-Funktionen eine wichtige Rolle.
• mit ROWS|RANGE <definition_des_zeilenbereichs> werden Zeilenbereiche gebildet. Mit diesem Parameter lässt sich angeben, wie viele Zeilen vor und nach der aktuellen ins Fenster aufgenommen werden.

Auf jeden dieser Parameter gehen wir in den folgenden Artikeln genauer ein.

Beispiel für die Verwendung einer Window-Funktion

Versuchen wir, mit Hilfe von Window-Funktionen eine Liste der Namen der Schüler und die Anzahl der Personen in ihrer Klasse zu erhalten.

Holen wir uns zunächst die Liste der Schüler und die ID der Klasse, in der sie lernen:

MySQL 8.1
SELECT
    Student.first_name,
    Student.last_name,
    Student_in_class.class
FROM
    Student_in_class
JOIN
    Student ON Student_in_class.student = Student.id;

Und jetzt, um zu berechnen, wie viele Schüler in jeder Klasse lernen, und um diese Information in einer neuen Spalte auszugeben, können wir eine Window-Funktion verwenden:

MySQL 8.1
SELECT
    Student.first_name,
    Student.last_name,
    Student_in_class.class,
    COUNT(*) OVER (PARTITION BY Student_in_class.class) AS student_count_in_class
FROM
    Student_in_class
JOIN
    Student ON Student_in_class.student = Student.id;

Was unsere Window-Funktion macht

Der Ausdruck PARTITION BY Student_in_class.class teilt alle Zeilen der Tabelle in Partitionen nach dem Feld class auf. Für jede Zeile werden der Window-Funktion also nur die Zeilen der Tabelle übergeben, in denen das Feld class mit dem Feld class der aktuellen Zeile übereinstimmt.

Die Funktion COUNT gibt die Anzahl der ihr übergebenen Zeilen zurück, und so bekommen wir, wie viele Schüler in jeder Klasse lernen.

Ausführungsreihenfolge von Window-Funktionen in SELECT

Bei der Verwendung von Window-Funktionen ist es wichtig zu verstehen, in welcher Reihenfolge sie ausgeführt werden. Wie wir im Schema unten sehen können, werden die Fenster im vorletzten Schritt verarbeitet, also nach dem Filtern und Gruppieren, aber vor dem finalen Sortieren der Ergebnisse.

Fazit

In diesem Artikel haben wir das Konzept der Window-Funktionen, ihre Möglichkeiten und ihren praktischen Nutzen kurz betrachtet. In den nächsten Artikeln gehen wir auf jeden Aspekt der Window-Funktionen ausführlicher ein.

Und zum Schluss prüfen wir, ob wir alles verstanden haben:

Was ist der zentrale Unterschied zwischen Window-Funktionen und Aggregatfunktionen mit Gruppierung in SQL?