Fensterrahmen

Im Kontext der SQL-Window-Funktionen legt das „Fenster“ die Teilmenge der Zeilen fest, die von der SQL-Funktion bei den Berechnungen betrachtet werden.

Anders gesagt ist das Fenster eine dynamische Menge von Zeilen, die über dein Abfrageergebnis "gleitet" und für jede Zeile unterschiedliche Datenmengen bildet, abhängig von der von dir festgelegten Fensterdefinition.

Fenster vs. Partitionen

Auch wenn die Begriffe "Fenster" und "Partition" ähnlich klingen, stehen sie für unterschiedliche Konzepte:

• Partition (PARTITION BY). Das ist die Aufteilung der gesamten Ergebnismenge in nicht überlappende Teilmengen, wobei jede Teilmenge Zeilen mit gleichen Werten in einer oder mehreren Spalten enthält. Window-Funktionen werden auf jede Partition separat angewendet, so als wäre jede von ihnen eine eigene Datenmenge.

  

• Fenster. Es legt fest, welche konkreten Zeilen in jeder Partition zur Berechnung der Window-Funktion für jede Zeile verwendet werden. Das Fenster kann sich von Zeile zu Zeile ändern.

  Wird zum Beispiel die Regel ROWS BETWEEN 1 PRECEDING AND CURRENT ROW verwendet, besteht das Fenster für jede Zeile aus ihr selbst und einer vorhergehenden Zeile. Das ist sozusagen eine "Unter-Partition" innerhalb der bereits existierenden Partition.

  

  Das heißt:

  • Unser erstes Fenster besteht nur aus 1 Datensatz, weil es keinen vorhergehenden Datensatz gibt. Dieser einzige Datensatz wird an die Aggregatfunktion AVG(price) übergeben und das Ergebnis wird in das Feld avg_price eingetragen.
  • Unser zweites Fenster enthält bereits den 1. und 2. Datensatz, die an AVG(price) übergeben werden und (170 + 220) / 2 = 195 zurückgeben.
  • Unser drittes Fenster enthält den 2. und 3. Datensatz, das Ergebnis ist (220 + 150) / 2 = 185.
  • und so weiter.

Anmerkung zu Fenstern ohne ROWS/RANGE

Fehlt in der Definition der Window-Funktion ROWS/RANGE, fällt das Fenster standardmäßig mit der Partition zusammen. In diesem Fall verarbeitet die Window-Funktion alle Zeilen innerhalb der Partition und beschränkt sich nicht auf eine Teilmenge. Das bedeutet, dass das Ergebnis der Funktion für alle Zeilen innerhalb derselben Partition gleich ist.

Definition der Fenstergrenzen

Mit der Syntax ROWS oder RANGE können wir festlegen, welches Datenfenster genau an die Window-Funktion zur Berechnung des Werts für die aktuelle Zeile übergeben wird.

Die Syntax zur Definition der Fenstergrenzen sieht aus wie die Angabe eines Bereichs relativ zur aktuellen Zeile.

MySQL 8.1
SELECT <window_funktion>(<tabellenfeld>)
OVER (
      ...
      ROWS|RANGE BETWEEN <anfang der fenstergrenze> AND <ende der fenstergrenze>
)

Wenn wir zum Beispiel wollen, dass bei den Berechnungen nur die zwei vorhergehenden Datensätze und die aktuelle Zeile in die Window-Funktion einfließen, sieht die Syntax so aus:

MySQL 8.1
... ROWS|RANGE BETWEEN 2 PRECEDING AND CURRENT ROW

Wenn wir wollen, dass die aktuelle Zeile und alle nachfolgenden an die Window-Funktion übergeben werden, sieht die Syntax so aus:

MySQL 8.1
... ROWS|RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Mögliche Definitionen der Fenstergrenzen

• UNBOUNDED PRECEDING, alle Zeilen, die der aktuellen vorangehen
• N PRECEDING, N Zeilen vor der aktuellen Zeile
• CURRENT ROW, die aktuelle Zeile
• N FOLLOWING, N Zeilen nach der aktuellen Zeile
• UNBOUNDED FOLLOWING, alle nachfolgenden Zeilen

Schema zur Definition der Fenstergrenzen

Unterschied zwischen ROWS und RANGE

Zur Definition der Fenstergrenzen werden die Schlüsselwörter ROWS und RANGE verwendet. Sie funktionieren unterschiedlich:

ROWS

• Basiert auf physischen Zeilen:

  Bei der Verwendung von ROWS basiert die Fensterdefinition auf der physischen Position der Zeilen relativ zur aktuellen Zeile. Zum Beispiel bedeutet 1 PRECEDING eine Zeile vor der aktuellen.

• Exakte Grenze:

  Die Definition des Fensters mit ROWS begrenzt die Anzahl der ins Fenster aufgenommenen Zeilen exakt und macht es vorhersagbar und konkret.

RANGE

• Basiert auf Werten:

  RANGE legt im Gegensatz zu ROWS die Fenstergrenzen auf Basis der Spaltenwerte fest, die gemäß ORDER BY in der Window-Funktion geordnet sind.

• Dynamische Grenzen:

  Die mit RANGE festgelegten Grenzen können je nach Daten variieren, was das Fenster flexibel macht, aber möglicherweise weniger vorhersagbar.

Lass uns prüfen, ob du den Unterschied zwischen ROWS und RANGE richtig verstanden hast:

Welche der folgenden Aussagen beschreibt den Unterschied zwischen der Verwendung von ROWS und RANGE im Kontext der SQL-Window-Funktionen am besten?