Оконные функции: паттерны и подводные камни

Реши задачу через оконные функции. Задача: {{task}}. Таблица: {{table}}.

Скелет оконной функции

function() OVER (
  PARTITION BY col1, col2     -- группировка окон (опц.)
  ORDER BY col3 ASC NULLS LAST -- порядок внутри окна
  ROWS BETWEEN N PRECEDING AND CURRENT ROW  -- frame
)

Паттерны

1. Running total / cumulative metric

SELECT user_id, event_at, amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY event_at
    ROWS UNBOUNDED PRECEDING
  ) AS lifetime_revenue
FROM payments;

2. Top-N per group

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) AS rn
  FROM items
) WHERE rn <= 3;

ROW_NUMBER — без дублей, RANK — с пропусками (1,1,3), DENSE_RANK — без пропусков (1,1,2).

3. Moving average (7-day)

AVG(value) OVER (
  ORDER BY day
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

4. Sessionization (gap > 30 мин = новая сессия)

WITH gaps AS (
  SELECT user_id, event_at,
    CASE WHEN EXTRACT(EPOCH FROM (event_at - LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at))) > 1800
         THEN 1 ELSE 0 END AS new_session
  FROM events
)
SELECT user_id, event_at,
  SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_at) AS session_id
FROM gaps;

5. Gap detection (пропуски в последовательности)

SELECT id, prev_id, id - prev_id AS gap_size
FROM (SELECT id, LAG(id) OVER (ORDER BY id) AS prev_id FROM t) x
WHERE id - prev_id > 1;

6. Islands (группы подряд идущих значений)

Классика gaps-and-islands: ROW_NUMBER минус ROW_NUMBER в группе даёт стабильный остров-id.

ROWS vs RANGE vs GROUPS

По умолчанию (если не указать frame) — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Для running sum это то что нужно, для moving average — нет.

Производительность

• Window function = один Sort на partition. Если PARTITION BY и ORDER BY совпадают с индексом — Sort скипается
• Несколько window'ов с одинаковой спецификацией — один проход (WindowAgg reuse)
• Избегай оконных функций в WHERE напрямую — нельзя; оборачивай в subquery / CTE
• PARTITION BY с high cardinality (millions) — много мелких сортировок, проверь work_mem
• Альтернатива через self-join обычно в 10-100× медленнее на больших объёмах

Анти-паттерны

• ❌ RANGE когда нужен ROWS — на дубликатах в ORDER BY frame расширяется
• ❌ Забыть ORDER BY в LAG/LEAD — порядок недетерминирован
• ❌ COUNT(*) OVER () для пагинации на больших таблицах — считает всё
• ❌ Использовать window там где хватит GROUP BY — оконки дороже агрегатов
• ❌ Несовпадающий frame в running sum — типичный баг с RANGE по умолчанию