Level 19: Fensterfunktionen: LAG, LEAD und Rahmen

Lektion

Eine Zeile mit der vorherigen zu vergleichen ist eine häufige Frage: Ist die Temperatur seit dem Vortag gestiegen? Ist dieser Monat besser als der vorherige? LAG(Spalte) OVER (ORDER BY …) liefert den Wert der vorherigen Zeile in der angegebenen Reihenfolge; LEAD liefert den der nächsten Zeile. temp_max - LAG(temp_max) OVER (ORDER BY day) ergibt die Veränderung gegenüber dem Vortag.

Die erste Zeile hat keine vorherige: LAG ist dort NULL, und die Veränderung auch. Ebenso ist LEAD in der letzten Zeile NULL. LAG(x, 1, 0) liefert 0 statt NULL, und LAG(x, 2) geht zwei Zeilen zurück.

Mit PARTITION BY bleiben LAG und LEAD innerhalb der Gruppe: Der erste Verkauf jedes Verkäufers hat keinen vorherigen, selbst wenn der Verkauf eines anderen Verkäufers in der Tabelle davor steht.

Man kann auch genau auswählen, welche Zeilen in die Berechnung eingehen: Das ist der Rahmen (Frame). ROWS BETWEEN 2 PRECEDING AND CURRENT ROW nimmt die aktuelle Zeile und die beiden vorherigen. AVG(temp_max) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ergibt einen gleitenden Durchschnitt über 3 Tage, der die Schwankungen glättet. Die ersten beiden Zeilen haben weniger Nachbarn: Ihr Durchschnitt umfasst 1, dann 2 Werte.

Es gibt zwei Arten, einen Rahmen abzugrenzen. ROWS zählt Zeilen. RANGE denkt in Sortierwerten: Gleichstehende Zeilen kommen gemeinsam hinzu oder fallen gemeinsam weg. Ohne angegebenen Rahmen verwendet ein OVER mit ORDER BY RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Das erklärt die laufenden Summen bei Gleichstand aus Level 18. Für ein gleitendes Fenster schreibe immer ROWS.

Wie bei Rängen filtert man nicht im WHERE derselben Abfrage nach LAG: Man berechnet es in einer CTE und filtert dann danach, zum Beispiel um die Monate mit Anstieg zu finden.

Syntax

SELECT d,
       x,
       x - LAG(x) OVER (ORDER BY d) AS veraenderung,
       AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS mittel3
FROM meine_tabelle;

Kommentiertes Beispiel

SELECT month, amount, LAG(amount) OVER (ORDER BY month) AS previous
FROM sales
WHERE seller = 'Ana';

Jeder Monat von Ana zeigt den Betrag des Vormonats; der Januar hat keinen (NULL).

Ergebnis des Beispiels

Das Wichtigste

• LAG = vorherige Zeile, LEAD = nächste Zeile.
• Die erste (oder letzte) Zeile ergibt NULL.
• ROWS BETWEEN n PRECEDING AND CURRENT ROW = gleitendes Fenster.

Häufige Fallen

• Ohne ORDER BY in OVER hat „vorherige“ keinen Sinn.
• WHERE amount > LAG(amount) … in derselben Abfrage ist nicht erlaubt: Gehe über eine CTE.

Weiterführend

FIRST_VALUE(x) und LAST_VALUE(x) liefern den ersten und den letzten Wert des Rahmens.

Die 5 Übungen des Levels

1. Geführt · Zeige für Paris jeden Tag, die Höchsttemperatur und ihre Veränderung gegenüber dem Vortag (change). (Tabelle: readings)
2. Training · Zeige für jedes Abspielen den Nutzer, die Uhrzeit des Abspielens und die Uhrzeit seines nächsten Abspielens (next_play). (Tabelle: plays)
3. Training · Zeige für jede Stadt und jeden Tag die Höchsttemperatur und ihren Durchschnitt über die letzten 3 Tage (der Tag selbst und die 2 davor), auf eine Nachkommastelle gerundet (avg3). (Tabelle: readings)
4. Training · Berechne über alle Städte hinweg die laufende Regensumme Tag für Tag auf zwei Arten: cum_range mit dem Standardrahmen (OVER (ORDER BY day)) und cum_rows Zeile für Zeile, mit ROWS und einer Sortierung nach Tag und dann nach Stadt. Zeige Stadt, Tag, Regen und beide laufenden Summen, sortiert nach Tag und dann nach Stadt. (Tabelle: readings)
5. Herausforderung · Zeige die Monate, in denen ein Verkäufer besser war als im Vormonat: Verkäufer, Monat und Anstieg (growth). (Tabelle: sales)

In SpeedQL wird jede Abfrage sofort geprüft: Ihr Ergebnis wird mit dem erwarteten verglichen, dann läuft die Abfrage noch einmal auf einer versteckten Kontrolldatenbank. Zu jeder Übung gibt es schriftliche Hinweise, die du nur bei Bedarf aufrufst.