Оконные функции в SQL: ROW_NUMBER, RANK, LAG и нарастающий итог без сворачивания строк

Новички часто считают окна продвинутой экзотикой. На деле они заменяют самосоединение таблицы и коррелированный подзапрос в задачах вроде «предыдущий заказ того же клиента» или «последняя версия записи». Запрос с OVER читается проще и обычно обходится одной сортировкой вместо многократного перебора строк.

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

Сначала база отбирает строки по WHERE, группирует их и фильтрует через HAVING. Затем для каждой строки она берёт строки с тем же значением PARTITION BY, сортирует их по ORDER BY и вырезает рамку. Функция считает результат по рамке и пишет его в новый столбец, а строки остаются на месте.

Отсюда первое практическое правило. Оконную функцию нельзя поставить в WHERE: по документации PostgreSQL 18, раздел 4.2.8, её вызовы допустимы только в списке SELECT и в ORDER BY. Поэтому выборка «первый заказ каждого клиента» собирается в два этажа: внутри WITH функция нумерует строки, снаружи условие оставляет нужный номер.

Учебный пример: дубли, топ-3 и пауза между покупками

Ситуация учебная. В CRM интернет-магазина копятся заявки из сайта, чата и звонков, и один человек часто оставляет две-три заявки с одним телефоном. Отделу продаж нужна одна, самая свежая. Таблица leads содержит поля id, phone, source и created_at.

WITH ranked AS (
  SELECT id, phone, source, created_at,
         ROW_NUMBER() OVER (PARTITION BY phone
                            ORDER BY created_at DESC, id DESC) AS rn
  FROM leads
)
SELECT id, phone, source, created_at
FROM ranked
WHERE rn = 1;

Второй ключ, id DESC, здесь не украшение: если две заявки пришли в одну секунду, без него база вправе пронумеровать их в любом порядке, и два запуска вернут разные строки.

Теперь топ-3 товаров в каждой категории. Допустим, в категории «Чайники» у двух моделей по 500 продаж, у третьей 300, у четвёртой 200. ROW_NUMBER выдаст 1, 2, 3, 4, случайно выбрав, какая из равных первая. RANK выдаст 1, 1, 3, 4: равные делят место, следующее пропускается. DENSE_RANK выдаст 1, 1, 2, 3, и условие «место не ниже третьего» вернёт четыре товара. «Ровно три строки» и «все, кто в тройке лидеров» — разные требования, и выбирает между ними заказчик отчёта.

Третья задача — пауза между покупками, с которой начинается анализ удержания:

SELECT client_id, created_at,
       created_at::date
         - LAG(created_at::date) OVER (PARTITION BY client_id
                                       ORDER BY created_at) AS days_since_prev
FROM orders
WHERE status = 'paid';

У первого заказа LAG вернёт NULL: предыдущей покупки нет. Если подставить ноль третьим аргументом, новые клиенты смешаются с купившими дважды за день. По этому столбцу считают долю вернувшихся в течение 30 дней. Тот же приём с MIN(created_at) OVER (PARTITION BY client_id) даёт дату первой покупки, по которой клиентов раскладывают на когорты.

Скользящее среднее: семь строк или семь дней

Нарастающий итог задают явной рамкой: SUM(revenue) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Явная рамка полезна всегда: тот, кто будет читать запрос, не должен помнить умолчания конкретной СУБД.

Со скользящим средним тоньше. Обычная запись среднего за неделю — AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Но в режиме ROWS число означает строки, а не дни, как определено в том же разделе документации PostgreSQL. Если в агрегате по дням нет строк за 1–3 января, «неделя» для 4 января растянется на десять календарных дней, и график сгладит провал.

В режиме RANGE смещение означает разницу значений столбца сортировки, и для дат PostgreSQL принимает интервал. Вариант ниже берёт ровно семь календарных дней, сколько бы строк в них ни оказалось. Делить сумму стоит на 7, а не брать AVG: среднее по существующим строкам снова исключит дни без продаж.

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day
         RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) / 7.0 AS avg_7d
FROM daily;

Перенести этот запрос в Microsoft SQL Server не получится. В описании предложения OVER на Microsoft Learn (обновлено 21 сентября 2026 года) сказано, что RANGE нельзя использовать с числовым смещением PRECEDING или FOLLOWING. В T-SQL сначала строят календарь всех дней, присоединяют к нему продажи через LEFT JOIN с нулём вместо пропуска и только потом считают AVG по ROWS BETWEEN 6 PRECEDING. В ClickHouse RANGE со смещением работает, но, по документации ClickHouse об оконных функциях, синтаксис INTERVAL для дат не поддерживается: смещение задают числом в единицах выражения сортировки, и при ORDER BY toDate(ts) единица — день.

Поэтому перед любым скользящим расчётом проверьте, есть ли строка на каждый день, и либо заполните пропуски календарём, либо задайте рамку по значению даты. Первые шесть дней периода всё равно опираются на неполное окно, их лучше подписать на графике.

Различия СУБД и скорость

Базовый синтаксис OVER одинаков в PostgreSQL, MySQL 8, SQL Server и ClickHouse, но детали расходятся. В ClickHouse, кроме RANGE, есть функции lagInFrame и leadInFrame, которые учитывают рамку окна; по документации, чтобы они вели себя как обычные LAG и LEAD, рамку раскрывают на всю группу: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Скорость оконного запроса определяет прежде всего сортировка. Каждое сочетание PARTITION BY и ORDER BY требует своей, поэтому окна с одинаковыми ключами объединяют под одним именем через WINDOW, а лишние строки отсекают фильтром до расчёта. В плане PostgreSQL под узлом WindowAgg видно, сортирует ли база данные сама или читает их по индексу.

С чего начать и как практиковаться

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

1. Установите PostgreSQL и любой клиент, загрузите открытую учебную базу с заказами и клиентами.
2. Посчитайте долю каждого заказа в выручке клиента через SUM(amount) OVER (PARTITION BY client_id) и сравните с тем же расчётом через GROUP BY и JOIN.
3. Пронумеруйте заказы клиентов через ROW_NUMBER, затем замените его на RANK и DENSE_RANK и найдите строки, где результаты разошлись.
4. Через LAG посчитайте паузы между покупками и изменение выручки месяц к месяцу.
5. Постройте нарастающий итог и скользящее среднее, удалите из данных три дня и посмотрите, как изменятся ROWS и RANGE.

Проверка в каждом упражнении одна и та же: число строк на выходе совпадает с числом строк на входе, а итог по окну совпадает с контрольной цифрой из GROUP BY. Если строк стало больше, вы соединили таблицы до расчёта окна и размножили записи.

Если хочется пройти эти шаги с проверкой заданий, посмотрите онлайн-курс Skillbox «SQL для анализа данных». По описанию на Учи.Онлайн, он рассчитан на два месяца, в блоке продвинутых запросов названы OVER, LAG, LEAD, RANK и ROW_NUMBER, задания проверяют кураторы, а дипломная работа — запросы по заданию условного заказчика с выгрузкой в Excel. В какой СУБД выполняются задания, в обзоре не сказано.

Типичные ошибки

Самая частая ошибка — забыть PARTITION BY. Тогда LAG у первого заказа клиента вернёт последний заказ предыдущего клиента, и пауза между покупками получится отрицательной или нелепо большой. Вторая — сортировка по неуникальному полю в ROW_NUMBER: отчёт проходит проверку, а через неделю те же данные дают другой результат. Третья — расчёт окна после JOIN с таблицей позиций заказа: сумма по клиенту умножается на число позиций.

Есть и ошибка постановки: фраза «покажи топ-10» не говорит, что делать с равными значениями. Уточните у заказчика, сколько строк нужно при равенстве и чем разбивать ничью.

Тем, кто учит SQL сразу на PostgreSQL, подойдёт онлайн-курс Eduson Academy «SQL-разработчик». В описании на Учи.Онлайн указаны работа в DBeaver, отдельный модуль «Подзапросы и оконные функции» с ROW_NUMBER и RANK, тренажёры и восемь бизнес-кейсов. Подробного перечня тем этого модуля в обзоре нет, его стоит запросить у школы.

Кому навык нужен в работе

Продуктовый аналитик через окна считает удержание и паузы между сессиями, BI-аналитик готовит витрину с нарастающими итогами, финансовый аналитик сравнивает месяц с тем же месяцем прошлого года. Разработчику они помогают искать последние версии записей.

Если SQL нужен как часть смены профессии, а не отдельный навык, обратите внимание на онлайн-курс ProductStar «Аналитик данных 2.0». По обзору на Учи.Онлайн, программа рассчитана на восемь месяцев и начинается с Excel; SQL-блок из 15 уроков идёт «от введения до оконных функций», затем разбирает оптимизацию запросов и учебный проект. Python в программе нет, это стоит учитывать, если вы целитесь в продуктовую аналитику крупных компаний.

Как навык влияет на доход

Отдельной надбавки за оконные функции в вакансиях не бывает: они входят в требование «уверенный SQL», а на собеседованиях аналитиков задачи на ROW_NUMBER и LAG встречаются часто. Ориентир дают анкеты в калькуляторе Хабр Карьеры (без фильтров по городу и уровню, проверено 15 октября 2026 года; по методике сервиса это медиана общего дохода вместе с премией за последние шесть месяцев). У аналитика по данным с навыком SQL — 229 166 ₽ по 358 анкетам, у аналитика по данным без фильтра по навыку — 170 000 ₽ по 1 092 анкетам.

Читать эту разницу как цену SQL нельзя: навык чаще указывают опытные специалисты. Практический эффект проще: аналитик, который сам пишет запросы с окнами, берёт задачи по удержанию и витринам, не дожидаясь выгрузки от разработчика.

Как выбрать обучение

Для навыка в текущей работе длинная программа обычно избыточна. Проверьте, названы ли оконные функции отдельной темой, на какой СУБД идут задания, проверяет ли решения человек и есть ли данные с дублями, пропусками дат и равными значениями.

Сравнить школы по формату и условиям обучения поможет рейтинг онлайн-школ с обучением SQL для анализа данных. Сейчас фильтр на этой странице показывает одну школу, поэтому используйте её как отправную точку, а программы остальных сверяйте по описаниям курсов.

Проверить готовность просто: на своей таблице с датами напишите три запроса — последняя запись в группе, изменение к прошлому периоду и среднее за семь календарных дней. Если все три сходятся с контрольными цифрами, учиться дальше стоит производительности и моделированию витрин.