.svg)
.svg)
Представьте задачу: нужно посчитать, какое место занимает каждый пользователь по выручке внутри своей страны, и при этом сохранить все строки таблицы. Или найти, насколько изменилась выручка по сравнению с прошлой неделей - для каждой строки отдельно. Классический GROUP BY здесь не поможет: он схлопывает строки. Подзапросы помогут, но превратят запрос в нечитаемую конструкцию на 40 строк.
Именно для таких задач существуют оконные функции. Они позволяют считать агрегаты, ранги и смещения прямо в SELECT, не теряя детализацию исходных данных. Это один из самых часто проверяемых навыков на мидл-собеседованиях и один из самых полезных инструментов в ежедневной работе продуктового аналитика.
В этой статье разберём, как работает механика окна, какие функции решают какие задачи, и покажем конкретные примеры - retention, скользящие метрики, сравнение периодов.
Коротко:
- Оконные функции считают результат по набору строк, не схлопывая их в одну - в отличие от GROUP BY.
- ROW_NUMBER, RANK и DENSE_RANK нужны для ранжирования; LAG и LEAD - для сравнения с соседними строками по времени.
- SUM OVER и AVG OVER с указанием рамки (ROWS BETWEEN) дают накопительные итоги и скользящие средние.
- Главная конструкция - OVER (PARTITION BY ... ORDER BY ...) - определяет, по каким строкам считается результат.
- Частая ошибка: путать RANK и DENSE_RANK, когда в данных есть дубли.
- Большинство задач retention, динамики и сравнения периодов решаются без единого подзапроса.
Как устроено «окно» и почему это не GROUP BY
Когда вы пишете GROUP BY, база данных группирует строки и возвращает одну строку на группу. Детали теряются. Оконная функция работает иначе: она смотрит на набор строк (окно), считает по ним результат, но возвращает значение для каждой строки отдельно.
Окно задаётся через конструкцию OVER (...). Внутри неё два ключевых параметра:
- PARTITION BY - делит строки на независимые группы, внутри которых считается функция. Аналог GROUP BY, но без схлопывания.
- ORDER BY - задаёт порядок строк внутри каждой группы. Нужен для ранжирования и функций смещения.
Третий необязательный параметр - рамка окна (ROWS BETWEEN или RANGE BETWEEN). Она уточняет, какие именно строки попадают в расчёт относительно текущей. Без рамки большинство агрегатных функций берут все строки раздела.
Пример базовой конструкции:
SELECT user_id, order_date, revenue, SUM(revenue) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative_revenue FROM orders;
Здесь для каждой строки считается накопленная сумма выручки конкретного пользователя с первой его покупки до текущей даты.
Ранжирование: ROW_NUMBER, RANK и DENSE_RANK
Три функции выглядят похоже, но ведут себя по-разному, когда в данных есть одинаковые значения.
| Функция | Что делает при дублях | Когда использовать |
|---|---|---|
| ROW_NUMBER | Присваивает уникальный номер, дубли получают разные значения | Дедупликация, выбор первой строки в группе |
| RANK | Дубли получают одинаковый ранг, следующий ранг пропускается (1,1,3) | Спортивные рейтинги, топ-N с пропусками |
| DENSE_RANK | Дубли получают одинаковый ранг, следующий ранг не пропускается (1,1,2) | Сегментация на уровни без пробелов |
Типичная задача в продуктовой аналитике - найти первую покупку каждого пользователя. Подзапрос с MIN(order_date) работает, но с ROW_NUMBER это читается проще и легче расширяется:
SELECT * FROM ( SELECT user_id, order_date, revenue, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn FROM orders ) t WHERE rn = 1;
Фильтр WHERE rn = 1 оставляет только первую строку каждого пользователя. Если нужны топ-3 заказа - меняем условие на rn <= 3.
Другой сценарий - ранжирование пользователей по выручке внутри каждого канала привлечения. Здесь PARTITION BY channel даст независимый рейтинг для каждого канала, и вы сразу увидите, кто топ-1 в органике, а кто в платном трафике.
Сравнение с предыдущим периодом: LAG и LEAD
LAG возвращает значение из предыдущей строки (по заданному порядку), LEAD - из следующей. Это основной инструмент для расчёта динамики: прирост выручки, изменение конверсии, разница между сессиями.
Синтаксис: LAG(column, offset, default) OVER (PARTITION BY ... ORDER BY ...)
- offset - на сколько строк назад смотреть (по умолчанию 1)
- default - что вернуть, если предыдущей строки нет (для первой строки раздела)
Сравнение недельной выручки с прошлой неделей:
SELECT week_start, revenue, LAG(revenue, 1, 0) OVER (ORDER BY week_start) AS prev_week_revenue, revenue - LAG(revenue, 1, 0) OVER (ORDER BY week_start) AS revenue_diff, ROUND( 100.0 * (revenue - LAG(revenue, 1, 0) OVER (ORDER BY week_start)) / NULLIF(LAG(revenue, 1, 0) OVER (ORDER BY week_start), 0), 1 ) AS pct_change FROM weekly_revenue ORDER BY week_start;
NULLIF защищает от деления на ноль, если прошлая неделя имела нулевую выручку.
LEAD полезен, когда нужно смотреть вперёд. Например, посчитать, сколько дней до следующей покупки пользователя - это сигнал для анализа паттернов повторных заказов.
Дней до следующей покупки:
Вакансии для аналитиков
SELECT user_id, order_date, LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS next_order_date, LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) - order_date AS days_to_next FROM orders;
Скользящее среднее и накопительные суммы
Агрегатные функции SUM, AVG, MIN, MAX становятся оконными, если добавить к ним OVER (...). Поведение меняется в зависимости от того, задана ли рамка.
Накопительный итог (running total) - сумма от начала раздела до текущей строки:
SUM(revenue) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
UNBOUNDED PRECEDING означает «с самого начала раздела». CURRENT ROW - текущая строка. Это явная запись накопительного итога; в большинстве диалектов она работает по умолчанию при наличии ORDER BY.
Скользящее среднее за последние N дней требует явной рамки:
Скользящее среднее за 7 дней:
SELECT event_date, daily_active_users, AVG(daily_active_users) OVER ( ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS dau_7d_avg FROM daily_metrics ORDER BY event_date;
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW включает текущую строку и 6 предыдущих - итого 7 строк. Это сглаживает дневные колебания и показывает тренд.
Разница между ROWS и RANGE важна: ROWS считает по физическим строкам, RANGE - по значениям ORDER BY. Если в данных есть несколько строк с одинаковой датой, RANGE включит их все в одну «рамку», что может дать неожиданный результат. Для большинства задач ROWS безопаснее.
Практика: считаем retention через оконные функции
Retention - доля пользователей, вернувшихся в продукт через N дней после первого визита. Классический подход требует двух подзапросов или CTE. Покажем, как сделать это компактнее.
Допустим, есть таблица user_events с полями user_id и event_date. Нужно посчитать Day 1 retention для каждой когорты.
WITH first_visits AS ( SELECT user_id, MIN(event_date) AS cohort_date FROM user_events GROUP BY user_id ), user_activity AS ( SELECT e.user_id, f.cohort_date, e.event_date, e.event_date - f.cohort_date AS days_since_first FROM user_events e JOIN first_visits f ON e.user_id = f.user_id ) SELECT cohort_date, COUNT(DISTINCT user_id) AS cohort_size, COUNT(DISTINCT CASE WHEN days_since_first = 1 THEN user_id END) AS retained_day1, ROUND( 100.0 * COUNT(DISTINCT CASE WHEN days_since_first = 1 THEN user_id END) / COUNT(DISTINCT user_id), 1 ) AS retention_day1_pct FROM user_activity GROUP BY cohort_date ORDER BY cohort_date;
Здесь оконные функции не нужны - задача решается через CTE и агрегацию. Но следующий шаг - добавить скользящее среднее retention по когортам - уже требует AVG OVER.
Скользящее среднее retention за последние 4 когорты:
SELECT cohort_date, retention_day1_pct, AVG(retention_day1_pct) OVER ( ORDER BY cohort_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW ) AS retention_4w_avg FROM cohort_retention ORDER BY cohort_date;
Это позволяет видеть тренд удержания, не реагируя на случайные всплески в отдельных когортах.
NTILE и процентили: сегментация без магических порогов
NTILE(n) делит строки на n равных частей и присваивает каждой строке номер части. Удобно для быстрой сегментации без ручного задания границ.
Разбивка пользователей на квартили по выручке:
SELECT user_id, total_revenue, NTILE(4) OVER (ORDER BY total_revenue DESC) AS revenue_quartile FROM user_revenue;
Квартиль 1 - топ 25% по выручке, квартиль 4 - нижние 25%. Это быстрая альтернатива RFM-сегментации для первичного анализа.
Для точных процентилей в PostgreSQL есть PERCENTILE_CONT и PERCENTILE_DISC - они работают как агрегатные функции и не требуют OVER. Но NTILE достаточно для большинства задач сегментации.
FIRST_VALUE и LAST_VALUE: якорные значения внутри группы
Иногда нужно не ранжировать строки, а прикрепить к каждой из них значение из первой или последней строки группы. Например, показать рядом с каждой покупкой дату первой покупки пользователя или его первый канал привлечения.
SELECT user_id, order_date, revenue, FIRST_VALUE(order_date) OVER ( PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS first_order_date, FIRST_VALUE(revenue) OVER ( PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS first_order_revenue FROM orders;
Важно явно указывать рамку UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING для LAST_VALUE, иначе по умолчанию рамка заканчивается на текущей строке и LAST_VALUE вернёт значение текущей строки, а не последней в группе.
Типичные ошибки
- Путать RANK и DENSE_RANK при дублях. Если в топ-10 по выручке два пользователя с одинаковой суммой, RANK даст им оба ранг 1 и пропустит ранг 2. Следующий получит ранг 3. DENSE_RANK в той же ситуации даст 1, 1, 2. Если вы фильтруете
WHERE rank <= 10, результаты будут разными. - Не указывать рамку для LAST_VALUE. По умолчанию рамка - от начала раздела до текущей строки, поэтому LAST_VALUE возвращает текущую строку, а не последнюю в группе. Всегда пишите рамку явно.
- Использовать ROWS вместо RANGE при пропусках в датах. Если в данных нет строк за некоторые дни, скользящее среднее за «7 дней» через ROWS BETWEEN 6 PRECEDING будет считаться по 7 строкам, а не по 7 календарным дням. Для точного расчёта по календарным дням нужна предварительная генерация дат.
- Считать оконные функции в WHERE. Результат OVER нельзя фильтровать напрямую в WHERE - только через подзапрос или CTE. Это частая ошибка у тех, кто только начинает работать с этим инструментом.
- Не думать о производительности. Несколько OVER с разными PARTITION BY в одном SELECT заставляют базу делать несколько проходов по данным. На больших таблицах это заметно. Иногда лучше вынести расчёты в CTE.
Когда оконные функции не нужны
Не каждая задача требует этого инструмента. Если нужна просто агрегация без сохранения детализации - GROUP BY проще и быстрее. Если задача - найти одно значение по всей таблице, обычный подзапрос или CTE читается понятнее.
Ещё один случай: очень маленькие таблицы, где производительность не важна и запрос пишется один раз для быстрой проверки гипотезы. Здесь усложнять синтаксис нет смысла.
Также стоит помнить, что не все диалекты SQL поддерживают полный набор возможностей. MySQL до версии 8.0 не поддерживал оконные функции вообще. SQLite поддерживает базовый набор, но без некоторых рамочных конструкций. Перед написанием запроса стоит проверить документацию своей СУБД.
Чеклист: оконные функции в аналитических задачах
- Нужно ранжировать строки внутри группы - используй ROW_NUMBER (уникальные номера) или RANK/DENSE_RANK (с учётом дублей).
- Нужно сравнить текущее значение с предыдущим периодом - LAG с нужным offset.
- Нужно посмотреть, что будет дальше (следующая покупка, следующий статус) - LEAD.
- Нужен накопительный итог - SUM OVER с ORDER BY и рамкой UNBOUNDED PRECEDING AND CURRENT ROW.
- Нужно сгладить метрику и убрать дневные скачки - AVG OVER с ROWS BETWEEN N PRECEDING AND CURRENT ROW.
- Нужна быстрая сегментация без ручных порогов - NTILE(n).
- Нужно прикрепить к каждой строке значение из первой/последней строки группы - FIRST_VALUE / LAST_VALUE с явной рамкой.
- Фильтруешь по результату оконной функции - выноси в CTE или подзапрос, в WHERE напрямую не работает.
- Несколько OVER в одном запросе - проверь, можно ли объединить в один CTE для читаемости и производительности.
FAQ
Чем оконные функции отличаются от GROUP BY?
GROUP BY схлопывает строки: вы получаете одну строку на группу и теряете детали. Оконные функции считают агрегат по набору строк, но возвращают результат для каждой строки отдельно. Это позволяет, например, показать выручку пользователя рядом с его долей от общей выручки - в одном запросе.
Что такое PARTITION BY и обязательно ли его указывать?
PARTITION BY делит данные на независимые группы, внутри которых работает функция. Если не указать PARTITION BY, функция применяется ко всем строкам результата как к одному разделу. Это нормально, например, для глобального ранжирования или накопительного итога по всей таблице.
Как посчитать скользящее среднее за 7 дней в SQL?
Используйте AVG(metric) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Это включает текущую строку и 6 предыдущих. Важно: если в данных есть пропуски по датам, «7 строк» не равно «7 календарным дням» - нужна предварительная генерация дат через generate_series или аналог.
В чём разница между LAG и просто подзапросом с JOIN?
Результат одинаковый, но LAG читается в разы проще и работает быстрее на больших данных. Подзапрос с самосоединением по предыдущей дате требует явного условия связи и дополнительного прохода по таблице. LAG делает то же самое за один проход.
Почему LAST_VALUE возвращает текущую строку, а не последнюю в группе?
По умолчанию рамка окна при наличии ORDER BY - от начала раздела до текущей строки. Поэтому «последняя» строка в этой рамке - текущая. Чтобы получить реально последнюю строку группы, нужно явно указать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Можно ли использовать оконные функции в WHERE?
Нет. Оконные функции вычисляются после WHERE и GROUP BY, поэтому фильтровать по их результату напрямую нельзя. Стандартный подход - завернуть запрос в CTE или подзапрос, а фильтр применить снаружи.
Какие диалекты SQL поддерживают window functions?
PostgreSQL, BigQuery, ClickHouse, Snowflake, Redshift, SQL Server, Oracle - полная поддержка. MySQL - с версии 8.0. SQLite - базовый набор с версии 3.25. Если работаете с MySQL 5.x или старыми версиями MariaDB, оконных функций там нет.
Итог
Оконные функции - это не «продвинутый SQL ради продвинутого SQL». Это конкретный инструмент для конкретного класса задач: ранжирование внутри группы, сравнение с соседними строками по времени, накопительные итоги, сглаживание метрик. Без них те же задачи решаются через громоздкие самосоединения и вложенные подзапросы, которые сложно читать и поддерживать.
Начните с трёх вещей: ROW_NUMBER для дедупликации и выбора первой строки, LAG для сравнения с прошлым периодом, AVG OVER с рамкой для скользящего среднего. Эти три паттерна закрывают 80% задач, с которыми сталкивается продуктовый аналитик в ежедневной работе. Остальное - детали синтаксиса, которые приходят с практикой.