Как выбрать TOP-N строк внутри каждой группы

SQL Studio / Разборы SQL / Как выбрать TOP-N строк внутри каждой группы

Решение: используйте ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC, product_id) во вложенном запросе и оставьте rn <= N. Важно: обычный LIMIT 2 ограничит весь результат, а не каждую категорию.

1. Два лучших товара в каждой группе

Сначала возьмём семь вымышленных оценок. В категории A два товара набрали по 90 баллов: специально создаём равенство, чтобы проверить порядок.

WITH scores(category, product_id, points) AS (
  VALUES ('A',1,100), ('A',2,90), ('A',3,90), ('A',4,70),
         ('B',5,80), ('B',6,70), ('B',7,60)
),
ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY points DESC, product_id
         ) AS rn
  FROM scores
)
SELECT category, product_id, points
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;

PARTITION BY category сбрасывает нумерацию для каждой категории; ORDER BY points DESC ставит лучшие строки первыми. Второй ключ product_id стабилизирует порядок при одинаковых баллах.

2. А если хотим включить всех, кто разделил место?

У товаров №2 и №3 одинаковые 90 баллов. Если оба должны входить в TOP-2 места, используйте RANK() без дополнительного уникального ключа в сортировке ранга:

WITH scores(category, product_id, points) AS (
  VALUES ('A',1,100), ('A',2,90), ('A',3,90), ('A',4,70),
         ('B',5,80), ('B',6,70), ('B',7,60)
),
ranked AS (
  SELECT *,
         RANK() OVER (
           PARTITION BY category
           ORDER BY points DESC
         ) AS rating
  FROM scores
)
SELECT category, product_id, points
FROM ranked
WHERE rating <= 2
ORDER BY category, rating, product_id;

В категории A будут три товара: №1 с первым местом и №2, №3 с общим вторым. В категории B — два товара. Следовательно, число строк на группу может быть больше N. Для «ровно N строк» подходит ROW_NUMBER, для «все разделившие место» — RANK.

3. TOP-2 реальных товаров в SQL Studio

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

WITH price_by_product AS (
  SELECT p.category, p.product_id,
         MAX(i.unit_price_kopecks) AS price_kopecks
  FROM products AS p
  JOIN order_items AS i ON i.product_id = p.product_id
  GROUP BY p.category, p.product_id
),
ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY price_kopecks DESC, product_id
         ) AS rn
  FROM price_by_product
)
SELECT category, product_id, price_kopecks
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;

На небольшом наборе в категории «Электроника» первыми окажутся товары №11 (35 000 копеек) и №19 (30 000). Для «Аксессуаров» — №16 (45 000) и №17 (12 000). Это не рейтинг по выручке: здесь нет количества, скидок и возвратов. Товары, не встретившиеся в позициях заказов, исключены.

4. ROW_NUMBER, RANK и DENSE_RANK в одной таблице

WITH scores(category, product_id, points) AS (
  VALUES ('A',1,100), ('A',2,90), ('A',3,90), ('A',4,70)
)
SELECT product_id, points,
       ROW_NUMBER() OVER (ORDER BY points DESC, product_id) AS rn,
       RANK() OVER (ORDER BY points DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dense_rnk
FROM scores
ORDER BY points DESC, product_id;

RANK после ничьей пропускает третье место, DENSE_RANK не пропускает. На собеседовании это обычно важнее, чем запоминание синтаксиса.

Задачи для самостоятельного решения

В тренажёре есть упражнение на лучшие товары категорий. После него измените порог с пяти на два и проверьте, остались ли ожидаемые товары.

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

Для последовательной практики на MySQL 8 — курс «SQL для аналитика» на Stepik с итоговой работой и возможностью получить сертификат.