Was sind LEAD und LAG?
In der vergangenen Woche hattet ihr gesehen, wie ihr mit FIRST_VALUE
und LAST_VALUE
die ersten und letzten Zeilen in einem Abfragefenster geben lasst. Doch wie geht ihr vor, wenn ihr eine Zeile der Sales.Order-Tabelle mit der vorangegangenen Bestellung des Kunden vergleichen möchtet? Dann sind LEAD
und LAG
das Mittel der Wahl. LEAD
gibt euch die nächste Zeile im Ergebnisfenster zurück, LAG
liefert hingegen die vorangegangene Zeile.
Wie könnt ihr mit LEAD und LAG arbeiten?
Wir starten wie schon in der letzten Woche wieder mit einer Abfrage auf die Sales.Order
-Tabelle und sehen uns einige Spalten an:
SELECT OrderDate ,OrderID ,ExpectedDeliveryDate ,CustomerID FROM [Sales].[Orders]
Nun fügen wir eine weitere Spalte hinzu in der wir mit der LAG
-Funktion das Datum der vorangegangenen Zeile im Fenster, das nach Kunden partitioniert ist, zurückgeben. Die Syntax ist dabei sehr ähnlich zu der von FIRST_VALUE
und LAST_VALUE
, allerdings bekommt die LAG
-Funktion (genauso wie die LEAD
-Funktion) noch eine ganze Zahl als Argument, das angibt, um wieviele Zeilen ihr zurückgehen möchtet (im Beispiel gehen wir um eine Zeile zurück, übergeben also die 1 als Argument):
SELECT OrderDate ,LAG(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) As DatePreviousOrder ,OrderID ,ExpectedDeliveryDate ,CustomerID FROM [Sales].[Orders]
Die erste Zeile für jeden Kunden hat dabei in der neu berechneten DatePreviousOrder
-Spalte keinen Eintrag (also NULL
als Wert), was daran liegt, dass die erste Bestellung jedes Kunden natürlich keinen Vorgänger hat.
Wie schon FIRST_VALUE
und LAST_VALUE
, könnt ihr natürlich auch LEAD
und LAG
in Funktionen verwenden:
SELECT OrderDate ,LAG(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) As DatePreviousOrder ,DATEDIFF(DAY, LAG(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate), OrderDate) As DaysSincePreviousOrder ,OrderID ,ExpectedDeliveryDate ,CustomerID FROM [Sales].[Orders]
Mit LEAD
funktioniert die Abfrage genauso nur dass hier die letzte Zeile keinen Wert in der neu berechneten Spalte hat:
SELECT OrderDate ,LAG(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) As DatePreviousOrder ,DATEDIFF(DAY, LAG(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate), OrderDate) As DaysSincePreviousOrder ,LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) As DateNextOrder ,DATEDIFF(DAY, OrderDate, LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) As DaysUnitlNextOrder ,OrderID ,ExpectedDeliveryDate ,CustomerID FROM [Sales].[Orders]
Ihr habt nun also das Werkzeug in der Hand, um in Tabellen mit zeitlich abhängigen Daten, Analysen durchzuführen, ihr könnt einerseits herausfinden, wann eine Aufzeichnung beginnt und andererseits auch, wie sie sich im Vergleich zum Vorwert verändert hat. Der Auswertung von Bestellungen, Messwerten oder anderen Datenreihen auch außerhalb dieses Dojos steht nun nichts mehr im Weg. Schnappt sie euch, Ninjas!