ПрограммированиеСистемный аналитикSenior

Напишите SQL-запрос: по каждой категории товаров найти три самых продаваемых товара по выручке. Чем оконные функции отличаются от GROUP BY?

Короткий ответ

GROUP BY схлопывает строки в одну на группу
Оконная функция считает по группе, сохраняя строки
OVER с PARTITION BY задаёт окно расчёта
ROW_NUMBER, RANK, DENSE_RANK для задач топ-N
RANK даёт пропуски при равенстве, DENSE_RANK — нет
Фильтр по окну — только через подзапрос или CTE

Топ-N по группе решается агрегацией выручки и ранжирующей оконной функцией в CTE с последующим фильтром, а ключевое отличие окон от GROUP BY — сохранение исходных строк.

Как сказать вслух

пример ответа

GROUP BY сворачивает группу строк в одну итоговую, а оконная функция считает агрегат или ранг по группе, но оставляет каждую строку на месте — это позволяет сравнивать строку с её группой. Для топ-три я сначала агрегирую выручку по товарам, потом нумерую товары внутри категории через ROW_NUMBER с PARTITION BY по категории и сортировкой по выручке, и отбираю номера до трёх. Фильтровать по оконной функции напрямую в WHERE нельзя, поэтому использую CTE.

Подробный ответ

Основной ответ

GROUP BY агрегирует: на каждую группу остаётся одна строка с результатами агрегатных функций, детальные строки теряются. Оконные функции считают значение по окну строк (OVER c PARTITION BY и ORDER BY), не схлопывая результат — каждая строка сохраняется и получает вычисленное значение. Это нужно для рангов, долей от итога, скользящих сумм, сравнения с предыдущей строкой (LAG/LEAD). Задача топ-N по группе — классика: агрегируем выручку по товару, затем ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC) и фильтр rn <= 3. Нюанс: оконные функции нельзя использовать в WHERE того же уровня запроса, так как они вычисляются после, поэтому нужен CTE или подзапрос. Выбор функции: ROW_NUMBER даёт строго три строки, RANK и DENSE_RANK при равной выручке могут вернуть больше — поведение при ничьих нужно уточнить у бизнеса.

Ключевые моменты

Сохранение строк. Окно считает по группе, но строки не схлопываются — можно сравнивать каждую строку с агрегатом её группы.
ROW_NUMBER, RANK, DENSE_RANK. ROW_NUMBER нумерует без повторов, RANK даёт одинаковый ранг с пропуском следующего, DENSE_RANK — без пропусков.
Порядок вычисления. Окна вычисляются после WHERE и GROUP BY, поэтому фильтр по ним возможен только во внешнем запросе или CTE.
Уточнение при ничьих. Выбор между ROW_NUMBER и RANK — это бизнес-решение о том, что делать с товарами с равной выручкой.

Практический контекст

Задача «топ-N по группе» — одна из самых частых на live-coding для middle и senior аналитиков. Интервьюер проверяет знание PARTITION BY, понимание, почему нельзя фильтровать по окну в WHERE, и разницу ранжирующих функций. В работе такие запросы нужны для отчётности, ABC-анализа, проверки данных витрин — аналитик пишет их сам, не дожидаясь дата-инженера.

Пример кода

WITH product_revenue AS (
  SELECT p.category_id,
         p.id   AS product_id,
         p.name,
         SUM(oi.qty * oi.price) AS revenue
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category_id, p.id, p.name
), ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY category_id
           ORDER BY revenue DESC
         ) AS rn
  FROM product_revenue
)
SELECT category_id, product_id, name, revenue
FROM ranked
WHERE rn <= 3;

Частые ошибки

Пытаются отфильтровать по оконной функции прямо в WHERE того же запроса
Не знают разницу ROW_NUMBER, RANK и DENSE_RANK при равных значениях
Пробуют решить топ-N по группе одним GROUP BY без окон

ИП Кочкин Алексей Сергеевич · ИНН 390509026279 · ОГРНИП 325390000030973 · jiniys2005@yandex.ru