Funkcje okienkowe w SQL: PostgreSQL i MySQL

Dzięki nim w jednym zapytaniu możesz obliczyć ranking produktów, sumę kroczącą, średnią z ostatnich 7 dni albo różnicę między bieżącą a poprzednią wartością. To właśnie one robią z SQL narzędzie do analizy danych, a nie tylko do ich przechowywania.

W tym artykule zobaczysz, jak działają funkcje okienkowe w PostgreSQL i MySQL, jak korzystać z OVER, PARTITION BY, ORDER BY i ramek okna (ROWS/RANGE), oraz jak unikać typowych błędów wydajnościowych przy dużych zestawach danych.

Czym są funkcje okienkowe i po co ich używać

Funkcje okienkowe (window functions) obliczają wartości dla danego wiersza na podstawie zbioru wierszy zdefiniowanego przez okno. W przeciwieństwie do GROUP BY nie agregują danych do jednego wiersza — zachowują kontekst wiersza źródłowego i dodają kolumny analityczne. Dzięki temu w jednym zapytaniu można uzyskać ranking i numerację wierszy, sumy kroczące, średnie ruchome czy porównania do wartości poprzednich/następnych (LAG/LEAD).

SQL funkcje okienkowe są standardem w PostgreSQL oraz w MySQL od wersji 8.0. Zastępują wiele skomplikowanych podzapytań korelowanych i upraszczają logikę raportów. Jeśli dopiero zaczynasz, odśwież składnię SELECT/WHERE/JOIN/GROUP BY: Podstawy SQL: SELECT, WHERE, JOIN, GROUP BY.

Składnia OVER, PARTITION BY i ORDER BY

Trzon składni to klauzula OVER. Definiuje ona okno: PARTITION BY dzieli dane na grupy (partycje), a ORDER BY ustala kolejność wewnątrz partycji. Dodatkowo można zdefiniować ramy okna (frame): ROWS lub RANGE, np. ROWS BETWEEN 5 PRECEDING AND CURRENT ROW. Bez ram, wiele baz domyślnie używa RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, co przy ORDER BY z duplikatami może dawać inne wyniki niż ROWS.

Różnice PostgreSQL/MySQL: oba silniki wspierają OVER PARTITION BY ORDER BY. W przypadku czasu/kwot i duplikatów kolejności preferuj ROWS, bo RANGE grupuje równe wartości (sumuje „wiązki” remisów). MySQL dodatkowo wspiera klauzule typu RANGE BETWEEN INTERVAL '7' DAY PRECEDING dla okien czasowych. Szczegóły różnic: PostgreSQL vs MySQL: różnice, które mają znaczenie.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
-- Przykład: skumulowana sprzedaż klienta w kolejności daty
SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS amount_cum
FROM orders;

Ranking i numeracja wierszy

Do rankingu służą ROW_NUMBER, RANK i DENSE_RANK. ROW_NUMBER nadaje unikalny numer każdemu wierszowi w partycji według ORDER BY. RANK nadaje te same pozycje remisującym (np. 1,1,3), a DENSE_RANK nie zostawia luk (1,1,2). Kluczowe jest stabilne ORDER BY (najlepiej po wielu kolumnach), by ranking był deterministyczny.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Top N produktów w kategorii wg sprzedaży
WITH product_totals AS (
  SELECT category_id, product_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY category_id, product_id
)
SELECT *
FROM (
  SELECT
    category_id,
    product_id,
    total_amount,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY total_amount DESC, product_id
    ) AS rn,
    RANK() OVER (
      PARTITION BY category_id
      ORDER BY total_amount DESC
    ) AS rnk,
    DENSE_RANK() OVER (
      PARTITION BY category_id
      ORDER BY total_amount DESC
    ) AS drnk
  FROM product_totals
) x
WHERE rn <= 3;

Sumy kroczące i średnie ruchome

Sumy kroczące liczymy najczęściej przez SUM(...) OVER z ramą ROWS. Średnie ruchome (np. 7-dniowe) wymagają agregacji do poziomu dnia, a następnie zastosowania ramy okna obejmującej ostatnie N wierszy. ROWS jest bezpieczne przy duplikatach dat po agregacji.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Dzienne przychody i 7-dniowa średnia ruchoma
WITH daily AS (
  SELECT
    order_date::date AS d,       -- w MySQL: DATE(order_date) AS d
    SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date::date      -- w MySQL: GROUP BY DATE(order_date)
)
SELECT
  d,
  revenue,
  SUM(revenue) OVER (
    ORDER BY d
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7d_sum,
  AVG(revenue) OVER (
    ORDER BY d
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7d_avg
FROM daily
ORDER BY d;

W MySQL okna czasowe można też wyrazić jako RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW, a w PostgreSQL jako RANGE BETWEEN INTERVAL '6 days' PRECEDING. Gdy pracujesz z pre-agregacją lub CTE, zajrzyj też do: Podzapytania i CTE w SQL.

Zastosowanie w raportach i analizie danych

Typowe zastosowania: udział procentowy w kategorii (SUM(amount) OVER PARTITION BY), porównania do poprzedniej wartości (LAG), do następnej (LEAD), wyszukiwanie pierwszej/ostatniej wartości w grupie (FIRST_VALUE, LAST_VALUE), detekcja zmian statusu (DIFFERENCE via LAG/LEAD). Funkcje okienkowe umieszczamy w SELECT, ORDER BY i w klauzulach analitycznych; nie można ich używać w WHERE. Filtruj wyniki w zewnętrznym SELECT/CTE.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
-- Udział produktu w sprzedaży kategorii
SELECT
  category_id,
  product_id,
  SUM(amount) AS product_sales,
  SUM(SUM(amount)) OVER (PARTITION BY category_id) AS category_sales,
  SUM(amount) / SUM(SUM(amount)) OVER (PARTITION BY category_id) AS pct_in_category
FROM orders
GROUP BY category_id, product_id;

Wydajność funkcji okienkowych

Funkcje okienkowe wymagają sortowania w obrębie PARTITION BY/ORDER BY. To koszt O(n log n) i potencjalne użycie pamięci/dysku tymczasowego. Praktyka: indeksy pokrywające na (PARTITION BY, ORDER BY) obniżają koszt sortowania; unikaj niepotrzebnie szerokich wierszy (SELECT tylko potrzebne kolumny); ogranicz liczbę ramek i funkcji w jednym zapytaniu.

W PostgreSQL sprawdzaj plan wykonania SQL przez EXPLAIN (ANALYZE, BUFFERS). MySQL 8.0: EXPLAIN FORMAT=JSON pokaże temp tables i filesort. Okrojenie danych wcześniej (WHERE), pre-agregacja i właściwe PARTITION BY często dają większy efekt niż mikrotuning ramek. Warto też wiedzieć, że różne ramy okna (ROWS vs RANGE) mogą mieć inne koszty. Więcej o doborze indeksów i diagnostyce: Optymalizacja zapytań SQL i analiza planów wykonania.

Przykładowe zadania i rozwiązania

• Top 3 produkty per kategoria (filtr po ROW_NUMBER):

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH totals AS (
  SELECT category_id, product_id, SUM(amount) AS s
  FROM orders
  GROUP BY category_id, product_id
),
ranked AS (
  SELECT
    t.*,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY s DESC, product_id
    ) AS rn
  FROM totals t
)
SELECT category_id, product_id, s
FROM ranked
WHERE rn <= 3;

• Month-over-month (MoM) zmiana przychodów:

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS m,      -- MySQL: DATE_FORMAT(order_date, '%Y-%m-01')
    SUM(amount) AS rev
  FROM orders
  GROUP BY 1
)
SELECT
  m,
  rev,
  LAG(rev) OVER (ORDER BY m) AS rev_prev,
  CASE WHEN LAG(rev) OVER (ORDER BY m) IS NULL THEN NULL
       ELSE (rev - LAG(rev) OVER (ORDER BY m)) / NULLIF(LAG(rev) OVER (ORDER BY m), 0)
  END AS mom_change
FROM monthly
ORDER BY m;

• Udział produktu w sprzedaży kategorii i pozycja w rankingu:

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH agg AS (
  SELECT category_id, product_id, SUM(amount) AS s
  FROM orders
  GROUP BY category_id, product_id
)
SELECT
  category_id,
  product_id,
  s,
  s / SUM(s) OVER (PARTITION BY category_id) AS pct_in_cat,
  DENSE_RANK() OVER (PARTITION BY category_id ORDER BY s DESC) AS pos
FROM agg
ORDER BY category_id, pos, product_id;

Przy złożonych raportach zadbaj o indeksy zgodne z PARTITION BY/ORDER BY (np. (category_id, s) po materializacji). Dalsze wskazówki: Indeksy w SQL: teoria i praktyka.