Funkcje okienkowe SQL w praktyce. Rankingi, sumy i LAG

Raport może wymagać jednocześnie pokazania każdego zamówienia, jego pozycji w rankingu i sumy sprzedaży narastająco. Właśnie wtedy funkcje okienkowe SQL robią największą różnicę: pozwalają analizować powiązane wiersze bez zwijania ich do jednego rekordu. Poniżej pokazuję składnię, praktyczne przykłady, różnice względem GROUP BY oraz pułapki związane z rankingiem, ramą okna i wydajnością.

Co naprawdę robi funkcja okienkowa

Najprościej wyobrazić ją sobie jako obliczenie wykonywane przez okno przesuwające się po wyniku zapytania. Dla każdego wiersza baza widzi nie tylko jego dane, lecz także określony zestaw innych rekordów. Dzięki temu można na przykład pokazać wynagrodzenie pracownika i średnią dla jego działu w tym samym wyniku.

Podstawowa różnica względem agregacji jest bardzo ważna. GROUP BY łączy wiele wierszy w jeden rekord, natomiast funkcja okienkowa dopisuje wynik obliczenia do każdego istniejącego wiersza.

SELECT
    pracownik,
    dzial,
    wynagrodzenie,
    AVG(wynagrodzenie) OVER (PARTITION BY dzial) AS srednia_w_dziale
FROM pracownicy;

Jeżeli dział „IT” ma pięciu pracowników, zapytanie nadal zwróci pięć rekordów. W kolumnie srednia_w_dziale pojawi się ta sama średnia dla każdego z nich. To właśnie sprawia, że można od razu policzyć różnicę względem średniej, procent odchylenia albo oznaczyć osoby przekraczające określony próg.

W praktyce używam tego mechanizmu wszędzie tam, gdzie samo grupowanie byłoby zbyt ubogie. Raport sprzedaży, analiza zachowania użytkownika, historia zmian cen czy kontrola duplikatów zwykle potrzebują zarówno szczegółu, jak i szerszego kontekstu.

Jak czytać OVER, PARTITION BY, ORDER BY i ramę

Każde zapytanie tego typu opiera się na klauzuli OVER. To ona mówi bazie, jakie wiersze należą do analizowanego zbioru i w jakiej kolejności należy je rozpatrywać.

funkcja() OVER (
    PARTITION BY kolumna_grupujaca
    ORDER BY kolumna_sortujaca
    ROWS BETWEEN ... AND ...
)

PARTITION BY tworzy niezależne grupy

PARTITION BY nie zmniejsza liczby rekordów. Dzieli wynik na logiczne części, w których obliczenia zaczynają się od początku. Dla sprzedaży można utworzyć osobne partycje dla każdego sklepu, miesiąca albo klienta.

SELECT
    sklep,
    produkt,
    wartosc,
    SUM(wartosc) OVER (
        PARTITION BY sklep
    ) AS sprzedaz_sklepu
FROM sprzedaz;

Bez PARTITION BY cała tabela wynikowa jest traktowana jak jedna partycja. To częsty błąd, szczególnie przy raportach wielooddziałowych. Wynik może wyglądać poprawnie, ale suma będzie obejmować wszystkie sklepy zamiast tylko tego przypisanego do bieżącego wiersza.

ORDER BY ustala kolejność analizy

Klauzula ORDER BY jest niezbędna przy numerowaniu, rankingach, porównywaniu z poprzednim rekordem i obliczeniach narastających. Sama kolejność w tabeli nie jest gwarantowana, dlatego zawsze trzeba wskazać kolumnę lub zestaw kolumn określających porządek.

SELECT
    data_sprzedazy,
    wartosc,
    SUM(wartosc) OVER (
        ORDER BY data_sprzedazy
    ) AS suma_narastajaco
FROM sprzedaz;

Jeśli dwie transakcje mają identyczną datę, kolejność między nimi może być niejednoznaczna. Przy ważnych raportach dodaję wtedy drugi warunek, na przykład ORDER BY data_sprzedazy, id_transakcji. Stabilny porządek jest szczególnie istotny w rankingach i deduplikacji.

Rama okna decyduje, ile wierszy bierze udział w obliczeniu

Rama, czyli frame, określa fragment partycji używany przez funkcję. Przykład ROWS BETWEEN 2 PRECEDING AND CURRENT ROW oznacza bieżący wiersz oraz dwa wcześniejsze rekordy.

SELECT
    data_sprzedazy,
    wartosc,
    AVG(wartosc) OVER (
        ORDER BY data_sprzedazy
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS srednia_3_transakcji
FROM sprzedaz;

ROWS liczy fizyczne wiersze, a RANGE odnosi się do wartości sortowania i może obejmować także rekordy o tej samej wartości ORDER BY. Przy sumach narastających bezpieczniej jest często wpisać ramę jawnie, zamiast polegać na domyślnym zachowaniu konkretnego silnika.

Najważniejsze rodziny funkcji i ich zastosowania

Funkcje okienkowe można podzielić według rodzaju problemu. Nie każda wymaga PARTITION BY, a część potrzebuje obowiązkowo uporządkowania rekordów.

ROW_NUMBER, RANK i DENSE_RANK

Te funkcje wyglądają podobnie, ale różnie traktują remisy. ROW_NUMBER() nadaje każdemu rekordowi unikalny numer. RANK() przy równych wartościach przydziela tę samą pozycję, a później zostawia lukę. DENSE_RANK() również obsługuje remisy, lecz nie tworzy przerw.

SELECT
    produkt,
    przychod,
    ROW_NUMBER() OVER (ORDER BY przychod DESC) AS numer,
    RANK() OVER (ORDER BY przychod DESC) AS ranking,
    DENSE_RANK() OVER (ORDER BY przychod DESC) AS gesty_ranking
FROM produkty;

Dla przychodów 1000, 1000 i 900 wyniki będą odpowiednio następujące. ROW_NUMBER zwróci 1, 2, 3, RANK da 1, 1, 3, a DENSE_RANK 1, 1, 2. Wybór funkcji nie jest kosmetyczny, bo wpływa na liczbę rekordów w raporcie top N.

LAG i LEAD do porównań okresów

LAG pobiera wartość z wcześniejszego wiersza, a LEAD z kolejnego. W analizie miesięcznej pozwalają policzyć zmianę sprzedaży bez łączenia tabeli z samą sobą.

SELECT
    miesiac,
    przychod,
    LAG(przychod) OVER (
        ORDER BY miesiac
    ) AS poprzedni_przychod,
    przychod - LAG(przychod) OVER (
        ORDER BY miesiac
    ) AS zmiana
FROM sprzedaz_miesieczna;

W pierwszym miesiącu poprzednia wartość będzie równa NULL, ponieważ nie istnieje wcześniejszy rekord. Można podać wartość domyślną, ale przy analizach biznesowych wolę zwykle pozostawić brak danych jako brak danych, zamiast udawać, że zmiana wynosi zero.

Praktyczne zapytania, które rozwiązują realne problemy

Suma narastająca

To jeden z najbardziej użytecznych wzorców. Dla każdego dnia otrzymujemy wartość z danego dnia oraz sumę od początku analizowanego okresu.

SELECT
    data_sprzedazy,
    wartosc,
    SUM(wartosc) OVER (
        PARTITION BY sklep
        ORDER BY data_sprzedazy
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS suma_narastajaco
FROM sprzedaz;

Partycja resetuje licznik dla każdego sklepu, a UNBOUNDED PRECEDING oznacza początek danej partycji. Gdy raport ma być liczony od początku każdego miesiąca, miesiąc trzeba dodać do PARTITION BY, na przykład PARTITION BY sklep, rok, miesiac.

Udział rekordu w całości

Do obliczenia udziału produktu lub zamówienia w całkowitej sprzedaży wystarczy podzielić wartość wiersza przez sumę obliczoną oknem.

SELECT
    produkt,
    wartosc,
    ROUND(
        100.0 * wartosc / SUM(wartosc) OVER (),
        2
    ) AS udzial_procentowy
FROM sprzedaz;

Jeśli udział ma być liczony tylko w obrębie kategorii, dodaj PARTITION BY kategoria. Zwracam uwagę na 100.0, ponieważ w niektórych bazach dzielenie dwóch liczb całkowitych może obciąć część ułamkową.

Najlepszy rekord w każdej grupie

Popularny problem brzmi: wybierz najnowsze zamówienie każdego klienta. Najpierw numerujemy rekordy w grupie, a dopiero potem filtrujemy wynik w zewnętrznym zapytaniu.

WITH numerowane AS (
    SELECT
        zamowienie_id,
        klient_id,
        data_zamowienia,
        ROW_NUMBER() OVER (
            PARTITION BY klient_id
            ORDER BY data_zamowienia DESC, zamowienie_id DESC
        ) AS numer
    FROM zamowienia
)
SELECT
    zamowienie_id,
    klient_id,
    data_zamowienia
FROM numerowane
WHERE numer = 1;

Funkcji okienkowej nie można zwykle umieścić bezpośrednio w WHERE tego samego poziomu zapytania. CTE, czyli nazwane zapytanie pomocnicze, rozwiązuje ten problem i poprawia czytelność. Drugi warunek sortowania rozstrzyga sytuację, gdy klient ma kilka zamówień z identyczną datą.

Średnia krocząca

Średnia z ostatnich siedmiu rekordów może wygładzić dzienne wahania. Trzeba jednak pamiętać, że jest to średnia z siedmiu wierszy, a niekoniecznie z siedmiu dni kalendarzowych.

SELECT
    data,
    liczba_uzytkownikow,
    AVG(liczba_uzytkownikow) OVER (
        ORDER BY data
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS srednia_7_rekordow
FROM aktywnosc;

Jeśli w danych brakuje dni, ten wzorzec obejmie siedem dostępnych obserwacji. Dla prawdziwego okna czasowego trzeba najpierw uzupełnić kalendarz albo użyć możliwości charakterystycznych dla danego systemu bazodanowego.

Ranking, top N i deduplikacja bez utraty danych

Ranking w obrębie grupy jest naturalnym zastosowaniem PARTITION BY. Przykładowo możemy znaleźć trzy najlepiej sprzedające się produkty w każdej kategorii.

WITH ranking AS (
    SELECT
        kategoria,
        produkt,
        przychod,
        ROW_NUMBER() OVER (
            PARTITION BY kategoria
            ORDER BY przychod DESC, produkt
        ) AS pozycja
    FROM wyniki_produktow
)
SELECT
    kategoria,
    produkt,
    przychod
FROM ranking
WHERE pozycja <= 3;

Użyłem ROW_NUMBER, bo wynik ma zawierać dokładnie trzy rekordy z każdej kategorii. Jeżeli wszystkie produkty z równym wynikiem powinny awansować, lepszy będzie RANK albo DENSE_RANK. Wtedy niektóre grupy mogą zwrócić więcej niż trzy wiersze, ale nie zgubimy remisu.

Ta sama technika dobrze działa przy usuwaniu duplikatów logicznych. Numerujemy rekordy według klucza biznesowego, zostawiamy najnowszy wpis, a pozostałe oznaczamy do archiwizacji lub usunięcia. Samo DISTINCT nie wystarczy, gdy chcemy zdecydować, który z kilku podobnych rekordów jest właściwy.

WITH duplikaty AS (
    SELECT
        id,
        email,
        data_aktualizacji,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY data_aktualizacji DESC, id DESC
        ) AS numer
    FROM klienci
)
SELECT *
FROM duplikaty
WHERE numer > 1;

Przed usuwaniem danych zawsze sprawdzam wynik takim zapytaniem jak powyżej. Najpierw kontrola, później operacja modyfikująca. Przy niejednoznacznych regułach duplikat może być w rzeczywistości poprawną historią zmian.

Błędy, wydajność i różnice między silnikami

Najczęstsze błędy logiczne

• Brak ORDER BY przy ROW_NUMBER, LAG lub sumie narastającej.
• Użycie RANK, gdy raport ma zwrócić dokładnie określoną liczbę rekordów.
• Pomylenie ROWS z RANGE przy powtarzających się wartościach sortowania.
• Filtrowanie funkcji okienkowej w WHERE bez CTE lub podzapytania.
• Brak jednoznacznego tie-breakera, czyli dodatkowej kolumny rozstrzygającej remis.
• Założenie, że brakujące daty zachowują się jak dni z wartością zero.

Szczególnie zdradliwa bywa funkcja LAST_VALUE. Przy domyślnej ramie może zwrócić ostatnią wartość do bieżącego wiersza, a nie ostatnią wartość całej partycji. Jeśli potrzebuję końca całej grupy, zapisuję ramę jawnie.

LAST_VALUE(wartosc) OVER (
    PARTITION BY klient_id
    ORDER BY data_zdarzenia
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
)

Wydajność zapytań

Funkcje okienkowe często wymagają posortowania dużego zbioru danych. Przy milionach rekordów koszt może być zauważalny, szczególnie gdy jedno zapytanie używa kilku różnych kombinacji PARTITION BY i ORDER BY.

Pomaga ograniczenie danych przed obliczeniem, wybór potrzebnych kolumn oraz indeksy dopasowane do filtrów i porządku analizy. Nie zakładam jednak automatycznie, że jeden indeks rozwiąże problem. Plan wykonania pokaże, czy baza rzeczywiście z niego korzysta i gdzie powstaje największy koszt.

Przeczytaj również: Bazy danych dla początkujących - SQL, model relacyjny i więcej

PostgreSQL, MySQL i SQL Server

Podstawowa składnia jest podobna w PostgreSQL, MySQL 8+, SQL Server i wielu innych systemach, ale szczegóły potrafią się różnić. Dotyczy to między innymi obsługiwanych funkcji, typów ram, filtrów przy agregatach oraz zachowania dla wartości NULL.

Dlatego przed przeniesieniem zapytania między silnikami sprawdzam trzy rzeczy. Po pierwsze, czy dana funkcja jest dostępna. Po drugie, jakie są domyślne granice ramy. Po trzecie, czy typy danych i dzielenie liczb dadzą ten sam rezultat. SQL jest wspólnym językiem, ale nie każda implementacja mówi tym samym dialektem.

Jak podejść do funkcji okienkowych w praktyce

Najłatwiej nauczyć się tej techniki na jednym małym zbiorze danych. Zaczynam od zapytania bez funkcji, dodaję jedną kolumnę z ROW_NUMBER albo SUM, a potem sprawdzam wynik na ręcznie policzonych kilku rekordach.

1. Określ, co ma być pojedynczym wierszem wyniku.
2. Zdecyduj, czy obliczenie dotyczy całego wyniku, czy grup określonych przez PARTITION BY.
3. Ustal jednoznaczne ORDER BY.
4. Jeśli liczysz zakres rekordów, wpisz ramę ROWS lub RANGE jawnie.
5. Gdy trzeba filtrować wynik funkcji, użyj CTE albo podzapytania.
6. Sprawdź przypadki brzegowe, takie jak remisy, puste wartości i brakujące daty.

W mojej pracy największą oszczędność daje nie znajomość dziesiątek funkcji, lecz umiejętność rozpoznania wzorca. Ranking, porównanie z poprzednim rekordem, suma narastająca i wybór najlepszego wpisu pokrywają dużą część codziennych zadań analitycznych.

Gdy zapytanie staje się długie, można zdefiniować wspólne okno za pomocą klauzuli WINDOW. To ogranicza powtarzanie tej samej partycji i sortowania, choć nie zawsze zmienia plan wykonania.

SELECT
    produkt,
    przychod,
    ROW_NUMBER() OVER w AS pozycja,
    SUM(przychod) OVER w AS suma
FROM wyniki
WINDOW w AS (
    PARTITION BY kategoria
    ORDER BY przychod DESC
);

Od pojedynczego raportu do świadomej analizy danych

Najważniejsza decyzja nie dotyczy nazwy funkcji, lecz tego, jak zdefiniować okno. Zanim napiszę OVER(), ustalam grupę, kolejność i zakres rekordów. Ta kolejność myślenia eliminuje większość błędów szybciej niż bezrefleksyjne kopiowanie gotowych przykładów.

Na początek warto przećwiczyć cztery zapytania: średnią dla grupy, ranking w grupie, sumę narastającą oraz porównanie z poprzednim wierszem. Kiedy te wzorce są zrozumiałe, bardziej złożone raporty SQL stają się składaniem znanych elementów, a nie walką z tajemniczą składnią.