Jak działa WINDOW FUNCTIONS w języku SQL? Przykłady zastosowania

Gdy pracuję z bazami danych, często potrzebuję wykonywać analizy, które wymagają bardziej zaawansowanych obliczeń niż standardowe funkcje agregujące typu SUM() czy AVG(). I właśnie tutaj na scenę wchodzą window functions, czyli funkcje okna w SQL. Pozwalają one wykonywać operacje na zbiorach wierszy w ramach określonego zestawu danych, bez konieczności grupowania wyników jak przy użyciu GROUP BY.

Co to są WINDOW FUNCTIONS?

Window functions w SQL pozwalają na wykonywanie operacji analitycznych na pewnej części zestawu wyników – zwanej oknem – jednocześnie zwracając wartości dla każdego wiersza z oryginalnej tabeli. Dzięki temu możemy tworzyć bardziej skomplikowane analizy danych w SQL bez pisania skomplikowanych podzapytań.

Funkcje te działają w obrębie pewnej grupy wierszy (okna), które definiujemy w klauzuli OVER(), określając zakres (PARTITION BY) i kolejność (ORDER BY).

Podstawowa składnia funkcji okna SQL

Podstawowa składnia funkcji okna wygląda tak:

funkcja_okna() OVER (
    [PARTITION BY kolumna1, kolumna2, ...]
    [ORDER BY kolumna3 ASC|DESC]
)

Gdzie:

• funkcja_okna() – to funkcja analityczna, np. RANK(), SUM(), AVG().
• PARTITION BY – opcjonalne, dzieli dane na podgrupy (okna).
• ORDER BY – opcjonalne, określa kolejność w obrębie okna.

Przykłady zastosowania funkcji okna

Najlepiej zrozumieć ich działanie na przykładach. Załóżmy, że mamy tabelę sprzedaz z danymi sprzedażowymi:

id	sprzedawca	region	kwota
1	Alicja	Północ	1000
2	Jan	Południe	1200
3	Alicja	Północ	900
4	Jan	Południe	1500
5	Karol	Zachód	800
6	Karol	Zachód	950

SUMA SPRZEDAŻY – PARTITION BY

Chcę obliczyć sumę sprzedaży każdego sprzedawcy, ale bez grupowania wyników, czyli chcę zachować wszystkie rekordy:

SELECT sprzedawca, region, kwota,
       SUM(kwota) OVER(PARTITION BY sprzedawca) AS suma_sprzedazy
FROM sprzedaz;

Wynik:

sprzedawca	region	kwota	suma_sprzedazy
Alicja	Północ	1000	1900
Alicja	Północ	900	1900
Jan	Południe	1200	2700
Jan	Południe	1500	2700
Karol	Zachód	800	1750
Karol	Zachód	950	1750

RANKING SPRZEDAWCÓW – ORDER BY

Teraz chcę nadać każdemu sprzedawcy ranking na podstawie jego sprzedaży od największej do najmniejszej:

SELECT sprzedawca, region, kwota,
       RANK() OVER(ORDER BY kwota DESC) AS ranking
FROM sprzedaz;

Wynik:

sprzedawca	region	kwota	ranking
Jan	Południe	1500	1
Jan	Południe	1200	2
Alicja	Północ	1000	3
Karol	Zachód	950	4
Alicja	Północ	900	5
Karol	Zachód	800	6

RÓŻNICA W SPRZEDAŻY – LEAD()

Funkcja LEAD() umożliwia porównanie wartości z następnym wierszem. Załóżmy, że chcę zobaczyć różnicę sprzedaży danego sprzedawcy między kolejnymi transakcjami:

SELECT sprzedawca, kwota,
       kwota - LEAD(kwota) OVER(PARTITION BY sprzedawca ORDER BY kwota DESC) AS roznica
FROM sprzedaz;

Najpopularniejsze funkcje okna

Oto kilka przydatnych funkcji okna w SQL:

1. SUM() – suma wartości w obrębie okna.
2. AVG() – średnia wartości.
3. RANK() – nadaje numer, pozwalając na ex aequo.
4. DENSE_RANK() – jak RANK(), ale bez pominięcia numeracji.
5. ROW_NUMBER() – unikalny numer wiersza w obrębie okna.
6. LEAD() – zwraca kolejną wartość.
7. LAG() – zwraca poprzednią wartość.

Podsumowanie

Funkcje okna w SQL to potężne narzędzie, które pozwala analizować dane w znacznie bardziej elastyczny sposób niż standardowe funkcje agregujące. Umożliwiają operacje na zbiorach wierszy bez ich grupowania, co czyni je świetnym narzędziem do raportowania i analizy biznesowej. Warto znać i wykorzystywać je w swoich zapytaniach!

 

Inny ciekawy artykuł:

Jak działa RECURSIVE CTE w języku SQL? Przykłady zastosowania