Podstawowe funkcje okienne do analizy

Zanim zaczniemy, wyobraź sobie, że pracujesz z tabelą zawierającą tysiące wierszy sprzedaży. Twoje zadanie: określić, kto z handlowców jest numerem jeden w każdej kategorii, kto jest drugi itd. Albo na przykład musisz ponumerować wszystkie wiersze w zapytaniu, żeby śledzić kolejność. Wszystko to łatwo ogarniesz za pomocą funkcji okiennych.

Funkcje okienne — to funkcje SQL, które działają na podzbiorze wierszy (nazwijmy to "oknem") z zestawu danych. W przeciwieństwie do funkcji agregujących, które łączą wiersze w jeden (np. SUM() albo AVG()), funkcje okienne zostawiają wiersze nietknięte, tylko dodają do nich wyliczone wartości.

Różnica względem funkcji agregujących

Funkcje agregujące "ściskają" dane, grupując wiersze:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Wynik: tylko kilka wierszy, po jednym na każdy dział.

Porównajmy to z funkcją okienną — tutaj wiersze zostają na miejscu, ale pojawia się nowe pole, np. ROW_NUMBER():

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

Dostaniesz te same wiersze, ale z dodatkową kolumną rank_within_department, gdzie każdemu pracownikowi przypisany jest numer w ramach swojego działu.

Podstawowe funkcje okienne

Składnia OVER()

Najważniejsza część każdej funkcji okiennej — to magiczne słowo OVER(). Ono określa, na jakim dokładnie "oknie" danych ta funkcja będzie działać. W środku OVER() możesz ustawić podział na grupy (PARTITION BY) i/lub kolejność sortowania (ORDER BY).

Ogólna składnia:

<funkcja_okienna>() OVER (
   [PARTITION BY <grupa>] 
   [ORDER BY <kolejnosc>]
)

Składniki:

• PARTITION BY: Dzieli wiersze na grupy. Na przykład: "podziel dane według działów".
• ORDER BY: Ustala kolejność przetwarzania wierszy. Na przykład: "sortuj pracowników według pensji od największej do najmniejszej".

Funkcja ROW_NUMBER()

Funkcja ROW_NUMBER() numeruje wiersze, zaczynając od 1, we wskazanym "oknie". Czasem przydaje się do prostego nadania numeru wiersza w tymczasowej tabeli albo do określenia pozycji rekordu.

Przykład. Tabela sales (sprzedaże):

Zapytanie:

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Wynik:

Jak to działa:

1. Dane dzielone są na grupy według product_category.
2. Każda grupa jest sortowana według revenue (malejąco).
3. Wiersze w każdej grupie dostają numer porządkowy.

Funkcja RANK()

Funkcja RANK() służy do rankingowania wierszy. W przeciwieństwie do ROW_NUMBER(), uwzględnia takie same wartości i przeskakuje numery (rangi), jeśli wartości się powtarzają.

Przykład:

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Wynik:

Funkcja DENSE_RANK()

DENSE_RANK() jest podobna do RANK(), z tą różnicą, że nie przeskakuje numerów rang, jeśli są powtarzające się wartości.

Przykład. Dodajmy sprzedaż z takim samym przychodem:

Zapytanie:

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Wynik:

Przykłady użycia: numerowanie wierszy

Zadanie: ponumerować wszystkie zamówienia w tabeli orders, posortowane według daty.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Wynik: dostajesz listę zamówień z numeracją według kolejności realizacji.

Przykłady użycia: top-3 sprzedawców w każdej kategorii

Zadanie: określić trzech najlepszych sprzedawców w każdej kategorii produktów.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

Przykłady użycia: wykrywanie takich samych wyników

Zadanie: sprawdzić, czy są sprzedawcy z takim samym przychodem w każdej kategorii.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Teraz możesz zobaczyć rangi, gdzie "przyklejają się" takie same wartości.