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!
