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