Оконные функции SQL

Оконные функции (window functions) — способ посчитать агрегат по группе строк, не схлопывая их. Обычный GROUP BY из десяти строк делает одну; оконная функция оставляет все десять и к каждой приписывает значение, посчитанное по её «окну» — набору соседних строк.

Отсюда весь класс задач, который без них решается через подзапросы и самосоединения: нарастающий итог, место внутри категории, доля строки от общего, разница с предыдущим периодом. В продуктовой аналитике это ежедневный инструмент — когорты, воронки по шагам, первое и последнее событие пользователя.

Как устроен синтаксис?

ФУНКЦИЯ() OVER (PARTITION BY ... ORDER BY ... ROWS ...)

• OVER — превращает функцию в оконную. Пустой OVER () означает «окно — вся таблица».
• PARTITION BY — делит строки на группы. Аналог GROUP BY, но без схлопывания.
• ORDER BY — порядок внутри окна. Обязателен для ранжирования и нарастающих итогов: без него понятие «предыдущая строка» не определено.
• ROWS / RANGE — граница окна: например, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW для скользящего среднего за 7 дней.

Какие функции чаще всего нужны?

Пример с числами

Задача: для каждого месяца показать выручку, накопленный итог и прирост к прошлому месяцу.

SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month) AS running_total,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue
ORDER BY month;

Тот же результат через подзапросы занял бы вчетверо больше строк и читался бы вдвое хуже. Классический пример на собеседовании — «второй по величине заказ каждого клиента»: через ROW_NUMBER() с PARTITION BY это четыре строки, через самосоединение — пятнадцать и ошибка при дублях.

Где чаще всего ошибаются?

• Фильтр по окну в WHERE. WHERE ROW_NUMBER() OVER (...) = 1 не работает. Оберните запрос в CTE и фильтруйте снаружи.
• RANK() там, где нужен ROW_NUMBER(). При одинаковых значениях RANK() вернёт несколько первых мест, и «взять первую строку» перестанет давать одну строку.
• Забыть ORDER BY внутри окна. Нарастающий итог без порядка вернёт общую сумму по всем строкам — тихо и без ошибки.
• Путать ROWS и RANGE. При одинаковых значениях в ORDER BY они дают разный результат: RANGE включает все строки-дубликаты, ROWS — строго заданное количество.
• Тяжёлое окно на больших таблицах. PARTITION BY по полю высокой кардинальности без подходящего индекса приводит к сортировке всей таблицы.

Связанные термины

• Когортный анализ — задача, которую на SQL почти всегда пишут через оконные функции.
• Медиана — считается оконной PERCENTILE_CONT.
• Процентиль — то же семейство функций для распределений.