Menu
CoddyTech

ROWS- & RANGE-Kriterium

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

Bislang 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 sich nicht um die Werte kümmert, sondern nur um die Positionen, während RANGE das Fenster anhand von Wertebereichen statt anhand von Zeilenpositionen definiert.

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

Zum Beispiel:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Hier wird ein Fenster erstellt, das die aktuelle Zeile, die vorherige Zeile und die nachfolgende Zeile enthält.

ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Hier wird ein Fenster erstellt, das für jede Stufe (aufsteigend sortiert) die aktuelle Stufe, die vorherige und die nächste Stufe umfasst. Wenn die aktuelle Stufe 5 ist, umfasst es die Stufen 4, 5 und 6.

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

Außerdem führt ein RANGE-Offset bei als Text gespeicherten Datumsangaben (wie '2024-01-05') keine Datumsberechnung durch: Bei 1 PRECEDING enthält das Fenster nur die Zeilen mit demselben Datum. Um Tage zu zählen, sortiere stattdessen nach einer Zahl, zum Beispiel julianday(date).

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

Mit 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
challenge icon

Aufgabe

Einfach

Verfügbare Tabellen und Spalten:

  • newspapers: date, num_newspapers

Für diese Herausforderung liegen uns Daten zur Zeitungsproduktion vor. Wir möchten die durchschnittliche Anzahl der Zeitungen wissen, die zwei Tage vor der aktuellen Zeile und einen Tag danach gedruckt wurden. Außerdem möchten wir die Differenz zwischen der maximalen und der minimalen Anzahl der Zeitungen wissen, die am aktuellen Datum und an den drei Tagen davor gedruckt wurden. Nenne diese Spalten entsprechend avg_newspapers und diff_newspapers.

Probier es selbst

quiz iconTeste dich selbst

Diese Lektion enthält ein kurzes Quiz. Starte die Lektion, um es zu beantworten und deinen Fortschritt zu speichern.

Alle Lektionen in Grundlagen

Übe selbstständig: SQL-Playground