SQL dla analityków danych: funkcje okienkowe, CTE i zaawansowane zapytania

Funkcje okienkowe SQL, CTE (Common Table Expressions) oraz zaawansowane wzorce zapytań stanowią fundament analitycznego SQL. Niezależnie od tego, czy chodzi o przygotowanie do rozmowy kwalifikacyjnej na stanowisko analityka danych, czy o tworzenie złożonych raportów — te techniki przekształcają rozwlekłe, trudne do odczytania podzapytania w czytelny i wydajny kod SQL.

Funkcje okienkowe SQL i klauzula OVER

Funkcja okienkowa stosuje obliczenie na zdefiniowanym oknie wierszy. Klauzula OVER kontroluje, które wiersze należą do tego okna i w jakiej kolejności są przetwarzane. W odróżnieniu od funkcji agregujących z GROUP BY, funkcje okienkowe zachowują każdy pojedynczy wiersz w zbiorze wynikowym.

Ogólna składnia jest spójna we wszystkich głównych systemach bazodanowych — PostgreSQL, MySQL 8+, BigQuery, SQL Server i Snowflake.

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  -- Ranks employees within each department by salary
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  -- Running total of salaries within department
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

PARTITION BY dzieli dane na grupy (podobnie jak GROUP BY, ale bez zwijania wierszy). ORDER BY określa kolejność wewnątrz każdej partycji. Opcjonalna klauzula ramki (ROWS BETWEEN ...) zawęża okno do określonego zakresu wierszy.

ROW_NUMBER, RANK i DENSE_RANK — porównanie

Te trzy funkcje rankingowe wyglądają podobnie, ale zachowują się odmiennie w przypadku remisów. Wybór niewłaściwej funkcji to częste źródło błędów w zapytaniach analitycznych.

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

ROW_NUMBER zawsze przypisuje unikalny numer sekwencyjny — nawet w przypadku remisów jeden wiersz arbitralnie otrzymuje niższy numer. RANK przypisuje taki sam numer dla remisów, ale pomija kolejne numery (1, 1, 3). DENSE_RANK również obsługuje remisy, lecz nigdy nie pomija numerów (1, 1, 2). Przy zapytaniach typu top-N, gdzie duplikaty powinny dzielić pozycję, DENSE_RANK jest zazwyczaj właściwym wyborem.

LAG i LEAD — analiza okres do okresu

LAG i LEAD umożliwiają dostęp do danych z poprzednich lub następnych wierszy bez konieczności stosowania self-joinów. Funkcje te są niezbędne do obliczania wzrostu w ujęciu okres do okresu, wykrywania trendów i identyfikowania zmian.

-- period_over_period.sql
SELECT
  month,
  revenue,
  -- Previous month revenue
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  -- Month-over-month growth rate
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  -- Next month revenue (forward-looking)
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

Drugi argument LAG/LEAD określa przesunięcie (domyślnie 1). Trzeci opcjonalny argument podaje wartość domyślną, gdy wiersz o danym przesunięciu nie istnieje — przydatne do unikania NULL w pierwszym i ostatnim wierszu. NULLIF w obliczeniu wzrostu zapobiega błędom dzielenia przez zero.

NTILE i podział na kwartyle

NTILE rozdziela wiersze na określoną liczbę w przybliżeniu równych grup. Analitycy danych wykorzystują tę funkcję do analizy kwartylowej, scoringu decylowego i segmentacji klientów.

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  -- Split customers into 4 quartiles by spend
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  -- Decile scoring for finer granularity
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

Kwartyl 1 zawiera klientów o najwyższych wydatkach, kwartyl 4 — o najniższych. Ten wzorzec bezpośrednio odpowiada segmentacji RFM (Recency, Frequency, Monetary) stosowanej w analityce marketingowej.

Common Table Expressions — zamiennik zagnieżdżonych podzapytań

CTE (klauzule WITH) rozbijają złożone zapytania na nazwane, czytelne kroki. Każde CTE działa jako tymczasowy, nazwany zbiór wynikowy istniejący tylko na czas wykonywania zapytania. Poza czytelnością CTE ułatwiają debugowanie — każdy krok można przetestować niezależnie.

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  -- Step 1: Aggregate raw orders into monthly totals
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  -- Step 2: Rank categories within each month
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
-- Step 3: Final output — top 3 categories per month
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

To trzystopniowe podejście zastępuje głęboko zagnieżdżone podzapytanie. Każde CTE ma jasno określoną odpowiedzialność: agregacja, rankingowanie i filtrowanie.

Rekurencyjne CTE dla danych hierarchicznych

Rekurencyjne CTE rozwiązują problemy dotyczące danych hierarchicznych lub grafowych — struktury organizacyjne, drzewa kategorii, zestawienia materiałowe i zapytania o ścieżki.

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  -- Base case: top-level managers (no manager above them)
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: join employees to their managers
  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

Przypadek bazowy wybiera węzły korzeniowe (pracowników bez przełożonego). Przypadek rekurencyjny łączy się z powrotem z samym CTE, budując hierarchię poziom po poziomie. Kolumna management_chain łączy nazwy, pokazując pełną ścieżkę podległości służbowej. Większość baz danych ogranicza głębokość rekurencji, aby zapobiec nieskończonym pętlom — PostgreSQL domyślnie ustawia limit na 100 iteracji.

Zaawansowane wzorce analityczne — luki, wyspy i sumy bieżące

Wzorzec luk i wysp (gaps and islands) identyfikuje ciągi kolejnych wartości w danych — aktywne okresy subskrypcji, kolejne dni logowań lub nieprzerwane serie produkcyjne. Technika ta łączy ROW_NUMBER z arytmetyką dat.

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    -- Subtracting row_number from date creates a constant for consecutive days
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

Kluczowa obserwacja: dla kolejnych dat odjęcie rosnącego numeru wiersza od daty zawsze daje tę samą wartość. Gdy pojawia się przerwa, wynikowa wartość przesuwa się, tworząc nową grupę. Powyższe zapytanie znajduje wszystkie serie logowań trwające co najmniej 3 kolejne dni dla każdego użytkownika.

Łączenie funkcji okienkowych z CASE w analityce warunkowej

W praktyce zapytania analityczne często łączą funkcje okienkowe z wyrażeniami CASE w celu obliczania metryk warunkowych w ramach jednego zapytania.

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  -- 7-day moving average
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  -- Cumulative sum resetting each quarter
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  -- Flag if current row exceeds 2x the category average
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

To pojedyncze zapytanie oblicza 7-dniową średnią kroczącą, skumulowaną sumę od początku kwartału resetującą się co kwartał oraz flagę wartości odstających — wszystko bez podzapytań i self-joinów.

Optymalizacja wydajności zapytań analitycznych

Funkcje okienkowe i CTE są potężnym narzędziem, ale mogą stać się wąskim gardłem na dużych tabelach. Kilka technik pozwala utrzymać wydajność na odpowiednim poziomie.

Po pierwsze, należy zaindeksować kolumny używane w klauzulach PARTITION BY i ORDER BY. Indeks złożony odpowiadający definicji okna eliminuje operacje sortowania.

Po drugie, dane powinny być filtrowane przed zastosowaniem funkcji okienkowych. Umieszczenie warunków w klauzuli WHERE (przed ewaluacją funkcji okienkowej) zmniejsza zbiór danych, który okno musi przetworzyć.

Po trzecie, warto unikać powtarzania definicji okien. Nazwane okna redukują powtórzenia i sygnalizują optymalizatorowi, że wiele funkcji współdzieli tę samą ramkę:

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

Klauzula WINDOW (obsługiwana w PostgreSQL, MySQL 8+ i BigQuery) definiuje okno jednokrotnie i wykorzystuje je ponownie w wielu funkcjach. Poprawia to zarówno czytelność, jak i potencjalną optymalizację planu zapytania.

Podsumowanie

• Funkcje okienkowe (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) operują na zbiorach wierszy bez ich zwijania — w odróżnieniu od agregatów z GROUP BY
• CTE rozbijają złożone zapytania na nazwane, testowalne kroki i zastępują głęboko zagnieżdżone podzapytania
• Rekurencyjne CTE obsługują dane hierarchiczne, takie jak struktury organizacyjne i drzewa kategorii, ale wymagają limitów głębokości i zaindeksowanych kolumn złączeń
• Technika luk i wysp (ROW_NUMBER + arytmetyka dat) identyfikuje kolejne sekwencje w danych czasowych
• Nazwane okna (klauzula WINDOW) redukują duplikację kodu i mogą poprawić optymalizację planu zapytania
• Filtrowanie danych w klauzuli WHERE przed ewaluacją funkcji okienkowych pozwala utrzymać wydajność na dużych tabelach
• Te wzorce pojawiają się regularnie na rozmowach kwalifikacyjnych z analizy danych i bezpośrednio znajdują zastosowanie w codziennym raportowaniu, segmentacji i analizie trendów