Оконные функции SQL: как работают и как их спрашивают на собесах
Оконные функции SQL считают значение по группе связанных строк и при этом оставляют каждую строку в результате. Этим они отличаются от GROUP BY, который сворачивает группу в одну строку. Нужны для рангов, сравнения с предыдущей строкой, накопительных итогов и скользящих средних.
Для собеседования это обязательная тема. Вопрос «какие оконные функции ты знаешь и чем они отличаются от агрегатных» один из самых частых в SQL-секции на реальных собесах наших учеников. Ниже разбираем всё на маленьких таблицах с результатом, задачи с собесов в конце. Примеры написаны для PostgreSQL, синтаксис окон почти такой же в других базах.
Таблица для примеров
Создай её у себя и запускай запросы по ходу чтения. Руками это запоминается в разы быстрее.
CREATE TABLE orders (order_id INT, customer TEXT, order_date DATE, amount INT);
INSERT INTO orders VALUES
(1, 'Аня', '2026-03-01', 500),
(2, 'Аня', '2026-03-04', 300),
(3, 'Аня', '2026-03-09', 200),
(4, 'Боря', '2026-03-02', 700),
(5, 'Боря', '2026-03-05', 100),
(6, 'Вика', '2026-03-03', 400),
(7, 'Вика', '2026-03-06', 900);
Чем оконные функции отличаются от GROUP BY
Задача: узнать, сколько всего потратил каждый клиент. С GROUP BY получишь по строке на клиента, сами заказы пропадут.
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
ORDER BY customer;
| customer | total |
|---|---|
| Аня | 1000 |
| Боря | 800 |
| Вика | 1300 |
Та же сумма через окно. Все семь заказов на месте, сумма дописана рядом с каждым.
SELECT customer, order_date, amount,
SUM(amount) OVER (PARTITION BY customer) AS customer_total
FROM orders
ORDER BY customer, order_date;
| customer | order_date | amount | customer_total |
|---|---|---|---|
| Аня | 2026-03-01 | 500 | 1000 |
| Аня | 2026-03-04 | 300 | 1000 |
| Аня | 2026-03-09 | 200 | 1000 |
| Боря | 2026-03-02 | 700 | 800 |
| Боря | 2026-03-05 | 100 | 800 |
| Вика | 2026-03-03 | 400 | 1300 |
| Вика | 2026-03-06 | 900 | 1300 |
Теперь легко посчитать, например, долю заказа в тратах клиента: amount и итог лежат в одной строке. Без окна пришлось бы делать подзапрос и JOIN.
Синтаксис OVER: PARTITION BY, ORDER BY и рамка
Любая оконная функция выглядит так:
функция() OVER (
PARTITION BY колонка
ORDER BY колонка
ROWS BETWEEN начало AND конец
)
Все три части необязательны.
PARTITION BYделит строки на группы. Функция считается внутри каждой группы отдельно. Без него вся таблица считается одной группой.ORDER BYзадаёт порядок строк внутри группы. Обязателен для рангов и дляLAG,LEAD. УSUMменяет смысл: появляется накопительный итог.- Рамка окна (
ROWS BETWEEN) говорит, какие строки относительно текущей попадают в расчёт. Например, две предыдущие и текущая.
Какие функции бывают:
| Вид | Функции | Для чего |
|---|---|---|
| Агрегатные | SUM, AVG, COUNT, MIN, MAX |
Итоги и средние рядом со строкой |
| Ранжирующие | ROW_NUMBER, RANK, DENSE_RANK, NTILE |
Места и нумерация |
| Смещения | LAG, LEAD, FIRST_VALUE, LAST_VALUE |
Значение из другой строки |
ROW_NUMBER, RANK и DENSE_RANK: в чём разница
Все три нумеруют строки по порядку. Разница видна только на одинаковых значениях. Возьмём таблицу, где у Бори и Вики поровну баллов.
CREATE TABLE scores (student TEXT, points INT);
INSERT INTO scores VALUES
('Аня', 90), ('Боря', 85), ('Вика', 85), ('Гриша', 70);
SELECT student, points,
ROW_NUMBER() OVER (ORDER BY points DESC, student) AS row_num,
RANK() OVER (ORDER BY points DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY points DESC) AS dense_rnk
FROM scores
ORDER BY points DESC, student;
| student | points | row_num | rnk | dense_rnk |
|---|---|---|---|---|
| Аня | 90 | 1 | 1 | 1 |
| Боря | 85 | 2 | 2 | 2 |
| Вика | 85 | 3 | 2 | 2 |
| Гриша | 70 | 4 | 4 | 3 |
ROW_NUMBERвсегда даёт разные номера. Если вORDER BYесть равные значения, порядок среди них база выбирает сама и результат может меняться от запуска к запуску. Поэтому мы добавилиstudentкак второй ключ сортировки.RANKдаёт равным одно место и пропускает следующие. Было два вторых места, значит следующее сразу четвёртое.DENSE_RANKтоже даёт равным одно место, но без пропусков. После двух вторых идёт третье.
Как выбирать: нужна ровно одна строка на группу, бери ROW_NUMBER. Нужны все победители при равенстве, бери RANK. Нужны топ-N разных значений, бери DENSE_RANK.
LAG и LEAD: значение из соседней строки
LAG достаёт значение из предыдущей строки, LEAD из следующей. Это стандартный способ сравнить событие с прошлым без соединения таблицы с самой собой.
SELECT customer, order_date, amount,
LAG(amount) OVER (PARTITION BY customer ORDER BY order_date) AS prev_amount,
LEAD(amount) OVER (PARTITION BY customer ORDER BY order_date) AS next_amount
FROM orders
ORDER BY customer, order_date;
| customer | order_date | amount | prev_amount | next_amount |
|---|---|---|---|---|
| Аня | 2026-03-01 | 500 | NULL | 300 |
| Аня | 2026-03-04 | 300 | 500 | 200 |
| Аня | 2026-03-09 | 200 | 300 | NULL |
| Боря | 2026-03-02 | 700 | NULL | 100 |
| Боря | 2026-03-05 | 100 | 700 | NULL |
| Вика | 2026-03-03 | 400 | NULL | 900 |
| Вика | 2026-03-06 | 900 | 400 | NULL |
У первой строки группы нет предыдущей, поэтому там NULL. Заменить его можно третьим аргументом. Второй аргумент это шаг смещения: LAG(amount, 1, 0) возьмёт значение на одну строку назад, а там, где строки нет, подставит 0.
Накопительный итог в SQL
Добавляем ORDER BY к SUM, и сумма начинает расти от строки к строке. Так считают выручку с начала месяца или баланс счёта.
SELECT customer, order_date, amount,
SUM(amount) OVER (PARTITION BY customer ORDER BY order_date) AS running_total
FROM orders
ORDER BY customer, order_date;
| customer | order_date | amount | running_total |
|---|---|---|---|
| Аня | 2026-03-01 | 500 | 500 |
| Аня | 2026-03-04 | 300 | 800 |
| Аня | 2026-03-09 | 200 | 1000 |
| Боря | 2026-03-02 | 700 | 700 |
| Боря | 2026-03-05 | 100 | 800 |
| Вика | 2026-03-03 | 400 | 400 |
| Вика | 2026-03-06 | 900 | 1300 |
Рамка окна: ROWS и RANGE
Рамка задаёт, какие строки участвуют в расчёте для текущей. Границы такие: UNBOUNDED PRECEDING (с начала группы), 2 PRECEDING (две строки назад), CURRENT ROW, 1 FOLLOWING (одна вперёд), UNBOUNDED FOLLOWING (до конца группы).
Скользящее среднее за три заказа, все клиенты вместе:
SELECT order_date, amount,
ROUND(AVG(amount) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 1) AS avg_3
FROM orders
ORDER BY order_date;
| order_date | amount | avg_3 |
|---|---|---|
| 2026-03-01 | 500 | 500.0 |
| 2026-03-02 | 700 | 600.0 |
| 2026-03-03 | 400 | 533.3 |
| 2026-03-04 | 300 | 466.7 |
| 2026-03-05 | 100 | 266.7 |
| 2026-03-06 | 900 | 433.3 |
| 2026-03-09 | 200 | 400.0 |
В первых двух строках окно неполное: считается среднее по одной и двум строкам. Это надо держать в голове, когда смотришь на начало графика.
Ловушка: рамка по умолчанию
Если в окне есть ORDER BY и нет явной рамки, база использует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE считает строки с одинаковым значением в ORDER BY одной группой. На повторяющихся датах это даёт неожиданный результат.
CREATE TABLE daily (day DATE, revenue INT);
INSERT INTO daily VALUES
('2026-03-01', 100), ('2026-03-02', 200), ('2026-03-02', 50), ('2026-03-03', 300);
SELECT day, revenue,
SUM(revenue) OVER (ORDER BY day) AS by_default,
SUM(revenue) OVER (
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS by_rows
FROM daily
ORDER BY day, revenue DESC;
| day | revenue | by_default | by_rows |
|---|---|---|---|
| 2026-03-01 | 100 | 100 | 100 |
| 2026-03-02 | 200 | 350 | 300 |
| 2026-03-02 | 50 | 350 | 350 |
| 2026-03-03 | 300 | 650 | 650 |
Обе строки за 2 марта в первом столбце получили 350, потому что RANGE взял сразу весь день. ROWS шёл построчно. Только порядок двух строк за один день при ORDER BY day база выбирает сама, поэтому на твоей машине значения 300 и 350 могут поменяться местами. Хочешь стабильный результат, добавь в сортировку уникальную колонку.
То же самое ломает LAST_VALUE. Рамка по умолчанию заканчивается на текущей строке, так что функция возвращает текущее значение вместо последнего.
SELECT order_date, amount,
LAST_VALUE(amount) OVER (ORDER BY order_date) AS wrong_last,
LAST_VALUE(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS real_last
FROM orders
WHERE customer = 'Аня'
ORDER BY order_date;
| order_date | amount | wrong_last | real_last |
|---|---|---|---|
| 2026-03-01 | 500 | 500 | 200 |
| 2026-03-04 | 300 | 300 | 200 |
| 2026-03-09 | 200 | 200 | 200 |
Почему нельзя написать WHERE row_number = 1
Частая ошибка новичка. Такой запрос не выполнится:
SELECT customer, ROW_NUMBER() OVER (ORDER BY amount) AS rn
FROM orders
WHERE ROW_NUMBER() OVER (ORDER BY amount) = 1;
Причина в порядке выполнения запроса, про него тоже спрашивают на собесах: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT (здесь считаются окна), DISTINCT, ORDER BY, LIMIT. На момент WHERE окна ещё не посчитаны. Поэтому результат окна заворачивают в CTE (WITH) или подзапрос и фильтруют снаружи. Заодно из этого порядка следует, что окно может использовать агрегаты из GROUP BY:
SELECT customer, SUM(amount) AS total,
RANK() OVER (ORDER BY SUM(amount) DESC) AS place,
SUM(SUM(amount)) OVER () AS grand_total
FROM orders
GROUP BY customer
ORDER BY place;
| customer | total | place | grand_total |
|---|---|---|---|
| Вика | 1300 | 1 | 3100 |
| Аня | 1000 | 2 | 3100 |
| Боря | 800 | 3 | 3100 |
Типовые задачи с собеседований
Первые три задачи звучали на реальных собесах учеников, топ-N добавили как типовую. Другие темы SQL разобраны в статье SQL на собеседовании.
Последняя покупка каждого пользователя
Самая частая задача на окна. Нумеруем покупки от новой к старой и берём первую.
WITH numbered AS (
SELECT customer, order_date, amount,
ROW_NUMBER() OVER (
PARTITION BY customer
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders
)
SELECT customer, order_date, amount
FROM numbered
WHERE rn = 1
ORDER BY customer;
| customer | order_date | amount |
|---|---|---|
| Аня | 2026-03-09 | 200 |
| Боря | 2026-03-05 | 100 |
| Вика | 2026-03-06 | 900 |
Дополнительный вопрос с собесов: как решить без ROW_NUMBER. Через GROUP BY и соединение. Разница в том, что при двух покупках за одну дату этот вариант вернёт обе.
SELECT o.customer, o.order_date, o.amount
FROM orders o
JOIN (
SELECT customer, MAX(order_date) AS last_date
FROM orders
GROUP BY customer
) m ON o.customer = m.customer AND o.order_date = m.last_date
ORDER BY o.customer;
Ещё один вопрос: можно ли обойтись окном совсем без подзапроса и JOIN. Можно, если хватает DISTINCT и FIRST_VALUE:
SELECT DISTINCT customer,
FIRST_VALUE(amount) OVER (
PARTITION BY customer ORDER BY order_date DESC, order_id DESC
) AS last_amount
FROM orders
ORDER BY customer;
| customer | last_amount |
|---|---|
| Аня | 200 |
| Боря | 100 |
| Вика | 900 |
Лучший продавец за каждый день, при равенстве все
Слова «при равенстве все» подсказывают функцию: нужен RANK, потому что ROW_NUMBER оставил бы одного.
CREATE TABLE sales (sale_date DATE, seller TEXT, amount INT);
INSERT INTO sales VALUES
('2026-03-01', 'Аня', 500), ('2026-03-01', 'Боря', 500), ('2026-03-01', 'Вика', 300),
('2026-03-02', 'Аня', 200), ('2026-03-02', 'Боря', 600);
WITH ranked AS (
SELECT sale_date, seller, amount,
RANK() OVER (PARTITION BY sale_date ORDER BY amount DESC) AS rnk
FROM sales
)
SELECT sale_date, seller, amount
FROM ranked
WHERE rnk = 1
ORDER BY sale_date, seller;
| sale_date | seller | amount |
|---|---|---|
| 2026-03-01 | Аня | 500 |
| 2026-03-01 | Боря | 500 |
| 2026-03-02 | Боря | 600 |
Дни, когда температура выше, чем вчера
Таблица weather(id, record_date, temperature), найти дни теплее предыдущего. Простой LAG здесь с ловушкой: 4 марта в данных нет, и 5 марта сравнится с 3-м. В условии сказано «вчера», значит, надо проверить и дату.
CREATE TABLE weather (id INT, record_date DATE, temperature INT);
INSERT INTO weather VALUES
(1, '2026-03-01', 5), (2, '2026-03-02', 7), (3, '2026-03-03', 6),
(4, '2026-03-05', 9), (5, '2026-03-06', 10);
WITH w AS (
SELECT id, record_date, temperature,
LAG(temperature) OVER (ORDER BY record_date) AS prev_temp,
LAG(record_date) OVER (ORDER BY record_date) AS prev_date
FROM weather
)
SELECT id, record_date, temperature
FROM w
WHERE temperature > prev_temp
AND record_date - prev_date = 1
ORDER BY record_date;
| id | record_date | temperature |
|---|---|---|
| 2 | 2026-03-02 | 7 |
| 5 | 2026-03-06 | 10 |
Без проверки даты в ответ попала бы и строка с id 4. Вычитание дат здесь в синтаксисе PostgreSQL, в других базах для этого есть свои функции.
Топ-2 заказа каждого клиента
DENSE_RANK возьмёт две самые большие суммы, даже если у какой-то суммы окажется несколько заказов. Замени на ROW_NUMBER, если нужно ровно две строки.
WITH ranked AS (
SELECT customer, order_date, amount,
DENSE_RANK() OVER (PARTITION BY customer ORDER BY amount DESC) AS rnk
FROM orders
)
SELECT customer, order_date, amount
FROM ranked
WHERE rnk <= 2
ORDER BY customer, amount DESC;
| customer | order_date | amount |
|---|---|---|
| Аня | 2026-03-01 | 500 |
| Аня | 2026-03-04 | 300 |
| Боря | 2026-03-02 | 700 |
| Боря | 2026-03-05 | 100 |
| Вика | 2026-03-06 | 900 |
| Вика | 2026-03-03 | 400 |
На практике оконные функции нужны постоянно: на них, например, считается когортный анализ. Если готовишься к выходу на рынок аналитиком и хочешь пройти SQL-секцию уверенно, посмотри, как мы помогаем найти работу в аналитике.
Частые вопросы
Чем оконные функции отличаются от агрегатных?
Агрегатная функция с GROUP BY сворачивает группу в одну строку. Оконная считает по группе и оставляет все строки. Одна и та же SUM становится оконной, когда к ней добавляют OVER.
Что делает ROW_NUMBER?
Присваивает строкам номера 1, 2, 3 в порядке из ORDER BY, внутри каждой группы из PARTITION BY нумерация начинается заново. Номера никогда не повторяются, даже на равных значениях.
Можно ли использовать оконную функцию в WHERE?
Нет. Окна считаются на шаге SELECT, позже WHERE и HAVING. Посчитай окно в CTE или подзапросе и фильтруй во внешнем запросе.
Какая рамка окна используется по умолчанию?
Без ORDER BY в окно входит вся группа. С ORDER BY работает RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, то есть от начала группы до текущей строки вместе со всеми строками, равными ей по сортировке.
Где решать задачи на оконные функции онлайн?
Для практики подойдёт DataLemur. Хороший минимум: одна-две задачи в день, пока не перестанешь подглядывать в синтаксис.