Window-функции SQL: ROWS vs RANGE vs GROUPS — глубокий разбор

Q: Когда default frame — это нормально?

Никогда не оставляй default для SUM/AVG/COUNT с ORDER BY. Для ROW_NUMBER/RANK/DENSE_RANK/LAG/LEAD frame игнорируется — там default не важен.

Q: LAG vs ROWS 1 PRECEDING?

LAG(x) OVER (ORDER BY date) ≈ FIRST_VALUE(x) OVER (ORDER BY date ROWS 1 PRECEDING), но LAG короче и понятнее. Для работы с peers (когда несколько строк в одной дате) поведение разное — проверь на тестовом датасете.

Q: ROWS vs RANGE на больших данных — что быстрее?

Зависит от движка. В Postgres ROWS обычно быстрее (не нужна логика peers). В ClickHouse оба компилируются в efficient code. Прямой выгоды нет, но RANGE с INTERVAL может быть медленнее на 100M+ строк из-за tipa range scanning.

Q: Можно ли смешивать window-функции и GROUP BY?

Да, window-функции применяются ПОСЛЕ GROUP BY и WHERE/HAVING, но ДО SELECT-проекции и ORDER BY:

Q: ClickHouse поддерживает window frames?

С версии 21.x — yes, синтаксис стандартный SQL. Раньше — нет, нужны были workarounds через arrays + arrayMap.

Q: Snowflake / BigQuery — отличия?

Стандарт SQL window functions есть везде. Snowflake — полный SQL:2003 + GROUPS. BigQuery — ROWS / RANGE без GROUPS. Synonyms / dialects: - Snowflake: QUALIFY для фильтрации после window (нет в Postgres). - BigQuery: PERCENTILE_CONT/DISC как window-функции. ---

💡 Никогда не работал с SQL? Сначала пройди серию SQL с нуля для аналитика — 10 связанных частей от «что такое БД» до Window Functions.

Оконные функции — главное оружие аналитика в SQL после JOIN и GROUP BY. SUM() OVER (ORDER BY ...) для running totals, LAG() для week-over-week, ROW_NUMBER() для дедупа.

Но есть нюанс, который проваливают даже мидлы на собесах: что такое window frame и чем ROWS отличается от RANGE/GROUPS.

Если ты пишешь SUM(x) OVER (ORDER BY date) — какой frame используется по умолчанию? Какой результат, если в одной дате 5 строк? Этот гайд — про это.

Что такое window frame

Window frame — это подмножество строк внутри партиции, по которому считается оконная функция для текущей строки.

SELECT date, amount,
       SUM(amount) OVER (
         ORDER BY date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales;

Здесь ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — это frame для каждой текущей строки: от начала партиции до неё включительно.

Три типа frame:

ROWS — по физическим строкам (offset = N строк).
RANGE — по значениям ORDER BY (offset = разница в значениях).
GROUPS — по группам строк с одинаковым ORDER BY (PostgreSQL 11+).

ROWS — физические строки

SELECT date, amount,
       SUM(amount) OVER (
         ORDER BY date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7day_sum
FROM sales;

Behavior: для каждой строки берём 7 строк (6 предыдущих + текущая) по порядку date.

Если несколько строк с одинаковой датой, ROWS их считает как отдельные:

date         amount   rolling_7d (ROWS)
2024-01-01   100      100  (только 1 строка в окне)
2024-01-02   50       150
2024-01-02   30       180   ← вторая строка той же даты считается отдельно
2024-01-03   80       260

Use cases:

Running total (cumulative sum)
Moving average на N последних транзакциях
LAG/LEAD на N строк назад (LAG = ROWS BETWEEN 1 PRECEDING)

RANGE — по значениям ORDER BY

SELECT date, amount,
       SUM(amount) OVER (
         ORDER BY date
         RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
       ) AS rolling_7day_sum
FROM sales;

Behavior: для каждой строки берём строки, чьё значение date в диапазоне [date - 6d, date].

В одной дате несколько строк? RANGE их склеивает (peers — строки с одинаковым ORDER BY ключом):

date         amount   rolling_7d (RANGE)
2024-01-01   100      100
2024-01-02   50       180   ← 100 + 50 + 30 (обе строки 2024-01-02)
2024-01-02   30       180   ← такое же значение, peers вместе
2024-01-03   80       260

Use cases:

Time-windowed metrics: «sum за последние 7 дней» — естественнее с RANGE.
Любой расчёт, где date с несколькими событиями = один день, не разные.

Подвох RANGE: до PostgreSQL 11 RANGE поддерживал только UNBOUNDED PRECEDING/FOLLOWING и CURRENT ROW — нельзя было RANGE BETWEEN 6 PRECEDING. С PG11+ работает с INTERVAL и числовыми offset.

GROUPS — по группам peers (PG 11+)

SELECT date, amount,
       SUM(amount) OVER (
         ORDER BY date
         GROUPS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS sum_7_groups
FROM sales;

Behavior: для каждой строки берём 7 уникальных значений date (6 предыдущих + текущая группа). Внутри каждой группы все строки.

Pages dates: 2024-01-01, 2024-01-02, 2024-01-02, 2024-01-03 — 3 группы (01, 02, 03).

date         amount   sum (GROUPS 6 PRECEDING)
2024-01-01   100      100
2024-01-02   50       180   ← включает обе строки 02 + строку 01
2024-01-02   30       180
2024-01-03   80       260   ← все 4 строки (3 группы)

Когда GROUPS: когда «N последних дат» важнее «N строк» или «N временных дней». Реже используется, но иногда — самое корректное решение.

Default frame — главный подвох

SELECT date, amount,
       SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales;

Какой frame по умолчанию? RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — RANGE, не ROWS!

Это значит, если в одной дате несколько строк — все они получат одно и то же значение running_total (peers вместе):

date         amount   running_total (default)
2024-01-01   100      100
2024-01-02   50       180   ← 100 + 50 + 30
2024-01-02   30       180   ← такое же!
2024-01-03   80       260

Если хотел истинный running total построково:

SELECT date, amount,
       SUM(amount) OVER (
         ORDER BY date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales;

Правило: для running total ВСЕГДА указывай ROWS явно. Не полагайся на default.

Источник: postgresql.org docs — window functions.

ROWS vs RANGE: ошибки на собесе

Вопрос на интервью: «Запрос с SUM(amount) OVER (ORDER BY date) — что вернёт, если в дате 2024-01-02 две строки?»

Неправильный ответ: «Running sum построково: 100, 150, 180...» (это ROWS behavior).

Правильный ответ: «По умолчанию RANGE — обе строки 02 января получат одинаковое значение 180. Это peers. Чтобы получить построково — нужно ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.»

Практический пример 1: Running total c учётом peers

-- Cumulative revenue по дням с правильным накоплением (по строкам)
SELECT
  date,
  amount,
  SUM(amount) OVER (
    ORDER BY date, transaction_id  -- transaction_id для tie-breaker
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM transactions
ORDER BY date, transaction_id;

Принципы:

Если в date много строк — добавь transaction_id в ORDER BY для детерминизма.
ROWS для построкового накопления.
В случае peers без tie-breaker, ROWS даёт corrected построковую логику; RANGE склеивает peers.

Практический пример 2: 7-day moving average (правильно)

-- DAU 7-day moving average
SELECT
  date,
  dau,
  AVG(dau) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma7
FROM daily_active_users
ORDER BY date;

Здесь ROWS — окей, потому что:

Один date = одна строка (агрегировано до daily).
7 «последних дат» = 7 строк.

Подвох: на первых 6 днях окно неполное (< 7 строк) — AVG будет считать по доступным. Если хочешь NULL до момента, когда окно полное:

CASE
  WHEN ROW_NUMBER() OVER (ORDER BY date) >= 7
    THEN AVG(dau) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
  ELSE NULL
END AS ma7

Практический пример 3: Rolling 7-day sum по timestamp

Когда даты разрозненны (события не каждый день), ROWS сломается:

-- ВЕРНО: 7 календарных дней назад, неважно сколько событий
SELECT
  event_at,
  amount,
  SUM(amount) OVER (
    ORDER BY event_at
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7d_sum
FROM events;

Почему RANGE: «последние 7 календарных дней» — это про время, не про строки. ROWS дал бы «последние 7 событий», что в датасете без событий каждый день — не то.

Подвох: RANGE INTERVAL требует PostgreSQL 11+, в старых версиях нужен self-join или workaround.

Centered window (moving avg с look-back и look-ahead)

-- 3-day centered moving average (день +/- 1)
SELECT
  date, dau,
  AVG(dau) OVER (
    ORDER BY date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS centered_ma3
FROM daily_active_users;

Useful для smoothing time series без leading-edge bias.

UNBOUNDED PRECEDING/FOLLOWING

-- Доля каждой строки от общей суммы партиции
SELECT
  product_id, revenue,
  revenue * 100.0 / SUM(revenue) OVER (
    PARTITION BY category
    ORDER BY product_id  -- любой ORDER BY
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS pct_of_category
FROM products;

UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING = вся партиция. Эквивалент агрегата без window frame, но позволяет смешивать с другими window-функциями в том же запросе.

FAQ

Когда default frame — это нормально?

Никогда не оставляй default для SUM/AVG/COUNT с ORDER BY. Для ROW_NUMBER/RANK/DENSE_RANK/LAG/LEAD frame игнорируется — там default не важен.

LAG vs ROWS 1 PRECEDING?

LAG(x) OVER (ORDER BY date) ≈ FIRST_VALUE(x) OVER (ORDER BY date ROWS 1 PRECEDING), но LAG короче и понятнее. Для работы с peers (когда несколько строк в одной дате) поведение разное — проверь на тестовом датасете.

ROWS vs RANGE на больших данных — что быстрее?