Wprowadzenie do funkcji okna SQL

Bardzo potężna funkcja, której kochasz nienawidzić (ale musisz wiedzieć)

Funkcje okna SQL zapewniają kilka niezwykle potężnych i użytecznych funkcji. Jednak dla wielu, ponieważ są one tak obce dla standardowego SQL, są trudne do nauczenia i zrozumienia, mają dziwną składnię — i bardzo często ich unika.

Funkcje okien można po prostu wytłumaczyć jako funkcje obliczeniowe podobne do agregacji, ale gdy normalne agregowanie za pomocą klauzuli GROUP BY powoduje ukrycie agregowanych pojedynczych wierszy, funkcje okna mają dostęp do poszczególnych wierszy i mogą dodać niektóre atrybuty z tych wierszy do zestaw wyników.

W tym samouczku dotyczącym funkcji okienkowych SQL, zamierzam rozpocząć pracę z funkcjami okienkowymi, wyjaśnię korzyści i kiedy będziesz ich używać, a także podam prawdziwe przykłady, które pomogą z koncepcjami.

Okno na Twoje dane

Jedną z najczęściej używanych i najważniejszych funkcji SQL jest możliwość agregowania lub grupowania wierszy danych w określony sposób. Jednak w niektórych przypadkach grupowanie może stać się niezwykle złożone, w zależności od wymagań.

Czy kiedykolwiek chciałeś przejrzeć wyniki swojego zapytania, aby uzyskać ranking, pierwszą listę X lub podobne? Czy miałeś jakieś projekty analityczne, w których chciałeś przygotować swoje dane idealnie do narzędzia do wizualizacji, ale okazało się to prawie niemożliwe lub tak skomplikowane, że nie było tego warte?

Funkcje okien mogą to ułatwić. Po otrzymaniu wyniku zapytania — tj. po klauzuli WHERE i dowolnej standardowej agregacji, funkcje okna będą działać na pozostałych wierszach ( oknie danych) i uzyskają to, czego chcesz.

Niektóre z funkcji okna, którym będziemy się przyglądać, obejmują:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

Zbyt łatwe

Klauzula OVER określa funkcję okna i musi być zawsze zawarta w instrukcji. Wartość domyślna w klauzuli OVER to cały zestaw wierszy. Jako przykład spójrzmy na tabelę pracowników w bazie danych firmy i pokaż łączną liczbę pracowników w każdym wierszu wraz z informacjami o każdym pracowniku, w tym kiedy rozpoczęli pracę w firmie.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Powyższe, podobnie jak wiele funkcji okien, można również napisać w bardziej znany sposób bez okien — co w tym prostym przykładzie nie jest takie złe:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Ale teraz powiedzmy, że chcemy pokazać liczbę pracowników, którzy rozpoczęli pracę w tym samym miesiącu, co pracownik z rzędu. Będziemy musieli zawęzić lub ograniczyć liczbę tylko do tego miesiąca dla każdego wiersza. Jak to się robi? Używamy klauzuli PARTITION okna, jak na przykład:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

Partycje umożliwiają filtrowanie okna na sekcje według określonej wartości lub wartości. Każda sekcja jest często nazywana ramą okienną.

Idąc dalej, powiedzmy, że nie tylko chcieliśmy dowiedzieć się, ilu pracowników rozpoczęło pracę w tym samym miesiącu, ale chcemy pokazać, w jakiej kolejności rozpoczęli pracę w tym miesiącu. W tym celu możemy użyć znanej klauzuli ORDER BY . Jednak w funkcji okna ORDER BY działa nieco inaczej niż na końcu zapytania.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

W tym przypadku ORDER BY modyfikuje okno tak, aby przechodziło od początku partycji (w tym przypadku miesiąc i rok rozpoczęcia pracy pracownika) do bieżącego wiersza. W ten sposób licznik jest ponownie uruchamiany na każdej partycji.

Ranga to

Funkcje okna mogą być bardzo przydatne do celów klasyfikacji. Wcześniej widzieliśmy, że użycie funkcji agregacji COUNT pozwoliło nam zobaczyć, w jakiej kolejności Pracownicy dołączają do firmy. Mogliśmy również użyć funkcji rankingu okien, takich jak ROW_NUMBER() , RANK() i DENSE_RANK() .

Różnice widać po dodaniu nowego pracownika w kolejnym miesiącu i usunięciu partycji:

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Widać różnice. ROW_NUMBER() podaje sekwencyjną liczbę w ramach danej partycji (ale w przypadku braku partycji, przechodzi przez wszystkie wiersze). RANK() podaje rangę każdego wiersza na podstawie klauzuli ORDER BY . Pokazuje remisy, a następnie pomija następny ranking. DENSE_RANK również pokazuje remisy, ale następnie kontynuuje z kolejną wartością z rzędu, tak jakby nie było remisu.

Inne funkcje rankingowe obejmują:

• CUME_DIST – Oblicza względną rangę bieżącego wiersza w obrębie partycji
• NTILE – dzieli wiersze dla każdej partycji okiennej tak równo, jak to możliwe
• PERCENT_RANK — procentowa pozycja bieżącego wiersza

Zauważ również, że w tym przykładzie możesz mieć wiele funkcji Window w jednym zapytaniu — a zarówno partycja, jak i kolejność mogą być różne w każdym z nich!

Rzędy i zakresy i ramki, ojej

Aby dokładniej zdefiniować lub ograniczyć ramkę okna w klauzuli OVER() , możesz użyć ROWS i RANGE . Za pomocą klauzuli ROWS możesz określić wiersze zawarte w partycji jako te poprzedzające lub następujące po bieżącym wierszu.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

W tym przykładzie rama okna przechodzi od pierwszego wiersza do bieżącego wiersza minus 1, a rozmiar okna nadal rośnie z każdym wierszem.

Zakres działa nieco inaczej i możemy uzyskać inny wynik.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Zakres będzie obejmował te wiersze w ramce okna, które mają takie same wartości ORDER BY jak bieżący wiersz. W związku z tym możliwe jest uzyskanie duplikatów za pomocą RANGE , jeśli ORDER BY nie jest unikatowe.

Niektórzy opisują ROWS jako operator fizyczny, podczas gdy RANGE to operator logiczny. W każdym razie wartości domyślne dla ROWS i RANGE to zawsze UNBOUNDED PRECEDING AND CURRENT ROW .

Co jeszcze?

Większość standardowych funkcji agregujących współpracuje z funkcjami Windows. Widzieliśmy już COUNT w przykładach. Inne to SUM , AVG , MIN , MAX , itp.

Dzięki funkcjom okien możesz również uzyskać dostęp zarówno do poprzednich, jak i kolejnych rekordów za pomocą LAG i LEAD oraz FIRST_VALUE i LAST_VALUE . Załóżmy na przykład, że chcesz wyświetlić w każdym wierszu wartość sprzedaży z bieżącego miesiąca oraz różnicę między wartością sprzedaży z poprzedniego miesiąca. Możesz zrobić coś takiego:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

Zasadniczo funkcje okna SQL są bardzo wydajne

Chociaż jest to szybkie wprowadzenie do funkcji okien SQL, miejmy nadzieję, że wzbudzi zainteresowanie, aby zobaczyć, co mogą zrobić. Dowiedzieliśmy się, że funkcje okien wykonują obliczenia podobne do funkcji agregacji, ale mają dodatkową zaletę, że mają dostęp do danych w poszczególnych wierszach, co czyni je dość potężnymi. Zawsze zawierają klauzulę OVER i mogą zawierać PARTITION BY , ORDER BY oraz wiele funkcji agregujących ( SUM , COUNT , itp.) i innych funkcji pozycyjnych ( LEAD , LAG ). Dowiedzieliśmy się również o ramach okiennych i sposobie, w jaki hermetyzują sekcje danych.

Zauważ, że różne odmiany SQL mogą implementować funkcje okien w różny sposób, a niektóre mogą nie implementować wszystkich funkcji okien lub klauzul. Sprawdź dokumentację platformy, z której korzystasz.

Jeśli jako programista SQL jesteś zainteresowany dostrajaniem wydajności bazy danych SQL, zapoznaj się z tematem Dostrajanie wydajności bazy danych SQL dla programistów .

Miłego okienkowania!

Aby uzyskać więcej informacji na temat konkretnych wdrożeń, zobacz:

• Dokumentacja funkcji okna PostgreSQL dla implementacji PostgreSQL.
• SELECT — OVER klauzula (Transact-SQL) docs firmy Microsoft.
• Funkcje okna w SQL Server , aby uzyskać doskonały przegląd implementacji SQL Server i jego część 2.