re:re:actable
Урок 073 минуты чтения

Как решить 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, PostgreSQLDISTINCT 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.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам