Was tun FIRST_VALUE und LAST_VALUE?
Wie schon die in den Wochen 12 bis 14 vorgestellten ROW_NUMBER und RANK-Funktionen, gehören auch FIRST_VALUE und LAST_VALUE zu den Window-Funktionen. Das heißt sie sind Funktionen, die auf einer geordneten Menge von Zeilen in der Ergebnismenge operieren. Den Namen entsprechend geben die Funktionen die erste beziehungsweise letzte Zeile des Fensters zurück, auf dem sie operieren.
Wie könnt ihr mit FIRST_VALUE und LAST_VALUE arbeiten?
Um erste und letzte Zeilen eines Ergebnisfensters zu ermitteln, nehmen wir uns in der WideWorldImporters-Datenbank die Sales.Order-Tabelle vor, um die erste Bestellung jedes Kunden zu finden. Folgende Spalten der Tabelle wollen wir uns ansehen:
SELECT
OrderDate
,OrderID
,ExpectedDeliveryDate
,CustomerID
FROM [Sales].[Orders]
Um nun für jede Bestellung die erste Bestellung des jeweiligen Kunden zu finden, benutzen wir die FIRST_VALUE-Funktion. Diese Funktion berechnet nun zu jeder Zeile in der Orders-Tabelle die entsprechenden ersten Werte:
SELECT
OrderDate as DateCurrentOrder
,FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as DateFirstOrder
,OrderID as IDCurrentOrder
,FIRST_VALUE(OrderID) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as IDirstOrder
,ExpectedDeliveryDate as ExpectedDeliveryCurrentOrder
,FIRST_VALUE(ExpectedDeliveryDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as ExpectedDeliveryFirstOrder
,CustomerID
FROM [Sales].[Orders]
ORDER BY CustomerID
Nun können wir mit Hilfe dieser Werte beispielsweise zu jeder Bestellung berechnen, wie viele Tage nach der ersten Bestellung sie aufgegeben wurde:
SELECT
OrderDate as DateCurrentOrder
,FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as DateFirstOrder
,DATEDIFF(DAY, FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate), OrderDate) DaysSinceFirstOrder
,OrderID as IDCurrentOrder
,FIRST_VALUE(OrderID) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as IDirstOrder
,ExpectedDeliveryDate as ExpectedDeliveryCurrentOrder
,FIRST_VALUE(ExpectedDeliveryDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as ExpectedDeliveryFirstOrder
,CustomerID
FROM [Sales].[Orders]
ORDER BY CustomerID
Auf die selbe Art können wir nun natürlich auch die Tage von der Bestellung zur jeweils letzten Bestellung des Kunden ermitteln, indem wir LAST_VALUE verwenden:
SELECT
OrderDate as DateCurrentOrder
,FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as DateFirstOrder
,LAST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) DateLastOrder
,DATEDIFF(DAY, FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate), OrderDate) DaysSinceFirstOrder
,OrderID as IDCurrentOrder
,FIRST_VALUE(OrderID) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as IDirstOrder
,ExpectedDeliveryDate as ExpectedDeliveryCurrentOrder
,FIRST_VALUE(ExpectedDeliveryDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as ExpectedDeliveryFirstOrder
,CustomerID
FROM [Sales].[Orders]
ORDER BY CustomerID
Doch zu unserer großen Überraschung sehen wir nun als LAST_VALUE immer den Wert der aktuellen Zeile als Letztes Bestelldatum des Kunden. Das liegt daran, dass wir schon in den ersten Beiträgen zu Window-Funktionen erklärt hatten, dass das Standard-Fenster immer vom Anfang des Definitionsbereichs bis zur aktuellen Zeile heranreicht. Um das zu ändern müssen eine sogenannte “range clause” angeben, die die Form ROWS BETWEEN a AND b hat:
SELECT
OrderDate as DateCurrentOrder
,FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as DateFirstOrder
,LAST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) DateLastOrder
,DATEDIFF(DAY, FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate), OrderDate) DaysSinceFirstOrder
,OrderID as IDCurrentOrder
,FIRST_VALUE(OrderID) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as IDirstOrder
,ExpectedDeliveryDate as ExpectedDeliveryCurrentOrder
,FIRST_VALUE(ExpectedDeliveryDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as ExpectedDeliveryFirstOrder
,CustomerID
FROM [Sales].[Orders]
ORDER BY CustomerID
Mit diesem Hintergrundwissen können wir nun auch den Abstand zur jeweils aktuellsten Bestellung des Kunden angeben:
SELECT
OrderDate as DateCurrentOrder
,FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as DateFirstOrder
,LAST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) DateLastOrder
,DATEDIFF(DAY, FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate), OrderDate) DaysSinceFirstOrder
,DATEDIFF(DAY, OrderDate, LAST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) DaysToLastOrder
,OrderID as IDCurrentOrder
,FIRST_VALUE(OrderID) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as IDirstOrder
,ExpectedDeliveryDate as ExpectedDeliveryCurrentOrder
,FIRST_VALUE(ExpectedDeliveryDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as ExpectedDeliveryFirstOrder
,CustomerID
FROM [Sales].[Orders]
ORDER BY CustomerID
FIRST_VALUE und LAST_VALUE sind echt nützliche Funktionen, trotz allem solltet ihr bei den hier gezeigten Techniken vorsichtig sein, denn Fenster-Funktionen sind potenzielle Performance-Killer, da im Zweifel ein Vielfaches der zurückgegebenen Ergebnismenge vom Server gelesen und verarbeitet werden muss um die Fenster komplett auszuführen.
