Последняя строка на группу в PostgreSQL: сделать упорядочение row_number детерминированным
Выберите одно целое событие на аккаунт с явным разрешающим ключом и продуманной политикой для отсутствующих меток.
В этом материале
Короткий ответ
Ранжируйте каждую группу с помощью row_number по PARTITION BY её идентификатора и ORDER BY метке события плюс устойчивый уникальный разрешающий ключ. Затем отфильтруйте rn=1 во внешнем запросе. Используйте NULLS LAST, если отсутствующие метки должны проигрывать известным. Порядок окна выбирает победителя в группе; отдельный внешний ORDER BY управляет порядком вывода выбранных строк.
Следуйте примеру небольшого запроса
Конструируемый аккаунт 7 имеет события 10 и 11 с одинаковой меткой, поэтому id DESC выбирает 11. Аккаунт 8 имеет только событие 12, поэтому он выбирает 12, даже несмотря на то, что его метка отсутствует. NULLS LAST не удаляет отсутствующие значения; он размещает их позади известных значений внутри каждой группы. Это ожидаемые следствия запроса, а не вывод, полученный из запроса, выполненного против этого проекта.
Определите, что означает последний
Укажите метку времени события, используемую для выбора, и то, являются ли отсутствующие значения допустимыми. Время ингрессии, время бизнес-события и числовой идентификатор могут представлять разные понятия свежести. Более высокий идентификатор является удобным детерминированным разрешающим ключом в этом примере, а не доказательством того, что событие произошло позже. Согласуйте это правило перед написанием запроса, особенно когда события могут поступать вне порядка.
Сохраняйте всю выигравшую строку
Агрегат, такой как max(event_time), находит метку, но сам по себе не выбирает другие столбцы из соответствующей строки. Соединение этого максимума обратно с таблицей может вернуть несколько строк, когда метки совпадают. Окно ранжирования вместо этого присваивает позицию каждому кандидату, сохраняя его полезную нагрузку и идентификатор. Вам всё ещё нужна явная политика, если бизнес хочет все связанные последние события, а не только одно.
Независимо секционируйте и сортируйте
PARTITION BY account_id создаёт отдельное ранжирование для каждого аккаунта. Окно ORDER BY event_time DESC NULLS LAST, id DESC помещает известные свежие метки первыми и разбивает равные метки с помощью идентификатора. Идентификатор должен различать строки-кандидаты, чтобы это упорядочение было детерминированным. Если источник не предоставляет такой ключ, выберите другой устойчивый уникальный разрешающий ключ, а не полагайтесь на порядок физического хранения строк.
WITH events (id, account_id, event_time, payload) AS (
VALUES
(10, 7, TIMESTAMP '2026-09-30 10:00:00', 'first'),
(11, 7, TIMESTAMP '2026-09-30 10:00:00', 'second'),
(12, 8, NULL::timestamp, 'unknown time')
), ranked AS (
SELECT events.*,
row_number() OVER (
PARTITION BY account_id
ORDER BY event_time DESC NULLS LAST, id DESC
) AS rn
FROM events
)
SELECT id, account_id, event_time, payload
FROM ranked
WHERE rn = 1
ORDER BY account_id;
Фильтруйте в правильном слое запроса
Результат окна вычисляется после того, как входные строки выбраны этим слоем запроса. CTE предоставляет слой, где rn существует как столбец, а внешний запрос фильтрует его. Также различайте фильтрацию кандидатов до ранжирования и фильтрацию выбранных победителей после. Например, последнее успешное событие и последнее событие, которое оказывается успешным, отвечают на разные вопросы и могут порождать разные аккаунты в выводе.
Установите политику для отсутствующих идентификаторов группы
Если account_id может быть null, эти строки формируют секцию вместе, а не автоматически становятся отдельными аккаунтами. Решите, исключать ли их перед ранжированием или рассматривать как явно определённую группу. Аналогично, NULLS LAST позволяет секции со всеми NULL метками произвести победителя. Если неизвестные метки никогда не должны выбираться, исключите их из входных кандидатов, а не ожидайте, что предложение упорядочения удалит их.
Проверьте порядок вывода и одновременные изменения
Внешний ORDER BY account_id задаёт порядок отображения; он не меняет, какое событие выиграло. Без внешнего предложения упорядочения приложения не должны предполагать, что строки приходят в стабильном порядке представления. Новые события, вставленные после того, как запрос прочитал свой снимок, могут изменить результат последующего запроса. Детерминированное разбиение связей делает выбор хорошо определённым для рассмотренных строк, но не замораживает изменяющийся набор данных между отдельными запросами.
Изучите производительность на реальной нагрузке
Оцените план запроса с реалистичными размерами групп и выбранными столбцами перед выбором индекса. Индекс, включающий столбцы секционирования и упорядочения, может помочь некоторым нагрузкам, но это не универсальное обещание исчезновения сортировки или покрытия каждой полезной нагрузки. Сначала сохраните правильную семантику выбора. Если вы выбираете другую технику PostgreSQL, такую как DISTINCT ON, сохраните те же правила NULL и связей при сравнении результатов.
Что проверить
- Определите метку времени и политику обработки NULL.
- Используйте устойчивый уникальный разрешающий ключ.
- Фильтруйте результаты окна во внешнем слое.
- Храните фильтры кандидатов отдельно от фильтров победителей.
- Используйте внешний ORDER BY для представления.
Границы применения
Запрос выбирает одного победителя на секцию согласно заданному упорядочению. Он не возвращает все связи, не выводит время события из идентификаторов, не исключает группы со всеми NULL автоматически и не гарантирует план индекса. Примеры строк являются гипотетическими.