Anatomia funkcji okna SQL

Aby zrozumieć dane przedsiębiorstwa; musisz dużo o to pytać. Kiedy mówię „dużo”, mam to na myśli. Praca z nieznanymi stosami danych jest często zniechęcająca i zawsze warto poświęcić trochę czasu na eksplorację i zrozumienie samych danych. Dobrze jest mieć podstawowe umiejętności wyszukiwania danych, ale znajomość funkcji analitycznych w celu uzyskania przydatnych spostrzeżeń z danych to wisienka na torcie i jest też zabawna!

Zajmuję się wizualizacją danych i kluczowe jest dla mnie nie tylko zrozumienie danych, ale także znalezienie wszelkich godnych uwagi ustaleń, aby przedstawić je szerszym zespołom. Również budowanie złożonych pulpitów nawigacyjnych jest dość często procesem ciągłym, w którym wracasz do źródła danych, aby zestawić dane, a funkcje okna SQL zawsze towarzyszyły mi w mojej podróży do analizy danych.

Mimo że są bardzo przydatne do analizy danych, istnieje pewne zamieszanie, a ludzie często boją się ich używać. Pisząc szczegółowy przewodnik po SQL Window Functions, zdałem sobie sprawę, że staje się on zbyt opisowy, a mimo to nie chciałem pomijać szczegółów, zwłaszcza dotyczących składni i klauzul używanych wraz z nim. Ważne jest, aby zrozumieć elementy składowe, tak? Spróbuję więc rozbić elementy składowe funkcji okna w tym artykule, aby przetwarzanie i wdrażanie ich nie było przytłaczające.

Jak zwykle do demonstracji użyjemy przykładowej bazy danych MySQL classicmodels , która zawiera dane biznesowe sprzedawcy samochodów. Poniżej znajduje się diagram ER w celach informacyjnych,

Po pierwsze, co to jest funkcja okna?

Podręcznikowa definicja funkcji okna to:

Funkcja okna wykonuje obliczenia w zestawie wierszy tabeli, które są w jakiś sposób powiązane z bieżącym wierszem.

Jak myślisz, co ten mały facet widzi przez okno? Częściowy widok sceny poza oknem tego pokoju lub budynku. Prawidłowy? Dokładnie to robi funkcja okna. Umożliwia wykonywanie obliczeń na podzbiorze danych bez agregowania bieżących wierszy.

Jaka jest potrzeba? Dlaczego funkcja okna? Gdzie pozostaje w tyle funkcja agregująca?

Oto przykładowe dane z tabeli PRODUKTY , dla celów demonstracyjnych ograniczyłem je do LINI PRODUKTÓW - Samoloty , Statki i Pociągi .

--sample data from table PRODUCTS.
SELECT
    *
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

--total quantity in stock for each productline
SELECT 
    PRODUCTLINE,
    SUM(QUANTITYINSTOCK) AS TOTAL_QUNATITY
FROM 
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains')
GROUP BY PRODUCTLINE;

      
                

                    
                    
                
            

Zmodyfikujmy teraz wymagania,

1. Wyświetl ilość każdego produktu w ramach LINII PRODUKTÓW wraz z całkowitą ilością w magazynie dla tej konkretnej LINII PRODUKTÓW .
2. Uporządkuj zestaw wyników pogrupowany według LINII PRODUKTU.

--sum() as a window function
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE) AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

Typy funkcji okien

Szczerze mówiąc, nie ma oficjalnej kategoryzacji funkcji okna, ale w oparciu o użycie możemy je krótko skategoryzować na 3 sposoby,

• Funkcje agregujące - Zwykła funkcja agregująca może być używana jako funkcja okna do obliczania agregacji kolumn liczbowych w partycjach okna, takich jak bieżąca całkowita sprzedaż, minimalna lub maksymalna wartość w partycji itp.
• Funkcje rankingu — te funkcje zwracają wartość rankingu dla każdego wiersza w partycji.
• Funkcje wartości — te funkcje są przydatne do generowania prostych statystyk lub analizy szeregów czasowych.

Typową składnią funkcji okna jest:

Zanim zagłębimy się w to; najpierw zrozummy znaczenie każdej klauzuli w nim zawartej,

OVER() Klauzula

Klauzula OVER() określa funkcję jako funkcję okna i dlatego zawsze musi być zawarta w instrukcji. Definiuje określony przez użytkownika podzbiór (okno) wierszy, w których zostanie zastosowana funkcja okna. Jeśli nie podasz niczego wewnątrz OVER() , funkcja okna zostanie zastosowana do całego zestawu wyników.

Kontynuując powyższy przykład,

--empty OVER() clause
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER () AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

PARTYCJA WEDŁUG klauzuli

PARTITION BY jest używany z klauzulą ​​OVER . Dzieli zestaw wyników zapytania na partycje lub segmenty na podstawie wyrażenia określonego przez użytkownika, a następnie funkcja okna ma zastosowanie do każdej partycji lub segmentu.

Jest opcjonalny, więc jeśli nie określisz klauzuli PARTITION BY , funkcja będzie traktować wszystkie wiersze jako pojedynczą partycję. Dokładnie to, co zrobiliśmy w powyższym przykładzie, po prostu określiliśmy pustą klauzulę OVER() bez klauzuli PARTITION BY , a zatem całkowita ilość została obliczona dla wszystkich LINII PRODUKTÓW .

Co się stanie, jeśli określimy jeden,

--OVER() with PARTITION BY
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE) AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

O co chodzi z klauzulami PARTITION BY i GROUP BY ? Czy są podobni czy różni?

Klauzula GROUP BY ,

• Grupuje wiele wierszy w podsumowane wiersze na podstawie jednej lub wielu kolumn/wyrażeń (zwracając 1 wiersz dla każdej grupy). Mówiąc prościej, zmniejsza liczbę wierszy w zestawie wyników.
• Jest używany wraz z funkcjami agregującymi, takimi jak SUM(), AVG(), MAX() itp.
• Jest umieszczany po klauzuli WHERE , ale przed klauzulami HAVING, ORDER BY . Wspólna składnia to,

• Jest używany z klauzulą ​​OVER() w funkcji okna. Dzieli zestaw wyników zapytania na partycje, a następnie funkcja okna ma zastosowanie do każdej partycji.
• PARTITION BY jest podobny do GROUP BY, ponieważ agreguje wynik na podstawie wyrażenia; jednak główna różnica polega na tym, że nie zmniejszy to wierszy zestawu wyników.
• Jest opcjonalny, więc jeśli nie określisz klauzuli PARTITION BY , funkcja będzie traktować wszystkie wiersze jako pojedynczą partycję.
• Wspólna składnia to,

Klauzula ORDER BY

Służy do sortowania zestawu wyników w kolejności rosnącej lub malejącej w ramach każdej partycji zestawu wyników. Domyślnie jest to w porządku rosnącym.

Klauzula WIERSZÓW/ZAKRESU

Teraz wiemy już, że kluczową cechą funkcji okna jest utworzenie okna lub partycji zestawu wyników za pomocą klauzuli PARTITION BY , a następnie wykonanie obliczeń na każdej partycji. Co jeśli dalej chcemy tworzyć podzbiory w ramach tych partycji? Łał! partycja w partycji? Tak, dlatego mamy klauzulę FRAME .

Klauzula FRAME dodatkowo definiuje podzbiór bieżącej partycji. Wykorzystuje WIERSZ lub ZAKRES do zdefiniowania punktów początkowych i końcowych tego podzbioru. Wymaga klauzuli ORDER BY .

Ramki są określane w odniesieniu do bieżącego wiersza, co oznacza po prostu, że przyjmujesz położenie bieżącego wiersza jako punkt bazowy iz tym odniesieniem definiujesz ramkę w obrębie partycji.

• WIERSZE — określa początek i koniec ramki, określając liczbę wierszy, które poprzedzają lub następują po bieżącym wierszu.
• RANGE — W przeciwieństwie do ROWS , RANGE określa zakres wartości porównywany z wartością bieżącego wiersza w celu zdefiniowania ramki w obrębie partycji.

{WIERZE | ZAKRES} MIĘDZY <początek_klatki> A <koniec_klatki>

Zanim przejdziemy dalej, poznajmy kilka podstawowych terminów definiujących ramę.

• UNBOUNDED PRECEDING - Określa wszystkie wiersze (zaczynając od pierwszego wiersza) przed bieżącym wierszem w partycji.
• N PRECEDING - Określa liczbę wierszy „N” przed bieżącym wierszem w partycji.
• UNBOUNDED FOLLOWING - Określa wszystkie wiersze po bieżącym wierszu (aż do ostatniego wiersza) w partycji.
• M FOLLOWING - Określa liczbę wierszy „M” poniżej bieżącego wiersza w partycji.

SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS TOTAL
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

Oto kilka przykładów klauzul FRAME ,

• RZĘDY MIĘDZY NIEOGRANICZONYMI POPRZEDZAJĄCYMI I NIEOGRANICZONYMI NASTĘPNYMI — oznacza to rozważenie ramki od pierwszego do ostatniego wiersza partycji.
• RZĘDY MIĘDZY NIEOGRANICZONYMI POPRZEDNIMI A 4 NASTĘPNYMI - Oznacza to rozważenie ramki od pierwszego rzędu partycji do 4 rzędu po bieżącym rzędzie.
• RZĘDY MIĘDZY 4 POPRZEDNIMI I 1 POPRZEDNIMI - Ramka będzie składać się z 4 poprzednich rzędów do 1 rzędu przed bieżącym rzędem.

{ROWS/RANGE} MIĘDZY NIEOGRANICZONYM POPRZEDNIM A BIEŻĄCYM WIERSZEM

Oznacza to, że rozważ ramkę jako wszystkie wiersze, począwszy od wiersza numer jeden do bieżącego wiersza w partycji.

Bez klauzuli ORDER BY , domyślna ramka jako,

{ROWS/RANGE} MIĘDZY NIEOGRANICZONYMI POPRZEDNIMI I NIEOGRANICZONYMI NASTĘPNYMI

Oznacza to po prostu całą partycję.

Definiowanie aliasu okna,

Jeśli w zapytaniu jest więcej niż jedna funkcja okna, która wykorzystuje to samo okno, możesz chcieć użyć aliasu okna.

--finding out minimum and maximum MSRP for each productline
SELECT
    PRODUCTNAME,
    PRODUCTLINE,
    MSRP,
    MIN(MSRP) OVER(PARTITION BY PRODUCTLINE) AS MIN_MSRP,
    MAX(MSRP) OVER(PARTITION BY PRODUCTLINE) AS MAX_MSRP
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

--using window alias
SELECT
    PRODUCTNAME,
    PRODUCTLINE,
    MSRP,
    MIN(MSRP) OVER MSRP_WINDOW AS MIN_MSRP,
    MAX(MSRP) OVER MSRP_WINDOW AS MAX_MSRP
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains')
WINDOW MSRP_WINDOW AS (PARTITION BY PRODUCTLINE);

Podczas wykonywania zapytania na zbiorze wyników wykonywane są Funkcje okna,

• Po klauzulach JOIN , WHERE , GROUP BY i HAVING oraz
• Przed klauzulą ​​ORDER BY LIMIT i SELECT DISTINCT .

Możesz eksplorować dane na 100 różnych sposobów, a funkcje okna są odpowiednie do takiej analizy. Ten artykuł był tylko wstępem do zrozumienia podstawowej składni i klauzul, aby nie przytłaczać Funkcji okna, z pewnością będzie lepiej z praktyką.

• Ściągawka dotycząca funkcji okna SQL
• Klauzula ramowa
• HackerRank lub LeetCode do ćwiczenia podstawowych/średnich/zaawansowanych problemów SQL.