Funkcje LAG i LEAD w TSQL

LagAndLeadInTSQL_00

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).

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:

result

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:

result

Ś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:

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

result

Ś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.

result

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

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

 

 

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

Leave a Comment

Your email address will not be published. Required fields are marked *