Die RANGE-Klausel in SQL-Fensterfunktionen: 5 praktische Beispiele

Was ist eine RANGE Klausel in SQL-Fensterfunktionen? Anhand von fünf praktischen Beispielen erfahren Sie, wie und wann Sie sie verwenden können.

Die RANGE -Klausel wird in SQL-Fensterfunktionen recht selten verwendet. Ich weiß nicht, warum; vielleicht sind die Leute nicht daran gewöhnt. Das ist schade, denn die Klausel ist keineswegs überflüssig, sondern kann sehr nützlich sein, wie ich Ihnen in fünf Beispielen zeigen werde.

Syntax der RANGE-Klausel

Wenn ich von der RANGE -Klausel spreche, meine ich die Klausel, die in den SQL-Fensterfunktionen verwendet wird, die die folgende Syntax haben:

OVER (   
  [ <PARTITION BY clause> ]
  [ <ORDER BY clause> ]
  [ <ROW or RANGE clause> ]
) 

Wenn Sie sich die obige Syntax ansehen, sehen Sie, dass sowohl ROW als auch RANGE Teil der Fensterfunktion sein können. Ihre Syntax lautet wie folgt:

[<ROWS oder RANGE-Klausel> BETWEEN <Start expr> AND <End expr>]

Der Standard-Fensterrahmen ohne die ORDER BY ist die gesamte Partition. Wenn Sie jedoch die ORDER BY-Klausel verwenden, ist der Standard-Fensterrahmen RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Es ist normal, dass man diese Syntax vergisst, vor allem, wenn sie für Sie neu ist oder Sie sie nicht sehr oft verwenden. Sie können gerne Ihr Fensterfunktionen Cheat Sheet öffnen, während Sie diesen Artikel lesen.

Bereit zum Eintauchen? Prima! Lassen Sie uns zunächst über RANGE und ROW sprechen.

Sind die RANGE- und die ROW-Klausel das Gleiche?

Nein, das sind sie nicht. Sie haben jedoch denselben Zweck: die Angabe von Anfangs- und Endpunkten innerhalb der Partition mit dem Ziel, Zeilen zu begrenzen. Jede Klausel tut dies jedoch auf unterschiedliche Weise. Bei der ROW -Klausel wird eine feste Anzahl von Zeilen angegeben, die vor oder nach der aktuellen Zeile liegen.

Die RANGE Klausel hingegen schränkt die Zeilen logisch ein; sie gibt den Wertebereich in Bezug auf den Wert der aktuellen Zeile an.

Welche Datentypen kann ich in der RANGE-Klausel verwenden?

Sie können zwei Datentypen mit der RANGE -Klausel verwenden: numerische und Datums-/Zeittypen.

Sie können die numerischen Datentypen und die RANGE -Klausel in fast allen gängigen Datenbanken verwenden. Microsoft SQL Server unterstützt dies leider nicht.

Was die Datums-/Zeit-Datentypen betrifft, so unterstützen nur wenige gängige Datenbanken deren Verwendung mit der RANGE -Klausel. Dies sind PostgreSQL, MySQL und Oracle DB.

Kommen wir nun zu den Beispielen und sehen wir uns an, wie RANGE in der Praxis funktioniert! Wenn Sie mit SQL-Fensterfunktionen noch nicht vertraut sind, sollten Sie sich vielleicht erst einmal informieren, was Fensterfunktionen sind und wie sie sich von Aggregatfunktionen unterscheiden. Kommen Sie wieder, wenn Sie mit dem Lesen fertig sind, und wir machen weiter.

Beispiel 1 - Berechnen der laufenden Summe

Die Daten, mit denen ich arbeiten werde, befinden sich in der Tabelle revenue. Die Spalten sind:

• id - Die ID des Datums und der Primärschlüssel (PK) der Tabelle.
• date - Das Datum des Umsatzes.
• revenue_amount - Der Betrag des Umsatzes.

Ihre Aufgabe ist es, die laufende Umsatzsumme mit Hilfe der RANGE Klausel zu berechnen. Lassen Sie uns das zunächst ohne SQL machen. Wenn Sie die folgenden Daten haben, wie lautet dann die laufende Summe (auch bekannt als kumulative Summe)?

Die laufende Summe für 2021-05-01 entspricht dem Umsatz für dieses Datum: 12.573,25. Das liegt daran, dass es keine vorherigen Zeilen gibt, die in die Berechnung einbezogen werden können. Dann kommt 2021-05-02. Die laufende Summe ist die Summe der heutigen Einnahmen und der Einnahmen des Vortages: 11,348.22 + 12,573.25 = 23,921.47.

Beachten Sie, dass es eine weitere Zeile mit einer anderen Einnahmesumme für 2021-05-02 gibt. Vielleicht ist dies für eine andere Branche, ein anderes Land, ein anderes Produkt oder was auch immer. Es funktioniert auf dieselbe Weise: 14.895,13 + 23.921,47 = 38.816,60. (Die RANGE-Klausel funktioniert auch, wenn es mehrere Zeilen mit demselben Datum gibt). Als nächstes kommt 2021-05-03. Die laufende Summe für dieses Datum beträgt 14.388,14 + 38.816,60 = 53.204,74. Schließlich wird die laufende Summe für 2021-05-04 18.847,54 + 53.204 = 72.052,28 betragen.

Wie kann man das Gleiche mit der Klausel RANGE machen? Es könnte so gemacht werden:

SELECT
  id,
  date,
  revenue_amount,
  SUM(revenue_amount) OVER (
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM revenue;

Ich habe die Funktion SUM() auf die Spalte revenue_amount angewendet; dies ist die erforderliche Operation, um die laufende Summe zu erhalten. Damit die Funktion SUM() zu einer Fensterfunktion wird, benötigen Sie die Klausel OVER(). Die Berechnung der Fensterfunktion erfolgt in aufsteigender Reihenfolge, denn ich möchte sicherstellen, dass die Einnahmen vom ältesten bis zum neuesten Datum summiert werden. Dann kommt die Klausel RANGE. Sie beschränkt das Fenster auf die Daten, die vor dem aktuellen Datum (BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) und dem aktuellen Datum liegen. Dies sind die Zeilen, die in die Berechnung der laufenden Summe einbezogen werden.

Wenn Sie den Code ausführen, erhalten Sie das folgende Ergebnis:

Beachten Sie, dass bei mehreren Werten für ein Datum (2021-05-02) der Code beide Zeilen in die Berechnung der laufenden Summe für dieses Datum einbezieht. Aus diesem Grund steht in der Spalte running_total für dieses Datum 38.816,60.

Beispiel 2 - Berechnen des gleitenden Durchschnitts

Lassen Sie uns nun sehen, wie Sie die RANGE Klausel mit Datums-/Zeit-Datentypen verwenden - und ohne die id-Spalte als Hilfe! Wir verwenden eine leicht veränderte Tabelle, die jetzt den Namen revenue_per_shop. Sie enthält die Umsatzdaten für zwei Geschäfte. Die Spalten sind:

• date - Das Datum des Umsatzes.
• shop - Der Name des Shops.
• revenue_amount - Die Höhe des Umsatzes für diesen Shop an diesem Datum.

Stellen Sie zunächst sicher, dass Sie verstehen, was ein gleitender Durchschnitt ist. Ein gleitender Zweitagesdurchschnitt umfasst den aktuellen Tag und den Vortag. Hier sind einige Beispieldaten, die Ihnen zeigen, wie ein gleitender Durchschnitt funktioniert:

Der gleitende Zwei-Tages-Durchschnitt für 2021-05-01 ist der Tagesumsatz selbst: 12,573.25. Das liegt daran, dass es keine anderen Zeilen gibt, die in die Berechnung einbezogen werden können. Die Berechnung für 2021-05-02 umfasst zwei Daten: (12.573,25 + 11.348,22)/2 = 11.960,74. Die anderen Zeilen folgen der gleichen Logik mit zwei Daten - dem aktuellen Datum und dem vorherigen Datum.

Wie berechnen Sie nun dieselbe Kennzahl für jeden einzelnen Shop? Etwa so:

SELECT
  shop,
  date,
  revenue_amount,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date ASC
    RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

Der Code wählt zunächst einige Spalten aus der Tabelle aus. Dann kommt der lustige Teil. Ich verwende die Funktion AVG() für die Spalte revenue_amount, weil ich den durchschnittlichen Umsatz haben möchte. Auch hier handelt es sich um eine Fensterfunktion, also muss sie die Klausel OVER() enthalten. Ich verwende PARTITION BY, um die Spalte anzugeben, über die ich die Daten aggregieren möchte; es ist die Spalte shop, weil ich den gleitenden Durchschnitt für jeden Shop einzeln haben möchte. Die Operation ist wiederum nach Datum geordnet. In der Klausel RANGE gebe ich lediglich an, welche Zeilen in die Berechnung einbezogen werden sollen. Da ich mit Datumsangaben arbeite, erhalte ich das vorherige Datum durch die Angabe: BETWEEN INTERVAL '1' DAY PRECEDING.

Die Arbeit mit Datums-/Zeitdaten unterscheidet sich von Datenbank zu Datenbank. In einigen Datenbanken müssen Sie es vielleicht so schreiben: RANGE BETWEEN 1 DAY PRECEDING AND CURRENT ROW. Das Ergebnis sollte jedoch dasselbe sein:

Gleitender Durchschnitt für Datenbanken, die die Verwendung von RANGE mit Datums-/Zeitdatentypen nicht unterstützen

Was sollten Sie tun, wenn Ihre Datenbank die Verwendung von RANGE mit Datums-/Zeitdatentypen nicht unterstützt? Es gibt eine Möglichkeit, Ihre Datenbank "auszutricksen"; wahrscheinlich gibt es sogar mehrere. Hier ist eine Möglichkeit, die die Differenz zwischen jedem Datum und 2021-05-01 (d. h. dem ersten Datum in den Daten) berechnet. Sie erhalten die Differenz als Ganzzahl, die Sie anstelle des Datums verwenden können. Dies ist der Code:

SELECT
  shop,
  date,
  revenue_amount,
  date - '2021_05_01' AS day_difference,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY (date - '2021_05_01')
    RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

Ich habe absichtlich die Spalte day_difference in das Ergebnis aufgenommen, damit Sie die Logik verstehen. Ich habe dieselbe Differenz in der ORDER BY -Klausel verwendet, damit ich eine ganze Zahl mit der RANGE -Klausel verwenden kann. Und der gleitende Durchschnitt ist der gleiche wie im obigen Beispiel; sehen Sie selbst. (Ich zeige nur die ersten fünf Zeilen, um Platz zu sparen.)

Beispiel 3 - Suche nach dem letzten Wert innerhalb eines Bereichs

Diese Verwendung der RANGE-Klausel ermöglicht es Ihnen, den letzten Wert innerhalb eines bestimmten Bereichs zu finden. Wenn ich zum Beispiel die Tabelle revenue_by_shopden letzten Wert für jedes Geschäft separat abrufen. Der letzte Wert ist in diesem Fall der letzte verfügbare Wert, d. h. der Umsatz des Jahres 2021-05-15. Wie erhalten Sie diese Daten?

Natürlich unter Verwendung der RANGE Klausel:

SELECT
  shop,
  date,
  revenue_amount,
  LAST_VALUE(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_value
FROM revenue_per_shop;

Die Fensterfunktion, die ich dieses Mal verwendet habe, ist LAST_VALUE(). Auch hier verwende ich sie für die Spalte revenue_amount. Ich habe die Daten wie zuvor nach Geschäften aufgeteilt. Und ich habe sie nach Datum geordnet, wie zuvor. Um den letzten Wert zu erhalten, habe ich RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING verwendet. Denken Sie daran, dass der Standardbereich mit der ORDER BY Klausel RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ist. Wenn Sie ihn nicht ändern, erhalten Sie das falsche Ergebnis. Das richtige Ergebnis lautet:

Beispiel 4 - Ermitteln der Anzahl der Elemente innerhalb eines Bereichs

Hier ist eine weitere lustige und nützliche Möglichkeit, die RANGE Klausel zu verwenden. Wie würden Sie herausfinden, wie oft der Tagesumsatz eines beliebigen Geschäfts zwischen 1.000 (Dollar, Euro...) unter und über dem aktuellen Wert lag?

Dieser Code könnte Ihnen helfen:

SELECT
  shop,
  date,
  revenue_amount,
  COUNT(*) OVER (
    ORDER BY revenue_amount ASC
    RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  ) AS number_of_days
FROM revenue_per_shop;

Ich verwende die Funktion COUNT() window. Da ich nicht daran interessiert bin, die Einnahmen nach Geschäften zu trennen, gibt es keine PARTITION BY. Die Zählung erfolgt in aufsteigender Reihenfolge nach der Höhe des Umsatzes. Der Bereich wird durch RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING definiert.

Der Code wird Folgendes zurückgeben:

Ich möchte Ihnen erklären, was dieses Ergebnis aussagt. Wenn Sie die erste Zeile nehmen, ist das Ergebnis in der Spalte number_of_days 2. Es gibt zwei Fälle, in denen die Einnahmen zwischen 3.987,56 und 5.987,56 liegen. Warum diese Spanne? Die Einnahmen für 2021-05-13 betragen 4.987,56. Also 4.987,56 - 1.000 = 3.987,56 und 4.987,56 + 1.000 = 5.987,56. Willst du das Ergebnis überprüfen? Welche beiden Fälle liegen innerhalb dieser Spanne? Offensichtlich die ersten beiden:

Willst du die zweite Zeile überprüfen? Sie besagt, dass es drei Fälle zwischen 4.894,12 und 6.894,12 gibt - diese drei:

Die gleiche Logik gilt für den Rest der Daten.

Beispiel 5 - Suche nach dem Maximalwert

Dies ist nicht so einfach, wie es scheinen mag; ich spreche nicht von dem allgemeinen Höchstwert. Um den zu finden, bräuchten Sie die RANGE Klausel nicht. Aber wie wäre es, den Höchstwert (oder in diesem Fall den Umsatz) über fünf Tage hinweg zu ermitteln? Diese fünf Tage umfassen das aktuelle Datum, bis zu drei Tage davor und einen Tag nach dem aktuellen Datum. Nach all diesen Beispielen für die Verwendung von RANGE kennen Sie wahrscheinlich schon die Logik. Hier ist meine Lösung:

SELECT
  shop,
  date,
  revenue_amount,
  MAX(revenue_amount) OVER (
    ORDER BY DATE
    RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING
  ) AS max_revenue
FROM revenue_per_shop;

Ich verwende die Funktion MAX() als Fensterfunktion. Und wieder verwende ich sie mit der Spalte revenue_amount. In der Klausel OVER() gibt es kein PARTITION BY, da ich nicht daran interessiert bin, die Daten auf irgendeiner Ebene zu trennen. Die Definition des Bereichs ist nicht so schwierig: RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING. Dies schließt das aktuelle Datum, drei Tage davor und einen Tag danach ein. Hier ist das Ergebnis:

Schauen wir uns das Ergebnis für 2021-05-05 an - rosa markiert. Der Bereich ist gelb markiert. Um den maximalen Umsatz in diesem Bereich zu ermitteln, vergleicht SQL die Werte: 14,388.14, 18,847.54, 9,845.29, 14,574.56, 11,500.63, 16,897.21, 9,634.56, 14,255.87, 11,248.33, 21,489.22. Welches ist der höchste Wert? Es ist 21.489,22.

Nachdem ich gelernt habe, wie man mit der Klausel RANGE den Maximalwert findet, habe ich die maximale Anzahl von Beispielen für diesen Artikel erreicht. Wenn Sie weitere Beispiele für Fensterfunktionen suchen , können Sie jederzeit diesen Artikel lesen.

RANGE hat wirklich eine ganze Reihe von Verwendungsmöglichkeiten, nicht wahr?

Ich denke, diese fünf Beispiele zeigen Ihnen einen guten Überblick über die Möglichkeiten der RANGE Klausel. Das ist aber nicht alles, was Sie damit machen können. Ihre Verwendung hängt von den Daten ab, die Sie haben, und wahrscheinlich auch von ein wenig Fantasie. Über diese Klausel wird nicht sehr oft gesprochen, was sehr schade ist. Ich empfehle jedem, sie zu lernen. Sie könnte Ihnen Zeit ersparen, die Sie für die Erstellung von Umgehungslösungen aufwenden müssen. Die RANGE-Klausel ist elegant und wirklich nicht sehr kompliziert.

Wo kann man etwas über RANGE lernen? Natürlich in unserem Fensterfunktionen Kurs. Dort lernen Sie nicht nur die RANGE-Klausel, sondern alle Aspekte der Fensterfunktionen kennen. Sie können den Kurs auch nutzen, um Ihre SQL-Kenntnisse zu trainieren. Wenn Sie mehr wissen wollen, erfahren Sie in diesem Artikel alles über den Kurs und seine Möglichkeiten.