ROWS- & RANGE-Kriterium

Bis jetzt können wir bei der Auswahl, wie viele Zeilen davor oder danach berücksichtigt werden sollen, nicht flexibel sein. Jetzt ist dies mit den Kriterien ROWS & RANGE möglich. Um sie zu verwenden, schreiben wir:

OVER (ROWS BETWEEN --START-- AND --END--)

OVER (RANGE BETWEEN --START-- AND --END--)

Und wir können die folgenden Optionen angeben:

• CURRENT ROW – die aktuelle Zeile
• n PRECEDING – Zeilen vor der aktuellen Zeile
• n FOLLOWING – Zeilen nach der aktuellen Zeile 

Der Unterschied zwischen ROWS & RANGE besteht darin, dass das Kriterium ROWS die Werte nicht berücksichtigt, sondern nur die Positionen, während RANGE das Fenster anhand von Wertebereichen statt von Zeilenpositionen definiert.

Für RANGE müssen wir ORDER BY --column_name-- angeben, denn andernfalls wüsste es nicht, wie das Fenster ausgewählt werden soll.

Zum Beispiel:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Hier wird ein Fenster erstellt, das die aktuelle Zeile, die Zeile davor und die Zeile danach umfasst.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

Hier wird für jedes levels (aufsteigend sortiert) ein Fenster erstellt, das die aktuelle Ebene, eine Ebene davor und eine Ebene danach umfasst. Wenn die aktuelle Ebene 5 ist, umfasst es die Ebenen 4, 5 und 6.

Hinweis: Die Verwendung von RANGE BETWEEN kann dazu führen, dass mehr Zeilen in dein Fenster aufgenommen werden, da sie alle Zeilen einschließt, die dieselben Werte wie diejenigen im Bereich haben, während ROWS BETWEEN immer dieselbe Anzahl von Zeilen einschließt (sofern sie im Datensatz verfügbar sind). 

Außerdem unterstützt RANGE keine Datumsspalten.

Hier ist ein einfaches Beispiel zur Veranschaulichung von ROWS im Vergleich zu RANGE:

Verwendung von ROWS:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg_salary_rows

Verwendung von RANGE:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
    ) as avg_salary_range