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

Оконная функция считает по группе строк, но строки не схлопывает. GROUP BY из шести заказов делает три итога и теряет исходные записи. Окно оставляет все шесть, а рядом дописывает расчёт: сумму по клиенту, номер в рейтинге, значение предыдущей строки. Отсюда и название — функция смотрит на соседей через «окно», не выходя из своей строки.

SQL / 01koddo

Оконная сумма дописывается к каждой строке

Весь разбор — на одной таблице orders:

Пользователи: 1 — Аня, 2 — Борис, 3 — Вера.

Чем оконная функция отличается от GROUP BY

GROUP BY возвращает по одной строке на группу. Оконная функция возвращает столько строк, сколько было на входе.

SELECT user_id, sum(amount) AS total
FROM orders
GROUP BY user_id;

Три строки, отдельных заказов больше нет. Теперь то же самое окном:

SELECT id, user_id, amount,
       sum(amount) OVER (PARTITION BY user_id) AS user_total
FROM orders;

Шесть заказов на месте, к каждому приписан итог его клиента. Это и есть типовой повод взять окно: показать строку и её долю в целом одновременно. Через GROUP BY так не выйдет — пришлось бы считать итоги отдельным запросом и присоединять обратно.

Из чего состоит OVER

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

функция(...) OVER (PARTITION BY колонка ORDER BY колонка)

• PARTITION BY режет таблицу на независимые группы. Расчёт идёт внутри каждой отдельно, между группами данные не перетекают. Без PARTITION BY вся выборка — одно окно.
• ORDER BY задаёт порядок строк внутри группы. Он нужен всему, что зависит от последовательности: нумерации, рангу, соседним строкам, накопительным итогам.

OVER () с пустыми скобками — тоже законная запись: одно окно на всю выборку без сортировки.

ROW_NUMBER, RANK и DENSE_RANK: в чём разница

Все три нумеруют строки, различаются обработкой одинаковых значений. У Бориса два заказа по 1200 — на них разница и видна.

SELECT id, amount,
       row_number() OVER (ORDER BY amount DESC) AS rn,
       rank()       OVER (ORDER BY amount DESC) AS rnk,
       dense_rank() OVER (ORDER BY amount DESC) AS dense
FROM orders;

• row_number() нумерует подряд и никогда не повторяется — двум одинаковым суммам всё равно достанутся 2 и 3.
• rank() даёт равным строкам общий номер, а потом прыгает через пропуск: после двух вторых мест идёт четвёртое.
• dense_rank() тоже уравнивает, но пропусков не делает: 1, 2, 2, 3.

Разница между этими нумерациями нужна не только для призовых мест: на ней стоит классический приём поиска непрерывных серий — gaps and islands, где разность двух нумераций и оказывается признаком одной серии.

Тонкость, о которой спрашивают следом: какая из двух строк по 1200 получит номер 2, а какая 3, база решает сама — при равенстве ключа сортировки порядок не определён. Для воспроизводимого row_number() добавь второй ключ в его окно: ORDER BY amount DESC, id. У rank() и dense_rank() оставь сортировку только по сумме, если одинаковые суммы должны сохранять общий ранг.

LAG и LEAD: сравнить строку с соседней

lag() берёт значение из предыдущей строки окна, lead() — из следующей. Это готовый ответ на вопросы вида «насколько заказ отличается от прошлого».

SELECT id, user_id, day, amount,
       lag(amount) OVER (PARTITION BY user_id ORDER BY day) AS prev_amount
FROM orders;

У первого заказа каждого клиента предыдущего нет, поэтому NULL. Считать разницу напрямую (amount - lag(amount) OVER (...)) тоже даст NULL — подставь запасное значение третьим аргументом: lag(amount, 1, 0).

Как посчитать накопительный итог

Сумма с ORDER BY внутри окна перестаёт быть общим итогом и становится накопительной: каждая строка видит себя и всё, что было раньше.

SELECT day, amount,
       sum(amount) OVER (ORDER BY day) AS running_total
FROM orders;

Присмотрись к двум строкам за 3 июня: у обеих итог 4500, хотя суммы разные. Это не ошибка, а поведение рамки по умолчанию — про неё дальше.

Рамка окна: почему ROWS и RANGE дают разный ответ

Когда в окне есть ORDER BY, база молча дописывает рамку RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE работает не по строкам, а по значениям ключа сортировки: все строки с одинаковым day считаются одной позицией и получают общий итог.

Нужен воспроизводимый пошаговый итог — задай рамку явно в строках и дополни сортировку уникальным id:

SELECT day, amount,
       sum(amount) OVER (ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders
ORDER BY day, id;

Итоговая сумма сошлась, промежуточные — нет. ROWS считает по строкам, а id определяет, какая из двух записей за 3 июня попадёт в накопительный итог первой. Одного ROWS при неуникальном ключе недостаточно: промежуточные суммы могут меняться вместе с порядком равных строк. Для дневного итога сначала сгруппируй данные по дню, а для пошагового задай и ROWS, и однозначную сортировку.

Топ-1 в каждой группе

Самый частый практический сценарий: последний заказ каждого клиента, бестселлер каждой категории, первое событие каждой сессии. Схема одна — пронумеровать внутри группы, затем отобрать номер 1.

SELECT id, user_id, day, amount
FROM (
  SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY day DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

Подзапрос здесь не для красоты, он обязателен — и вот почему.

Почему оконную функцию нельзя написать в WHERE

Запрос выполняется в фиксированном порядке: FROM → WHERE → GROUP BY → HAVING → оконные функции → SELECT → ORDER BY → LIMIT. Окна считаются после WHERE, поэтому в момент фильтрации их результата ещё не существует.

-- не выполнится
SELECT * FROM orders
WHERE row_number() OVER (PARTITION BY user_id ORDER BY day DESC) = 1;

PostgreSQL ответит window functions are not allowed in WHERE. Обходов два: подзапрос или CTE (WITH ranked AS (...) SELECT * FROM ranked WHERE rn = 1) — работают везде. В ClickHouse, Snowflake и BigQuery есть сокращение QUALIFY, которое фильтрует по окну напрямую, но в PostgreSQL и MySQL его нет.

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

Частые вопросы

В каких СУБД работают оконные функции

Это стандарт SQL:2003. PostgreSQL поддерживает их с версии 8.4, SQL Server — с 2005, MySQL — только с 8.0, SQLite — с 3.25. На MySQL 5.7 запрос с OVER не выполнится, там всё ещё пишут через переменные или самосоединения.

Можно ли использовать окно вместе с GROUP BY

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

SELECT day, sum(amount) AS day_revenue,
       sum(sum(amount)) OVER (ORDER BY day) AS running_total
FROM orders
GROUP BY day
ORDER BY day;

Двойной sum(sum(...)) пугает, но читается прямо: внутренний схлопывает день, внешний идёт окном по дням.

Чем окно лучше подзапроса

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

Что почитать дальше

Окна почти всегда стоят рядом с соединениями: сначала собираешь данные из нескольких таблиц, потом считаешь по ним рейтинги. Если соединения пока не уложились — начни с разбора видов JOIN в SQL, а типовые ошибки с NULL собраны в статье про LEFT JOIN.

Для практики открой задачу на лидера каждого отдела: там ROW_NUMBER() должен выбрать ровно одного сотрудника с детерминированным разрешением ничьих.