Funkcje LAG i LEAD w TSQL

SQL Server 2012 przynosił ze sobą wiele użytecznych zmian. Jednymi z moich ulubionych funkcji wprowadzonych w tamtej wersji są LAG i LEAD, które pokrótce chciałbym opisać w ramach niniejszego posta – zapraszam!

Zapewne wielu z Was zna bardzo dobrze funkcje okna takie jak RANK czy ROW_NUMBER – mógłbym również zaryzykować tezę, iż funkcje te większość z Was lubi i używa – tutaj właśnie pojawia się pierwsza dobra wiadomość! LAG i LEAD również są funkcjami okna! Co za tym idzie wymagają aby użyć składnika OVER(ORDER BY) i pozwalają odnosić się (tak jak wskazuje nazwa) do wiersza poprzedzającego i następującego po  bieżącym wierszu. Zilustrujmy to na przykładzie.

Tradycyjnie w naszej demonstracji będziemy używać bazy AdventureWorks2012DW (oczywiście możecie ją ściągnąć z Codeplex).

SELECT
   [r].[ResellerName],
   [d].[CalendarYear],
   [d].[MonthNumberOfYear],
   SUM([f].[SalesAmount]) as [SalesAmount] 
FROM
   [dbo].[FactResellerSales] AS [f]
JOIN 
   [dbo].[DimDate] AS [d]
ON 
   [d].[FullDateAlternateKey]=[f].[OrderDate]
JOIN 
    [DimReseller] AS [r]
ON 
    [r].[ResellerKey]=[f].[ResellerKey]
WHERE 
   [f].[ResellerKey]=2
GROUP BY
    [r].[ResellerName],
    [d].[CalendarYear],
    [d].[MonthNumberOfYear]
ORDER BY 
    [r].[ResellerName],
    [d].[CalendarYear],
    [d].[MonthNumberOfYear]

Powyższe zapytanie zwraca wartość sprzedaży w danym miesiącu kalendarzowym dla konkretnego detalisty. W rezultacie otrzymujemy zestaw ośmiu wierszy jak to zostało przedstawione poniżej:

Co natomiast gdy chcemy odwołać się do poprzedniego wiersza z poziomu wiersza bieżącego? Tutaj z pomocą przychodzi nam bohater dzisiejszego artykułu czyli funkcja LAG:

SELECT
	[r].[ResellerName],
	[d].[CalendarYear],
	[d].[MonthNumberOfYear],
	SUM([f].[SalesAmount]) AS [SalesAmount],
	LAG(SUM([f].[SalesAmount])) OVER (ORDER BY [r].[ResellerName],[d].[CalendarYear],[d].[MonthNumberOfYear]) AS PreviousPeriodSalesAmount
FROM
	[dbo].[FactResellerSales] AS [f]
JOIN 
	[dbo].[DimDate] AS [d]
ON 
	[d].[FullDateAlternateKey]=[f].[OrderDate]
JOIN 
	[DimReseller] AS [r]
ON 
	[r].[ResellerKey]=[f].[ResellerKey]
WHERE 
	[f].[ResellerKey]=2
GROUP BY
	[r].[ResellerName],
	[d].[CalendarYear],
	[d].[MonthNumberOfYear]
ORDER BY 
	[r].[ResellerName],
	[d].[CalendarYear],
	[d].[MonthNumberOfYear]

Świetnie! Jak widać posługiwanie się funkcją LAG jest proste i intuicyjne – przy jej pomocy możemy budować naprawdę wyrafinowane zapytania jak m.in agregacje akumulacyjne czy też porównania rok do roku. Można już odłożyć w niepamięć podzapytania inline i nadmierne złączenia aby osiągnąć pożądany efekt.

W bardzo podobny sposób działa druga z naszych dzisiejszych bohaterek czyli funkcja LEAD – wskazuje ona na wiersz następujący po bieżącym wierszu. Obie funkcje wyposażone są w dodatkowy parametr mówiący o tym o ile wierszy w tył/przód chcemy się cofnąć. Domyślnie parametr ten jest równy 1 – widzieliśmy to powyżej – zmodyfikujmy to zachowanie jawnie wskazując, iż chcemy cofnąć się o dwa wiersze:

SELECT
	[r].[ResellerName],
	[d].[CalendarYear],
	[d].[MonthNumberOfYear],
	sum([f].[SalesAmount]) AS [SalesAmount],
	LAG(sum([f].[SalesAmount]),2) OVER(ORDER BY [r].[ResellerName],[d].[CalendarYear],[d].[MonthNumberOfYear]) as SalesAmountOffset2
FROM
	[dbo].[FactResellerSales] AS [f]
JOIN 
	[dbo].[DimDate] AS [d]
ON 
	[d].[FullDateAlternateKey]=[f].[OrderDate]
JOIN 
	[DimReseller] AS [r]
ON 
	[r].[ResellerKey]=[f].[ResellerKey]
where 
	[f].[ResellerKey]=2
GROUP BY
	[r].[ResellerName],
	[d].[CalendarYear],
	[d].[MonthNumberOfYear]
ORDER BY 
	[r].[ResellerName],
	[d].[CalendarYear],
	[d].[MonthNumberOfYear]

Tak właśnie otrzymujemy pożądany rezultat:

Świetnie! Oczywiście jeśli nie chcemy pokazywać wartości NULL to standardowo możemy je obsłużyć funkcją ISNULL lub też COALESCE – możemy również podać trzeci parametr, którego wartość zostanie podstawiona zamiast wartości NULL.

LAG(sum(f.SalesAmount),2,0) over(ORDER BY ResellerName,CalendarYear,d.MonthNumberOfYear)

Proste, intuicyjne i mam nadzieję, że dla Was użyteczne.

Zapraszam do zapoznania się z innymi artykułami z tej serii:

• ROW_NUMBER, RANK, DENSE_RANK oraz NTILE
• Agregacja z OVER, PARTITION BY, ROWS BETWEEN
• Optymalizacja funkcji okna z wykorzystaniem tradycyjnych indeksów