Was sind SQL-Fensterfunktionen?

Wollen Sie Ihre Datenanalyse verbessern? Lernen Sie diese SQL-Fensterfunktionen hier und Sie werden Ihre Analysefähigkeiten auf die nächste Stufe bringen. Inklusive praktischer Übungen und detaillierter Erklärungen!

Als ich das erste Mal von den SQL-Fensterfunktionen hörte, dachte ich, es handele sich um eine seltsame Verbindung zwischen SQL und Windows. Ich habe mich geirrt. Die SQL-Fensterfunktionen haben nichts mit diesem berühmten Betriebssystem zu tun. Es handelt sich um SQL-Funktionen, die Berechnungen mit einer Reihe von Zeilen durchführen, die mit der aktuellen Zeile zusammenhängen. Dieser Satz von Zeilen wird als Fenster oder Fensterrahmen bezeichnet - daher der Name der Funktion.

Man spricht auch von Windowing-Funktionen in SQL, analytischen Funktionen oder den OVER()-Funktionen. Dies sind alles nur alternative Bezeichnungen für SQL-Fensterfunktionen - ein äußerst hilfreiches Instrumentarium für die Datenanalyse.

In diesem Artikel zeigen wir Ihnen, was Sie mit Fensterfunktionen tun können und wie. Ich beginne mit einer kurzen Geschichte der SQL-Fensterfunktionen und erkläre, warum sie so genannt werden. Dann führe ich Sie durch die Syntax und zeige Ihnen anhand einiger Beispiele, wie sie funktioniert. Nachdem wir die Syntax geübt haben, sind wir bereit für reale Beispiele von Fensterfunktionen aus dem Leben eines Datenanalysten. Und jetzt kommt der interessanteste Teil: Nach jedem Beispiel gibt es eine Übung, die Sie lösen und durch Programmieren lernen können.

Die Hauptquelle für Ihr Wissen zu diesem Thema sollte jedoch unser Fensterfunktionen Kurs sein. In den 218 interaktiven Übungen werden die Funktionen des SQL-Fensters im Detail behandelt. Mit anderen Worten: Sie lernen etwas über Fensterrahmen und die Klauseln OVER(), PARTITION BY und ORDER BY. All dies ist notwendig, um Daten mithilfe von Fensterfunktionen zu aggregieren, zu ordnen und zu analysieren.

Eine kurze Geschichte von SQL Fensterfunktionen

Fensterfunktionen wurden erstmals in der Oracle8i-Datenbank eingeführt, die 1998 veröffentlicht wurde. Sie wurden jedoch erst fünf Jahre später mit SQL:2003 in den SQL-Standard aufgenommen.

Dann nahm Microsoft sie in SQL Server 2005 auf. Andere Datenbankmanagementsysteme (DBMS) folgten; PostgreSQL unterstützt sie seit der Veröffentlichung von PostgreSQL 8.4 im Jahr 2009; MariaDB nahm sie mit der Version 10.2 (2016) auf, und MySQL fügte sie 2018 der Version 8 hinzu.

Fensterfunktionen sind eine relativ neue Funktion in SQL. Aus diesem Grund sind sie nicht Teil des üblichen SQL-Lehrplans. Wenn Sie sie lernen, haben Sie im Vergleich zu vielen SQL-Benutzern einen Vorsprung.

Was ist ein Fenster?

Eine Gruppe von Zeilen, die sich auf die aktuelle Zeile bezieht, wird als Fenster oder Fensterrahmen bezeichnet. Daher auch der Name dieser Funktionen: Ihr Ergebnis basiert auf einem gleitenden Fensterrahmen.

Sie können zum Beispiel eine kumulative Summe berechnen, wie unten gezeigt:

Das Fenster für die kumulative Summe 2023-10-04 ist grün hervorgehoben. Es umfasst die aktuelle Zeile (für 2023-10-04) und alle vorherigen Zeilen. Die kumulative Summe wird also als Summe aller vorherigen und aktuellen Verkäufe berechnet: 4.241 + 2.389 + 1.580 + 3.395 = 11.605. (Beachten Sie, dass die mit roten Punkten umrandete Zeile weder im Fenster noch in der Summe enthalten ist).

Wenn wir zur nächsten Zeile wechseln, verschiebt sich auch das Fenster: Es umfasst nun alle vorherigen Zeilen (grün) und die aktuelle Zeile (rot gepunktet). Die kumulative Summe beträgt nun 4.241 + 2.389 + 1.580 + 3.395 + 1.265 = 12.870.

Das Fenster ist also die Menge der Zeilen, die mit der aktuellen Zeile zusammenhängen und die für die Berechnungen dieser Zeile verwendet werden. Das Fenster ändert sich (gleitet), wenn wir uns durch die Zeilen bewegen; dank dieser Bilder eines gleitenden Fensters erhalten wir den Namen dieser Funktionen.

Syntax für SQL-Fensterfunktionen

Die Syntax für Fensterfunktionen lautet:

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

Die einzelnen Teile haben folgende Bedeutung:

• <window_function> - Gibt die Funktion an, die auf dieses Fenster angewendet werden soll.
• OVER() - Definiert das Fenster (eine Reihe von Zeilen) und gibt an, dass es sich um eine Fensterfunktion handelt; ohne diese Klausel handelt es sich nicht um eine Fensterfunktion.
• <window_frame> - Definiert die Größe des Fensterrahmens (optional).
• PARTITION BY - Unterteilt das Fenster in kleinere Gruppen, die Partitionen genannt werden (optional); Wenn die Klausel weggelassen wird, ist die gesamte Ergebnismenge eine einzige Partition.
• ORDER BY - Sortiert die Zeilen innerhalb des Fensterrahmens (optional), d.h. entscheidet, in welcher Reihenfolge die Fensteroperation durchgeführt wird; Wenn diese Klausel weggelassen wird, ist die Reihenfolge der Zeilen innerhalb der Partition willkürlich.

Zusätzliche Klauseln können das Fenster weiter definieren. Ihre Syntax lautet:

[<ROWS or RANGE clause> BETWEEN <lower_bound> AND <upper_bound>]

Die Klausel ROWS definiert das Fenster in Form einer festen Anzahl von Zeilen in Bezug auf die aktuelle Zeile.

Die Klausel RANGE tut dasselbe. Sie berücksichtigt jedoch auch alle Zeilen, die in den in der Klausel ORDER BY angegebenen Spalten dieselben Werte aufweisen wie die aktuelle Zeile.

Die Fenstergrenzen können wie folgt definiert werden

• UNBOUNDED PRECEDING - Alle Zeilen vor der aktuellen Zeile.
• n PRECEDING - Eine bestimmte Anzahl von Zeilen vor der aktuellen Zeile.
• CURRENT ROW - Einschließlich der aktuellen Zeile.
• n FOLLOWING - Eine definierte Anzahl von Zeilen nach der aktuellen Zeile.
• UNBOUNDED FOLLOWING - Alle Zeilen nach der aktuellen Zeile.

Schauen wir uns nun an, wie dies in der Praxis funktioniert.

Beispiele für Datensatz und Syntax

Wir verwenden die Tabelle album_catalogue in all diesen Beispielen. Sie können sie mit diesem Skript selbst erstellen. Unten sehen Sie einen Schnappschuss der Daten:

Der Datensatz ist eine Liste von Alben mit Länge, Genre, Künstler und Verkaufsdaten, einschließlich der Anzahl der verkauften Exemplare und des Zeitraums (Quartale). Die Daten reichen bis zum dritten Quartal 2023.

Ich zeige Ihnen zunächst einige Beispiele und erkläre dabei jeden wichtigen Teil der Syntax der Fensterfunktionen.

Syntaxbeispiel 1: OVER ()

Sie können die Fensterfunktion SUM() nur mit der Klausel OVER() verwenden, um die Gesamtverkäufe im vierten Quartal 2022 zu ermitteln:

SELECT sales_period,
 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER() AS sold_in_4Q_2022
FROM album_catalogue
WHERE sales_period = '2022_4Q';

Ich möchte die Summe der verkauften Exemplare, also gebe ich diese Spalte in SUM() an. Die Klausel OVER() ist obligatorisch. Wenn Sie OVER() ohne eine der optionalen Klauseln verwenden möchten, lassen Sie die Klammern einfach leer.

Ich verwende WHERE, um nur Daten aus dem gewünschten Quartal auszugeben.

Wenn Sie eine Abfrage wie diese - mit einer leeren OVER() - schreiben, wird der gesamte Ergebnissatz (ausgewählte Spalten, angewandte Filter usw.) bei der Durchführung der Berechnungen der Fensterfunktion berücksichtigt. Hier zeigt das Ergebnis die einzelnen Verkäufe jedes Albums, das im vierten Quartal 2022 verkauft wurde. Es zeigt auch die Gesamtverkäufe aller in diesem Zeitraum verkauften Alben an.

Mit Hilfe von SUM() und OVER() kann ich die Verkäufe jedes einzelnen Albums und die vierteljährliche Gesamtsumme anzeigen.

Syntaxbeispiel Nr. 2: OVER (ORDER BY)

Sie können zusätzliche Klauseln innerhalb der OVER() Klausel hinzufügen, um die Definition des Fensterrahmens zu ändern. Eine solche Klausel ist ORDER BY. Die Klausel ORDER BY definiert die Sortierung der Zeilen innerhalb eines Fensterrahmens: Die Zeilen können von der Fensterfunktion in einer bestimmten Reihenfolge verarbeitet werden.

Sehen wir uns ein Beispiel an. Sie können die kumulierte Summe berechnen, indem Sie ORDER BY zur vorherigen Abfrage hinzufügen. In diesem Beispiel möchte ich sehen, wie sich das Album "In the Right Place" im Laufe der Zeit verkauft und die kumulierte Anzahl der verkauften Alben bis zu einem bestimmten Zeitraum. Hier ist die Abfrage:

SELECT sales_period,
	 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER(ORDER BY sales_period ASC) AS cumulative_sum
FROM album_catalogue
WHERE album_title = 'In the Right Place';

Die gleiche Spalte copies_sold befindet sich in SUM(). Diesmal enthält OVER() die Klausel ORDER BY. Sie möchten die kumulierten Verkäufe vom frühesten bis zum letzten Quartal anzeigen. Deshalb brauchen Sie sales_period und ASC in ORDER BY.

In jeder Zeile sehen Sie die Verkäufe für jedes Quartal und die kumulative Summe, d. h. die Summe aus dem aktuellen und allen vorherigen Quartalen. So wurde das Album beispielsweise im dritten Quartal 2022 94 Mal verkauft. Die Gesamtverkäufe im Jahr 2022 bis dahin (oder in drei Quartalen) sind: 222 + 208 + 94 = 524.

Syntaxbeispiel Nr. 3: OVER (PARTITION BY)

Eine weitere Klausel, die Sie in OVER() verwenden können, ist PARTITION BY. PARTITION BY wird verwendet, um das Fenster anhand bestimmter Kriterien in kleinere Segmente zu unterteilen. Sie können zum Beispiel die Alben, ihre Verkaufsdaten für das vierte Quartal 2022 und die Verkäufe nach Genre für dieses Quartal auflisten:

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 SUM (copies_sold) OVER(PARTITION BY album_genre) AS sales_by_genre
FROM album_catalogue
WHERE sales_period = '2022_4Q';

Auch hier verwenden wir die gleiche Fensterfunktion SUM(). Diesmal verwenden wir jedoch PARTITION BY, um das Fenster in kleinere Segmente auf der Grundlage des Albumgenres zu unterteilen. Alles andere bleibt gleich.

Die Abfrage liefert das unten stehende Ergebnis. Es handelt sich um eine Analyse der Albumverkaufsdaten nach Genre für das letzte Quartal 2022.

Die kumulative Summe für die Folk-Alben beträgt zum Beispiel 809 + 246 + 400 + 214 + 412 = 2.081.

Syntaxbeispiel Nr. 4: OVER (ORDER BY PARTITION BY)

Sie können auch sowohl PARTITION BY als auch ORDER BY in OVER() verwenden. Die Zeilen werden mit PARTITION BY in Segmente unterteilt und mit ORDER BY in einer bestimmten Reihenfolge verarbeitet.

Mit der folgenden Abfrage kann ich alle analytischen Daten der Alben anzeigen und die kumulative Summe für jedes Album separat berechnen:

SELECT sales_period,
	 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER(PARTITION BY album_title ORDER BY sales_period ASC) AS cumulative_sum_by_album
FROM album_catalogue;

Ich berechne dies mit Hilfe der Funktion SUM() window, wie ich es zuvor getan habe. Ich habe das Fenster nach Album unterteilt. Das bedeutet, dass die Summe kumuliert wird, bis die Funktion die letzte Zeile eines bestimmten Albums erreicht. Wenn sie ein anderes Album erreicht, wird sie zurückgesetzt und beginnt mit der Kumulierung der Summe von Anfang an.

Ich verwende auch ORDER BY, um die Funktion anzuweisen, die Summe vom frühesten bis zum letzten Quartal zu kumulieren.

Sie können sehen, dass die kumulative Summe für "A Whole New Thing" 3.654 beträgt. Das nächste Album ("Blue") beginnt mit der nächsten Zeile, so dass die Kumulierung zurückgesetzt wird: Die kumulierte Summe entspricht den Einzelverkäufen des Albums im ersten Quartal 2022. Dann wird sie bis zum nächsten Album kumuliert. Das Ergebnis geht bis zum letzten Album, das in unserem Fall "Wednesday Morning, 3 A.M." ist.

Mit diesen Beispielen habe ich Ihnen die gängigsten Möglichkeiten zur Definition von Fensterrahmen gezeigt. Aber das sind nicht die einzigen Möglichkeiten. Sie können auch die Klauseln ROW oder RANGE mit der Syntax und den Begrenzungen verwenden, die wir bereits erläutert haben. Machen Sie sich keine Sorgen. Sie werden die praktische Anwendung in realen Beispielen sehen.

Ich habe in diesen Beispielen nur eine Funktion, SUM(), verwendet. Dies ist eine von vielen Fensterfunktionen; lassen Sie uns kurz auf einige der anderen eingehen.

Was sind die gebräuchlichsten Fensterfunktionen?

Die gebräuchlichsten Fensterfunktionen lassen sich in drei Kategorien einteilen:

1. Aggregieren Fensterfunktionen:

• COUNT() - Zählt die Anzahl der Zeilen innerhalb eines Fensters.
• SUM() - Summiert gegebene Werte innerhalb eines Fensters.
• AVG() - Berechnet den Durchschnitt von gegebenen Werten innerhalb eines Fensters.
• MIN() - Findet den kleinsten Wert innerhalb eines Fensters.
• MAX() - Findet den größten Wert innerhalb eines Fensters.

2. Rangliste Fensterfunktionen:

• ROW_NUMBER() - Reiht die Werte nacheinander ein, mit unterschiedlichen Rängen für die gleichrangigen Werte.
• RANK() - Reiht Werte mit demselben Rang für gebundene Werte; überspringt den nächsten Rang nach den gebundenen Werten (z. B. 1, 2, 2, 4).
• DENSE_RANK() - Reiht Werte mit demselben Rang für gebundene Werte; überspringt nicht den nächsten Rang nach den Gleichheiten (z. B. 1,2,2,3,4).

3. Analytisch Fensterfunktionen:

• LEAD() - Ruft Daten ab einem definierten Offset (d. h. einer bestimmten Anzahl von Zeilen) nach der aktuellen Zeile ab.
• LAG() - Ruft Daten ab einem bestimmten Offset (d. h. einer bestimmten Anzahl von Zeilen) vor der aktuellen Zeile ab.

Es gibt noch mehr Fensterfunktionen, die Sie nützlich finden könnten. Sehen Sie sich diese in unserem kostenlosen SQL Fensterfunktionen Cheat Sheet an .

Beispiele für SQL-Fensterfunktionen in der realen Welt

Bis jetzt habe ich mich mehr auf die Syntax der SQL-Fensterfunktionen konzentriert. Jetzt zeige ich Ihnen die häufigsten praktischen Anwendungen von Fensterfunktionen und wie sie Datenanalysten bei ihrer Arbeit helfen können.

Für diese Beispiele wird derselbe Datensatz wie zuvor verwendet.

Beispiel Nr. 1: Prozentualer Anteil am Gesamtumsatz

Wir zeigen Informationen über jedes Album und seine Verkäufe im ersten Quartal 2023. Zusätzlich zeigen wir die vierteljährlichen Verkäufe für jedes Genre. Dann wollen wir berechnen, wie viel jedes Album (in Prozent) zu den Genreverkäufen beiträgt.

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 SUM(copies_sold) OVER (PARTITION BY album_genre) AS sales_by_genre,
	 (copies_sold*1.0/ SUM(copies_sold) OVER (PARTITION BY album_genre))*100.0 AS percent_of_genre_sales
FROM album_catalogue
WHERE sales_period = '2023_1Q'
ORDER BY album_genre, copies_sold DESC;

Um die Verkäufe nach Genre zu ermitteln, verwende ich wieder die Funktion SUM() window. In der Klausel OVER() verwende ich nur PARTITION BY. Auf diese Weise kann ich das Fenster nach dem Genre des Albums unterteilen.

In der nächsten Codezeile teile ich die verkauften Exemplare (jedes Albums) und dividiere sie durch die Genreverkäufe. Dazu kopiere ich einfach die Berechnung aus der vorherigen Zeile. Anschließend multiplizieren Sie den Quotienten mit 100, um den Prozentsatz zu erhalten. Sie werden feststellen, dass ich auch copies_sold mit 1,0 multipliziert habe. Dies dient der Umwandlung von Ganzzahlen in Dezimalwerte.

Filtern Sie das gewünschte Quartal mit WHERE. Schließlich ordnen Sie die Ausgabe alphabetisch nach Genre und dann absteigend nach verkauften Exemplaren.

Hier ist das Ergebnis:

Überprüfen wir die Berechnung für die erste Zeile. Von dem Album von Simon & Garfunkel wurden 809 Exemplare verkauft. Die Gesamtzahl der verkauften Folk-Alben in diesem Quartal betrug 2.081. Der prozentuale Anteil der Einzelverkäufe an den Gesamtverkäufen des Genres beträgt also 809/2.081*100 = 38,88 %.

Die Summe der Prozentsätze für jedes Genre sollte 100% betragen. Überprüfen wir dies an einem Folk-Genre: 38,88% + 19,80% + 19,22% + 11,82% + 10,28 % = 100%.

Fensterfunktion vs. Aggregatfunktion vs. GROUP BY

Ich verwende wieder die Aggregatfunktion als Fensterfunktion. Ich hätte auch eine einfache Aggregatfunktion SUM() mit GROUP BY verwenden können, um die Verkäufe für jedes Genre für das angegebene Quartal zu erhalten. Worin besteht also der Unterschied?

Mit einer Fensterfunktion können Sie sowohl analytische als auch aggregierte Daten anzeigen (Einzelverkäufe mit Verkäufen nach Genre und dem Quotienten dieser Werte), während eine Aggregatfunktion, die mit GROUP BY verwendet wird, die einzelnen Zeilen ausblenden und nur den Gesamtwert (die Summe der Verkäufe für das Quartal) anzeigen würde.

Lösen Sie diese Übung zur Übung

Schreiben Sie die obige Abfrage mithilfe von Fensterfunktionen so um, dass sie den durchschnittlichen Verkauf nach Genre anzeigt. Zeigen Sie außerdem, um wie viel die Verkäufe jedes Albums über oder unter dem Genre-Durchschnitt liegen (in Prozent). Zeigen Sie nur Verkäufe aus dem dritten Quartal 2023 an. Zeigen Sie den Albumtitel, den Künstler, die verkauften Exemplare und das Genre des Albums an. Sortieren Sie die Ausgabe aufsteigend nach Genre und einzelnen Albumverkäufen.

Lösung:

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 AVG(copies_sold) OVER (PARTITION BY album_genre) AS average_sales_by_genre,
	   ((copies_sold/AVG(copies_sold) OVER (PARTITION BY album_genre))-1)*100 AS pct_from_average	   
FROM album_catalogue
WHERE sales_period = '2023_3Q'
ORDER BY album_genre, copies_sold;

Ausgabe:

Beispiel #2: Rangdaten

In diesem Beispiel verwende ich eine Fensterfunktion, um Daten zu ordnen. Ich möchte jeden einzelnen Albumtitel und seine Länge anzeigen und sie nach Länge ordnen. Das längste Album wird als erstes angezeigt.

SELECT *, 
	 RANK() OVER (ORDER BY album_length DESC) AS album_length_rank
FROM (SELECT DISTINCT album_title,
	       album_length
	FROM album_catalogue) AS distinct_album;

Erläutern wir zunächst die Unterabfrage: Wir verwenden sie, um eindeutige Alben und deren Länge auszuwählen.

Dann verwenden wir die Hauptabfrage, um alle Daten aus der Unterabfrage auszuwählen. Verwenden Sie nun die Funktion RANK() window, um eine Rangliste der Alben zu erstellen. Sie können auch andere Ranking-Funktionen verwenden, je nach Ihren Daten und Aufgaben.

Damit das Ranking so funktioniert, wie Sie es wünschen, verwenden Sie die Klausel ORDER BY in OVER(). Geben Sie die Spalte an, nach der Sie die Rangliste erstellen möchten und in welcher Reihenfolge. In diesem Fall ist es absteigend nach Länge.

Hier ist die Rangfolge:

Lösen Sie diese Übung zum Üben

Ordnen Sie jedes einzelne Album nach seinen Verkaufszahlen innerhalb seines Genres. Zeigen Sie nur Daten für das erste Quartal 2023. Zeigen Sie den Albumtitel, die Verkaufszahlen, das Genre und den Rang an. Wenn es Alben mit der gleichen Anzahl an Verkäufen gibt, ordne sie gleich an und überspringe nicht den nächsten Rang.

Lösung:

SELECT *, 
	 DENSE_RANK() OVER (PARTITION BY album_genre ORDER BY copies_sold DESC) AS album_sales_rank
FROM (SELECT DISTINCT album_title,
	       copies_sold,
	       album_genre
	FROM album_catalogue
	WHERE sales_period = '2023_1Q') AS distinct_album;

Ausgabe:

Beispiel #3: Laufende Summe

In diesem Beispiel zeige ich den Verkaufszeitraum eines bestimmten Albums, den Titel, den Künstler und die verkauften Exemplare an. Außerdem füge ich eine laufende Summe der verkauften Exemplare hinzu, die drei Zeilen umfasst: die aktuelle Zeile und die beiden vorherigen. Die Summe soll vom frühesten bis zum letzten Quartal berechnet werden.

SELECT sales_period, 
	 album_title,
	 artist,
	 copies_sold,
	 SUM(copies_sold) OVER (ORDER BY sales_period ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sales_running_total
FROM album_catalogue
WHERE album_title = 'In a Silent Way';

Ich verwende wieder die Funktion SUM() window. Dann gibt es eine ORDER BY in OVER(), um die Verkäufe so zu ordnen, dass wir sie in aufsteigender Reihenfolge summieren können.

Als nächstes muss ich den Rahmen des gleitenden Fensters definieren. Die laufende Summe sollte die aktuelle Zeile und die beiden vorherigen Zeilen enthalten. Dies sind die untere und obere Grenze, die in der Klausel ROWS angegeben sind. Die untere Grenze sind die beiden vorherigen Zeilen, d. h. BETWEEN 2 PRECEDING. Die obere Grenze ist CURRENT ROW. Die beiden Schranken werden mit dem Schlüsselwort AND in einem Fensterrahmen zusammengefügt.

Ich möchte die Berechnung für das Album "In a Silent Way" von Miles Davis anzeigen, also filtere ich die Daten mit WHERE.

Hier sind die laufenden Summen:

Lassen Sie uns das Ergebnis überprüfen und erklären, was eine laufende Summe ist.

Eine laufende Gesamtsumme ähnelt einer kumulativen Gesamtsumme (oder Summe), aber sie sind nicht dasselbe. Die kumulierte Summe gibt die Summe der aktuellen Zeile und aller vorherigen Zeilen an, d. h. der Fensterrahmen wird mit jeder Zeile größer. Eine laufende Summe ist eine Summe innerhalb eines definierten Fensterrahmens, der die gleiche Größe hat, sich aber mit jeder Zeile verschiebt. In unserem Fall ist das Fenster definiert als die aktuelle Zeile und die beiden vorherigen Zeilen.

Schauen Sie sich die hervorgehobenen Werte an. Die laufende Summe für das erste Quartal 2022 beträgt 428, was dem Einzelverkauf entspricht. Es gibt keine vorherigen Zeilen, so dass die laufende Summe nur die aktuelle Zeile umfasst.

Die nächste laufende Summe ist 428 + 1.053 = 1.481. Sie summiert die aktuelle und die vorherige Zeile, da es nur eine vorherige Zeile gibt.

Die laufende Summe für das dritte Quartal 2022 ist 428 + 1.053 + 19 = 1.500. Hier erhalten Sie zum ersten Mal das gesamte Fenster, d. h. die aktuelle Zeile und die beiden vorherigen Zeilen.

Wenn Sie zur nächsten Zeile gehen, verschiebt sich das Fenster, aber seine Größe bleibt gleich. Die laufende Summe für das folgende Quartal beträgt 428 + 1.053 + 19 + 65 = 1.137. Auch hier werden die aktuelle Zeile und die beiden vorangegangenen Zeilen berücksichtigt - allerdings andere als im Quartal.

Lösen Sie diese Übung zum Üben

Schreiben Sie die obige Abfrage so um, dass sie die laufende Summe für das Album "The New Folk Sound of Terry Callier" berechnet. Die laufende Summe sollte vom frühesten bis zum spätesten Quartal berechnet werden. Sie sollte vier Quartale umfassen: die beiden vorhergehenden, das aktuelle und das folgende. Geben Sie außerdem den Verkaufszeitraum, den Albumtitel, den Interpreten und die Anzahl der verkauften Exemplare an.

Lösung:

SELECT sales_period, 
	 album_title,
	 artist,
	 copies_sold,
	 SUM(copies_sold) OVER (ORDER BY sales_period ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS sales_running_total
FROM album_catalogue
WHERE album_title = 'The New Folk Sound of Terry Callier';

Ausgabe:

Beispiel Nr. 4: Differenz von Quartal zu Quartal

In diesem letzten Beispiel zeige ich, wie man die Fensterfunktionen verwendet, um die Verkaufsdifferenz zwischen den Quartalen zu berechnen:

SELECT *,
	 LAG(quarterly_copies_sold) OVER (ORDER BY sales_period) AS previous_quarter_sales,
	 quarterly_copies_sold - LAG(quarterly_copies_sold) OVER (ORDER BY sales_period) AS quarterly_sales_difference
FROM (SELECT sales_period,
	SUM(copies_sold) AS quarterly_copies_sold
FROM album_catalogue
GROUP BY sales_period) AS quarterly_sales; 

Zuerst schreibe ich eine Unterabfrage, die die Gesamtverkäufe für jedes Quartal berechnet. Ich verwende die Aggregatfunktion SUM() und gruppiere die Ergebnisse nach dem Verkaufszeitraum.

Anschließend wähle ich alle Daten aus der Unterabfrage in der Hauptabfrage aus.

Jetzt muss ich die Umsätze des vorherigen Quartals abrufen. Ich schreibe die Funktion LAG() window, die für den Zugriff auf Werte aus den vorherigen Zeilen verwendet wird. Der Wert, auf den ich zugreifen möchte, wird in der Funktion angegeben. In diesem Fall handelt es sich um die verkauften Exemplare des Quartals aus der Unterabfrage. Durch die Definition des Offset-Arguments kann ich mit der Funktion festlegen, wie weit ich zurückgehen möchte. Ich habe es nicht definiert, also ist der Standardversatz eins. Mit anderen Worten: Die Funktion holt die Daten aus der vorherigen Zeile/dem vorherigen Quartal. Wenn Sie jedoch zwei Zeilen/Quartale zurückgehen wollen, müssen Sie LAG(quarterly_copies_sold, 2) schreiben.

Ich verwende auch ORDER BY in OVER(), um sicherzustellen, dass die Werte innerhalb des Rahmens vom ältesten bis zum neuesten Quartal sortiert sind.

Diese Verwendung der Fensterfunktion dient dazu, dass klarer wird, was ich in der nächsten Codezeile tun werde. Hier erfolgt die eigentliche Berechnung des Vergleichs der Umsätze des aktuellen und des vorherigen Quartals. Jetzt ist es ganz einfach: Ziehen Sie die oben definierte Fensterfunktion von der Spalte quarterly_copies_sold ab.

Hier ist die Ausgabe:

Für 2022_1Q gibt es keine vorherigen Werte, da es kein vorheriges Quartal gibt. Der vierteljährliche Umsatz für 2022_2Q beträgt 7.581. Der Umsatz im vorangegangenen Quartal betrug 9.519. Die Berechnung zeigt, dass der aktuelle Absatz um 1.938 Exemplare (7.581 - 9.519) unter dem Absatz des Vorquartals liegt.

Sie können den Rest der Ausgabe auf die gleiche Weise analysieren.

Lösen Sie diese Übung zum Üben

Schreiben Sie die obige Abfrage so um, dass sie die Differenz zwischen den vierteljährlichen Verkäufen auf Jahresbasis anzeigt - vergleichen Sie z. B. das erste Quartal 2023 mit dem ersten Quartal 2022. Geben Sie den Verkaufszeitraum, die im Quartal verkauften Exemplare, die Verkäufe im gleichen Quartal des Vorjahres und die Differenz zwischen den Quartalen im Vergleich zum Vorjahr an.

Lösung:

SELECT *,
	 LAG(quarterly_copies_sold, 4) OVER (ORDER BY sales_period) AS year_over_year_sales,
	 quarterly_copies_sold - LAG(quarterly_copies_sold, 4) OVER (ORDER BY sales_period) AS year_over_year_difference
FROM (SELECT sales_period,
	   SUM(copies_sold) AS quarterly_copies_sold
FROM album_catalogue
GROUP BY sales_period) AS quarterly_sales; 

Ausgabe:

Wenn Sie mehr wollen, finden Sie hier weitere Beispiele für Fensterfunktionen. Als Übungsmaterial können Sie sich diese 11 SQL-Fensterfunktionen-Übungen ansehen.

SQL Fensterfunktionen: Ein Fenster zur besseren Datenanalyse

Dies war ein ziemlich umfassender Artikel über SQL-Fensterfunktionen. Sie haben die Fensterfunktionen kennen gelernt und wissen, wie jeder wichtige Teil ihrer Syntax funktioniert.

Sie wissen auch, dass es mehrere Kategorien von Fensterfunktionen gibt. Die am häufigsten verwendeten sind Aggregat-, Ranglisten- und analytische Fensterfunktionen. Die praktischen Beispiele haben Ihnen gezeigt, wie Fensterfunktionen bei allgemeinen Datenanalyseaufgaben verwendet werden können.

Hoffentlich haben Sie die Übungen in diesem Artikel nicht übersprungen. Falls doch, empfehle ich Ihnen noch einmal, sie zu lösen. Nur durch Üben können Sie wirklich begreifen, worum es bei SQL-Fensterfunktionen geht.

Die umfangreichste Ressource zum Lernen und Üben ist unser Fensterfunktionen Kurs. Es ist ein interaktiver Kurs mit über 200 praktischen Übungen, der die gesamte Syntax der Fensterfunktionen abdeckt. Wenn Sie ein Vorstellungsgespräch vor sich haben, sollten Sie unbedingt diese Fragen zu SQL-Fensterfunktionen durchgehen. Viel Glück und lernen Sie weiter SQL!