Что такое оконные функции и когда они вместо GROUP BY?

Порог между junior и middle. Кто освоил окна — перестаёт городить подзапросы и самосоединения там, где хватает одной строчки с OVER.

Как рассуждать

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

Синтаксически окно — это агрегат (или спецфункция) плюс OVER (...). Внутри OVER два рычага: PARTITION BY делит строки на группы-окна (аналог GROUP BY, но без схлопывания), а ORDER BY задаёт порядок внутри окна — он нужен для нумерации, накоплений и сравнения с соседями.

Вопрос-триггер «когда окно вместо группировки»: как только рядом с детальной строкой нужен агрегат по группе — доля в общем, ранг, разница с предыдущей — это окно.

Эталонный ответ

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

SELECT
  f.title,
  c.name AS category,
  sum(p.amount) AS film_revenue,
  sum(sum(p.amount)) OVER (PARTITION BY c.name) AS category_revenue
FROM payment p
JOIN rental r      ON r.rental_id = p.rental_id
JOIN inventory i   ON i.inventory_id = r.inventory_id
JOIN film f        ON f.film_id = i.film_id
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c    ON c.category_id = fc.category_id
GROUP BY f.title, c.name;

Обрати внимание: окно тут работает поверх агрегата — сначала GROUP BY считает выручку фильма, потом окно суммирует эти суммы по категории. Так GROUP BY и окно уживаются в одном запросе.

Типичные оконные функции: row_number/rank/dense_rank для нумерации и рейтингов, sum/avg ... OVER для накоплений и долей, lag/lead для сравнения с предыдущей и следующей строкой (рост месяц-к-месяцу). Разницу нумерующих функций разбираем отдельно — чем ROW_NUMBER, RANK и DENSE_RANK отличаются.

Сильный ответ добавит, что окно вычисляется после WHERE/GROUP BY/HAVING, но до ORDER BY и LIMIT — поэтому фильтровать по результату окна напрямую нельзя, нужен подзапрос или CTE.

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

• Путают PARTITION BY с GROUP BY и ждут, что строки схлопнутся. Окно строки не убирает — если нужно по одной на группу, это GROUP BY. Почему при GROUP BY нельзя тащить лишние колонки — в разборе про колонку без агрегата.
• Пишут WHERE row_number() OVER(...) = 1 — и получают ошибку: по окну в WHERE фильтровать нельзя. Нужен внешний запрос.
• Забывают ORDER BY внутри OVER там, где он критичен (накопления, lag), и получают неверный порядок.
• Считают, что окна — это «продвинуто и медленно». На деле они часто быстрее и читабельнее, чем коррелированные подзапросы.