Handbuch der SQL-Fensterfunktionen

Jeder Datenanalyst kann von der Kenntnis der SQL-Fensterfunktionen profitieren. Sie verbessern Ihre Datenanalyse und öffnen Ihnen die Türen zu einer Datenanalyse auf einer völlig neuen Ebene. Dieser Artikel ist Ihr Ausgangspunkt für die Welt der SQL-Fensterfunktionen.

SQL-Fensterfunktionen ermöglichen es Ihnen, alle Datenzeilen und ihre Aggregatwerte gleichzeitig anzuzeigen. Klingt ein bisschen wie GROUP BY auf Steroiden, nicht wahr? Das ist aber noch nicht alles. Da Fensterfunktionen verschiedene Berechnungen über den Zeilensatz durchführen, ermöglichen sie auch die Aufteilung von Datensätzen in Teilmengen. Sie helfen Ihnen, Daten einfach zu ordnen, zu aggregieren, Unterschiede zwischen verschiedenen Zeiträumen zu berechnen (z. B. Gewinnsteigerung/-verringerung zwischen zwei Monaten/Quartalen/Jahren) und kumulative Summen, laufende Summen, gleitende Durchschnitte usw. zu ermitteln.

Fensterfunktionen sind ein komplexes Thema, aber mit unserem Kurs ist es viel einfacher, sie zu lernen. Fensterfunktionen Kurs. Es handelt sich um einen interaktiven Kurs mit 218 Übungen, die ein systematisches Lernen mit viel Programmieren ermöglichen. Sie lernen alles über die wichtigsten Fensterfunktionsklauseln - z. B. OVER(), ORDER BY und PARTITION BY - und was ein Fensterrahmen ist. Dann lernen Sie, wie Sie all das im Zusammenhang mit verschiedenen Fensterfunktionen verwenden können.

Nach dem Lernen ist es Zeit für die Praxis (die auch Lernen ist). Unser Fensterfunktionen Practice Set bietet Ihnen 100 zusätzliche interaktive Übungen, damit Sie Ihr Wissen auch wirklich in die Praxis umsetzen können.

Die Syntax von SQL Fensterfunktionen

Fensterfunktionen haben ihren Namen von einem Fensterrahmen, der eine Reihe von Zeilen enthält, die mit der aktuellen Zeile in Beziehung stehen.

Um eine Fensterfunktion auf dem Fensterrahmen auszuführen, müssen Sie die allgemeine Syntax für Fensterfunktionen kennen:

SELECT column_1,
       column_2,
	<window_function> OVER(PARTITION BY … ORDER BY … <window_frame>) AS column_alias
FROM table;

Es gibt mehrere wesentliche Teile dieser Syntax, die erklärt werden müssen:

• OVER-Klausel: Dies ist die obligatorische Klausel, die zur Definition eines Fensterrahmens erforderlich ist. Sie kennen zum Beispiel die Fensterfunktion SUM(order_value) OVER() as a SUM(). Ohne OVER() ist dies nur eine normale SUMME() Aggregatfunktion.
• PARTITION BY: Dies ist eine optionale Klausel zur Partitionierung des Datensatzes, d. h. zur Aufteilung in Teilmengen. Damit können Sie eine Fensterfunktion auf jede Partition separat anwenden. Wird diese Klausel weggelassen, so ist die gesamte Ergebnismenge eine Partition.
• ORDER BY: Diese optionale Klausel (für einige Fensterfunktionen) wird verwendet, um die Reihenfolge der Zeilen in einem Fensterrahmen festzulegen. Wenn Sie diese Klausel weglassen, ist die Reihenfolge der Zeilen im Fensterrahmen willkürlich.
• <window_frame>: Damit werden die oberen und unteren Grenzen eines Fensterrahmens festgelegt. Zwei wichtige Klauseln, die dafür verwendet werden, sind ROWS und RANGE. ROWS definiert die Anzahl der Zeilen, die vor und nach der aktuellen Zeile liegen. Die Klausel RANGE definiert den Bereich der Zeilen auf der Grundlage ihres Wertes im Vergleich zur aktuellen Zeile. Weitere Informationen finden Sie in unserem Artikel über die Unterschiede zwischen ROWS und RANGE. Dieser Teil der Syntax wird in der Regel weggelassen, da der Standardfensterrahmen das ist, was die Benutzer am häufigsten benötigen.

Der Standard-Fensterrahmen hängt in diesem Fall davon ab, ob Sie die ORDER BY -Klausel in OVER() verwenden. Wenn Sie dies tun, ist der Rahmen die aktuelle Zeile und alle Zeilen vor ihr in der aktuellen Partition. Wenn Sie ORDER BY nicht angeben, ist der Fensterrahmen die aktuelle Zeile und alle davor und danach liegenden Zeilen in der aktuellen Partition. Im zweiten Fall ist der Fensterrahmen im Grunde der gesamte Datensatz - oder die gesamte Partition, wenn Sie auch PARTITION BY verwenden.

Allgemeine SQL Fensterfunktionen

Es gibt viele verschiedene Fensterfunktionen. Hier finden Sie einen Überblick über einige der gebräuchlichsten:

SQL Fensterfunktionen Beispiele

Nachdem wir nun die Grundlagen durchgegangen sind, ist es an der Zeit, einige praktische Beispiele für Fensterfunktionen zu zeigen.

In allen Beispielen verwende ich die gleiche Tabelle. Sie trägt den Titel exchange_rates und enthält die Wechselkurse der Europäischen Zentralbank (EZB) für April 2024 für drei Währungspaare: EUR vs. USD, EUR vs. CHF und EUR vs. JPY.

Hier sehen Sie einen Ausschnitt der Daten.

Beispiel für eine Ranking-Fensterfunktion

Dieses Beispiel zeigt Ihnen, wie DENSE_RANK() funktioniert. Die beiden anderen Ranking-Fensterfunktionen können auf die gleiche Weise verwendet werden; sie könnten (je nach Daten) leicht unterschiedliche Ergebnisse liefern.

Der folgende Code ordnet die Daten in der Tabelle vom höchsten zum niedrigsten Wechselkurs:

SELECT date,
	 currency_pair,
	 ecb_rate, 
	 DENSE_RANK() OVER (ORDER BY ecb_rate DESC) AS rank_ecb_rate
FROM exchange_rates;

Ich wähle das Datum, das Währungspaar und den Kurs. Jetzt habe ich DENSE_RANK() gewählt, um die Daten zu ordnen. Das ist nur für den Fall, dass es die gleichen Wechselkurse gibt (höchst unwahrscheinlich, aber trotzdem...); ich möchte, dass sie gleich eingestuft werden, und ich möchte keine Lücken in der Einstufung haben.

Auf die Funktion DENSE_RANK() folgt die Klausel OVER(), die die Fensterfunktion definiert. In den Klammern der Klausel verwende ich eine weitere Klausel für die Fensterfunktion -ORDER BY. Auf diese Weise weise ich die Fensterfunktion DENSE_RANK() an, die Daten nach dem EZB-Satz in absteigender Reihenfolge zu ordnen.

Dies ist die Ausgabe, die ich erhalte:

Wie Sie sehen können, wurde jede Zeile nach dem Wert des Zinssatzes geordnet. Bei gleichen Kursen würde DENSE_RANK() denselben Rang zuweisen und den Rang nicht überspringen. RANK() würde dasselbe tun, nur würde es die Rangfolge überspringen. ROW_NUMBER() würde einen fortlaufenden Rang zuweisen, auch wenn einige Zeilen denselben Wechselkurs haben.

Beispiel für die Aggregate Window Funktion

Hier ist ein schönes Beispiel dafür, wie Sie die Funktion AVG() window verwenden können, um den Durchschnittskurs für jedes Währungspaar zu berechnen:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 AVG(ecb_rate) OVER (PARTITION BY currency_pair) AS avg_rate_by_currency
FROM exchange_rates
ORDER BY currency_pair, date;

Wie bei der regulären Aggregatfunktion AVG() müssen Sie die Spalte angeben, über die Sie den Durchschnitt ermitteln möchten. Um die Funktion in eine Fensterfunktion umzuwandeln, verwenden Sie die Klausel OVER(). In diesem Fall verwende ich PARTITION BY in der Spalte currency_pair. Auf diese Weise unterteile ich die Daten in Teilmengen je nach Währungspaar.

Einfach ausgedrückt, ich berechne den Durchschnittskurs für jedes Währungspaar separat.

Werfen Sie einen Blick auf die Codeausgabe. Sie wurde nach Währungspaaren und Datum sortiert:

Der Durchschnittskurs EUR vs. CHF beträgt 0,9793, und dieser Wert wird für jede Zeile EUR vs. CHF wiederholt. Wenn die Fensterfunktion das nächste Währungspaar erreicht, wird der Durchschnitt zurückgesetzt und neu berechnet; für EUR vs. JPY beträgt er 164,1211. Der Durchschnitt für EUR gegen USD beträgt schließlich 1,0795.

Mit dieser Fensterfunktion konnte ich die Durchschnitte separat berechnen und die Durchschnittswerte anzeigen, ohne einzelne Zeilen zusammenzufassen. Mit anderen Worten, ich kann jeden Tageskurs zusammen mit dem Durchschnitt für dieses Währungspaar sehen.

Beispiele für analytische Fensterfunktionen

In diesem Abschnitt zeige ich drei Beispiele für verschiedene analytische SQL-Fensterfunktionen.

LAG()

Das Beispiel LAG() zeigt, wie man die tägliche Veränderung berechnet. Diese Funktion wird verwendet, um auf den Wert der vorangegangenen Zeilen zuzugreifen. Eine weitere analytische Fensterfunktion ist LEAD(), die genau das Gegenteil tut - sie holt die Daten aus den folgenden Zeilen. Beide Funktionen haben im Grunde die gleiche Syntax; Sie ändern nur den Namen der Funktion.

In diesem Beispiel möchte ich die tägliche Veränderung der Wechselkurse berechnen:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 ecb_rate - LAG(ecb_rate) OVER (PARTITION BY currency_pair ORDER BY date) AS daily_rate_change
FROM exchange_rates;

LAG() ist eine Funktion für den Zugriff auf den/die Wert(e) in der/den Zeile(n) vor der aktuellen Zeile. Im obigen Beispiel gebe ich die Spalte ecb_rate in LAG() an. Das bedeutet, dass ich auf den Wert des EZB-Kurses zugreifen möchte. Ich gebe nicht explizit die Anzahl der Zeilen an, zu denen ich zurückgehen möchte, daher wird der Standardwert von einer Zeile verwendet.

OVER() kommt nach dem Funktionsnamen. Hier partitioniere ich den Datensatz nach Währungspaaren, da ich die tägliche Kursänderung für jedes Währungspaar separat berechnen möchte.

Ich verwende auch ORDER BY, um die Daten innerhalb der Partitionen zu sortieren. Da die Logik darin besteht, einen Tag zurückzugehen, müssen die Daten aufsteigend nach Datum sortiert werden.

Der Teil LAG() des Codes steht also für den Wechselkurs des Vortags. Um die tägliche Differenz zu erhalten, ziehe ich diesen Wert einfach vom aktuellen Wechselkurs ab (ecb_rate - LAG(ecb_rate)).

Hier ist die Ausgabe:

In der ersten Zeile steht NULL, weil es kein vorheriges Datum gibt, so dass die Differenz nicht berechnet werden kann. In der nächsten Zeile ist die tägliche Kursänderung 0,9792-0,9765 = 0,0027. Das gleiche Prinzip, bei dem der Wert der vorherigen Zeile vom aktuellen Wert subtrahiert wird, wird mit jeder Zeile fortgesetzt.

Da der Datensatz nach Währungspaaren unterteilt ist, wird die Berechnung zurückgesetzt, sobald andere Währungspaare erreicht werden, d. h. EUR vs. JPY und EUR vs. USD.

ERSTER_WERT()

Die Fensterfunktion FIRST_VALUE() kann auf unsere Daten angewendet werden, um die Unterschiede zwischen dem aktuellen und dem ersten Wechselkurs des Monats zu berechnen.

Dies ist möglich, weil FIRST_VALUE() den ersten Wert innerhalb der Partition zurückgibt. Ihr Gegenstück ist LAST_VALUE(), die den letzten Wert in der Partition zurückgibt. Beide Funktionen haben im Grunde die gleiche Syntax, nur der Name der Funktion ist anders (und natürlich das Ergebnis!).

SELECT date,
	 currency_pair,
	 ecb_rate,
	 ecb_rate - FIRST_VALUE(ecb_rate) OVER (PARTITION BY currency_pair ORDER BY date) AS difference_current_first
	  FROM exchange_rates
ORDER BY currency_pair, date;

Ich möchte den Kurswert; deshalb gibt es die Spalte ecb_rate in FIRST_VALUE(). Die Daten sind wiederum nach Währungspaaren aufgeteilt, da ich eine Berechnung für jedes Paar möchte.

OK, FIRST_VALUE() wird also verwendet, um den Wert aus der ersten Zeile in der Partition zu holen. Aber was passiert, wenn ich die Daten in der Partition aufsteigend nach Datum ordne? Richtig, die erste Zeile ist diejenige mit dem Kurs des ersten Tages des Monats. In unserem Fall ist es der erste Wechselkurs im April.

Ziehen Sie nun diesen vom aktuellen Wechselkurs ab. Da wir wissen, dass unsere Daten nur für April gelten, erhalten wir die Differenz zwischen dem aktuellen und dem ersten Kurs für diesen Monat.

Wenn die Differenz 0 ist, sind der aktuelle und der früheste Kurs gleich. Für EUR vs. CHF ist der erste Kurs 0,9765. Prüfen wir die ersten beiden Zeilen: 0.9765 - 0.9765 = 0.0000; 0.9792 - 0.9765 = 0.0027.

Das gleiche Prinzip gilt für die beiden anderen Währungspaare.

NTILE()

Das letzte Beispiel, das ich Ihnen zeige, ist die Funktion NTILE(), die das Fenster (oder die Partition) in Gruppen unterteilt. Das Argument in den Klammern der Funktion NTILE() gibt die Anzahl der Gruppen an, in die Ihr Datensatz aufgeteilt werden soll.

Die Aufteilung erfolgt chronologisch, indem die Daten aufsteigend nach Datum geordnet werden:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 NTILE(3) OVER (ORDER BY date) AS group_number
FROM exchange_rates;

Und hier ist das Ergebnis:

Die Daten werden in drei Gruppen aufgeteilt. Da die Daten insgesamt 27 Zeilen umfassen, könnten sie in gleiche Gruppen von neun Zeilen aufgeteilt werden.

Fensterfunktionen vs. GROUP BY und Aggregatfunktionen

Aggregat-Fensterfunktionen werden, wie ihr Name vermuten lässt, zum Aggregieren von Daten verwendet. Aber was ist mit "normalen" Aggregatfunktionen und GROUP BY? Auch sie werden zum Aggregieren von Daten verwendet. Worin unterscheiden sich also die Fensterfunktionen?

Der Hauptunterschied besteht darin, dass Aggregat-Fensterfunktionen (und Fensterfunktionen im Allgemeinen) einzelne Zeilen nicht einklappen, sondern den aggregierten Wert anzeigen. Andererseits können GROUP BY und Aggregatfunktionen nur die aggregierten Werte anzeigen; sie klappen einzelne Zeilen aus.

Mit einfachen Worten: Mit Fensterfunktionen können Sie die analytischen und aggregierten Daten gleichzeitig anzeigen.

Fortgeschrittenes SQL Verwendung von Fensterfunktionen

Fensterfunktionen werden in der Datenanalyse häufig verwendet, da sie viele Anforderungen an die Geschäftsberichterstattung erfüllen können.

Im Folgenden finden Sie einen Überblick über einige der komplexeren Verwendungen von Fensterfunktionen.

Rangliste

Ja, ich habe bereits über das Ranking gesprochen und Ihnen ein Beispiel dafür gezeigt, wie es funktioniert. Es gibt drei Fensterfunktionen für das Ranking von Daten: ROW_NUMBER(), RANK(), und DENSE_RANK(). Sie liefern nicht notwendigerweise das gleiche Ergebnis, da sie alle leicht unterschiedliche Methoden zur Einstufung von Daten haben. Welche Sie verwenden, hängt von Ihren Daten und den gewünschten Ergebnissen ab.

Laufende Summen und gleitende Durchschnitte

Diese beiden Berechnungen werden normalerweise in der Zeitreihenanalyse verwendet. Zeitreihen sind Daten, die Werte zu bestimmten Zeitpunkten anzeigen. Die Analyse solcher Daten ist, nun ja, die Zeitreihenanalyse. Ihr Zweck ist es, Trends in Daten aufzudecken und mögliche Ursachen für signifikante Abweichungen vom Trend zu finden.

Die laufende Summe (oder kumulative Summe) ist die Summe der Werte der aktuellen Zeile und aller vorangegangenen Zeilen. Wenn Sie sich in die Zukunft bewegen, erhöht sich die Größe des Zeitrahmens um eine Zeile/einen Datenpunkt, und der Wert wird zur laufenden Summe der vorherigen Zeile hinzugefügt.

Gleitende Durchschnitte sind der Durchschnittswert der letzten n Perioden. Wenn Sie sich in die Zukunft bewegen, verschiebt sich der Zeitrahmen - seine Größe bleibt jedoch gleich. Dies wird häufig in der Finanzbranche verwendet, z. B. ein gleitender 5-Tage-Durchschnitt in der Aktienkursanalyse. Auf diese Weise wird der Durchschnittspreis ständig aktualisiert, und die Auswirkungen erheblicher kurzfristiger Änderungen auf den Aktienkurs werden neutralisiert.

Differenz zwischen zwei Zeilen oder Zeitabschnitten

Die Fensterfunktion zur Berechnung der Differenz zwischen zwei Zeilen ist LAG(). Sie ermöglicht den Zugriff auf die Werte der vorhergehenden Zeilen. Die Differenz zwischen zwei Zeiträumen ist im Grunde dasselbe; sie bezieht sich nur auf die Ermittlung von Differenzen bei der Arbeit mit Zeitreihen. Im Abschnitt mit den Beispielen habe ich Ihnen gezeigt, wie man das macht.

Analysieren von Zeitreihen

Die Fensterfunktionen eignen sich hervorragend für die Analyse von Zeitreihen. Dafür gibt es nicht nur die Funktion LAG(), sondern viele andere.

Häufige Probleme mit SQL Fensterfunktionen

Es gibt mehrere Probleme, auf die jeder, der Fensterfunktionen verwendet, früher oder später stößt:

1. Die Verwechslung von Fensterfunktionen mit Aggregatfunktionen und GROUP BY, die wir bereits besprochen haben.
2. Der Versuch, Fensterfunktionen in WHERE zu verwenden. Dies ist nicht möglich, da SQL die WHERE-Bedingungen vor den Fensterfunktionen verarbeitet.
3. Der Versuch, Fensterfunktionen in GROUP BY zu verwenden, was aufgrund der Reihenfolge der Operationen in SQL ebenfalls nicht zulässig ist: Fensterfunktionen werden nach GROUP BY ausgeführt.

Zusätzliche Ressourcen zum Üben von SQL Fensterfunktionen

SQL-Fensterfunktionen gehören zu den nützlichsten SQL-Werkzeugen, die Datenanalysten zur Verfügung stehen. Dies gilt insbesondere dann, wenn Sie über einfache Berichte hinausgehen und anspruchsvolle Berechnungen sowie die Möglichkeit benötigen, analytische und aggregierte Daten gleichzeitig anzuzeigen.

Alle in diesem Artikel behandelten Themen erfordern weitere Übungen an praktischen Beispielen, die Sie in den folgenden Artikeln und Kursen finden können:

1. 11 SQL Fensterfunktionen Übungen mit Lösungen
2. Die 10 wichtigsten SQL Fensterfunktionen Interview-Fragen
3. SQL Fensterfunktionen Spickzettel
4. Fensterfunktionen Kurs
5. Fensterfunktionen Übungssatz

Denken Sie daran - Übung macht den Meister! Lesen Sie also nicht nur die Artikel, sondern üben Sie auch das Programmieren in der Praxis. Viel Spaß beim Lernen!