5 praktische Beispiele für die Verwendung von ROWS BETWEEN in SQL

SQL-Fensterfunktionen sind äußerst nützlich für die Berechnung komplexer Aggregationen wie gleitender Durchschnitte oder laufender Gesamtsummen. Mit der ROWS-Klausel können Sie Zeilen für Ihre Berechnungen angeben, was noch ausgefeiltere Fensterrahmen ermöglicht. Im Folgenden finden Sie fünf praktische Beispiele für die Nutzung der ROWS BETWEEN-Klausel in SQL.

Fensterfunktionen (auch OVER-Funktionen genannt) berechnen ihr Ergebnis auf der Grundlage eines gleitenden Fensterrahmens (d. h. eines Satzes von Zeilen). Sie ähneln den Aggregatfunktionen insofern, als Sie den Durchschnitt, die Summe oder den Minimal-/Maximalwert über eine Gruppe von Zeilen berechnen können. Es gibt jedoch einige wichtige Unterschiede:

• Bei Fensterfunktionen werden Zeilen nicht wie bei Aggregatfunktionen komprimiert. Sie können also weiterhin Attribute aus einer einzelnen Zeile mit den Ergebnissen einer Fensterfunktion mischen.
• Fensterfunktionen erlauben gleitende Fensterrahmen, was bedeutet, dass die Menge der Zeilen, die für die Berechnung einer Fensterfunktion verwendet werden, für jede einzelne Zeile unterschiedlich sein kann.

Die Syntax einer Fensterfunktion ist unten in blauer Schrift dargestellt:

SELECT , ,
  OVER (
 	PARTITION BY <...>
   	ORDER BY <...>
    	) 
FROM ;

Wenn Sie eine Fensterfunktion in der SELECT-Anweisung verwenden, berechnen Sie im Grunde eine andere Spalte mit dieser Funktion:

• Sie beginnen mit der Angabe einer Funktion (z. B. AVG(), SUM() oder COUNT()).
• Mit dem Schlüsselwort OVER können Sie eine Reihe von Zeilen definieren. Optional können Sie:
  • Gruppieren Sie die Zeilen mit PARTITION BY, so dass die Funktionen innerhalb dieser Gruppen und nicht für die gesamte Menge der Zeilen berechnet werden.
  • Sortieren Sie die Zeilen innerhalb eines Fensterrahmens mit ORDER BY, wenn die Reihenfolge der Zeilen wichtig ist (z. B. bei der Berechnung von laufenden Summen).
  • Legen Sie den Bezug des Fensterrahmens zur aktuellen Zeile fest (z. B. soll der Rahmen die aktuelle Zeile und zwei vorhergehende Zeilen oder die aktuelle Zeile und alle folgenden Zeilen usw. umfassen).

Ein Fensterrahmen wird mit den Klauseln ROWS, RANGE und GROUPS definiert. In diesem Artikel werden wir uns auf die Klausel ROWS und ihre Optionen konzentrieren. Um mehr über Fensterfunktionen und die Definition von Fensterrahmen zu erfahren, lesen Sie diesen Artikel mit Beispielen für Fensterfunktionen, diesen Erklärungsleitfaden und natürlich unser zweiseitiges SQL Fensterfunktionen Cheat Sheet.

ROWS-Klausel: Syntax und Optionen

Der Zweck der ROWS-Klausel ist die Angabe des Fensterrahmens in Bezug auf die aktuelle Zeile. Die Syntax lautet:

ROWS BETWEEN lower_bound AND upper_bound

Der Rahmen kann eine der folgenden fünf Optionen sein:

• UNBOUNDED PRECEDING - Alle Zeilen vor der aktuellen Zeile.
• n PRECEDING - n Zeilen vor der aktuellen Zeile.
• CURRENT ROW - Nur die aktuelle Zeile.
• n FOLLOWING - n Zeilen nach der aktuellen Zeile.
• UNBOUNDED FOLLOWING - Alle Zeilen nach der aktuellen Zeile.

Quelle: SQL Fensterfunktionen Spickzettel

Bei der Definition von Fensterrahmen mit der ROWS Klausel sind einige Dinge zu beachten:

• Der Fensterrahmen wird in jeder Partition separat ausgewertet.
• Die Standardoption hängt davon ab, ob Sie ORDER BY verwenden:
  • Mit ORDER BY ist der Standardrahmen RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • Ohne ORDER BY ist der Standardrahmen ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• Wenn eine Ihrer Begrenzungen eine aktuelle Zeile ist, können Sie die Angabe dieser Begrenzung überspringen und eine kürzere Version der Fensterrahmen-Definition verwenden:
  • UNBOUNDED PRECEDING ist dasselbe wie BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • n PRECEDING ist identisch mit BETWEEN n PRECEDING AND CURRENT ROW.
  • n FOLLOWING ist dasselbe wie BETWEEN CURRENT ROW AND n FOLLOWING.
  • UNBOUNDED FOLLOWING ist derselbe wie BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Gehen wir nun zu den Beispielen über, um zu sehen, wie dies in der Praxis funktioniert.

5 Praktische Beispiele für die Verwendung von ROWS in Fensterfunktionen

Beispiel 1

Für die ersten Schritte mit der ROWS -Klausel verwenden wir die folgende Tabelle mit den Verkaufsdaten einer Buchhandlung.

In unserem ersten Beispiel möchten wir eine weitere Spalte hinzufügen, die den Gesamtumsatz vom ersten Datum bis zum Datum der aktuellen Zeile anzeigt (d. h. eine laufende Summe). Hier ist die Abfrage, die wir verwenden können:

SELECT date, revenue,
	SUM(revenue) OVER (
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_total
FROM sales
ORDER BY date;

Um die laufende Summe mit Hilfe einer Fensterfunktion zu berechnen, gehen wir wie folgt vor:

• Berechnen des Gesamtumsatzes mit der Aggregatfunktion SUM().
• Ordnen der Datensätze im Fensterrahmen nach Datum (standardmäßig in aufsteigender Reihenfolge), da die Reihenfolge der Zeilen bei der Berechnung einer laufenden Summe von Bedeutung ist.
• Spezifizieren des Fensterrahmens durch Festlegen der unteren Grenze als UNBOUNDED PRECEDING und der oberen Grenze als CURRENT ROW. Dies schließt alle Zeilen bis einschließlich der aktuellen Zeile ein. Beachten Sie, dass das Standardverhalten ohne die Angabe der Klausel ROWS in diesem Fall das gleiche wäre. Der Standardrahmen verwendet RANGE, nicht ROWS. Da jeder Tag nur einmal in der Tabelle vorkommt, ist das Ergebnis für RANGE und ROWS dasselbe. Wir könnten also auch die folgende Abfrage verwenden, um die gleichen Ergebnisse zu erhalten:

SELECT date, revenue,
	SUM(revenue) OVER (
      ORDER BY date) running_sum
FROM sales
ORDER BY date;

Wie Sie sehen, hat die Abfrage wie vorgesehen funktioniert, und wir erhalten die laufende Summe in unserer dritten Spalte. Am ersten Tag entspricht sie dem Umsatz dieses Tages - 1515,45 $; am zweiten Tag entspricht sie der Summe der Umsätze des ersten und zweiten Tages - 3860,80 $; in der nächsten Zeile erhalten wir die Summe der Umsätze der ersten drei Tage - 4764,79 $ usw.

In den nächsten Beispielen werden wir sehen, wie die ROWS Klausel funktioniert, wenn die Datensätze in mehrere Gruppen unterteilt sind.

Um die Definition von Fensterrahmen zu üben, sehen Sie sich diesen interaktiven Fensterfunktionen Kurs mit über 200 Programmieraufgaben.

Beispiel 2

Für die nächsten Beispiele verwenden wir die folgende Tabelle. Sie enthält fiktive Daten zur durchschnittlichen Temperatur (in °C) und zum Gesamtniederschlag (in mm) in zwei italienischen Städten (Rom und Florenz) an fünf aufeinanderfolgenden Tagen.

Wir wollen die gleitende Drei-Tages-Durchschnittstemperatur für jede Stadt separat berechnen. Um die Berechnungen für die beiden Städte zu trennen, fügen wir die Klausel PARTITION BY ein. Bei der Angabe des Zeitfensters werden dann der aktuelle Tag und die beiden vorangegangenen Tage berücksichtigt:

Beachten Sie auch, dass wir unsere Fensterfunktion innerhalb der Funktion ROUND() platziert haben, damit der gleitende Drei-Tage-Durchschnitt auf eine Dezimalstelle gerundet wird. Hier ist das Ergebnis:

Der gleitende Durchschnitt wurde für Florenz und Rom getrennt berechnet. Für den 1. September entspricht der gleitende Durchschnitt der durchschnittlichen Tagestemperatur, da wir keine vorangegangenen Aufzeichnungen haben. Für den 2. September wird das gleitende Mittel aus der Durchschnittstemperatur des 1. und 2. Septembers berechnet (17,5 °C in Florenz bzw. 18,8 °C in Rom). Am 3. September haben wir schließlich genügend Daten, um die Durchschnittstemperatur für drei Tage (die beiden vorangegangenen und den aktuellen Tag) zu berechnen, die in Florenz 18,0 °C und in Rom 19,2 °C beträgt. Dann wird der gleitende Drei-Tages-Durchschnitt für den 4. September als Durchschnittstemperatur für den 2., 3. und 4. September berechnet, und so weiter.

Und noch etwas ist zu beachten: Die Reihenfolge der Datensätze im Fensterrahmen spielt eine wichtige Rolle bei der Festlegung der zu berücksichtigenden Zeilen.

In der obigen Abfrage haben wir die Datensätze im Fensterrahmen nach Datum in aufsteigender Reihenfolge geordnet (unter Verwendung der Standardeinstellung), d. h. wir beginnen mit dem frühesten Datum. Um dann zwei Tage vor dem aktuellen Tag in unsere Berechnungen einzubeziehen, haben wir die untere Grenze auf 2 PRECEDING gesetzt.

Wir könnten jedoch genau denselben Fensterrahmen erhalten, indem wir die Datensätze in absteigender Reihenfolge ordnen und dann die Option ROWS so ändern, dass 2 FOLLOWING anstelle von 2 PRECEDING einbezogen wird:

SELECT city, date, temperature,
	ROUND(AVG(temperature) OVER (
      PARTITION BY city
      ORDER BY date DESC
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING), 1) mov_avg_3d_city
FROM weather
ORDER BY city, date;

Diese Abfrage liefert genau das gleiche Ergebnis.

Beispiel 3

In diesem Beispiel berechnen wir die Gesamtniederschlagsmenge der letzten drei Tage (d. h. eine laufende Drei-Tage-Summe) getrennt für zwei Städte.

SELECT city, date, precipitation,
	SUM(precipitation) OVER (
      PARTITION BY city
      ORDER BY date
      ROWS 2 PRECEDING) running_total_3d_city
FROM weather
ORDER BY city, date;

In dieser Abfrage werden die Daten wieder nach Städten aufgeteilt. Wir verwenden die Funktion SUM(), um die Gesamtniederschlagsmenge für die letzten drei Tage einschließlich des aktuellen Tages zu berechnen. Beachten Sie auch, dass wir bei der Definition des Fensterrahmens eine Abkürzung verwenden, indem wir nur die untere Grenze angeben: 2 PRECEDING.

Hier ist die Ausgabe der obigen Abfrage:

Ab dem 3. September erhalten wir eine dreitägige Gesamtsumme der Niederschläge in Florenz: 30 mm. Dies ist die Summe von 5 mm Niederschlag vom 1. September, 15 mm vom 2. September und 10 mm vom 3. September.

Wissen Sie, wie wir die 12 mm Niederschlag für Rom am 5. September erhalten haben? Versuchen Sie, die Ergebnisse in unserer Ausgabetabelle zu verfolgen, um sicherzustellen, dass Sie verstehen, wie Fensterfunktionen mit bestimmten Fensterrahmen funktionieren.

Lassen Sie uns nun zu neuen Daten und Beispielen übergehen.

Beispiel 4

Für die nächsten beiden Beispiele verwenden wir die unten gezeigten Daten. Sie enthalten tägliche Informationen über die Anzahl der neuen Abonnenten in drei sozialen Netzwerken: Instagram, Facebook und LinkedIn.

Wir beginnen mit der Berechnung der laufenden Gesamtzahlen für die Anzahl der neuen Abonnenten für jedes Netzwerk separat. Im Grunde wollen wir für jeden Tag sehen, wie viele Personen sich seit Beginn der Datenerfassung bis zum Datum der aktuellen Zeile angemeldet haben.

Hier ist eine SQL-Abfrage, die diese Anforderung erfüllt:

SELECT social_network, date, new_subscribers,
	SUM(new_subscribers) OVER (
      PARTITION BY social_network
      ORDER BY date
      ROWS UNBOUNDED PRECEDING) running_total_network
FROM subscribers
ORDER BY social_network, date;

Wir beginnen mit der Berechnung der Gesamtzahl der neuen Abonnenten mit der Aggregatfunktion SUM(). Dann verwenden wir die PARTITION BY-Klausel, um separate Berechnungen für jedes Netz durchzuführen. Außerdem sortieren wir die Datensätze nach Datum in aufsteigender Reihenfolge (Standardeinstellung). Schließlich definieren wir den Fensterrahmen als UNBOUNDED PRECEDING, um alle Datensätze bis einschließlich des aktuellen Datensatzes einzuschließen.

Die Ausgabe sieht wie folgt aus:

In der Ergebnistabelle können Sie sehen, wie die Anzahl der neuen Abonnenten für jeden neuen Datensatz zur kumulativen Gesamtzahl hinzugefügt wird. Die laufende Summe wird für jedes Netz separat berechnet, wie in der Fensterfunktion angegeben.

Beispiel 5

In unserem letzten Beispiel möchte ich Ihnen zeigen, wie Sie den ersten und den letzten Wert eines bestimmten Satzes von Datensätzen mithilfe von Window-Funktionen und der ROWS-Klausel anzeigen können. Diesmal fügen wir der Ausgabe zwei Spalten hinzu:

• Die Anzahl der neuen Abonnenten, die am ersten Tag hinzugefügt wurden, und
• die Anzahl der neuen Abonnenten, die am letzten Tag hinzugefügt wurden.

Anhand dieser Informationen, die für jedes soziale Netzwerk separat berechnet werden, können wir sehen, wie die Leistung jedes Tages im Vergleich zum Anfang und zum aktuellen Stand ist.

Hier ist die SQL-Abfrage, um die gewünschte Ausgabe zu erhalten:

SELECT social_network, date, new_subscribers,
    FIRST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date) AS first_day,
    LAST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_day
FROM subscribers
ORDER BY social_network, date;

Wie Sie sehen, verwenden wir die Funktionen FIRST_VALUE() und LAST_VALUE(), um die Informationen über den ersten bzw. den letzten Tag zu erhalten. Beachten Sie auch, wie wir den Fensterrahmen für jede der Funktionen angeben:

• Wir fügen die ROWS-Klausel nicht in die FIRST_VALUE()-Funktion ein, da das Standardverhalten (d. h. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) für unsere Zwecke ausreichend ist.
• Wir geben jedoch den Fensterrahmen mit der Funktion LAST_VALUE() an, da die Standardoption den aktuellen Zeilenwert als letzten Wert für jeden Datensatz verwenden würde; dies ist in diesem Beispiel nicht erwünscht. Wir geben den Fensterrahmen als ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING an, um sicherzustellen, dass alle Datensätze berücksichtigt werden.

Und hier ist die Ergebnismenge:

Wie gewünscht, haben wir die Anzahl der neuen Abonnenten am ersten und letzten Tag separat für jedes soziale Netzwerk berechnet.

Übung mit ROWS in SQL Fensterfunktionen

Nach den obigen Beispielen sind Sie hoffentlich motiviert, die SQL-Fensterfunktionen und die ROWS-Optionen gründlicher zu lernen. Mit diesem Toolkit können Sie einen gleitenden Fensterrahmen festlegen und die Berechnung komplexer Aggregationen wie gleitende Durchschnitte und laufende Summen ermöglichen.

Wenn Sie sich mit den Fensterfunktionen vertraut machen wollen, empfehle ich Ihnen den interaktiven LearnSQL.deFensterfunktionen Kurs. Er zeigt Ihnen, wie Sie laufende Summen und Durchschnittswerte berechnen, verschiedene Arten von Ranglisten erstellen, Trends im Zeitverlauf untersuchen und vieles mehr. Noch besser ist, dass Sie die Übungen selbst durchführen können, denn das ist die beste Art zu lernen.

Wenn Sie die Verwendung von SQL für die Datenanalyse wirklich beherrschen wollen, enthält unser Fortgeschrittenes SQL Lernpfad auch GROUP BY Erweiterungen in SQL und gemeinsame Tabellenausdrücke (CTEs). Damit können Sie Ihr Wissen über Fensterfunktionen hervorragend erweitern.

Möchten Sie zunächst mit etwas Lektüre beginnen? Hier sind die 8 wichtigsten Artikel zu den SQL-Fensterfunktionen.

Vielen Dank fürs Lesen und viel Spaß beim Lernen.