Wann verwende ich SQL-Fensterfunktionen?

Haben Sie schon einmal gehört, wie einer Ihrer Kollegen mit der Verwendung von SQL-Fensterfunktionen geprahlt hat? Kennen Sie die Grundlagen von SQL, aber nicht viel oder gar nichts über Fensterfunktionen? Wenn Sie etwas über Fensterfunktionen und ihre Verwendung in einem geschäftlichen Kontext erfahren möchten, sind Sie hier genau richtig!

Das kommt häufig vor. Sie haben einen bestimmten Wissensstand erreicht und fühlen sich wie der König (oder die Königin) der Welt. Dann hören Sie zufällig, wie Ihre Kollegen über etwas sprechen, von dem Sie noch nie gehört haben (in diesem Fall über Fensterfunktionen). Sofort schämen Sie sich ein wenig dafür, dass Sie die Fensterfunktionen noch nicht kennen. Kommt Ihnen das bekannt vor?

Dieses Gefühl ist nicht angenehm. Aber es kann auch etwas Gutes sein, wenn es Sie dazu bringt, sich zu fragen, was Fensterfunktionen sind. Sie googeln also danach und finden einige nützliche Artikel (wie diesen hier). Plötzlich verwandelt sich die Verlegenheit in Erleichterung, wenn Sie erkennen, dass Fensterfunktionen nicht geheimnisvoll oder nicht erlernbar sind. Wieder fühlen Sie sich wie der König der Welt. Oder Königin. Vielleicht sogar beides.

Lassen Sie uns diesem königlichen Gefühl näher kommen! Ich werde versuchen, Sie nicht mit SQL zu bombardieren. Stattdessen werde ich den umgekehrten Weg einschlagen. Ich werde mehrere Beispiele aus der Geschäftswelt anführen, die Ihnen die praktische Verwendung von Fensterfunktionen zeigen.

Was sind Fensterfunktionen?

Eine Fensterfunktion ist, einfach ausgedrückt, eine Funktion, die Berechnungen in einer Reihe von Tabellenzeilen durchführt. Der Name leitet sich von der Tatsache ab, dass der Satz von Zeilen als Fenster oder Fensterrahmen bezeichnet wird.

Hier ist ein Beispiel dafür, wie ein Fenster aussieht. In der folgenden Tabelle sind die Fenster durch unterschiedliche Farben gekennzeichnet. Die kumulative Summe wird für jede Region berechnet, so dass in diesem Fall die Fenster nach Regionen definiert sind.

Die Syntax der Fensterfunktion lautet:

window_function ([ALL] expression)
OVER ([PARTITION BY partition_list] [ORDER BY order_list] [window_frame_clause])

Der Fensterfunktionsteil der Syntax kann eine Aggregatfunktion sein, z. B. SUM() oder AVG(). Es kann sich auch um eine andere Art von Funktion handeln, z. B. eine analytische Funktion oder eine Ranking-Funktion.

Das Fenster bzw. die Anzahl der Zeilen, über die bestimmte Berechnungen durchgeführt werden, wird durch die Klausel OVER() definiert.

PARTITION BY ist eine optionale Klausel in Fensterfunktionen. Sie definiert eine Partition, auf der die Fensterfunktion die Operation durchführen wird. Wenn PARTITION BY nicht definiert ist, führt die Window-Funktion die Operation an der gesamten Tabelle durch. Mehr dazu erfahren Sie in diesem Artikel.

ORDER BY sortiert die Zeilen innerhalb jeder Partition in der gewünschten Reihenfolge. Wenn diese Klausel nicht definiert ist, wird die Klausel die gesamte Tabelle verwenden.

Die Fensterrahmenklausel definiert die Anzahl der Zeilen, auf die die Fensterfunktion angewendet wird, mit Hilfe von zwei Schlüsselwörtern. Das eine ist ROWS, mit dem die Anzahl der Zeilen begrenzt wird, indem die Anzahl der Zeilen vor oder nach der aktuellen Zeile angegeben wird. Das andere ist RANGE, mit dem die Anzahl der Zeilen durch die Angabe eines Wertebereichs in Bezug auf den aktuellen Zeilenwert begrenzt wird. Aus diesem Grund wird die Fensterrahmenklausel auch ROW oder RANGE genannt.

Um die Verwendung dieser Syntax zu erlernen und zu üben, ist der KursLearnSQL.de über Fensterfunktionen die richtige Adresse. Wenn Sie an weiteren Erklärungen zu Fensterfunktionen interessiert sind, finden Sie diese hier, zusammen mit einigen Beispielen.

Kommen wir nun zum spaßigen Teil, dem Lösen von Problemen!

Beispiel 1: Berechnung des Durchschnittsgehalts und Vergleich von Individuell mit dem Durchschnittsgehalt

Dies ist ein einfaches Beispiel für ein häufiges Problem in der Geschäftswelt. Nehmen wir an, es gibt eine Tabelle employee, die Daten über die Gehälter der Mitarbeiter enthält. Sie besteht aus den folgenden Zeilen:

• id - eindeutige ID
• first_name - Vorname des Mitarbeiters
• last_name - Nachname des Mitarbeiters
• department - Abteilung des Mitarbeiters
• salary - Monatsgehalt des Mitarbeiters

Anhand dieser Daten müssen Sie zunächst das Durchschnittsgehalt für das gesamte Unternehmen berechnen. Dann müssen Sie berechnen, um wie viel das Gehalt jedes einzelnen Mitarbeiters über oder unter dem Durchschnittsgehalt liegt. Mit dem folgenden Code ist das schnell erledigt:

SELECT	first_name,
		last_name,
		department,
		salary,
		AVG(salary) OVER() AS avg_salary,
		salary - AVG(salary) OVER() AS diff_salary
FROM employee;

Da Sie mit SQL vertraut sind, erkennen Sie wahrscheinlich zumindest einen Teil dieses Codes. Er wählt first_name, last_name, department, und salary aus der Tabelle employee. Das ist nichts Neues.

In der Zeile AVG(salary) OVER() AS avg_salary wird das Durchschnittsgehalt in dem durch OVER() definierten Fenster berechnet. Das Ergebnis wird in der Spalte avg_salary angezeigt.

Die Zeile salary - AVG(salary) OVER() AS diff_salary berechnet die Differenz zwischen dem Gehalt der einzelnen Mitarbeiter und dem Durchschnittsgehalt. Das Ergebnis wird in der Spalte diff_salary angezeigt.

Wenn Sie diesen Code ausführen, erhalten Sie eine hilfreiche Tabelle. Nachfolgend sehen Sie einen Auszug daraus:

Beispiel 2: Berechnung der durchschnittlichen Anzahl der verkauften Produkte nach Datum und Region

Stellen Sie sich vor, Sie arbeiten in einem Unternehmen, das in drei Regionen tätig ist. Die Geschäftsleitung möchte die durchschnittliche Anzahl der in jeder Region verkauften Produkte wissen. Außerdem möchte sie die durchschnittliche Anzahl der verkauften Produkte für jedes Datum wissen. Mit den Fensterfunktionen können Sie das ganz einfach tun.

In diesem Beispiel werden die Daten in der Tabelle sales gespeichert, die drei Spalten hat:

• date - Datum des Produktverkaufs
• region - Name der Region
• products_sold - Anzahl der verkauften Produkte

Hier ist der Code, der für die Erstellung dieses Berichts benötigt wird:

SELECT	date,
		region,
		products_sold,
		AVG(products_sold) OVER(PARTITION BY date) AS avg_date,
		AVG(products_sold) OVER(PARTITION BY region) AS avg_region
FROM sales
ORDER BY region, date;

Dieser Code wählt die Spalten date, region und products_sold aus. Dann wird die durchschnittliche Anzahl der verkauften Produkte an jedem Datum berechnet. Dies wird durch die Klausel PARTITION BY() definiert. Das Ergebnis wird in der Spalte avg_date angezeigt.

In der nächsten Zeile wird ebenfalls die durchschnittliche Anzahl der verkauften Produkte berechnet, dieses Mal in jeder Region. Das Ergebnis wird in der Spalte avg_region angezeigt. Anschließend wird das Ergebnis mit Hilfe der Klausel ORDER BY nach Region und Datum geordnet.

Sehen Sie die Ergebnisse unten:

Beispiel 3: Berechnen der kumulativen Summe der verkauften Produkte nach Region

Die Geschäftsleitung war mit Ihrem vorherigen Bericht zufrieden! Jetzt sollen Sie die kumulierte Summe (oder die laufende Summe) der in jeder Region verkauften Produkte berechnen. Fensterfunktionen sind für solche Berechnungen nützlich.

Die Berechnung wird mit der Tabelle sales die in Beispiel 2 verwendet wurde. Mit diesem Code können Sie schnell die gewünschten Zahlen liefern:

SELECT	date,
		region,
		products_sold,
		SUM(products_sold) OVER(PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_region
FROM sales
ORDER BY region, date;

Der erste Teil des Codes wählt die gleichen Spalten aus wie der Code in Beispiel 2. Dann wird die Summe der Produkte nach Region berechnet. Dies wird durch die Klausel PARTITION BY() definiert. Natürlich brauchen Sie die kumulierte Summe, nicht die Gesamtsumme. Deshalb wird das Fenster mit dem Befehl ORDER BY nach dem Datum sortiert.

Da nun alles definiert ist, müssen Sie SQL anweisen, den Wert der aktuellen Zeile zur Summe der vorherigen Zeilen innerhalb des Fensters zu addieren. Dies geschieht mit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Die Fensterfunktion summiert alle Zeilen vor der aktuellen Zeile (deshalb UNBOUNDED PRECEDING) und keine Zeilen nach der aktuellen Zeile (deshalb CURRENT ROW) innerhalb des angegebenen Fensters. Das Ergebnis wird in der Spalte cumulative_region angezeigt.

Das Ergebnis wird wie im vorherigen Beispiel nach Region und Datum geordnet.

Nachdem Sie den Code ausgeführt haben, erhalten Sie die unten stehende Tabelle:

Beispiel 4: Vergleich von Aktienkursen mit Mindest- und Höchstkursen auf dem Markt

Ihr Unternehmen überwacht die Aktienkurse an der NASDAQ und der NYSE. Die Geschäftsleitung möchte, dass Sie die minimalen und maximalen Aktienkurse an beiden Märkten in den letzten 365 Tagen berechnen. Außerdem soll ermittelt werden, wie sich der Preis jeder im Jahr 2020 gehandelten Aktie vom Mindest- und Höchstpreis auf dem Markt unterscheidet.

Die Tabelle stockprice enthält Daten vom 15.3.2019 bis 14.3.2020 und besteht aus den folgenden Spalten:

• date - Datum des Handels
• stock_name - Name der Aktie
• stock_price - Kurs der Aktie
• stock_market - Markt, an dem die Aktie gehandelt wird
• max_price - Höchstkurs an der Börse in den letzten 365 Tagen
• min_price - Mindestkurs an der Börse in den letzten 365 Tagen
• diff_max - Aktienkurs abzüglich des Höchstkurses an der Börse in den letzten 365 Tagen
• diff_min - Aktienkurs abzüglich des Mindestkurses auf dem Markt in den letzten 365 Tagen

Der Code, mit dem sich der gewünschte Bericht schnell erstellen lässt, sieht wie folgt aus:

SELECT	date,
		stock_name,
		stock_price,
		stock_market,
		MAX(stock_price) OVER(PARTITION BY stock_market) as max_price,
		MIN(stock_price) OVER(PARTITION BY stock_market) as min_price,
		stock_price - MAX(stock_price) OVER(PARTITION BY stock_market) AS diff_max,
		stock_price - MIN(stock_price) OVER(PARTITION BY stock_market) AS diff_min
FROM stockprice
WHERE date > '2019-12-31'
ORDER BY date;

Der erste Teil des Codes wählt die ursprünglichen Spalten in der Tabelle aus: date, stock_name, stock_price, und stock_market. Dann berechnet die Fensterfunktion MAX() mit der Klausel PARTITION BY den Höchstpreis für jeden Markt, NASDAQ und NYSE, separat. Das Ergebnis wird in der Spalte max_price angezeigt.

Die nächste Codezeile funktioniert auf die gleiche Weise, nur dass jetzt der Mindestpreis berechnet wird. Das Ergebnis wird in der Spalte min_price angezeigt.

Die folgenden Codezeilen berechnen die Differenz zwischen dem Aktienkurs und dem Höchst- bzw. Mindestkurs für jeden Markt. Die Ergebnisse werden in den Spalten diff_max und diff_min angezeigt.

Da der Bericht nur Daten aus dem Jahr 2020 anzeigen soll, habe ich die Klausel WHERE verwendet. Schließlich ist die resultierende Tabelle nach dem Datum geordnet, was für einen solchen Bericht logisch ist.

So sehen die ersten Zeilen des Berichts aus:

Beispiel 5: Berechnung der prozentualen Preisänderung und der gleitenden Durchschnitte

Die Geschäftsleitung weiß nun, dass Sie Berichte mit großer Genauigkeit und Geschwindigkeit erstellen können. Sie sind beeindruckt! Sie wissen nicht, dass Sie sich mit den Funktionen des SQL-Fensters vertraut gemacht haben und dass das, was sie verlangt haben, für Sie ein Kinderspiel ist.

Als Nächstes werden Sie nach etwas gefragt, das viel mehr Zeit in Anspruch nehmen sollte. Aber das wird es nicht! Sie haben eine Tabelle Preis, die alle Veränderungen price Änderungen einer Aktie im Jahr 2020 enthält. Manchmal gibt es nur eine Veränderung pro Tag, manchmal sind es mehrere. Die Tabelle besteht aus den folgenden Spalten:

• date - Datum des Kurses
• stock_price - Preis der Aktie

Die Geschäftsleitung hat Sie gebeten, ihr einen Bericht zu schicken, der jeden Kurs mit dem vorherigen vergleicht. Außerdem sollst du den gleitenden Durchschnitt des Aktienkurses berechnen. So werden Sie es machen:

SELECT	date,
		stock_price,
		(stock_price/LAG(stock_price) OVER(ORDER BY date)) - 1 AS percent_change,
AVG(stock_price) OVER(ORDER BY date ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING) AS moving_avg
FROM price;

Was macht dieser Code? Zunächst werden die ursprünglichen Spalten in der Tabelle ausgewählt: date und stock_price.

Die nächste Zeile führt eine neue Fensterfunktion ein: LAG(). Diese Funktion greift auf die Daten der vorherigen Zeile zu, was für diese Aufgabe ideal ist. stock_price wird durch den vorherigen Preis geteilt (daher die Funktion LAG() ). Dann wird 1 vom Ergebnis abgezogen, um einen Prozentsatz zu erhalten. Das Ergebnis wird in der Spalte percent_change angezeigt.

In der folgenden Zeile wird der gleitende Durchschnitt berechnet. Sie verwendet die Funktion AVG() window, die Sie bereits kennen. In der Klausel OVER() werden die Daten nach dem Datum geordnet.

Die Verwaltung hat nicht angegeben, wie der gleitende Durchschnitt berechnet werden soll. Daher habe ich beschlossen, ihn anhand von fünf Preisänderungen zu berechnen, was in ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING angegeben ist. Bei der Berechnung des gleitenden Durchschnitts berücksichtigt der Code die aktuelle Zeile und die vier darauf folgenden Zeilen, also insgesamt fünf.

Sie können mit diesem Teil des Codes spielen, da es keine festgelegte Anzahl von Daten gibt, die bei der Berechnung von gleitenden Durchschnitten berücksichtigt werden sollten. Das hängt von der Menge und der Art der Daten sowie von den persönlichen Vorlieben ab. Durch Ändern der Zahlen, die vor PRECEDING und FOLLOWING stehen, können Sie die Berechnung einfach und schnell ändern, je nachdem, welche Methode Sie verwenden möchten.

Sehen Sie sich die Ergebnisse unten an:

Glauben Sie, dass das Lernen von Fensterfunktionen Ihnen bei Ihrer Arbeit helfen kann?

Anstelle einer reinen Analyse des Codes von Fensterfunktionen habe ich Beispiele aus der Geschäftswelt als Ausgangspunkt gewählt. Alle fünf Beispiele sind etwas, womit ich in meinem Beruf zu tun hatte.

Auf diese Weise habe ich SQL gelernt. Zuerst hatte ich ein Problem zu lösen. Dann habe ich versucht, herauszufinden, wie man es mit SQL lösen kann. Wenn Sie glauben, dass Fensterfunktionen für Ihre Arbeit nützlich sind, ist dieser LearnSQL.de Kurs eine gute Möglichkeit, mehr zu lernen.

Wenn Sie diese Beispiele interessant fanden oder uns Ihre eigenen Beispiele aus der Praxis mitteilen möchten, können Sie gerne unten einen Kommentar abgeben!