Как разбить данные на группы с помощью оконных функций в SQL

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

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

Как отвечать

Окно задаётся в OVER: PARTITION BY делит строки на группы, ORDER BY задаёт порядок внутри группы, рамка (ROWS или RANGE) определяет, какие строки попадают в расчёт для текущей.

• нумерация: ROW_NUMBER() даёт уникальный номер, RANK() одинаковый при равенстве с пропусками, DENSE_RANK() без пропусков;
• деление на части: NTILE(4) OVER (ORDER BY amount) раскладывает строки на 4 группы, размеры которых отличаются не больше чем на одну строку. Так делят клиентов на квартили по выручке;
• соседние строки: LAG и LEAD берут значение из предыдущей или следующей строки, на них считают разницу между событиями;
• агрегаты: SUM(amount) OVER (PARTITION BY region) кладёт итог группы в каждую строку, удобно для доли от итога.

Типовая задача, которую стоит уметь писать с ходу, это сессии. Через LAG(event_time) находите разрыв больше 30 минут и ставите флаг 1, а накопительная SUM(flag) OVER (PARTITION BY user_id ORDER BY event_time) превращает флаги в номер сессии.

Топ-N в группе: ROW_NUMBER() в подзапросе или CTE и фильтр rn <= 3 снаружи. В Snowflake, BigQuery и DuckDB для этого есть QUALIFY.

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

Добавляют ORDER BY в окно и ждут итог группы. С ORDER BY рамка по умолчанию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, и SUM превращается в нарастающий итог, а строки с одинаковой датой получают одно значение.

Пишут оконную функцию в WHERE. Окна вычисляются после WHERE и GROUP BY, поэтому фильтровать по ним можно только во внешнем запросе.

Используют ROW_NUMBER() с неполной сортировкой. При равных значениях номер между запусками может меняться, и дедупликация оставляет разные строки. Добавьте в ORDER BY уникальный столбец.