Funkcje okna i CTE (Common Table Expressions) w SQL

Czy Twoje zapytania SQL stają się coraz dłuższe, bardziej skomplikowane i trudne do odczytania? Masz wrażenie, że proste GROUP BY i złączenia (JOIN) to za mało, aby sprostać analitycznym wyzwaniom? Jeśli tak, to ten artykuł jest dla Ciebie! Poznaj Funkcje Okna (Window Functions) oraz CTE (Common Table Expressions) – dwa potężne narzędzia, które odmienią Twój sposób pisania zapytań SQL.

W tym wpisie przeprowadzimy Cię od podstawowych koncepcji do zaawansowanych przykładów, pokazując, jak te funkcje mogą uprościć Twoją pracę i otworzyć nowe możliwości analityczne.

🎯 Część 1: Czym są i dlaczego warto używać CTE (Common Table Expressions)?

Wyobraź sobie, że piszesz bardzo złożone zapytanie z wieloma podzapytaniami. Szybko staje się ono nieczytelne i trudne w utrzymaniu. Tu z pomocą przychodzą CTE!

Common Table Expression to nic innego jak tymczasowy, nazwany zbiór wyników, do którego możesz odwoływać się w kolejnych częściach zapytania (SELECT, INSERT, UPDATE lub DELETE). Można o nim myśleć jak o jednorazowej, inteligentnej tabeli tymczasowej lub widoku.

Kluczowe zalety CTE:

  • Czytelność: Dzielą skomplikowane zapytania na logiczne, łatwe do zrozumienia kroki.
  • Wielokrotne użycie: Możesz wielokrotnie odwoływać się do tego samego CTE w jednym zapytaniu.
  • Rekurencja: Umożliwiają pisanie zapytań rekurencyjnych, co jest idealne do pracy z danymi hierarchicznymi (np. struktury organizacyjne, drzewa kategorii).

Składnia CTE

Składnia jest prosta. Zaczynamy od słowa kluczowego WITH, nadajemy nazwę naszemu CTE, a następnie definiujemy zapytanie w nawiasach.

SQL

WITH MojeCTE AS (
    -- Tutaj umieszczasz zapytanie SELECT, które tworzy tymczasowy zbiór wyników
    SELECT klient_id, SUM(wartosc_zamowienia) AS laczna_wartosc
    FROM zamowienia
    GROUP BY klient_id
)
-- Teraz możesz użyć "MojeCTE" jak zwykłej tabeli
SELECT *
FROM MojeCTE
WHERE laczna_wartosc > 1000;

CTE a podzapytania

Choć podobne, CTE często przewyższają podzapytania czytelnością. Porównajmy:

SQL

-- Z użyciem podzapytania (mniej czytelne)
SELECT *
FROM (
    SELECT klient_id, SUM(wartosc_zamowienia) AS laczna_wartosc
    FROM zamowienia
    GROUP BY klient_id
) AS sprzedaz_klientow
WHERE laczna_wartosc > 1000;

W przypadku wielu kroków pośrednich, zagnieżdżone podzapytania stają się koszmarem. CTE pozwalają na ich sekwencyjne i płaskie ułożenie.

윈 Część 2: Magia Funkcji Okna

Funkcje okna to prawdziwy przełom w analitycznym SQL. Pozwalają one na wykonywanie obliczeń na zbiorze wierszy (nazywanym “oknem” lub “partycją”), które są w jakiś sposób powiązane z bieżącym wierszem.

W przeciwieństwie do GROUP BY, które agreguje wiersze do jednego rekordu, funkcje okna zwracają wartość dla każdego wiersza, zachowując oryginalną liczbę rekordów.

Składnia Funkcji Okna

Kluczem do funkcji okna jest klauzula OVER().

SQL

NAZWA_FUNKCJI() OVER (
    PARTITION BY kolumna1, kolumna2 -- Dzieli wiersze na "okna" (partycje). To jest opcjonalne.
    ORDER BY kolumna3 -- Sortuje wiersze wewnątrz każdej partycji.
    -- ROWS/RANGE ... (opcjonalne, definiuje ramkę wewnątrz partycji)
)
  • PARTITION BY: Działa podobnie do GROUP BY, ale nie zwija wierszy. Tworzy partycje, w ramach których funkcja jest liczona niezależnie.
  • ORDER BY: Określa kolejność wierszy w partycji, co jest kluczowe dla funkcji rankingowych (ROW_NUMBER, RANK) oraz porównawczych (LAG, LEAD).

Popularne Funkcje Okna

  1. Ranking: ROW_NUMBER(), RANK(), DENSE_RANK()
    • Idealne do tworzenia rankingów, np. “top 3 najlepiej sprzedających się produktów w każdej kategorii”.
    SQLSELECT nazwa_produktu, kategoria, sprzedaz, ROW_NUMBER() OVER(PARTITION BY kategoria ORDER BY sprzedaz DESC) as ranking_w_kategorii FROM sprzedaz_produktow;
  2. Porównawcze: LAG() i LEAD()
    • LAG() daje dostęp do wartości z poprzedniego wiersza w partycji.
    • LEAD() daje dostęp do wartości z następnego wiersza.
    • Świetne do obliczania różnic, np. porównania sprzedaży z poprzednim miesiącem.
    SQLSELECT miesiac, sprzedaz, LAG(sprzedaz, 1, 0) OVER(ORDER BY miesiac) as sprzedaz_poprzedni_miesiac FROM sprzedaz_miesieczna;
  3. Agregujące: SUM(), AVG(), COUNT()
    • Mogą działać jak funkcje okna, np. do obliczenia sumy kroczącej lub procentowego udziału w kategorii.
    SQLSELECT pracownik, dzial, pensja, SUM(pensja) OVER(PARTITION BY dzial) as suma_pensji_w_dziale FROM pracownicy;

🤝 Część 3: Połączenie Mocy – CTE i Funkcje Okna razem

Prawdziwa siła tych narzędzi ujawnia się, gdy używamy ich razem. CTE mogą posłużyć do przygotowania i oczyszczenia danych, a następnie na tymczasowym zbiorze wyników możemy zastosować funkcje okna do skomplikowanych analiz.

Przykład: Znajdźmy pracowników w każdym dziale, którzy zarabiają więcej niż średnia w swoim dziale.

SQL

WITH DanePracownikow AS (
    SELECT
        imie,
        nazwisko,
        dzial,
        pensja,
        -- Krok 1: Oblicz średnią pensję dla każdego działu za pomocą funkcji okna
        AVG(pensja) OVER (PARTITION BY dzial) AS srednia_pensja_w_dziale
    FROM pracownicy
)
-- Krok 2: Użyj CTE do prostej filtracji
SELECT
    imie,
    nazwisko,
    dzial,
    pensja,
    srednia_pensja_w_dziale
FROM DanePracownikow
WHERE pensja > srednia_pensja_w_dziale;

Dzięki takiemu podejściu zapytanie jest niezwykle czytelne: najpierw przygotowujemy dane (obliczamy średnią), a potem je filtrujemy.

⚡ Część 4: Wydajność i Dobre Praktyki

  • Indeksuj! Wydajność funkcji okna drastycznie wzrasta, jeśli kolumny użyte w PARTITION BY i ORDER BY są zaindeksowane.
  • Filtruj wcześnie: Zawsze staraj się ograniczyć zbiór danych za pomocą klauzuli WHERE przed zastosowaniem skomplikowanych operacji.
  • CTE a wydajność: W niektórych systemach baz danych (np. starszych wersjach PostgreSQL), CTE mogą działać jak “bariery optymalizacyjne”, co czasem pomaga, a czasem szkodzi. Warto testować wydajność w porównaniu z podzapytaniami. Nowoczesne bazy danych zazwyczaj radzą sobie z tym znacznie lepiej.
  • Różnice między bazami danych: Chociaż składnia jest w dużej mierze spójna, istnieją drobne różnice w implementacji i dostępnych funkcjach między systemami takimi jak PostgreSQL, SQL Server, MySQL czy Oracle. Zawsze warto sprawdzić dokumentację swojej bazy danych.

Podsumowanie

Opanowanie Common Table Expressions i funkcji okna to krok milowy w rozwoju umiejętności SQL. Pozwalają one pisać czystsze, bardziej czytelne i znacznie potężniejsze zapytania analityczne. Zamiast męczyć się z zagnieżdżonymi podzapytaniami i skomplikowanymi złączeniami, możesz tworzyć eleganckie i wydajne rozwiązania.

Nie bój się eksperymentować! Zacznij od prostych rankingów i sum kroczących, a szybko odkryjesz, jak wiele problemów, które wydawały się trudne, staje się prostszych z odpowiednimi narzędziami.

Źródła

  • Oficjalna dokumentacja PostgreSQL dotycząca zapytań WITH (CTE).
  • Oficjalna dokumentacja PostgreSQL dotycząca funkcji okna.
  • Oficjalna dokumentacja Microsoft SQL Server dotycząca WITH common_table_expression.
  • Oficjalna dokumentacja Microsoft SQL Server dotycząca klauzuli OVER.
  • Oficjalna dokumentacja MySQL 8.0 dotycząca WITH (Common Table Expressions).
  • Oficjalna dokumentacja MySQL 8.0 dotycząca funkcji okna.
  • Artykuł “SQL Window Functions and CTEs” na portalu Medium.
  • Artykuł “CTE vs Subquery in SQL” na portalu Geekflare.
  • Tutorial “CTEs and Window Functions” na stronie Crunchy Data.