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

2 myśli na temat “Potęga funkcji okna – SELECT LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

  1. Dzień dobry.
    Korzystałem z funkcji opisanych powyżej. Są bardzo przydatne, podobnie jak Pana materiał.
    Czy spotkał się Pan z korzystaniem w selekcie z frazą( funkcją) WINDOW. Przynajmniej teoretycznie powinno działać w ten sposób, że zamiast ciągle powtarzać funkcję okna , po słowie OVER, określa ją się raz i później tylko się do niej odnosi.
    np.
    select *, RoznicaZPoprzednia = Kwota – LAG(Kwota) OVER w from …
    WINDOW w as (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC)
    Coś w tym stylu.

    przykład z sieci:
    Jeśli chcemy wywołać kilka funkcji okienkowych o tym samej strukturze ramki, można użyć frazy (klauzuli) WINDOW, żeby uniknąć kopiowania

    SELECT sum(salary) OVER w, avg(salary) OVER w
    FROM empsalary
    WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
    Jestem początkujący, ale u mnie to nie działa i mało o tym jest napisane.
    Pozdrawiam
    Robert K

    1. Robert,
      omawiane przykłady dotyczą bazy SQL Server.
      Jego język – Transact SQL (T-SQL) niestety nie obsługuje wyrażenia WINDOW, które formalnie zwie się „Named Windows”, a które dostępne jest w Oracle czy choćby MySQL.
      Trzeba więc każde okno danych powtarzać, co niewątpliwie jest kłopotliwe i wprowadza lekkie zaszumienie w kodzie, niemniej jednak nie jest aż tak uciążliwe i być może w pewnych przypadkach nie wprowadza w błąd (np. omyłkowym odniesieniem się do niepoprawnego okna).

      Pełna specyfikacja składni funkcji okienkowych dostępna jest pod linkiem:
      https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

Dodaj komentarz