Чем оконные функции отличаются от группировки (GROUP BY)

Что проверяют

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

Как отвечать

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

select user_id, event_date, amount,
       sum(amount) over (partition by user_id order by event_date) as running,
       amount / sum(amount) over (partition by user_id) as share
from payments;

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

Полезно назвать типовые применения: ROW_NUMBER для выбора последней записи на ключ, LAG и LEAD для сравнения с соседним периодом, рамка ROWS BETWEEN для скользящего среднего, разница между RANK и DENSE_RANK при равных значениях.

Где спотыкаются

Утверждают, что GROUP BY невозможен без агрегатной функции. Он прекрасно работает сам по себе и в таком виде просто убирает дубли по ключам, а агрегаты нужны только тогда, когда из схлопнутых строк надо что-то достать.

Второе: не задают рамку окна. Стоит написать сортировку внутри окна, и по умолчанию берётся диапазон от начала раздела до текущей строки вместе со всеми равными ей, поэтому при повторяющихся значениях ключа накопительная сумма ведёт себя не так, как ожидали.

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