Potęga funkcji okna – SELECT LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Z dokumentacji technet:
https://msdn.microsoft.com/en-us/library/hh231256.aspx
https://msdn.microsoft.com/en-us/library/hh213125.aspx
https://msdn.microsoft.com/en-us/library/hh213018.aspx
https://msdn.microsoft.com/en-us/library/hh231517.aspx

LAG provides access to a row at a given physical offset that comes before the current row.
LEAD provides access to a row at a given physical offset that follows the current row.
FIRST_VALUE returns the first value in an ordered set of values.
LAST_VALUE returns the last value in an ordered set of values.

Uwielbiam Window Functions. Użyte w odpowiedni sposób potrafią istotnie przyspieszyć pisanie zapytań jak i ich wydajność. Dzisiaj odpowiemy sobie na pytanie jak dostać się do wartości dowolnego wiersza…będąc w innym 🙂

Bardzo często występuje potrzeba wyliczania wartości, które w czasie agregacji mogą być zależne od innych występujących w danej partycji (naszym polu, po którym „agregujemy okienka”, czyli patrzymy z perspektywy konkretnej wartości)

Najprostszym przykładem jest wyliczanie różnicy np kwot w stosunku do poprzedniej i następnej transakcji oraz pierwszej i ostatniej.
Użyjemy do tego właśnie omawianych funkcji. Dodatkowo dwie z nich można użyc do „przewijania” okna danych i uzyskania informacji jaka wartośc byla w konkretnej kolumnie np 2 czy 3 wiersze przed obecnym (LAG z offsetem) lub po (LEAD z offsetem)

Naszym oknem dla partycji będą unikalne wartości w polu Sprzedawca. To pole zatem użyjemy w PARTITION wewnątrz polecenia OVER.
Natomiast transakcje sortujemy od najstarszej, zatem pole Data użyjemy w ORDER wewnątrz polecenia OVER.

Zobaczcie:

Wynik:

Jak widać dla funkcji LAG i LEAD jeśli nie ma poprzednika/następnika to wstawiana jest wartość NULL

Wspominałem o offsecie, oto przykład:

Wynik:

 

To tylko proste przykłady, które pokazują jedną z potrzeb dostępu do danych powyżej/poniżej wiersza, w którym jesteśmy. Takie funkcje możemy również śmiało wykorzystać przy poszukiwaniach informacji o istnieniu jakiejkolwiek wartości (NULL/NOT NULL) poprzednika/następnika w łańcuchach relacji czy wyliczaniu innych istotnych wartości opartych na kolejności w oknie.

POST PIERWOTNIE OPUBLIKOWANY JAKO TSQL NA DZIŚ #32

Dodaj komentarz