Оконные функции в PostgreSQL: что выбрать для top-N

Если нужно выбрать последний заказ каждого клиента, одного MAX(created_at) недостаточно: при одинаковом времени он вернёт несколько строк, а не один заказ. В PostgreSQL 16 для такой задачи можно использовать оконную функцию, DISTINCT ON или агрегат с последующим соединением. Выбор влияет не только на краткость запроса: от него зависит, получите ли вы ровно одну строку, сможете ли выбрать top-N и насколько просто объяснить результат коллегам.

Сравниваем на одной задаче

Предположим, есть таблица orders с полями id, customer_id, created_at и total. Нужно получить последний заказ по каждому клиенту. Во всех примерах ниже последний заказ определяется одинаково: сначала по времени создания, а при совпадении времени — по большему id. Это важно: без второго критерия база не обязана выбирать одну и ту же строку при каждом выполнении.

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

Три способа получить последний заказ

1. Оконная функция: удобно для top-N

ROW_NUMBER() нумерует заказы внутри каждого клиента в заданном порядке. После этого внешний запрос оставляет строку с номером 1. Чтобы получить три последних заказа на клиента, достаточно заменить условие rn = 1 на rn <= 3.

WITH ranked_orders AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY o.customer_id
            ORDER BY o.created_at DESC, o.id DESC
        ) AS rn
    FROM orders AS o
)
SELECT id, customer_id, created_at, total
FROM ranked_orders
WHERE rn = 1;

Внутренний запрос ранжирует записи, внешний фильтрует результат. Фильтр нельзя перенести в тот же уровень запроса, где вычисляется ROW_NUMBER(): оконные значения формируются после обработки WHERE. Цена подхода — запрос чуть длиннее, чем у DISTINCT ON; выигрыш — понятное расширение до нескольких строк на группу.

2. DISTINCT ON: короткий вариант для одной строки

DISTINCT ON — расширение PostgreSQL, которое оставляет первую строку для каждого значения указанного ключа. Первые выражения в ORDER BY должны соответствовать DISTINCT ON; оставшиеся задают, какая строка станет первой.

SELECT DISTINCT ON (o.customer_id)
       o.id, o.customer_id, o.created_at, o.total
FROM orders AS o
ORDER BY o.customer_id, o.created_at DESC, o.id DESC;

Это компактно и сразу выражает задачу «одна запись на клиента». Но запрос привязан к PostgreSQL и сам по себе не решает выбор top-3: оконная нумерация для этого понятнее. Не убирайте из сортировки id, если при равных датах вам нужна одна воспроизводимо выбранная запись.

3. GROUP BY и JOIN: когда нужны именно максимальные даты

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

SELECT o.id, o.customer_id, o.created_at, o.total
FROM orders AS o
JOIN (
    SELECT customer_id, MAX(created_at) AS latest_created_at
    FROM orders
    GROUP BY customer_id
) AS latest
  ON latest.customer_id = o.customer_id
 AND latest.latest_created_at = o.created_at;

Запрос возвращает все заказы, созданные в максимальный момент времени. Если у клиента две записи с одинаковым created_at, обе попадут в результат. Это не ошибка SQL: агрегат нашёл дату, но не выбрал одну строку. Добавление условия по максимальному id требует ещё одного шага или уровня агрегации; если же нужны top-N, этот подход быстро становится сложнее оконного.

Сначала решите, что означает «последний»: одна строка по строгому порядку или все строки, разделившие максимальную дату. Запросы могут выглядеть похоже, но отвечать на разные вопросы.

Короткое сравнение

Термин. PARTITION BY делит строки на независимые группы для вычисления оконной функции. Здесь каждая группа — заказы одного клиента; в отличие от GROUP BY, окно не сворачивает эти строки в одну запись.

Когда что выбирать

• Нужна одна последняя запись, а запрос должен быть коротким и останется в PostgreSQL — начните с DISTINCT ON.
• Нужны последние две, три или десять записей на группу — используйте ROW_NUMBER() и фильтр по номеру.
• Нужны все записи, имеющие максимальное значение, — агрегат с соединением может прямо выразить это поведение. Проверьте, что несколько результатов на группу допустимы.

Для ранжирования с сохранением равных мест есть также RANK() и DENSE_RANK(). В отличие от ROW_NUMBER(), они присваивают одинаковый ранг равным значениям сортировки. Поэтому фильтр по рангу может вернуть больше N строк: например, все записи на третьем месте. Выбирайте функцию по ожидаемому результату, а не по названию.

Что ломается и как проверить план

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

Большая таблица может потребовать сортировки большого числа строк. Для этой схемы можно проверить индекс по клиенту и убывающему времени с дополнительной сортировкой по идентификатору: (customer_id, created_at DESC, id DESC). Индекс не гарантирует ускорения: планировщик учитывает размер таблицы, долю выбранных строк, статистику и стоимость чтения. Даже подходящий индекс не отменяет проверки фактического плана.

Сравнивайте варианты на одинаковых данных и с одинаковой семантикой — в частности, одинаково обработайте совпадения. Выполните EXPLAIN (ANALYZE, BUFFERS) для запросов в тестовой среде или на репрезентативной копии данных. Смотрите, сколько строк проходит через узлы плана, есть ли сортировка и сколько буферов прочитано. ANALYZE действительно выполняет запрос, поэтому не запускайте тяжёлый замер на нагруженной базе без оценки риска.

Что делать дальше

Зафиксируйте правило выбора строки: одна запись или все совпадения, и что делать при равных датах. Затем сравните два подходящих варианта на реальном объёме данных через план выполнения. Если запрос нужен для top-N, проверьте отдельно случай, когда на границе рейтинга есть одинаковые значения: именно там проявляется разница между ROW_NUMBER() и ранговыми функциями. Инвалидация и обновление данных в кэше — отдельная тема; здесь речь только о выборе строк запросом.