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 Zeilen PRECEDING– Zeilen vor der aktuellen Zeilen 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 FOLLOWINGHier 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 FOLLOWINGHier 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_rowsVerwendung von RANGE:
SELECT employee_name, salary,
AVG(salary) OVER (
ORDER BY salary
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
) as avg_salary_rangeAufgabe
EinfachVerfü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
Diese Lektion enthält ein kurzes Quiz. Starte die Lektion, um es zu beantworten und deinen Fortschritt zu speichern.
Alle Lektionen in Grundlagen
4Weitere Schlüsselwörter
Das IN-SchlüsselwortDas BETWEEN-SchlüsselwortDas LIKE-SchlüsselwortDas AS-SchlüsselwortRückblick – Handymodelle7Datumsangaben
Datumsangaben behandeln – Teil 1Datumsangaben behandeln – Teil 2Datumsangaben behandeln – Teil 32Bedingungen
Grundlagen der BedingungenDas Schlüsselwort ANDDas Schlüsselwort ORDas Schlüsselwort NOTMehrere kombinierte BedingungenKlammernBoolesche Werte5Arithmetische Operationen
Mathematische OperatorenMathematische SpaltenDie Modulo-OperationDie ROUND()-Funktion3Spezifisches Rückgabeformat
NULL-WerteErgebnisse sortieren Teil 1Ergebnisse sortieren Teil 2Zusammenfassung - Cyber Security FirmAnzahl der Datensätze begrenzenZusammenfassung - Vehicle Factory6Einführungsaufgaben
Wiederholung – ParlamentswahlWiederholung – Festnahme eines Straftäters durch die PolizeiWiederholung – Getränkebehälter an der BarWiederholung – Neue Spalten erstellen9Mehrere Tabellen
Grundlegender Join Teil 1Grundlegender Join Teil 2Rückblick – JoinSelf-JoinRückblick – Self-JoinUnionAbfragen vereinfachen, WITH-SchlüsselwortRückblick – WITH-AbfragenRückblick – Immobilienauftragnehmer12Fensterfunktionen Teil 2
RANK- & DENSE_RANK-FunktionenWiederholung – RANK & DENSE_RANKNTILE-FunktionAggregationsfunktionenROWS- & RANGE-KriteriumÜbe selbstständig: SQL-Playground