Подзапросы, CTE и оконные функции: задача «топ-N в группе»

Вопрос уровня middle+: подзапрос, CTE или оконная функция — и как достать топ-N в каждой группе.

Оконная функция считает значение по группе строк, но не схлопывает строки: у каждой записи остаётся её собственная строка плюс новая колонка с результатом по «окну».

Вопрос 1. Чем CTE отличается от подзапроса?

Что проверяет интервьюер. Умеете ли вы писать читаемый SQL. Аналитический запрос живёт годами и его правят другие люди, поэтому «работает» — это половина требования, вторая половина — «понятно, что тут происходит».

Технически CTE (WITH … AS) — это именованный подзапрос, объявленный до основного SELECT. Разница практическая:

	Подзапрос	CTE
Читаемость	вложенность растёт вглубь	шаги идут сверху вниз
Переиспользование	копипаста при повторе	объявил один раз, сослался дважды
Рекурсия	нет	`WITH RECURSIVE` — иерархии, календари
Отладка	надо вырезать кусок	заменил финальный SELECT — увидел шаг

Отдельно стоит знать про коррелированный подзапрос — тот, который ссылается на внешний запрос и выполняется для каждой строки. Он самый медленный и самый частый источник «запрос висит десять минут».

-- коррелированный: подзапрос выполняется для КАЖДОГО клиента
SELECT c.name,
       (SELECT SUM(o.amount) FROM orders o WHERE o.client_id = c.id) AS total
FROM clients c;

-- то же самое через JOIN + GROUP BY: один проход по orders
SELECT c.name, SUM(o.amount) AS total
FROM clients c
LEFT JOIN orders o ON o.client_id = c.id
GROUP BY c.id, c.name;

Вопрос 2. Достаньте лучшего менеджера в каждом городе

Это задача «топ-N в группе» — её дают почти на каждом собеседовании аналитика. Она нерешаема одним GROUP BY: группировка по городу теряет менеджера, группировка по паре «город + менеджер» теряет сравнение внутри города. Нужны два шага, и CTE делает их наглядными.

CREATE TABLE sales (id INTEGER PRIMARY KEY AUTOINCREMENT, city TEXT, manager TEXT, amount INTEGER);
INSERT INTO sales (city, manager, amount) VALUES
 ('Москва','Анна',1500),('Москва','Анна',2500),('Москва','Борис',800),
 ('Казань','Вера',1200),('Казань','Глеб',400),('Пермь','Дина',300);

WITH by_manager AS (
  SELECT city, manager, SUM(amount) AS total
  FROM sales
  GROUP BY city, manager
),
best AS (
  SELECT city, MAX(total) AS best_total
  FROM by_manager
  GROUP BY city
)
SELECT b.city, m.manager, m.total
FROM best b
JOIN by_manager m ON m.city = b.city AND m.total = b.best_total
ORDER BY m.total DESC;

Вывод:

Москва  Анна  4000
Казань  Вера  1200
Пермь   Дина   300

Читается как три предложения: посчитали сумму по паре «город — менеджер», нашли максимум по городу, вернули того, кто этому максимуму равен. Обратите внимание на честный побочный эффект: при ничьей вернутся оба менеджера. Это правильное поведение, но на собеседовании о нём стоит сказать вслух.

Вопрос 3. А как то же самое через оконные функции?

Оконная функция решает задачу в один шаг. Синтаксис: ФУНКЦИЯ() OVER (PARTITION BY … ORDER BY …), где PARTITION BY задаёт «окно» — группу строк, внутри которой считаем, а ORDER BY — порядок внутри окна.

WITH by_manager AS (
  SELECT city, manager, SUM(amount) AS total
  FROM sales
  GROUP BY city, manager
)
SELECT city, manager, total
FROM (
  SELECT city, manager, total,
         ROW_NUMBER() OVER (PARTITION BY city ORDER BY total DESC) AS rn
  FROM by_manager
) t
WHERE rn <= 2
ORDER BY city, rn;

Здесь rn <= 2 даёт топ-2 в каждом городе — поменяв одну цифру, получаем топ-N. Через MAX так уже не сделать, поэтому вопрос «а если нужен топ-3?» — стандартное продолжение.

Три нумерующие функции обязательно надо различать. Для значений 100, 90, 90, 80:

Функция	Результат	Смысл
`ROW_NUMBER()`	1, 2, 3, 4	сквозная нумерация, ничьих не бывает
`RANK()`	1, 2, 2, 4	ничья делит место, следующий номер «прыгает»
`DENSE_RANK()`	1, 2, 2, 3	ничья делит место, без разрывов

Вторая частая задача на окна — накопительный итог и сравнение с предыдущим периодом:

SELECT
  dt,
  revenue,
  SUM(revenue) OVER (ORDER BY dt) AS running_total,
  LAG(revenue) OVER (ORDER BY dt) AS prev_day,
  revenue - LAG(revenue) OVER (ORDER BY dt) AS delta,
  AVG(revenue) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_revenue
ORDER BY dt;

LAG берёт значение предыдущей строки окна, LEAD — следующей, а конструкция ROWS BETWEEN 6 PRECEDING AND CURRENT ROW задаёт скользящее окно из семи дней — так одной строкой считают недельное сглаживание.

Типичные ошибки кандидатов

Пытаются решить «топ-N в группе» одним GROUP BY с MAX и не замечают, что менеджер в результате не соответствует максимуму.
Пишут WHERE ROW_NUMBER() OVER (…) = 1. Так нельзя: окна считаются после WHERE, нужен подзапрос или QUALIFY там, где он есть.
Не различают RANK и DENSE_RANK — самый популярный уточняющий вопрос.
Говорят, что CTE «всегда быстрее подзапроса». Это миф: в большинстве СУБД оптимизатор разворачивает CTE так же, выигрыш — в читаемости.
Строят коррелированный подзапрос на большой таблице там, где хватило бы одного JOIN с GROUP BY.

Как ответить кратко

«CTE — это именованный подзапрос: тот же результат, но запрос читается сверху вниз, шаг можно переиспользовать и легко отладить. Задачу "топ-N в группе" решаю оконной функцией: считаю агрегат по паре ключей, нумерую строки через ROW_NUMBER() OVER (PARTITION BY группа ORDER BY метрика DESC) и во внешнем запросе беру rn <= N. Фильтровать по окну прямо в WHERE нельзя — окна считаются позже. Если окон в СУБД нет, тот же результат даёт CTE с MAX и обратным JOIN, но там при ничьей вернётся несколько строк».