Оконные функции PostgreSQL: рейтинг, итоги и сравнения

Нужно показать каждый заказ и рядом — его место в рейтинге покупателя, сумму всех предыдущих заказов или разницу с прошлым? Обычный GROUP BY схлопнет строки, а оконная функция сохранит их и добавит вычисление к каждой. Ниже — четыре практических задачи для PostgreSQL 16: от накопительных итогов до скользящего среднего, с разбором границ окна и типичных ошибок.

Что оконная функция делает со строками

Оконная функция вычисляет значение для текущей строки, используя другие строки из той же выборки. Какие именно строки попадут в расчёт, задают PARTITION BY, ORDER BY и рамка окна. В отличие от агрегирования через GROUP BY, оконная функция не объединяет строки.

Примеры ниже используют таблицу orders с полями id, customer_id, created_at и amount. Сначала выберите нужные записи, например за период: окно считает результат только по строкам, которые уже попали в выборку.

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

Термин. Окно — набор строк, доступных функции при вычислении значения для текущей строки. PARTITION BY делит выборку на группы, а ORDER BY задаёт порядок строк внутри каждой группы.

Четыре задачи, для которых нужны окна

1. Рейтинг заказов внутри группы

Например, нужно найти три самых крупных заказа каждого покупателя. row_number() присваивает каждой строке уникальный номер. Чтобы при одинаковой сумме порядок не менялся от запуска к запуску, добавьте уникальный идентификатор в сортировку.

SELECT id, customer_id, amount,
       row_number() OVER (
           PARTITION BY customer_id
           ORDER BY amount DESC, id
       ) AS order_position
FROM orders;

Чтобы оставить только первые три строки в каждой группе, оберните запрос и отфильтруйте результат снаружи: оконные функции нельзя проверять в WHERE того же уровня запроса.

SELECT *
FROM (
    SELECT id, customer_id, amount,
           row_number() OVER (
               PARTITION BY customer_id
               ORDER BY amount DESC, id
           ) AS order_position
    FROM orders
) ranked_orders
WHERE order_position <= 3;

Если при равных суммах все связанные заказы должны делить место, используйте rank(). Если нужны места без пропусков после ничьей — dense_rank(). Эти функции дают разный результат: при суммах 100, 100 и 90 rank() вернёт 1, 1, 3, а dense_rank() — 1, 1, 2.

2. Накопительный итог

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

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

Рамка означает: от первой строки покупателя до текущей включительно. Без явной рамки накопительная сумма может учитывать строки с тем же значением сортировки как одну группу равных строк. Для уникальной последовательности строк безопаснее задать ROWS и добавить в ORDER BY уникальный ключ.

3. Сравнение с предыдущей строкой

Функция lag() читает значение из предыдущей строки в заданном порядке. Это удобно, например, чтобы сравнить сумму текущего заказа с суммой предыдущего заказа того же покупателя.

SELECT id, customer_id, created_at, amount,
       lag(amount) OVER (
           PARTITION BY customer_id
           ORDER BY created_at, id
       ) AS previous_amount,
       amount - lag(amount) OVER (
           PARTITION BY customer_id
           ORDER BY created_at, id
       ) AS amount_change
FROM orders;

У первой строки каждой группы предыдущей строки нет, поэтому lag() вернёт NULL. Это не ошибка: обработайте NULL отдельно, если в отчёте вместо него нужно показывать, например, ноль или подпись «нет предыдущего заказа». Для следующей строки есть lead(); порядок сортировки остаётся тем же.

4. Скользящее среднее по последним строкам

Среднее по текущему и пяти предыдущим заказам можно посчитать агрегатной функцией с рамкой в шесть строк:

SELECT id, customer_id, created_at, amount,
       avg(amount) OVER (
           PARTITION BY customer_id
           ORDER BY created_at, id
           ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
       ) AS average_last_six_orders
FROM orders;

Это среднее по шести строкам, а не по шести дням. У покупателя с редкими заказами интервал может растянуться на месяцы. Если нужна календарная неделя или месяц, задайте границы периода отдельно и проверьте, что они соответствуют бизнес-правилу: рамка ROWS считает строки, не время.

Как выбрать функцию

Выбирайте функцию по смыслу результата. Нужен ровно один лидер на группу — подойдёт row_number(). Нужно сохранить ничьи — rank() или dense_rank(). Сравниваете последовательные события — lag(). Считаете итог или среднее по заданному диапазону строк — агрегатная функция с рамкой.

Что ломается чаще всего

• Нет уникального порядка. Если сортировка не различает строки, их относительное положение может быть неопределённым. Добавьте в ORDER BY идентификатор или другой уникальный ключ.
• Фильтр стоит в неправильном месте. Нельзя отфильтровать результат оконной функции в WHERE того же уровня запроса. Рассчитайте значение во вложенном запросе, затем отберите строки снаружи.
• Рамка не соответствует задаче. ROWS BETWEEN 5 PRECEDING AND CURRENT ROW означает максимум шесть строк, а не шесть дней. Сверьте формулировку отчёта с единицей расчёта.
• NULL трактуют как ноль. Если предыдущей строки нет, lag() возвращает NULL. Подстановка нуля меняет смысл сравнения; делайте её только если это соответствует правилам отчёта.
• Оконный расчёт запускают по слишком большой выборке. Разбиение на группы не отменяет обработку строк. Сначала ограничьте период, если по условиям задачи старые данные не нужны, и проверьте план выполнения на реальном объёме.

Что проверить дальше

Возьмите небольшой набор данных, где есть равные суммы, одинаковое время заказов и покупатель только с одной записью. Проверьте на нём ничьи в рейтинге, первую строку для lag() и границы скользящего среднего. Затем сравните результат на полном объёме и посмотрите план выполнения через EXPLAIN. Настройка индексов и ускорение оконных запросов зависят от фильтров и сортировки конкретного запроса — это следующий шаг, а не повод менять логику расчёта наугад.

Примеры ориентированы на PostgreSQL 16. Оконные функции описаны в официальной документации PostgreSQL; синтаксис стоит сверять с документацией той версии, которую использует ваш сервер.