LEAD-&-LAG-Funktionen
Teil des Abschnitts Grundlagen der SQL-Journey von Coddy. Lektion 61 von 72.
Mit den Funktionen LEAD und LAG können wir auf den Wert der aktuellen Zeile n Schritte zurück oder n Schritte voraus zugreifen.
Wenn wir beispielsweise das Verhältnis des Umsatzes eines Unternehmens für die aktuelle Zeile und vor einem Monat berechnen möchten, können wir den Wert des vorherigen Monats extrahieren:
| id | revenue | month |
| 1 | 532 | 5 |
| 2 | 492 | 6 |
| 3 | 393 | 7 |
| 4 | 723 | 8 |
SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY MONTH) as prev_month_revenue
FROM table1 ORDER BY idDadurch wird die folgende Tabelle erstellt:
| id | revenue | prev_month_revenue |
| 1 | 532 | |
| 2 | 492 | 532 |
| 3 | 393 | 492 |
| 4 | 723 | 393 |
So können wir das Verhältnis prev_month_revenue/revenue berechnen.
Wenn wir stattdessen die Funktion LEAD verwenden würden, würde sie den Umsatz des jeweils nächsten Monats jeder Zeile übernehmen:
SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY id| id | revenue | next_month_revenue |
| 1 | 532 | 492 |
| 2 | 492 | 393 |
| 3 | 393 | 723 |
| 4 | 723 |
Aufgabe
EinfachVerfügbare Tabellen und Spalten:
air_conditioners:id,efficiency,strength,month
Vergleiche die Effizienz jeder Klimaanlage mit der vorherigen.
Schreibe eine Abfrage, die die Effizienz jeder Klimaanlage zusammen mit der Effizienz der zuvor installierten Klimaanlage (basierend auf der id) anzeigt. Berechne außerdem den Effizienzunterschied zwischen der aktuellen und der vorherigen Klimaanlage. Sortiere das Ergebnis aufsteigend nach id und efficiency.
Erwartete Ausgabespalten:
idefficiencyprevious_efficiency(mit LAG)efficiency_difference(aktuelle Effizienz - vorherige Effizienz)
Umschließe die Abfrage schließlich und filtere die erste Zeile heraus, deren previous_efficiency (und daher auch efficiency_difference) leer ist, also NULL:
SELECT * FROM (
-- Deine Abfrage hier
)
WHERE previous_efficiency IS NOT NULLProbier es selbst
Teste dich selbstDiese 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 3
2Bedingungen
Grundlagen der BedingungenDas Schlüsselwort ANDDas Schlüsselwort ORDas Schlüsselwort NOTMehrere kombinierte BedingungenKlammernBoolesche Werte5Arithmetische Operationen
Mathematische OperatorenMathematische SpaltenDie Modulo-OperationDie ROUND()-Funktion8Statistik
Integrierte Aggregatfunktionen Teil 1Integrierte Aggregatfunktionen Teil 2Gruppierung Teil 1Gruppierung Teil 2Unterabfragen Teil 1Unterabfragen Teil 2Rückblick – Total Gain ShopRückblick – Scooter ShopRückblick – Coffee Shop11Fensterfunktionen Teil 1
ROW_NUMBER-FunktionORDER-BY-KriteriumPARTITION-BY-KriteriumPARTITION & ORDERLEAD-&-LAG-FunktionenRückblick – LEAD & LAGRückblick – BilderRückblick – Boxen3Spezifisches 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 erstellenÜbe selbstständig: SQL-Playground