Die wichtigsten Window-Funktionen

In den vorigen Artikeln haben wir uns angeschaut, wie Window-Funktionen funktionieren, und das Konzept des Datenfensters kennengelernt, das an eine Window-Funktion übergeben wird. Jetzt ist es an der Zeit, sich anzusehen, welche Arten von Window-Funktionen es gibt.

Arten von Window-Funktionen

Window-Funktionen lassen sich in 3 Gruppen einteilen:

• Aggregat-Window-Funktionen
• Rang-Window-Funktionen
• Versatz-Window-Funktionen

Aggregat-Window-Funktionen

Aggregatfunktionen sind Funktionen, die auf einer Datenmenge arithmetische Berechnungen ausführen und ein Gesamtergebnis zurückgeben.

• SUM — berechnet die Gesamtsumme der Werte;
• COUNT — zählt die Gesamtanzahl der Einträge in einer Spalte;
• AVG — berechnet das arithmetische Mittel;
• MAX — ermittelt den größten Wert;
• MIN — ermittelt den kleinsten Wert.

MySQL 8.1
SELECT id,
	home_type,
	price,
	SUM(price) OVER(PARTITION BY home_type) AS "Sum",
	COUNT(price) OVER(PARTITION BY home_type) AS "Count",
	AVG(price) OVER(PARTITION BY home_type) AS "Avg",
	MAX(price) OVER(PARTITION BY home_type) AS "Max",
	MIN(price) OVER(PARTITION BY home_type) AS "Min"
FROM Rooms;

Rang-Window-Funktionen

Rang-Window-Funktionen sind Funktionen, die für jede Zeile in einem Fenster einen Rang vergeben.

Bei Rangfunktionen ist nach dem Schlüsselwort OVER zwingend die Angabe der Bedingung ORDER BY erforderlich, nach der die Sortierung für die Rangvergabe erfolgt.

• ROW_NUMBER — gibt die Zeilennummer zurück und wird zum Nummerieren verwendet;
• RANK — gibt den Rang jeder Zeile zurück. So funktioniert es:
  • Sortierung: Zunächst werden die Zeilen nach einer oder mehreren Spalten sortiert. Diese Spalten werden in der ORDER BY-Klausel innerhalb von OVER angegeben.
  • Rangvergabe: Jeder eindeutigen Zeile oder Gruppe von Zeilen mit gleichen Werten in den Sortierspalten wird ein Rang zugewiesen. Der Rang beginnt bei 1.
  • Gleiche Werte: Wenn mehrere Zeilen in den Sortierspalten dieselben Werte haben, erhalten sie denselben Rang. Wenn zum Beispiel zwei Zeilen den zweiten Platz belegen, erhalten beide den Rang 2.
  • Überspringen von Rängen: Nach einer Gruppe von Zeilen mit gleichem Rang erhöht sich der nächste Rang um die Anzahl der Zeilen in dieser Gruppe. Wenn zwei Zeilen also den Rang 2 haben, bekommt die nächste Zeile den Rang 4, nicht 3.
  • Fortsetzung der Sortierung: Dieser Vorgang setzt sich fort, bis allen Zeilen in der Ergebnismenge Ränge zugewiesen wurden.
• DENSE_RANK — gibt ebenfalls den Rang jeder Zeile zurück. Im Gegensatz zur Funktion RANK überspringt sie aber keine Ränge: Nach einer Gruppe gleicher Werte erhöht sich der Rang um eins und nicht um die Anzahl der Zeilen. Wenn zum Beispiel zwei Zeilen den Rang 2 haben, bekommt die nächste Zeile den Rang 3, nicht 4.

MySQL 8.1
SELECT id,
	home_type,
	price,
	ROW_NUMBER() OVER(PARTITION BY home_type ORDER BY price) AS "row_number",
	RANK() OVER(PARTITION BY home_type ORDER BY price) AS "rank",
	DENSE_RANK() OVER(PARTITION BY home_type ORDER BY price) AS "dense_rank"
FROM Rooms;

Versatz-Window-Funktionen

Versatz-Window-Funktionen sind Funktionen, mit denen man sich innerhalb eines Fensters bewegen und auf andere Zeilen relativ zur aktuellen Zeile zugreifen kann, sowie auf Werte am Anfang oder Ende des Fensters.

• LAG — greift auf Daten aus vorherigen Zeilen des Fensters zu.

  Hat drei Argumente: die Spalte, deren Wert zurückgegeben werden soll, die Anzahl der Zeilen für den Versatz (standardmäßig 1) und den Wert, der zurückgegeben werden soll, falls der Versatz NULL ergibt.

• LEAD — greift auf Daten aus nachfolgenden Zeilen zu. Hat analog zu LAG 3 Argumente.

• FIRST_VALUE — gibt den ersten Wert im Fenster zurück. Erwartet als Argument die Spalte, deren Wert zurückgegeben werden soll.

• LAST_VALUE — gibt den letzten Wert im Fenster zurück. Erwartet als Argument die Spalte, deren Wert zurückgegeben werden soll.

  Bei Verwendung von ORDER BY werden die Fensterränder standardmäßig vom Beginn der Partition bis zur aktuellen Zeile gesetzt (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Deshalb gibt LAST_VALUE den Wert der aktuellen Zeile zurück, nicht den der letzten Zeile der gesamten Partition. Um wirklich den letzten Wert der Partition zu bekommen, musst du die Fenstergrenzen explizit erweitern: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

MySQL 8.1
SELECT id,
	home_type,
	price,
	LAG(price) OVER(PARTITION BY home_type ORDER BY price) AS "lag",
	LAG(price, 2) OVER(PARTITION BY home_type ORDER BY price) AS "lag_2",
	LEAD(price) OVER(PARTITION BY home_type ORDER BY price) AS "lead",
	FIRST_VALUE(price) OVER(PARTITION BY home_type ORDER BY price) AS "first_value",
	LAST_VALUE(price) OVER(PARTITION BY home_type ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "last_value"
FROM Rooms;