Wie man einen Fensterrahmen in SQL-Fensterfunktionen definiert

Die Fensterfunktion ist eine äußerst leistungsfähige Ressource der SQL-Sprache. Das Herzstück jeder Fensterfunktion ist eine Gruppe von Datensätzen, der so genannte Fensterrahmen, der mit einer OVER-Klausel definiert. Um zu verstehen, wie Fensterfunktionen funktionieren, muss man wissen, welche Datensätze sich im Fensterrahmen befinden, wie sie geordnet sind und welche Ober- und Untergrenzen sie haben. In diesem Artikel werden wir analysieren und anhand von Beispielen erklären, wie Sie verschiedene Arten von Fensterrahmen definieren können. Lesen Sie weiter und machen Sie einen wichtigen Schritt zur Erweiterung Ihrer SQL-Kenntnisse!

Verwendung von PARTITION BY zur Definition eines Fensterrahmens

SQL-Fensterfunktionen führen Berechnungen auf der Grundlage einer Reihe von Datensätzen durch. Sie möchten zum Beispiel das Durchschnittsgehalt einer bestimmten Gruppe von Mitarbeiterdatensätzen berechnen. Diese Gruppe von Datensätzen wird als Fensterrahmen bezeichnet, und seine Definition ist von zentraler Bedeutung, um zu verstehen, wie Fensterfunktionen funktionieren und wie wir sie uns zunutze machen können.

Der Fensterrahmen ist ein Satz von Zeilen, die sich auf die aktuelle Zeile beziehen, in der die Fensterfunktion zur Berechnung verwendet wird. Der Fensterrahmen kann ein anderer Satz von Zeilen für die nächste Zeile im Abfrageergebnis sein, da er von der aktuell verarbeiteten Zeile abhängt. Jede Zeile in der Ergebnismenge der Abfrage hat ihren eigenen Fensterrahmen.

Im weiteren Verlauf dieses Artikels werden wir Beispielabfragen auf der Grundlage einer Datenbank einer Autohausgruppe zeigen. Die Gruppe speichert die Verkaufsinformationen gruppiert nach Monat in einer Tabelle namens monthly_car_sales. Nachfolgend sehen Sie die Tabelle mit einigen Beispieldaten:

monthly_car_sales

Eine einfache Möglichkeit, einen Fensterrahmen zu erstellen, ist die Verwendung einer OVER Klausel mit einer PARTITION BY Subklausel. Im folgenden SQL-Beispiel erstellen wir einen Bericht über die Einnahmen nach Automarke für das Jahr 2021.

SELECT make,
       SUM(revenue) OVER (PARTITION BY make) AS total_revenue
FROM   monthly_car_sales
WHERE  year = 2021

Nachfolgend sind die durch die vorherige Abfrage erzeugten Fensterrahmen in verschiedenen Farben dargestellt (rot für Ford und blau für Renault). Alle Datensätze mit demselben Wert in der Spalte make (die unten farblich markierten Zeilen) gehören zum selben Fensterrahmen. Da wir nur zwei verschiedene Werte in der Spalte make haben, haben wir zwei Fensterrahmen.

Das Ergebnis der Abfrage ist:

Ich möchte Ihnen 2 Artikel empfehlen, in denen Sie viele einführende Informationen über SQL-Fensterfunktionen finden können: "SQL-Kurs des Monats - Fensterfunktionen" und "Wann verwende ich SQL Fensterfunktionen?".

Ordnen der Zeilen innerhalb eines Fensterrahmens mit ORDER BY

Zusätzlich zu PARTITION BY können wir eine ORDER BY Subclause verwenden, um die Zeilen innerhalb eines Fensterrahmens zu ordnen. Wenn der Fensterrahmen nach bestimmten Kriterien geordnet ist, können wir u. a. analytische Fensterfunktionen wie LEAD(), LAG() und FIRST_VALUE() verwenden.

Wenn wir z. B. die Umsatzdifferenz zwischen aufeinanderfolgenden Monaten ermitteln wollen, können wir den Fensterrahmen nach Monaten ordnen. Dann kann die Fensterfunktion LAG() bei einer beliebigen aktuellen Zeile eine beliebige Spalte des Vormonats zurückgeben. Sehen wir uns ein Beispiel an, bei dem die Umsatzdifferenz zwischen jedem Paar aufeinanderfolgender Monate ermittelt wird.

SELECT make,
       model,
       month,
       revenue AS current_month_revenue,
       LAG(revenue) OVER ( ORDER BY month) AS previous_month_revenue,
	 revenue - LAG (revenue) OVER (ORDER BY month) AS delta_revenue
FROM monthly_car_sales
WHERE year = 2021
  AND model = 'Mustang'

Das Ergebnis der vorherigen Abfrage lautet:

Die erste Zeile des Ergebnisses hat NULL Werte in den Spalten previous_month_revenue und delta_revenue. Das liegt daran, dass es keinen Vormonat für Januar gibt. Die Spalte delta_revenue wird zwischen dem Umsatz des aktuellen Monats und dem Umsatz des Vormonats berechnet, wobei letzterer mit der Fensterfunktion LAG() ermittelt wird.

Bei der Verwendung von ORDER BY in einer OVER -Klausel gibt es ein neues Element zu berücksichtigen: die Grenzen des Fensterrahmens. Wenn wir keine ORDER BY Klausel angeben, wird die gesamte Partition zum Fensterrahmen. Wenn wir jedoch eine ORDER BY Subklausel verwenden, wird die aktuelle Zeile zur oberen Grenze des Fensterrahmens. Mit anderen Worten, die auf die aktuelle Zeile folgenden Zeilen (basierend auf den ORDER BY Kriterien) werden nicht in den Fensterrahmen aufgenommen. Im nächsten Abschnitt wird das Konzept der Fensterrahmenbegrenzungen im Detail behandelt.

Definition von Fensterrahmengrenzen mit ROWS

Eine sehr interessante Funktion der OVER Klausel ist die Möglichkeit, die oberen und unteren Grenzen eines Fensterrahmens anzugeben. Diese Grenzen können mit Hilfe einer der beiden Unterklauseln der OVER -Klausel angegeben werden: ROWS oder RANGE. In diesem Abschnitt wird die Verwendung der Unterklausel ROWS der OVER-Klausel erläutert.

Der Fensterrahmen ist ein Satz von Zeilen, die in irgendeiner Weise mit der aktuellen Zeile in Beziehung stehen. Ihre Grenzen können für jede Zeile im Abfrageergebnis mit einer ROWS Subklausel definiert werden, die die folgende Syntax hat:

ROWS BETWEEN lower_bound AND upper_bound

Wie bereits im vorherigen Abschnitt erwähnt, ist es wichtig zu wissen, was die Standardgrenzen des Fensterrahmens sind. Wenn wir eine ORDER BY Subclause angeben, ist die aktuelle Zeile standardmäßig die obere Grenze des Fensterrahmens. In einigen Fällen müssen wir jedoch diese obere Grenze (oder die untere Grenze) ändern, wie wir weiter unten sehen werden.

Sehen wir uns ein Beispiel an, in dem wir die Grenzen eines Fensterrahmens angeben müssen. Angenommen, wir möchten einen Bericht mit den Gesamtverkäufen für den aktuellen Monat, den Gesamtverkäufen des Vormonats und den maximalen Verkäufen in jedem einzelnen Monat des Jahres, jeweils nach Marke und Modell. Die Abfrage, um einen solchen Bericht zu erhalten, lautet:

SELECT make,
       model,
       month,
       revenue AS current_month_revenue,
       LAG(revenue) OVER (PARTITION BY make, model ORDER BY month) AS    
                                                   prev_month,
	 MAX(revenue) OVER (PARTITION BY make, model ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_year_revenue
FROM monthly_car_sales
WHERE year = 2021

Hätten wir die Subklausel ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING in der Funktion MAX() window in der vorherigen Abfrage weggelassen, hätten wir das Maximum zwischen dem ersten Monat und dem aktuellen Monat erhalten. Das ist falsch, denn wir wollen die maximalen monatlichen Einnahmen für das gesamte Jahr (einschließlich der Monate nach dem aktuellen Monat). Wir müssen also alle verfügbaren Monate in die Tabelle aufnehmen. Dies geschieht durch Hinzufügen der Unterklausel ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING zur Klausel OVER.

Die nächste Abbildung zeigt alle möglichen Begrenzungen, die wir angeben können, um die unteren und oberen Grenzen eines Fensterrahmens zu definieren:

Die Optionen für untere und obere Begrenzungen in der OVER Klausel sind:

• UNBOUNDED PRECEDING
• N PRECEDING
• CURRENT ROW
• N FOLLOWING
• UNBOUNDED FOLLOWING

Wir müssen sicherstellen, dass die untere Grenze kleiner als die obere Grenze ist.

Abschließend möchte ich Ihnen den Artikel Why Learn SQL Window Functions in 2021? empfehlen, in dem Sie lernen können, wie man Fensterfunktionen verwendet.

Definieren von Fensterrahmengrenzen mit RANGE

Im vorigen Abschnitt haben wir die Grenzen eines Fensterrahmens in Form von ROWS definiert. In diesem Abschnitt wird erklärt, wie man die Subclause RANGE verwendet, um die Grenzen eines Fensterrahmens in Zeilenbereichen anzugeben. Die Syntax der Subklausel RANGE lautet wie folgt:

RANGE BETWEEN lower_bound AND upper_bound

Ein Bereich ist eine Menge von Zeilen mit demselben Wert für das Kriterium PARTITION BY. Wenn wir zum Beispiel einen PARTITION BY Monat haben, können wir den Unterschied im nächsten Bild sehen, wenn wir ROWS oder RANGE verwenden, um einen Fensterrahmen zu definieren:

OVER ( PARTITION BY …... 

             ORDER BY month 

             ROWS BETWEEN 1 PRECEDING 

             AND 1 FOLLOWING )

OVER ( PARTITION BY ….. 

             ORDER BY month 

             RANGE BETWEEN 1 PRECEDING 

             AND 1 FOLLOWING )

Wenn wir einen Umsatzbericht nach Marke für den aktuellen Monat und für jeden der letzten drei Monate wünschen, können wir die folgende Abfrage verwenden:

SELECT make,
       model,
       month,
	 revenue AS model_revenue_current_month,
       SUM(revenue) OVER ( PARTITION BY make
                           ORDER BY month
                           RANGE BETWEEN 0 PRECEDING AND CURRENT ROW
                         ) AS make_current_month,
	 SUM(revenue) OVER (PARTITION BY make
                           ORDER BY month
                           RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
                        ) AS make_last_2_months,
	   SUM(revenue) OVER (PARTITION BY make
                            ORDER BY month
                            RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
                        ) AS make_last_3_months
FROM monthly_car_sales
WHERE year = 2021
ORDER BY 1,3,2

Die vorherige Abfrage verwendet die Subklausel RANGE, um einen Fensterrahmen mit allen Datensätzen der aktuellen Marke für einen Bereich von N Monaten anzugeben. Ein Beispiel:

SUM(revenue) OVER (
                   PARTITION BY make
                   ORDER BY month
                   RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
                  ) AS make_last_2_months

Die vorherige Subklausel RANGE BETWEEN 1 PRECEDING AND CURRENT ROW gibt einen Fensterrahmen an, der den Vormonat und den aktuellen Monat umfasst. Dann gibt die Funktion SUM() den Gesamtumsatz der letzten beiden Monate zurück.

In ähnlicher Weise können wir die folgende Klausel OVER verwenden, um den Gesamtumsatz der letzten drei Monate zu erhalten.

SUM(revenue) OVER (
                   PARTITION BY make
                   ORDER BY month
                   RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
                  ) AS make_last_3_months

Es gibt verschiedene Abkürzungen, um die Syntax für diese Begrenzungsklauseln zu vereinfachen:

Zu guter Letzt finden Sie hier meinen bevorzugten Spickzettel zu Fensterfunktionen mit vielen Details zur Syntax: SQL Fensterfunktionen Spickzettel.

Nutzen Sie die Vorteile von Fensterfunktionen!

Eine zentrale Idee bei der Fensterfunktion ist der Fensterrahmen, d.h. die Gruppe von Datensätzen, über die die Fensterfunktion arbeitet. In diesem Artikel haben wir erklärt, dass der Fensterrahmen von der aktuellen Zeile abhängt und durch die OVER Klausel definiert wird. Wir haben auch mehrere Beispiele dafür gezeigt, wie man definiert, welche Datensätze in den Fensterrahmen aufgenommen werden sollen, wie man die Zeilen innerhalb des Fensterrahmens anordnet und wie man seine Grenzen definiert.

Wer tiefer einsteigen möchte, dem empfehle ich den SQL-Online-Kurs Fensterfunktionen mit vielen Beispielen zu den verschiedenen Fensterfunktionen. Entwickeln Sie Ihre Fähigkeiten und steigern Sie Ihr Vermögen!!