sqlоконные функцииrow_numberсобеседованиеаналитик данных

SQL: топ-N в группе — решение через ROW_NUMBER

2026-06-09 9 мин

Чтобы выбрать топ-N записей в каждой группе на SQL (top-N per group), используют оконную функцию ROW_NUMBER (или RANK / DENSE_RANK) с PARTITION BY в подзапросе или CTE, а затем фильтруют по номеру строки во внешнем запросе. Прямой WHERE rn <= N рядом с оконной функцией не работает — её нельзя ставить в WHERE того же уровня, поэтому нужен второй уровень запроса. Базовый шаблон:

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
) t
WHERE rn <= 3;

Это вернёт по три самых дорогих товара в каждой категории. Дальше разберём пошагово: чем отличаются ROW_NUMBER, RANK и DENSE_RANK, как переписать через CTE, когда выручит LATERAL JOIN и что выбрать ради производительности.

Что такое задача «топ-N в группе»?

Top-N per group — это запрос, который для каждой группы (категория, пользователь, регион, день) возвращает не одну агрегатную цифру, а несколько лучших по какому-то критерию строк целиком. Примеры, которые реально спрашивают на собесах:

Ключевое слово — «в каждой группе». Обычный GROUP BY тут не поможет: он схлопывает группу в одну строку и теряет остальные колонки. Нам нужны полные строки, отсортированные внутри группы. Именно для этого существуют оконные функции с PARTITION BY.

Чем ROW_NUMBER отличается от RANK и DENSE_RANK?

Все три — ранжирующие оконные функции. Они присваивают номер строкам внутри партиции по заданному ORDER BY. Разница только в том, как они ведут себя при одинаковых значениях (ties).

ФункцияДубли значенийПропуски номеровПример при значениях 100, 100, 90
ROW_NUMBERразные номера всегданет1, 2, 3
RANKодинаковый номересть1, 1, 3
DENSE_RANKодинаковый номернет1, 1, 2

Простое правило выбора:

Сравнить поведение на живых данных можно прямо в SQL-тренажёре — там есть категория на оконные функции с автопроверкой.

Как решить топ-N через ROW_NUMBER (пошагово)?

Разберём классику: 3 самых дорогих товара в каждой категории. Решаем по шагам.

Шаг 1: пронумеровать строки внутри группы

Добавляем оконную функцию. PARTITION BY category делит таблицу на группы по категории, ORDER BY price DESC сортирует внутри каждой группы по убыванию цены. ROW_NUMBER присваивает номер 1 самому дорогому товару группы:

SELECT
    id,
    name,
    category,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products;

На этом шаге строки ещё не отфильтрованы — мы только разметили их номерами. Каждая категория получила свою независимую нумерацию от 1.

Шаг 2: обернуть в подзапрос

Оконную функцию нельзя использовать в WHERE того же уровня, потому что окна вычисляются после WHERE. Поэтому заворачиваем запрос из шага 1 в подзапрос (производную таблицу) и даём ему алиас:

SELECT *
FROM (
    SELECT
        id, name, category, price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
) ranked;

Теперь rn — обычная колонка внешнего запроса, по ней можно фильтровать.

Шаг 3: отфильтровать по номеру

Во внешнем запросе оставляем строки с номером не больше N:

SELECT id, name, category, price
FROM (
    SELECT
        id, name, category, price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
) ranked
WHERE rn <= 3
ORDER BY category, price DESC;

Готово: по три самых дорогих товара в каждой категории, отсортированные читаемо. Финальный ORDER BY category, price DESC нужен, потому что после фильтрации порядок строк не гарантирован.

Когда использовать CTE вместо подзапроса?

CTE (Common Table Expression, выражение WITH) делает ровно то же самое, что подзапрос, но читается сверху вниз и не вкладывает запросы друг в друга. Тот же топ-3, переписанный через CTE:

WITH ranked AS (
    SELECT
        id, name, category, price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
)
SELECT id, name, category, price
FROM ranked
WHERE rn <= 3
ORDER BY category, price DESC;

Что выбрать:

По производительности в PostgreSQL разницы практически нет: начиная с версии 12 CTE инлайнятся в план запроса так же, как подзапросы. В старых версиях (до 12) CTE были «оптимизационным барьером» и могли материализоваться в память — это важно помнить, если работаете на legacy-базе. Подробнее про оба приёма и их разницу — в курсе SQL с нуля.

Как взять только одну верхнюю строку на группу (top-1)?

Частный и очень частый случай — N = 1: последний заказ каждого клиента, последнее событие по сессии, самая свежая цена по товару. Логика та же, просто WHERE rn = 1:

WITH last_order AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders
)
SELECT *
FROM last_order
WHERE rn = 1;

В PostgreSQL для top-1 есть более короткий и часто более быстрый приём — DISTINCT ON:

SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC;
DISTINCT ON (customer_id) оставляет первую строку для каждого customer_id в порядке, заданном ORDER BY. Колонки в DISTINCT ON обязаны идти первыми в ORDER BY. Это нестандартный синтаксис только для PostgreSQL — в MySQL, SQLite или ClickHouse он не сработает, там остаётся вариант с ROW_NUMBER.

Когда выручает LATERAL JOIN?

LATERAL JOIN (в PostgreSQL — CROSS JOIN LATERAL или LEFT JOIN LATERAL) позволяет для каждой строки левой таблицы выполнить отдельный коррелированный подзапрос. Это другой способ собрать top-N: вместо того чтобы ранжировать всю таблицу фактов и потом фильтровать, мы идём по списку групп и для каждой подтягиваем её топ.

Допустим, есть таблица categories и таблица products. Берём по 3 товара на категорию:

SELECT c.name AS category, p.name, p.price
FROM categories c
CROSS JOIN LATERAL (
    SELECT name, price
    FROM products
    WHERE products.category_id = c.id
    ORDER BY price DESC
    LIMIT 3
) p;

Когда LATERAL выгоднее оконной функции:

Когда LATERAL проигрывает:

Похожие приёмы с коррелированными подзапросами часто встречаются в тестовых заданиях на позицию аналитика.

Что выбрать ради производительности?

Универсального ответа нет — зависит от соотношения «число групп / число строк в группе» и наличия индексов. Ориентир:

СценарийЛучший приёмПочему
top-1 на группу, PostgreSQLDISTINCT ONкороче, использует индекс на (group, sort)
N строк, групп много, строк в группе малоROW_NUMBER в CTEодин проход, окно дешевле миллиона подзапросов
N строк, групп мало, строк в группе миллионыLATERAL + LIMIT + индексindex scan берёт ровно N, не сортирует всё
нужны ties (одинаковые на N-м месте)RANK / DENSE_RANKсохраняет все строки нужного ранга

Главный рычаг производительности — индекс под ORDER BY оконной функции. Для запроса с PARTITION BY category ORDER BY price DESC индекс (category, price DESC) позволяет PostgreSQL читать строки уже в нужном порядке и не делать дорогой sort. Без индекса планировщик отсортирует всю таблицу — на больших данных это бутылочное горлышко.

Что проверить через EXPLAIN ANALYZE:

Какие ошибки делают на собеседовании?

Типичные грабли, на которых заваливаются кандидаты:

Отработать ровно эти ситуации с разбором можно в формате AI мок-интервью — интервьюер задаёт follow-up по ties и производительности, как на реальном собесе. А подборка похожих задач лежит в разделе вопросы с собесов.

Краткая шпаргалка

Потренируйся бесплатно: реши задачи на top-N per group в SQL-тренажёре — там настоящий PostgreSQL в браузере с автопроверкой и подсказками. Когда захочешь систематизировать оконные функции и CTE, пройди бесплатные уроки курса SQL с нуля, а перед собеседованием прогони себя через AI мок-интервью.

Закрепи оконные функции
Десятки задач на ROW_NUMBER/RANK с автопроверкой и настоящим PostgreSQL в браузере.
Открыть SQL-тренажёр →

Похожие статьи