ROWS & RANGE Criterion

Teil des Abschnitts Grundlagen der SQL-Journey von Coddy — Lektion 69 von 72.

Bisher konnten wir nicht flexibel wählen, wie viele Zeilen vor oder nach zu berücksichtigen sind. Jetzt ist es möglich mit ROWS & RANGE Kriterien. 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 ROWS-Kriterium keine Werte berücksichtigt, sondern nur die Positionen, während RANGE das Fenster in Bezug auf Wertebereiche statt Zeilenpositionen definiert.

Für RANGE müssen wir ORDER BY --column_name-- angeben, weil es sonst nicht wüsste, wie es das Fenster wählen soll.

Zum Beispiel:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Hier erstellt es ein Fenster, das die aktuelle Zeile, die Zeile davor und die Zeile danach enthält.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

Hier erstellt es ein Fenster, das für jede Stufe (sortiert in aufsteigender Reihenfolge) die aktuelle Stufe, eine Stufe davor und eine Stufe danach enthält. Wenn die aktuelle Stufe 5 ist, enthält es die Stufen 4, 5 und 6.

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

Auch RANGE unterstützt keine Datumsspalten.

Hier ist ein einfaches Beispiel, um ROWS vs RANGE zu veranschaulichen:

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

Aufgabe

Einfach

Verfügbare Tabellen und Spalten:
newspapers: date, num_newspapers

Für diese Herausforderung haben wir Daten zu produzierten Zeitungen. Wir möchten den Durchschnitt der gedruckten Zeitungen zwei Tage vor der aktuellen Zeile und einen Tag danach kennen. Außerdem möchten wir den Unterschied zwischen der maximalen und minimalen Anzahl gedruckter Zeitungen vom aktuellen Datum und allen drei Tagen davor wissen. Nennen Sie diese Spalten avg_newspapers und diff_newspapers jeweils.

Spickzettel

Verwenden Sie ROWS und RANGE, um flexible Fensterrahmen in Fensterfunktionen zu definieren:

ROWS – definiert das Fenster nach Zeilenpositionen:

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

RANGE – definiert das Fenster nach Wertebereichen (erfordert ORDER BY):

OVER (ORDER BY column_name RANGE BETWEEN --START-- AND --END--)

Optionen für Fenstergrenzen:

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

Beispiele:

-- ROWS: includes current row + 1 before + 1 after (by position)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- RANGE: includes values within range (by value)
ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Wichtige Unterschiede:

ROWS – basierend auf Zeilenpositionen, immer gleiche Anzahl von Zeilen
RANGE – basierend auf Werten, kann mehr Zeilen mit gleichen Werten einschließen
RANGE unterstützt keine Datumsspalten

Probier es selbst