← вся теориятеория к собесу · SQL

SQL: оконные функции

Зачем это спрашивают

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

Типовые формулировки: «топ-3 товара по выручке в каждой категории», «сколько времени прошло между заказами клиента», «нарастающий итог по дням».

// Если GROUP BY - это пресс, который давит целую группу в одну строку, то оконка - рентген: строки остаются на месте, а к каждой приписывается число, посчитанное по её соседям.

Что такое окно

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

Записывается так: функция() OVER (PARTITION BY … ORDER BY … ROWS …). PARTITION BY говорит, из каких строк собирать окно - это «GROUP BY, который не схлопывает». ORDER BY задаёт порядок внутри окна, а ROWS - какой кусок окна взять. Забудешь PARTITION BY - окном станет вся таблица, и ранги с долями посчитаются глобально, а не по группе.

// Оконка вычисляется после WHERE, GROUP BY и HAVING, поэтому окно видит только те строки, что пережили фильтры. «Доля от всех» после WHERE - на самом деле доля от отфильтрованных.

агрегат: число, посчитанное по набору строк: сумма, среднее, количество, максимум

Три функции нумерации и ничьи

Ничья - это когда у строк совпало значение, по которому идёт ORDER BY: две одинаковые выручки, две одинаковые даты. На ничьих три функции нумерации ведут себя по-разному, и в этом весь вопрос.

ROW_NUMBER нумерует подряд и уникально, ничью разбивает произвольно: 1, 2, 3. RANK даёт равным строкам одинаковый номер, а потом пропускает столько мест, сколько строк поделили предыдущее: 1, 1, 3. DENSE_RANK тоже даёт одинаковый номер, но не пропускает: 1, 1, 2. Разница не косметическая. «Топ-3 при ничьих» - это три разных ответа, и следующий вопрос интервьюера будет именно про них.

// Если ничьи есть, а какие из равных строк попадут наверх, тебе не всё равно - добавь в ORDER BY второй столбец для разрешения спора, хоть id. Иначе порядок между равными не определён и может меняться от запуска к запуску.

Топ-N в группе: вопрос номер один

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

ROW_NUMBER вернёт ровно N строк на группу, RANK при ничьих вернёт больше - и выбор между ними и есть содержательная часть ответа. В Snowflake, BigQuery и ClickHouse тот же фильтр пишется без обёртки, через QUALIFY rn <= 3; в Postgres и MySQL такого нет, подзапрос обязателен.

SELECT city, product, revenue FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY city ORDER BY revenue DESC) AS rn
  FROM sales
) t
WHERE rn <= 3

Соседние строки, бегущие итоги и рамка

LAG и LEAD достают значение из соседней строки окна - предыдущей и следующей. Через них считают дельту день к дню, время между заказами клиента, момент смены статуса.

Нарастающий итог - это SUM(x) OVER (PARTITION BY … ORDER BY date). Рамка по умолчанию тянется от начала окна до текущей строки, поэтому сумма и накапливается. А скользящее среднее за неделю рамку требует явно: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW - шесть строк назад плюс текущая.

// Подвох дефолта: без явного ROWS работает RANGE, а он набирает в рамку строки не по счёту, а по значению ORDER BY. Все строки с одинаковой датой попадают в рамку разом и получают один и тот же итог - ступенькой. Если даты повторяются, пиши ROWS.

Как отвечать: «Найди топ-3 товара по выручке в каждой категории»

Нумерую товары внутри категории: ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC), оборачиваю в подзапрос и снаружи фильтрую по номеру не больше трёх. Прямо в WHERE оконку писать нельзя, она вычисляется позже, чем отрабатывает WHERE. Дальше сам уточню про ничьи: ROW_NUMBER даст ровно три строки, а если равные выручки должны делить место, беру DENSE_RANK и принимаю, что строк вернётся больше трёх. И если равные выручки вообще возможны, добавлю в ORDER BY второй столбец, чтобы порядок был воспроизводимым.

Рецепт целиком, названа причина запрета в WHERE и разобраны ничьи до того, как про них спросили. Тайбрейкер в конце - деталь, которую вспоминает тот, кто такие запросы правда писал.

На чём валят

−Оконная функция в WHERE - не работает: фильтруй во внешнем запросе или через QUALIFY там, где он есть.
−RANK вместо ROW_NUMBER, когда нужно ровно N строк: при ничьих вернётся больше.
−Нарастающий итог по повторяющимся датам с дефолтной рамкой RANGE: равные даты слипаются в ступеньку, нужен ROWS.
−Забытый PARTITION BY: ранги и доли посчитались по всей таблице, а не внутри группы.
−ORDER BY без тайбрейкера при возможных ничьих: какая из равных строк окажется первой, не определено.

Проверьте себя

Пять вопросов из банка по этой подтеме. Всего их 34, остальные разбираются в тренажёре.

#window_functions1 / 5
Как выбрать последний заказ каждого юзера (вся строка заказа)?
A)SELECT user_id, MAX(created_at), * FROM orders GROUP BY user_id — по одной строке
B)Просто ORDER BY created_at DESC LIMIT 1 по всей таблице заказов
C)GROUP BY user_id HAVING created_at = MAX(created_at)
D)ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)=1 в CTE
показать ответ и разбор
+D)ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)=1 в CTE
// разбор: GROUP BY не отдаёт «всю строку победителя»: неагрегированные колонки в SELECT при группировке запрещены (вариант A — синтаксическая ошибка, C — тоже). Паттерн row_number-в-CTE-затем-фильтр — универсален; в Postgres идиоматичен DISTINCT ON с ORDER BY user_id, created_at DESC. Обязательный паттерн для секции живого SQL.
#window_functions2 / 5
Чем оконная функция с OVER (PARTITION BY ...) принципиально отличается от GROUP BY?
A)GROUP BY схлопывает группу в строку; оконка хранит все строки + агрегат по окну
B)Оконная функция работает быстрее, чем GROUP BY
C)PARTITION BY обязательно требует наличия подходящего индекса по колонке разбиения
D)Оконные функции не поддерживают SUM и AVG внутри окна
показать ответ и разбор
+A)GROUP BY схлопывает группу в строку; оконка хранит все строки + агрегат по окну
// разбор: «Зарплата и её доля в фонде отдела в одной строке» через GROUP BY требует join с агрегатом обратно; оконкой — одно выражение salary / SUM(salary) OVER (PARTITION BY dept). Плюс frame-клаузы (ROWS BETWEEN) дают скользящие окна для ретеншена и кумулятивов. LAG/LEAD — доступ к соседним строкам без self-join.
#window_functions3 / 5
Что делает LAG(revenue) OVER (ORDER BY month)?
A)Физически сдвигает всю таблицу целиком на одну строку вниз при каждой выборке
B)Считает скользящее среднее revenue по заданному временному окну
C)Даёт revenue предыдущей строки в порядке month — прирост без self-join
D)Просто удаляет самую первую строку из итогового результата запроса
показать ответ и разбор
+C)Даёт revenue предыдущей строки в порядке month — прирост без self-join
// разбор: LAG/LEAD дают доступ к соседним строкам окна: revenue − LAG(revenue) OVER (ORDER BY month) — абсолютный прирост, а с делением — темп роста. У первой строки LAG вернёт NULL (есть третий аргумент-дефолт). С PARTITION BY считается внутри каждой группы отдельно — «прирост по каждому продукту».
#window_functions4 / 5
Как выбрать 5 самых дорогих товаров одним запросом?
A)ORDER BY price DESC LIMIT 5
B)GROUP BY price HAVING price = MAX(price) без сортировки результата
C)WHERE price = 5 по условию точного равенства цены товара
D)Применить DISTINCT к колонке price по всей таблице товаров вообще без ограничения строк
показать ответ и разбор
+A)ORDER BY price DESC LIMIT 5
// разбор: Сортировка по убыванию цены и LIMIT 5 отсекают верхние пять строк — базовый паттерн «топ-N». Для топ-N внутри каждой группы (например, по 3 товара на категорию) простого LIMIT мало — там нужна оконная ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC).
#window_functions5 / 5
Нужен накопительный итог выручки по дням (cumulative sum) отдельной колонкой рядом с дневной выручкой. Как?
A)GROUP BY day с SUM(revenue) — сама группировка по дате и посчитает нужный накопительный итог по возрастанию дней
B)Подзапрос с COUNT(*) по предыдущим строкам — накопительный итог как раз и получается подсчётом числа этих строк
C)SUM(revenue) OVER (ORDER BY day) — оконная сумма по нарастающему окну от начала до текущей строки
D)Просто ORDER BY day DESC — обычная сортировка строк по убыванию даты сама и даёт колонку накопительного итога
показать ответ и разбор
+C)SUM(revenue) OVER (ORDER BY day) — оконная сумма по нарастающему окну от начала до текущей строки
// разбор: Накопительный итог — оконная функция: SUM(revenue) OVER (ORDER BY day) суммирует от первой строки до текущей (дефолтная рамка от начала до CURRENT ROW), возвращая нарастающий итог на каждую строку, не схлопывая их (проверено: 5, 12, 15). GROUP BY, наоборот, схлопнул бы строки в одну сумму, а сортировка сама ничего не накапливает. Для итога отдельно по группам добавляют PARTITION BY.

дальше

Теорию прочитали. Навык ставится повторением

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

Открыть тренажёр Вся тема: SQL