Window Functions sind ein leistungsstarkes Werkzeug in SQL, das es ermöglicht, Berechnungen über eine Menge von Zeilen durchzuführen, die in irgendeiner Weise mit der aktuellen Zeile zusammenhängen. Diese Funktionen sind besonders nützlich, wenn Sie aggregierte Werte berechnen möchten, ohne die Zeilen in der Ausgabe zu gruppieren. Window Functions werden durch das Vorhandensein einer OVER()-Klausel identifiziert und können in drei Hauptkategorien unterteilt werden: Ranking-Funktionen, Aggregatfunktionen und analytische Funktionen. In diesem Blogbeitrag werden wir jede dieser Kategorien im Detail untersuchen und mit technischen Beispielen illustrieren.
1. Ranking Window Functions
Ranking-Funktionen werden verwendet, um Zeilen innerhalb eines Fensters zu sortieren und zu nummerieren. Sie sind besonders nützlich, um Ranglisten zu erstellen oder Zeilen in bestimmte Gruppen einzuteilen.
1.1 ROW_NUMBER()
Die ROW_NUMBER()-Funktion weist jeder Zeile innerhalb des Fensters eine eindeutige Nummer zu, beginnend bei 1. Die Reihenfolge wird durch die ORDER BY-Klausel innerhalb der OVER()-Klausel bestimmt.
Beispiel:
SELECT
MitarbeiterID,
Gehalt,
ROW_NUMBER() OVER (ORDER BY Gehalt DESC) AS Rang
FROM
Mitarbeiter;
Erklärung: Dieses Query gibt eine Liste der Mitarbeiter zurück, sortiert nach ihrem Gehalt in absteigender Reihenfolge. Jeder Mitarbeiter erhält eine eindeutige Rangnummer basierend auf seinem Gehalt.
1.2 RANK()
Die RANK()-Funktion weist jeder Zeile einen Rang zu, wobei Zeilen mit denselben Werten denselben Rang erhalten. Es gibt jedoch Lücken in der Rangfolge, wenn mehrere Zeilen denselben Rang haben.
Beispiel:
SELECT
MitarbeiterID,
Gehalt,
RANK() OVER (ORDER BY Gehalt DESC) AS Rang
FROM
Mitarbeiter;
Erklärung: Wenn zwei Mitarbeiter das gleiche Gehalt haben, erhalten sie denselben Rang. Der nächste Mitarbeiter erhält dann den Rang, der der Anzahl der vorherigen Zeilen entspricht, was zu Lücken in der Rangfolge führen kann.
1.3 DENSE_RANK()
Die DENSE_RANK()-Funktion ähnelt RANK(), jedoch ohne Lücken in der Rangfolge. Wenn mehrere Zeilen denselben Rang haben, erhält die nächste Zeile den nächsten Rang ohne Überspringung.
Beispiel:
SELECT
MitarbeiterID,
Gehalt,
DENSE_RANK() OVER (ORDER BY Gehalt DESC) AS Rang
FROM
Mitarbeiter;
Erklärung: Auch hier erhalten Mitarbeiter mit demselben Gehalt denselben Rang, aber es gibt keine Lücken in der Rangfolge.
1.4 NTILE()
Die NTILE()-Funktion teilt die Zeilen in eine angegebene Anzahl von Gruppen (Buckets) ein. Jeder Zeile wird eine Gruppennummer zugewiesen.
Beispiel:
SELECT
MitarbeiterID,
Gehalt,
NTILE(4) OVER (ORDER BY Gehalt DESC) AS Quartil
FROM
Mitarbeiter;
Erklärung: Dieses Query teilt die Mitarbeiter in vier gleich große Gruppen (Quartile) basierend auf ihrem Gehalt ein.
2. Aggregate Window Functions
Aggregatfunktionen können auch als Window Functions verwendet werden, um aggregierte Werte über ein Fenster von Zeilen zu berechnen, ohne die Zeilen zu gruppieren.
2.1 MIN(), MAX(), AVG(), SUM()
Diese Funktionen berechnen den minimalen, maximalen, durchschnittlichen oder summierten Wert über ein Fenster von Zeilen.
Beispiel:
SELECT
MitarbeiterID,
Gehalt,
AVG(Gehalt) OVER (PARTITION BY AbteilungID) AS Durchschnittsgehalt
FROM
Mitarbeiter;
Erklärung:
Dieses Query berechnet das durchschnittliche Gehalt für jede Abteilung (AbteilungID) und zeigt es neben dem Gehalt jedes Mitarbeiters an.
2.2 COUNT(), COUNT_BIG()
Die COUNT()-Funktion zählt die Anzahl der Zeilen im Fenster, während COUNT_BIG() für große Datensätze verwendet wird.
Beispiel:
SELECT
AbteilungID,
COUNT(MitarbeiterID) OVER (PARTITION BY AbteilungID) AS Mitarbeiteranzahl
FROM
Mitarbeiter;
Erklärung: Dieses Query zählt die Anzahl der Mitarbeiter in jeder Abteilung.
2.3 STDEV(), STDEVP(), VAR(), VARP()
Diese Funktionen berechnen die Standardabweichung und Varianz über ein Fenster von Zeilen.
Beispiel:
SELECT
AbteilungID,
STDEV(Gehalt) OVER (PARTITION BY AbteilungID) AS Gehaltsstandardabweichung
FROM
Mitarbeiter;
Erklärung: Dieses Query berechnet die Standardabweichung der Gehälter in jeder Abteilung.
3. Analytic Window Functions
Analytische Funktionen ermöglichen es, komplexe Berechnungen über ein Fenster von Zeilen durchzuführen, z. B. den Zugriff auf vorherige oder nachfolgende Zeilen.
3.1 LAG() und LEAD()
Die LAG()-Funktion ermöglicht den Zugriff auf eine vorherige Zeile, während LEAD() den Zugriff auf eine nachfolgende Zeile ermöglicht.
Beispiel:
SELECT
MitarbeiterID,
Gehalt,
LAG(Gehalt, 1) OVER (ORDER BY Gehalt) AS VorherigesGehalt,
LEAD(Gehalt, 1) OVER (ORDER BY Gehalt) AS NaechstesGehalt
FROM
Mitarbeiter;
Erklärung: Dieses Query zeigt das Gehalt des vorherigen und des nächsten Mitarbeiters in der sortierten Liste an.
3.2 FIRST_VALUE() und LAST_VALUE()
Diese Funktionen geben den ersten bzw. letzten Wert im Fenster zurück.
Beispiel:
SELECT
MitarbeiterID,
Gehalt,
FIRST_VALUE(Gehalt) OVER (ORDER BY Gehalt) AS NiedrigstesGehalt,
LAST_VALUE(Gehalt) OVER (ORDER BY Gehalt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS HoechstesGehalt
FROM
Mitarbeiter;
Erklärung: Dieses Query zeigt das niedrigste und höchste Gehalt in der gesamten Tabelle an.
3.3 PERCENT_RANK(), PERCENTILE_CONT(), PERCENTILE_DISC()
Diese Funktionen berechnen den prozentualen Rang oder den Wert an einem bestimmten Perzentil.
Beispiel:
SELECT
MitarbeiterID,
Gehalt,
PERCENT_RANK() OVER (ORDER BY Gehalt) AS ProzentRang,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Gehalt) OVER () AS MedianGehalt
FROM
Mitarbeiter;
Erklärung: Dieses Query berechnet den prozentualen Rang jedes Gehalts und den Median des Gehalts.
3.4 CUME_DIST()
Die CUME_DIST()-Funktion berechnet die kumulative Verteilung eines Werts innerhalb eines Fensters.
Beispiel:
SELECT
MitarbeiterID,
Gehalt,
CUME_DIST() OVER (ORDER BY Gehalt) AS KumulativeVerteilung
FROM
Mitarbeiter;
Erklärung: Dieses Query zeigt die kumulative Verteilung der Gehälter an, d. h. den Anteil der Gehälter, die kleiner oder gleich dem aktuellen Gehalt sind.
Fazit
Window Functions sind ein äußerst nützliches Werkzeug in SQL, das es ermöglicht, komplexe Berechnungen über Fenster von Zeilen durchzuführen, ohne die Daten gruppieren zu müssen. Durch die Verwendung von Ranking-, Aggregat- und analytischen Funktionen können Sie leistungsstarke Abfragen erstellen, die tiefe Einblicke in Ihre Daten liefern. Mit den oben genannten Beispielen sollten Sie in der Lage sein, Window Functions in Ihren eigenen SQL-Abfragen effektiv einzusetzen.