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

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

Весь разбор — на одной таблице 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.

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

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 считаются одной позицией и получают общий итог.

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

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

Итоговая сумма сошлась, промежуточные — нет. На дневной выручке разница безобидна (в пределах дня итог всё равно один), на дашборде с несколькими записями в одну секунду она превращается в расхождение, которое ищут часами. Правило простое: если ключ сортировки не уникален и нужен пошаговый итог — пиши 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(...)) пугает, но читается прямо: внутренний схлопывает день, внешний идёт окном по дням.

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

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

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

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

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