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 für jede Zeile eine Gruppe zusammenhängender Zeilen (ein sogenanntes „Fenster“) bestimmt, für die ein eigener Aggregatwert berechnet wird.

Diese Zeilenmenge kann sein:

• die gesamte Tabelle
• einzelne Partitionen der Tabelle, also eine Gruppe von Zeilen auf Basis eines oder mehrerer Felder
• oder ein Fensterrahmen, also ein konkreter Zeilenbereich innerhalb einer Tabelle oder Partition.

Wir können zum Beispiel einen Fensterrahmen definieren, der 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. Der Rahmen „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 Fensterrahmen kann auch eine spezifischere Menge von Zeilen angegeben werden. Zum Beispiel kann der Rahmen als "vorherige + aktuelle Zeile" der Tabelle definiert werden. Dann sieht es folgendermaßen aus:

  

  Anzumerken ist, dass der Rahmen für die erste Zeile nur aus einem 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, enthält das Fenster alle Zeilen des Abfrageergebnisses.

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> wird der Fensterrahmen festgelegt. Mit diesem Parameter lässt sich angeben, wie viele Zeilen vor und nach der aktuellen Zeile 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 Window-Funktionen 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?