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

Michał Pawlikowski

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:

DECLARE  @T TABLE (id int identity primary key, Sprzedawca varchar(20), DataSprzedaży date, Kwota money) --tworzymy naszą tabelę

INSERT INTO @T VALUES 
('SPRZEDAWCA 1','2015-01-01',1000),
('SPRZEDAWCA 1','2015-01-02',1000),
('SPRZEDAWCA 1','2015-01-03',2000),
('SPRZEDAWCA 1','2015-01-05',4000),
('SPRZEDAWCA 1','2015-01-07',1000),
('SPRZEDAWCA 1','2015-01-08',50000),
('SPRZEDAWCA 2','2015-02-15',5000),
('SPRZEDAWCA 2','2015-04-11',1000),
('SPRZEDAWCA 2','2015-05-24',2000),
('SPRZEDAWCA 2','2015-07-30',1000)
--dodaliśmy różne wartości


select *,
RoznicaZPoprzednia = Kwota - LAG(Kwota) OVER (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC), --LAG pobierze wartośc z poprzedniego wiersza partycji
DataPoprzedniejSprzedazy = LAG(DataSprzedaży) OVER (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC),

RoznicaZNastepna = Kwota - LEAD(Kwota) OVER (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC), --LEAD pobierze wartośc z następnego wiersza partycji
DataNastepnejSprzedazy = LEAD(DataSprzedaży) OVER (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC),

RoznicaZPierwsza = Kwota - FIRST_VALUE(Kwota) OVER (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC), --FIRST_VALUE pobierze wartośc z pierwszego wiersza partycji
DataPierwszejSprzedazy = FIRST_VALUE(DataSprzedaży) OVER (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC),

RoznicaZOstatnia = Kwota - FIRST_VALUE(Kwota) OVER (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC), --a LAST_VALUE z ostatniego
DataOstatniejSprzedazy = FIRST_VALUE(DataSprzedaży) OVER (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC)
 From @T

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:

select *,
 [Różnica: wiersz w - 2] = Kwota - LAG(Kwota,2) OVER (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC), --LAG pobierze wartośc z poprzedniego wiersza partycji
 [Data Sprzedaży w - 2] = LAG(DataSprzedaży,2) OVER (PARTITION BY Sprzedawca ORDER BY DataSprzedaży ASC)
 From @T

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

    Odpowiedz

    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

      Odpowiedz

Dodaj komentarz