Funkcje analityczne i funkcje okna w SQL Server

W artykule przyjrzymy się funkcjom analitycznym i funkcjom okienkowym dostępnym w SQL Server, ich zastosowaniom oraz korzyściom płynącym z ich używania.

Po przeczytaniu tego artykułu będziesz wiedzieć, czym są funkcje analityczne i czym różnią się od funkcji okna OVER.

SQL Server to jeden z najpopularniejszych i najbardziej zaawansowanych systemów zarządzania bazami danych. Zawarty w nim zestaw funkcji analitycznych sprawia, że jest on ważnym narzędziem w pracy analityków danych.

Funkcje analityczne i funkcje okna w SQL Server prezentuję używając baz AdventureWorks oraz Northwind.

Czym są funkcje analityczne w SQL Server?

Funkcje analityczne w języku SQL to wyspecjalizowane funkcje przeznaczone do wykonywania złożonych obliczeń na zestawie wierszy. Pojęcie „funkcje analityczne” jest często używane w odniesieniu do funkcji okienkowych.

Wśród funkcji analitycznych znajdują się:

funkcje okna (okienkowe)
funkcje agregujące
funkcje rankingu
funkcje wartościowe

Funkcje agregujące, rankingu i wartościowe mogą być stosowane dla całej tabeli lub dla wybranego zestawu rekordów (okna) w tabeli. Dlatego często mówi się o nich, że są to funkcje okna.

Czym są funkcje okna w SQL Server?

Funkcje okna umożliwiają podzielenie danych na logiczne grupy (okna) i wykonywanie obliczeń na tych grupach.

W przeciwieństwie do standardowych funkcji agregujących, funkcje okna pozwalają na zachowanie dostępu do poszczególnych rekordów podczas wykonywania obliczeń.

Grupowanie i funkcje agregujące okna

Szczegółowe informacje na temat standardowego grupowania znajdziesz w artykule MS SQL grupowanie

W tym artykule skupiam się na funkcjach agregujących okna. Funkcje agregujące okna uruchamiamy przez dopisanie klauzuli OVER.

Klauzula OVER

Klauzula OVER może wystąpić w zapytaniach, które nie zawierają klauzuli GROUP BY i pozwala wywołać każdą funkcję grupującą.

Najczęściej używane funkcje agregujące:

COUNT() – liczba wierszy w partycji
MIN() – najmniejsza wartość w partycji
MAX() – największa wartość w partycji
SUM() – suma w partycji lub suma narastająco w partycji
AVG() – średnia w partycji

Zwykłe funkcje grupujące umożliwiają wyświetlenie podsumowań dla całej tabeli lub pogrupowanie danych według wybranej kolumny lub kolumn.

Poniższy przykład to podsumowanie dla całej tabeli

SELECT COUNT(*) as liczba_prod,
       AVG(UnitPrice) as srednia,
       MIN(UnitPrice) as minimum,
       MAX(UnitPrice) as maximum
FROM Products;

Wynik zapytania:

Wynikiem działania poniższego kodu jest uzyskanie podsumowań dla każdej kategorii

SELECT CategoryID, 
       COUNT(*) as liczba_prod,
       AVG(UnitPrice) as srednia,
       MIN(UnitPrice) as minimum,
       MAX(UnitPrice) as maximum
FROM Products
GROUP BY CategoryID;

Wynik zapytania

Poniżej zapytanie z użyciem klauzuli OVER, w którym wyświetlamy:

nazwę każdego produktu i jego cenę
liczbę produktów w całej tabeli
średnią wartość oraz minimalną i maksymalną wartość dla całej tabeli

Dzięki użyciu klauzuli OVER w klauzuli SELECT możemy wyświetlić kolumny ProductName i UnitPrice bez konieczności umieszczania ich w klauzuli GROUP BY i tworzenia według nich grup.

SELECT 
  ProductName,
  UnitPrice,
  COUNT(*) OVER() as liczba_prod,
  AVG(UnitPrice) OVER() as srednia,
  MIN(UnitPrice) OVER() as minimum,
  MAX(UnitPrice) OVER() as maximum
FROM Products;

Wynik zapytania

Klauzula PARTITION BY

W klauzuli OVER możemy umieścić klauzulę PARTITION BY, która służy do podziału tabeli na partycje (okna, części).

Jeśli w klauzuli OVER umieścimy słowo kluczowe PARTITION BY, funkcja grupująca lub funkcja rankingu zostaną wywołane dla każdej zdefiniowanej w ten sposób partycji.

W przykładzie poniżej tabelę partycjonujemy (dzielimy na części) według wartości kolumny UnitPrice. Aby zrobić podział umieszczamy kolumnę UnitPrice w klauzuli PARTITION BY.

W wyniku zapytania są trzy partycje:

pierwsza zawierająca rekordy z ceną 14,00
druga zawierająca rekordy z ceną 18,00
trzecia zawierająca rekordy z ceną 10,00

SELECT
  ProductName,
  UnitPrice,
  COUNT(*) OVER (PARTITION BY UnitPrice) as liczba_prod
FROM Products
ORDER BY liczba_prod DESC;

Wynik zapytania

Klauzula ORDER BY

W klauzuli OVER możemy umieścić słowo kluczowe ORDER BY.

Jeśli w klauzuli OVER umieścimy słowo kluczowe ORDER BY, okno na którym działa funkcja będzie zawierało wiersze od pierwszego wiersza w partycji do bieżącego (nie do końca partycji).

Klauzula ORDER BY umieszczona wewnątrz klauzuli OVER określa kolejność, w jakiej funkcja okienkowa ma przetwarzać wiersze w obrębie zdefiniowanego okna (partycji).

SELECT
  OrderID,
  CustomerID,
  OrderDate,
  Freight,
  SUM(Freight) OVER(PARTITION BY CustomerID ORDER BY OrderDate) as suma,
  AVG(Freight) OVER(PARTITION BY CustomerID ORDER BY OrderDate) as srednia,
  MAX(Freight) OVER(PARTITION BY CustomerID ORDER BY OrderDate) as maks,
  COUNT(*) OVER(PARTITION BY CustomerID ORDER BY OrderDate) as liczba
FROM orders
ORDER BY CustomerID, OrderDate;

Wynik zapytania

Funkcja SUM z klauzulą OVER i ORDER BY pozwala obliczać sumę narastającą w ramach partycji – sumowanie odbywa się od początku partycji do bieżącego wiersza.

W przykładzie mamy dwie partycje:

pierwsza zawiera rekordy dla klienta ALFKI
druga zawiera rekordy dla klienta ANATR

Sortowanie odbywa się według kolumny OrderDate. Daty w kolumny OrderDate decydują o tym, w którym miejscu będzie bieżący wiersz. Bieżący wiersz zmienia się wraz ze zmianą daty.

29,46 – suma z kolumny Freight dla pierwszego wiersza (suma od początku partycji do daty 1997-08-25)
114,42 – suma z kolumny Freight dla pierwszego, drugiego i trzeciego wiersza (suma od początku partycji utworzonej dla klienta ALFKI do bieżącego wiersza tj. do daty 1997-10-03.

Funkcje rankingu

Funkcje rankingu, w przeciwieństwie do funkcji grupujących mogą być wywoływane dla utworzonych za pomocą klauzuli OVER partycji. Nie mogą być wywoływane dla grup utworzonych za pomocą GROUP BY.

Wynik funkcji rankingu zależy od uporządkowania wierszy. Dlatego wymagają one posortowania partycji. Funkcje rankingu są wywoływane raz dla każdej partycji.

Funkcje rankingu:

ROW_NUMBER() – numeruje wiersze
RANK() – numeruje wiersze, nadaje ten sam numer dla tych samych wartości
DENSE_RANK() – numeruje wiersze, ten sam numer dla tych samych wartości – zachowuje ona ciągłość numeracji wierszy wyniku
NTILE() – dzieli wiersze wyniku na określoną liczbę bloków – w jej przypadku dla każdego bloku zostanie zaklasyfikowanych tyle samo wierszy
CUME_DIST() – pozycja w rankingu ale wyrażona w procentach, pierwszy element nie jest zerowy
PERCENT_RANK() – pozycja w rankingu ale wyrażona w procentach, pierwszy element jest zawsze zero
PERCENTILE_CONT() – oblicza medianę oraz kwartyle (oblicza interpolację)
PERCENTILE_DISC() – oblicza medianę oraz kwartyle (tylko faktyczne wartości)

Funkcje ROWNUMBER, RANK, DENSE_RANK, NTILE

W przykładzie po wprowadzeniu nazwy funkcji wprowadzamy wyrażenie OVER. Następnie w nawiasie wprowadzamy sortowanie za pomocą klauzuli ORDER BY, na podstawie którego tworzony jest ranking.

Funkcja NTILE() jako jedyna wymaga argumentu. W argumencie podajemy na ile grup chcemy podzielić rekordy.

SELECT ProductName,
       SupplierID, 
       ROW_NUMBER() OVER (ORDER BY SupplierID) as [ROW_NUMBER],
       RANK() over(order by SupplierID) as [RANK], 
       DENSE_RANK() over(order by SupplierID) as [DENSE_RANK], 
       NTILE(4) over(order by SupplierID) as [NTILE]
FROM Products;

Wynik zapytania

Funkcja CUME_DIST

Funkcja CUME_DIST() oblicza, jaki procent wierszy ma wartość mniejszą lub równą w danej partycji.

SELECT 
   ProductName,
   SupplierID,
   UnitPrice,
   CUME_DIST() OVER(PARTITION BY SupplierID ORDER BY UnitPrice) as cume_dist
FROM Products
ORDER BY SupplierID, UnitPrice;

Wynik zapytania

Wyjaśnienie dla partycji SupplierID nr 2.

Najwyższa cena (UnitPrice) równa się 22.00. W partycji mamy 4 wiersze.

wiersz z ceną 17.00 ma wartość mniejszą lub równą 22. Jest to 1 z 4 wierszy, co daje dystrybucję 1/4 = 0.25
wiersz z ceną 21.05 to 2 z 4 wierszy, co daje dystrybucję 2/4 = 0.50
wiersz z ceną 21.35 to 3 z 4 wierszy, co daje dystrybucję 3/4 = 0.75
wiersz z ceną 22.00 to 4 z 4 wierszy, co daje dystrybucję 2/4 = 1.00. Wszystkie 4 wiersze mają wartość UnitPrice mniejszą lub równą 22.00.

Funkcja PERCENT_RANK

Funkcja PERCENT_RANK() oblicza jaki procent wierszy ma wartość mniejszą w partycji.

Początkowa wartość jest zawsze pomijana i pierwszy wiersz ma zawsze wartość 0.

SELECT 
   ProductName,
   SupplierID,
   UnitPrice,
   PERCENT_RANK() OVER(PARTITION BY SupplierID ORDER BY UnitPrice) PERCENT_RANK
FROM Products
ORDER BY SupplierID, UnitPrice;

Wynik zapytania

Funkcja PERCENT_RANK() oblicza procentowy ranking w następujący sposób:

PercentRank = rank of current row−1 /total rows−1

Gdzie:

rank of current row to pozycja bieżącego wiersza w posortowanym zestawie wyników, zaczynając od 1
total rows to całkowita liczba wierszy w zestawie wyników.

Wyjaśnienie dla partycji SupplierID nr 2.

Wartość 17.00 to najniższa wartość w zestawie wyników. Ponieważ nie ma żadnych wartości przed nią, procentowy ranking wynosi 0.0000.
Sposób obliczenia: (1-1)/(4-1) = 0
Wartość 21.05 jest na drugiej pozycji w zestawie wyników.
Sposób obliczenia: (2-1)/(4-1) = 0.3333
Wartość 21.35 jest na trzeciej pozycji w zestawie wyników.
Sposób obliczenia: (3-1)/(4-1) = 0.6667
Wartość 22.00 to najwyższa wartość w zestawie wyników, więc procentowy ranking wynosi 1.0000.
Sposób obliczenia: (4-1)/(4-1) = 1.0000

Funkcja PERCENT_RANK() jest przydatna do obliczania względnej pozycji wiersza w zestawie wyników, wyrażonej jako procent. Wartość 0 oznacza najniższą pozycję, a wartość 1 oznacza najwyższą pozycję w rankingu.

Funkcja PERCENTILE_DISC

Funkcja PERCENTILE_DISC() pozwala obliczyć medianę oraz kwartyle:

PERCENTILE_DISC(0.25) – oblicza 1. kwartyl
PERCENTILE_DISC(0.5) – oblicza medianę (2. kwartyl)
PERCENTILE_DISC(0.75) – oblicza 3. kwartyl

Przykład, w którym użyjemy funkcji PERCENTILE_DISC do obliczenia mediany cen produktów.

SELECT 
  ProductID,
  ProductName, 
  UnitPrice,
  PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY UnitPrice) OVER() as mediana
FROM Products
ORDER BY UnitPrice DESC;

Wynik zapytania:

PERCENTILE_DISC to funkcja, która zwraca wartość odpowiadającą określonemu percentylowi w rozkładzie danych. W tym przypadku, 0.5 oznacza 50-ty percentyl, czyli medianę.

Klauzula WITHIN GROUP (ORDER BY UnitPrice) – określa, że obliczenia powinny być wykonane w oparciu o posortowaną kolumnę UnitPrice.

OVER() bez dodatkowych klauzul oznacza, że obliczenia są wykonywane na całym zbiorze danych, a nie na podgrupach.

Mediana (50-ty percentyl) jest wartością, która dzieli posortowany zbiór danych na dwie równe części.

W przykładzie mediana została wyliczona jako wartość 19,50. Znajduje się ona w 39 wierszu.

Funkcja PERCENTILE_CONT

Funkcja PERCENTILE_CONT() działa podobnie jak funkcja PERCENTILE_DISC , ale w razie potrzeby interpoluje obliczaną wartość.

Interpolacja to metoda matematyczna używana do estymowania wartości między znanymi punktami danych. W kontekście analizy danych i obliczania percentyli, interpolacja pozwala na dokładniejsze oszacowanie wartości, które leżą pomiędzy istniejącymi danymi.

SELECT 
  ProductID, 
  ProductName, 
  UnitPrice,
  PERCENTILE_DISC(0.1) WITHIN GROUP(ORDER BY UnitPrice) OVER() perc_disc,
  PERCENTILE_CONT(0.1) WITHIN GROUP(ORDER BY UnitPrice) OVER() perc_cont
FROM Products
ORDER BY UnitPrice;

Wynik zapytania

Wyjaśnienie:

PERCENTILE_DISC(0.1) – oblicza wartość 10-tego percentyla w zestawie danych
PERCENTILE_CONT(0.1)- oblicza wartość 10-tego percentyla w zestawie danych, ale używa interpolacji do obliczeń

Obliczanie percentyli:

PERCENTILE_DISC(0.1) – oblicza wartość 10-tego percentyla w sposób dyskretny. Oznacza to, że zwraca wartość, która jest rzeczywiście obecna w zbiorze danych, jeśli istnieje odpowiednia pozycja. Jeśli zestaw danych jest posortowany, wartość odpowiadająca 10-tym percentylowi jest bezpośrednio wybierana z danych.
PERCENTILE_CONT(0.1) – oblicza wartość 10-tego percentyla w sposób ciągły, używając interpolacji. Jeśli dokładna wartość percentyla nie znajduje się w danych, funkcja interpoluje między dwoma najbliższymi wartościami.

Funkcje zwracające wartość wiersza

Funkcje zwracające wartość wiersza to:

LEAD() – wartość z sąsiedniego, następnego (kolejnego) rekordu
LAG() – wartość z sąsiedniego, poprzedniego (wcześniejszego) rekordu
FIRST_VALUE() – wartość pierwsza rekordu w całej partycji
LAST_VALUE() – wartość ostatnia rekordu w całej partycji

Funkcja LEAD

Funkcja LEAD() zwraca wartość z kolejnego wiersza. W klauzuli OVER() konieczne jest zdefiniowanie kolejności przez podanie użycie ORDER BY.

SELECT 
  ProductName, 
  UnitPrice,
  LEAD(ProductName) OVER(ORDER BY UnitPrice) as prod_lead
FROM Products
ORDER BY UnitPrice;

Wynik zapytania

Wyjaśnienie:

funkcja LEAD() zwraca nazwę produktu z następnego wiersza w porządku rosnącym według UnitPrice
OVER(ORDER BY UnitPrice) określa, że wartości mają być pobierane z następnego wiersza w ramach porządku określonego według kolumny UnitPrice

Funkcja LAG

Funkcja LAG() zwraca wartość z poprzedniego wiersza. W klauzuli OVER() konieczne jest zdefiniowanie kolejności za pomocą klauzuli ORDER BY.

SELECT 
  ProductName, 
  UnitPrice,
  LAG(ProductName) OVER(ORDER BY UnitPrice) as prod_lag
FROM Products
ORDER BY UnitPrice;

Wynik zapytania

Wyjaśnienie:

Funkcja LAG() zwraca nazwę produktu z poprzedniego wiersza w porządku rosnącym według UnitPrice
OVER(ORDER BY UnitPrice) określa, że wartości mają być pobierane z poprzedniego wiersza w ramach porządku określonego według kolumny UnitPrice.

Funkcja FIRST_VALUE

Funkcja FIRST_VALUE() zwraca pierwszą wartość w partycji.

SELECT 
  ProductName, 
  SupplierID,
  FIRST_VALUE(ProductName) OVER(PARTITION BY SupplierID ORDER BY ProductName)
FROM Products;

Wynik zapytania

Funkcja LAST_VALUE

Funkcja LAST_VALUE() zwraca ostatnią wartość w partycji.

SELECT 
  ProductName, 
  SupplierID,
  LAST_VALUE(ProductName) OVER(PARTITION BY SupplierID ORDER BY SupplierID)
FROM Products;

Wynik zapytania

Jeśli używamy klauzuli ORDER BY to okno obejmuje elementy od pierwszego do bieżącego (nie do ostatniego elementu partycji).

Aby okno obejmowało całą partycję musimy zmienić polecenie

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

na polecenie:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Wynik zapytania

Parametry OFFSET i DEFAULT

Funkcje LEAD() i LAG() dają możliwość sięgania dalej niż najbliższy sąsiad oraz generowania wartości domyślnej. Służą do tego opcjonalne parametry: offset i default:

SELECT 
  ProductName,
  UnitPrice, 
  LEAD(ProductName, 2, 'No Next Product') OVER (ORDER BY UnitPrice) AS NextProduct
FROM Products
ORDER BY UnitPrice;

Wynik zapytania

Wyjaśnienie LEAD(ProductName, 2, 'No Next Product’):

ProductName – kolumna, z której chcemy pobrać wartość
2 – OFFSET – oznacza, że interesuje nas wartość z wiersza, który jest 2 wiersze do przodu względem obecnego wiersza
’No Next Product’- DEFAULT – zwraca 'No Next Product’, jeśli dla określonego OFFSET nie ma wiersza.