Как решить top N per group
На этой странице
Top N per group — найти N лучших строк в каждой группе: 3 самых дорогих товара в категории, последний заказ каждого пользователя. Стандартное решение — пронумеровать строки внутри группы row_number() OVER (PARTITION BY group ORDER BY metric DESC, id) и во внешнем запросе оставить rn <= N. Если нужно учитывать «делящих место», используют rank/dense_rank. В PostgreSQL для top-1 удобен DISTINCT ON, а когда групп немного или нужна лишь часть из них, а есть индекс (group, metric), самым быстрым часто оказывается LATERAL с ORDER BY … LIMIT N. Подход через GROUP BY + max() даёт только значение, а не всю строку, и ломается при равных значениях.
Вопрос
Как найти top N строк в каждой группе?
ROW_NUMBER
-- 3 самых дорогих товара в каждой категории
SELECT category_id, id, title, price
FROM (
SELECT p.*,
row_number() OVER (PARTITION BY category_id ORDER BY price DESC, id) AS rn
FROM products p
WHERE p.is_published
) t
WHERE rn <= 3
ORDER BY category_id, rn;С учётом равных значений
-- все товары, чья цена входит в топ-3 цен категории
... dense_rank() OVER (PARTITION BY category_id ORDER BY price DESC) AS dr ...
WHERE dr <= 3Разница между функциями — в уроке ROW_NUMBER, RANK и DENSE_RANK.
DISTINCT ON для top-1 (PostgreSQL)
-- последний заказ каждого пользователя
SELECT DISTINCT ON (user_id) user_id, id, total, created_at
FROM orders
ORDER BY user_id, created_at DESC, id DESC;LATERAL + LIMIT
SELECT c.id AS category_id, p.id, p.title, p.price
FROM categories c
CROSS JOIN LATERAL (
SELECT id, title, price FROM products
WHERE category_id = c.id AND is_published
ORDER BY price DESC, id
LIMIT 3
) p;
CREATE INDEX products_cat_price_idx ON products (category_id, price DESC, id) WHERE is_published;Для каждой категории читаются ровно 3 записи индекса — не нужно ранжировать всю таблицу. Подробнее — LATERAL JOIN.
Что выбрать
| Ситуация | Подход |
|---|---|
| Универсально, любая СУБД | row_number() в подзапросе |
| Top-1, PostgreSQL | DISTINCT ON |
| Мало групп, много строк в группе, есть индекс | LATERAL ... LIMIT N |
| Нужны «делящие место» | rank / dense_rank |
Почему не GROUP BY + max
-- ✗ при двух заказах с одинаковым максимальным created_at вернутся оба;
-- и нужен лишний JOIN, чтобы получить остальные столбцы
SELECT o.* FROM orders o
JOIN (SELECT user_id, max(created_at) AS mx FROM orders GROUP BY user_id) m
ON m.user_id = o.user_id AND m.mx = o.created_at;Частые ошибки
- Фильтр
rn <= 3в том же уровне запроса, где вычисляется окно. - Нет tie-breaker — нестабильный результат между запусками.
- Фильтр по строкам (
is_published) снаружи, а не внутри подзапроса — нумерация учитывает лишние строки. LATERALбез индекса по(group, metric).
Что важно помнить
- Шаблон: окно с
PARTITION BY+ внешний фильтр по номеру. DISTINCT ON— короткий top-1 в PostgreSQL.LATERAL+ индекс — самый быстрый вариант для небольшого числа групп.
Мини-шпаргалка
row_number() OVER (PARTITION BY g ORDER BY m DESC, id) AS rn→WHERE rn <= N.DISTINCT ON (g) ... ORDER BY g, m DESC.CROSS JOIN LATERAL (... ORDER BY m DESC LIMIT N).
Документация
PostgreSQL: Window Functions, PostgreSQL: SELECT DISTINCT ON.