Топ-N строк в каждой группе в SQL

Урок 12.6 · Время чтения: ~7 мин · Задачи для практики ↓

Топ-N в каждой группе (англ. «greatest N per group») - задача выбрать несколько лучших строк в каждой группе: трёх крупнейших клиентов каждого магазина, два самых длинных фильма каждой категории, последний заказ каждого клиента. Она постоянно встречается в отчётах и на собеседованиях по SQL. В этом уроке вы решите её с помощью ROW_NUMBER() и PARTITION BY, увидите, как равные значения меняют ответ, и изучите две альтернативы: коррелированный подзапрос и LATERAL-соединение.

ORDER BY ... LIMIT 3 возвращает три первые строки всего результата, а не каждой группы. Группировка тоже не помогает: GROUP BY сворачивает каждую группу в одну строку, а нам нужно несколько исходных строк на группу. Решение - пронумеровать строки внутри каждой группы и оставить первые N.

Примеры работают на учебной базе Sakila (MySQL 8.0).

Задача

Найдите трёх клиентов, которые заплатили больше всех, в каждом из двух магазинов.

Сначала - сумма по каждому клиенту:

SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
       SUM(p.amount) AS total_paid
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
ORDER BY total_paid DESC
LIMIT 3;

Результат: KARL SEAL (магазин 2, 221.55), ELEANOR HUNT (магазин 1, 216.54), CLARA SHAW (магазин 1, 195.58) - три лучших в целом, а не по магазинам.

Решение 1: ROW_NUMBER с PARTITION BY

PARTITION BY store_id начинает нумерацию заново для каждого магазина; ORDER BY total_paid DESC даёт номер 1 самому крупному плательщику. Оконную функцию нельзя использовать в WHERE того же запроса, поэтому фильтр переносится во внешний запрос:

WITH customer_total AS (
    SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
           SUM(p.amount) AS total_paid
    FROM customer c
    JOIN payment p ON p.customer_id = c.customer_id
    GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
),
ranked AS (
    SELECT store_id, first_name, last_name, total_paid,
           ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_paid DESC) AS rn
    FROM customer_total
)
SELECT store_id, first_name, last_name, total_paid, rn
FROM ranked
WHERE rn <= 3
ORDER BY store_id, rn;

Результат: 6 строк, по три на магазин.

Равные значения: ROW_NUMBER, RANK или DENSE_RANK

RHONDA KENNEDY и MARION SNYDER заплатили одинаково, но ROW_NUMBER дал им разные номера. При равных значениях три функции отвечают на разные вопросы. Посмотрим на самые длинные фильмы двух категорий, где длительность часто повторяется:

WITH ranked AS (
    SELECT c.name AS category, f.title, f.length,
           ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS row_num,
           RANK()       OVER (PARTITION BY c.name ORDER BY f.length DESC) AS rnk,
           DENSE_RANK() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS dense_rnk
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
)
SELECT category, title, length, row_num, rnk, dense_rnk
FROM ranked
WHERE category IN ('Action', 'Comedy') AND dense_rnk <= 3
ORDER BY category, row_num;

Результат: 9 строк; в Action у двух фильмов одинаковая максимальная длительность - 185 минут.

• ROW_NUMBER() <= N: ровно N строк на группу; среди равных значений выбор произвольный. Добавьте в ORDER BY дополнительный критерий, например f.length DESC, f.title, чтобы результат был стабильным.
• RANK() <= N: все, кто попал бы в топ-N, поэтому группа может вернуть больше N строк.
• DENSE_RANK() <= N: строки с N наибольшими различными значениями.

На собеседовании уточните, что имеется в виду: «топ-3 фильма» - обычно ROW_NUMBER, «фильмы с тремя наибольшими длительностями» - DENSE_RANK.

Решение 2: коррелированный подзапрос

Без оконных функций (MySQL до 8.0) строка попадает в топ-N своей группы, если в той же группе меньше N строк с бо́льшим значением:

SELECT c.name AS category, f.title, f.length
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
WHERE c.name = 'Action'
  AND (
      SELECT COUNT(*)
      FROM film f2
      JOIN film_category fc2 ON fc2.film_id = f2.film_id
      WHERE fc2.category_id = fc.category_id
        AND f2.length > f.length
  ) < 2
ORDER BY f.length DESC;

Результат: 2 строки, DARN FORRESTER и WORST BANGER - два фильма, длиннее которых в Action нет; это ведёт себя как RANK() <= 2.

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

Решение 3: LATERAL-соединение

Подзапрос LATERAL может ссылаться на столбцы таблиц, стоящих перед ним во FROM. Это позволяет буквально написать «для каждой категории возьми 2 самых длинных фильма» с ORDER BY и LIMIT внутри:

SELECT c.name AS category, top_films.title, top_films.length
FROM category c
JOIN LATERAL (
    SELECT f.title, f.length
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    WHERE fc.category_id = c.category_id
    ORDER BY f.length DESC, f.title
    LIMIT 2
) AS top_films ON TRUE
WHERE c.name IN ('Action', 'Comedy')
ORDER BY category, top_films.length DESC;

Результат: 4 строки: DARN FORRESTER и WORST BANGER для Action, CONTROL ANTHEM и SATURN NAME для Comedy.

LATERAL поддерживают PostgreSQL и MySQL 8.0.14+, но не MariaDB; в SQL Server ту же идею реализует CROSS APPLY. При наличии индекса по столбцам группировки и сортировки это часто самый быстрый способ, когда группы большие, а N маленькое.

Практика

• Популярные зоны посадки по месяцам
• Самые высокооплачиваемые сотрудники по отделам
• Рейтинг фильмов по популярности

Ключевые выводы:

• LIMIT и GROUP BY не могут вернуть несколько строк на группу; вместо этого пронумеруйте строки внутри каждой группы.
• ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) и WHERE rn <= N во внешнем запросе - стандартное решение.
• При равных значениях ROW_NUMBER возвращает ровно N строк, RANK может вернуть больше, а DENSE_RANK возвращает N наибольших различных значений.
• Коррелированный подзапрос решает задачу без оконных функций, но медленнее.
• JOIN LATERAL (или CROSS APPLY в SQL Server) с ORDER BY ... LIMIT N - читаемая и часто быстрая альтернатива.

Часто задаваемые вопросы

Как выбрать топ-N строк в каждой группе в SQL?

Пронумеруйте строки каждой группы с помощью ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) в CTE или подзапросе, затем во внешнем запросе оставьте строки с номером не больше N.

Почему оконную функцию нельзя использовать в WHERE?

Оконные функции вычисляются после WHERE, GROUP BY и HAVING, на этапе SELECT. Чтобы фильтровать по их результату, вычислите их в CTE или подзапросе и отфильтруйте во внешнем запросе. В некоторых СУБД (Snowflake, DuckDB, BigQuery) для этого есть предложение QUALIFY.

Чем отличаются ROW_NUMBER, RANK и DENSE_RANK для топ-N?

ROW_NUMBER даёт каждой строке уникальный номер, поэтому возвращает ровно N строк и разрешает ничьи произвольно. RANK даёт равным значениям одинаковый ранг и пропускает следующие номера, поэтому может вернуть больше N строк. DENSE_RANK не пропускает номера и возвращает все строки с N наибольшими различными значениями.

Как получить последнюю строку для каждого клиента?

Это топ-1 в группе: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) и WHERE rn = 1. В PostgreSQL короче написать SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, created_at DESC.