Dlaczego kocham funkcje okienkowe T-SQL, a Ty też powinieneś!

By Kathi Kellenberger

Od lat używałem ROW_NUMBER w moim kodzie T-SQL, nie zdając sobie sprawy, że jest to jedna z grupy niesamowicie użytecznych funkcji zwanych funkcjami okienkowymi. Dopiero podczas przygotowywania prezentacji na temat nowych funkcji T-SQL w wersji SQL Server 2012 zdałem sobie sprawę, że jest tak wiele do omówienia, że stworzyłem osobną prezentację poświęconą nowym funkcjom okna. Ostatecznie ta jedna prezentacja zamieniła się w dwie prezentacje, moją książkę wydaną przez Apress oraz kurs Pluralsight. Każdemu, kto słucha, opowiadam o tych wspaniałych funkcjach i sam używam ich na co dzień w pracy.

Funkcje okna pozwalają na wykonywanie obliczeń na zbiorze wierszy. Są one podobne w działaniu do grupowania za pomocą funkcji agregujących, ale umożliwiają istotną różnicę: Zapytania agregujące zwracają jeden wiersz na grupę, a szczegóły są eliminowane w wynikach. Zapytania zawierające funkcje okna zwracają wszystkie wiersze szczegółów wraz z wynikiem wyrażenia funkcji okna. Masz szczegóły i dostajesz również agregat.

Kiedy mówię na SQL Server i wydarzeniach dla deweloperów o funkcjach okna, zazwyczaj około połowa sali jest zaznajomiona z ROW_NUMBER. Garstka będzie wiedziała o więcej niż to. Jest to rozczarowujące, ponieważ te przydatne funkcje są dostępne od lat.

Moja ulubiona funkcja okna nazywa się LAG. LAG pozwala na złapanie dowolnej kolumny z innego wiersza w zestawie wyników. Możesz osiągnąć to samo za pomocą innych metod, ale wydajność jest znacznie gorsza. Poniżej znajdują się dwa zapytania do bazy danych AdventureWorks. Oba zapytania dają ten sam rezultat. Pierwsze z nich wykonuje to poprzez złączenie zewnętrzne, podczas gdy drugie wykorzystuje LGD.

–Listuj klientów z datami zamówień i dniami od poprzedniego zamówienia

SELECT CustomerID, OrderDate,

DATEDIFF(day,OAPPLY.PrevOrder, OrderDate) AS DaysSincePrevOrder

FROM Sales.SalesOrderHeader AS SOH

OUTER APPLY (

SELECT TOP(1) OrderDate AS PrevOrder

FROM Sales.SalesOrderHeader AS OA

WHERE OA.CustomerID = SOH.CustomerID

AND OA.OrderDate < SOH.OrderDate

ORDER BY OrderDate) AS OAPPLY

ORDER BY CustomerID, OrderDate;

–Przynosi te same wyniki używając LAG

SELECT CustomerID, OrderDate,

DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate) AS DaysSinceLastOrder

FROM Sales.SalesOrderHeader

ORDER BY CustomerID, OrderDate;

Drugie zapytanie wykorzystujące funkcję LAG jest prostsze do napisania i wykonuje się lepiej. Rzeczywisty plan wykonania pokazuje, że pierwsze zapytanie zajmuje 99% zasobów w partii. Drugie zapytanie zajmuje tylko 1% zasobów. Poniżej znajdują się dwa plany wykonania, które można zobaczyć na własne oczy:

Jeśli nie jesteś zaznajomiony ze składnią funkcji okna, zapytanie LAG może być nieco mylące. Wszystkie funkcje okna będą miały klauzulę OVER, która definiuje okno lub zestaw wierszy dla obliczeń. Nie chcę porównywać dat zamówień różnych klientów, więc użyłem opcji PARTITION BY, aby podzielić wiersze na grupy według CustomerID:

LAG(OrderDate) OVER(PARTITION BY CustomerID …

Klauzula OVER posiada również opcję ORDER BY. Jest to wymagane w przypadku LGD, aby wiersze były ustawione w odpowiedniej kolejności, aby silnik mógł pobrać odpowiednie wartości z właściwych wierszy. Moja ostateczna inwokacja LGD wygląda następująco:

LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)

LAG jest tylko jedną z wielu funkcji okna. Funkcje te nie pozwalają na zrobienie niczego, co byłoby niemożliwe bez nich, ale zdecydowanie ułatwiają kodowanie. Przekonasz się również, że wiele razy twoje zapytania działają szybciej, gdy sformułujesz je przy użyciu tych nowych funkcji.

Skończyło się na tym, że używam funkcji okna na co dzień. Ułatwiają mi one życie i poprawiają wydajność moich zapytań. Uwielbiam z nich korzystać i Ty też powinieneś.

O autorze

Kathi Kellenberger jest konsultantką w Linchpin People. Lubi pisać i mówić na tematy związane z SQL Server – napisała ponad dwa tuziny artykułów, współtworzyła cztery książki i prezentowała na wielu imprezach związanych z SQL Server. W wolnym czasie Kathi lubi spędzać czas z rodziną i przyjaciółmi, śpiewać i jeździć na rowerze.

Dowiedz się jeszcze więcej o funkcjach okienkowych i ich mocy w rozwiązywaniu trudnych problemów z zapytaniami z książki Kathi Kellenberger „Expert T-SQL Window Functions in SQL Server” (ISBN 9781484211045). Inne książki Kathi to Beginning SQL Server Reporting Services (2016) i Beginning T-SQL (2014).

.