Базовые оконные функции / Хабр

Для начала разберемся в том, что это вообще такое, как это использовать, и почему нельзя просто агрегировать данные. Представим, что у нас есть таблица с заказами и нам нужно для каждого заказа добавить средний чек этого пользователя.

user_id	order_id	amount
1	101	1000
1	102	600
2	103	2000
3	104	500
3	105	700

Как это сделать (без знания оконных функций):

Код:

SELECT 
    user_id,
    AVG(amount) as avg_check
FROM orders
GROUP BY user_id;SELECT     event_date,    COUNT(DISTINCT user_id) as dauFROM user_eventsWHERE event_date = '2024-01-15' -- конкретный деньGROUP BY event_date;

Результат:

user_id	avg_check
1	800
2	2000
3	600

Что тут не так: мы тут теряем детализацию, у нас нет информации о каждом конкретном заказе, тут видим только средние по пользователям.

Как это сделать с оконной функцией

Код:

SELECT 
    user_id,
    order_id,
    amount,
    AVG(amount) OVER (PARTITION BY user_id) as avg_check
FROM orders;

Результат:

user_id	order_id	amount	avg_check
1	101	1000	800
1	102	600	800
2	103	2000	2000
3	104	500	600
3	105	700	600

Что тут важно: каждая строка с данными осталась на месте, мы видим всю детализацию, но к ней добавилась дополнительная информация – это отдельный столбец со средним чеком по пользователю.

Что такое оконная функция

Итак сформулируем, что же такое вообще оконная функция:

Оконная функция, это функция, которая:1 - смотрит на группу строк (окно);2 - вычисляет что-то для этой группы, но не схлопывает строки в одну – каждая строка остаётся на месте;3 - к каждой строке дописывает результат вычисления.

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

Сравнительная таблица оконной функции и агрегации (подзапроса):

Обычная агрегация(подзапрос)	Оконная функция
GROUP BY + SUM()	SUM() OVER (PARTITION BY ...)
Схлопывает все строки в одну	Сохраняет все строки и информацию в них
Теряется детализация	Детализация сохраняется, результат к каждой строке в новом столбце
Получаем итог по группе	Получаем итог по группе и сохраняем всю детализацию

Подробнее о составляющих оконных функций

Любая оконная функция состоит из:

<функция> OVER (
[PARTITION BY столбец1, столбец2, ...] -- как GROUP BY, но без схлопывания
[ORDER BY столбец1, столбец2, ...] -- задаем порядок строк в окне
)

Обязательные составляющие: ФУНКЦИЯ() OVER ()
Не обязательные составляющие: PARTITION BY, ORDER BY

OVER() – это ключевое слово, которое как бы считает функцию для каждой группы строк, указанных в партиции внутри скобок и приписывает каждой строке значение этой функции. Без OVER() функция работает как обычная, она просто схлопывает строки и группирует данные, а с OVER() функция становится оконной, она сохраняет все исходные данные, и добавляет новые.

PARTITION BY – это правило, которое разбивает таблицу на те самые группы (окна), в этом правиле мы и указываем по какому полю группу создавать.

Например, у тебя есть коробка с яблоками, грушами и апельсинами: PARTITION BY fruit_type (разбиваем фрукты по типам): яблоки – это одно окно, груши – другое окно, апельсины – третье окно. Внутри каждого окна можем что-то посчитать отдельно, например, общее количество.

Рассмотри партиции на примере таблицы с заказами:

AVG(amount) OVER (PARTITION BY user_id)

Что тут происходит по этапам:

1 - PARTITION BY user_id – тут разбиваем таблицу на группы по пользователям, получается три окна:
Окно 1: все заказы user_id = 1 (два заказа: 1000 и 600)
Окно 2: все заказы user_id = 2 (один заказ: 2000)
Окно 3: все заказы user_id = 3 (два заказа: 500 и 700)
2 - Далее внутри каждого окна считаем AVG(amount)
3 - Результат записываем в каждую строку этого окна.

Если PARTITION BY не указать и написать просто AVG(amount) OVER ()

Тогда всё таблица будет как одно большое окно и средний чек будет один для всех строк:

user_id	order_id	amount	avg_check
1	101	1000	960
1	102	600	960
2	103	2000	960
3	104	500	960
3	105	700	960

Среднее по всем заказам: (1000+600+2000+500+700)/5 = 960

ORDER BY внутри OVER() – это правило, которое задает в каком порядке рассматривать строки внутри окна, то есть не сортирует финальный результат (для этого есть отдельный ORDER BY в конце запроса), а именно задаёт порядок вычислений внутри каждой группы.

ORDER BY внутри OVER() → как считаемORDER BY в конце запроса → как показываем

Это важно для нумерации ROW_NUMBER() - тут функция нумерует строки в том порядке, который ты укажешь и накопительных сумм - тут сумма нарастает от первой строки к последней.

Оконные функции: ранжирование

Функции ранжирования – это функции, которые присваивают номера строкам внутри группы, их несколько потому что есть несколько вариантов того, как они обрабатывают одинаковые значения:

Функция	Как обрабатывает одинаковые значения
ROW_NUMBER()	Каждой строке даёт уникальный номер, даже если значения одинаковые
RANK()	Одинаковым значениям даёт одинаковый номер, но пропускает следующий номер
DENSE_RANK()	Одинаковым значениям даёт одинаковый номер, не пропускает следующий

Разберем на примере данных.
У нас есть результаты соревнований и три участника показали одинаковое время:

Имя	Время	Место (ROW_NUMBER)	Место (RANK)	Место (DENSE_RANK)
Анна	10.2	1	1	1
Иван	10.2	2	1	1
Пётр	10.2	3	1	1
Ольга	10.5	4	4	2
Сергей	10.7	5	5	3

ROW_NUMBER: тут каждому дали уникальное место (спорно, ведь время одинаковое). Особенности расчета в том, что каждая строка получает уникальный номер, даже если значения одинаковые, то номера всё равно разные и идут подряд: 1, 2, 3, 4, 5...

Когда использовать: когда нужно пронумеровать строки в определённом порядке, без повторок и выбрать четко первую, вторую, третью строку или еще когда нужно удалить дубликаты (оставить только одну).

RANK: все с одинаковым временем получили 1-е место, но следующее место сразу 4-е (честно, но с пропуском). Особенности расчета в том, что тут все одинаковые значения получают одинаковый ранг, но после группы одинаковых значений пропускается следующий номер. Когда использовать: когда нужно честное спортивное ранжирование (как в олимпиаде)

DENSE_RANK: все с одинаковым временем получили 1-е место, следующее место – 2-е (без пропусков). Особенности этой функции в том, что одинаковые значения получают одинаковый ранг и после группы одинаковых значений не пропускается следующий номер. Использовать лучше тогда, когда нужна непрерывная нумерация уровней, например, если нужно создание категорий (топ 3, топ 5) без разрывов.

Агрегатные функции как оконные (без схлопывания строк)

Агрегатные функции (SUM, COUNT, AVG, MIN, MAX) в обычном режиме схлопывают строки и из группы строк получается одна, а в оконном режиме они сохраняют каждую строку, просто дописывают к ней результат выбранной агрегации.

Разберем на примере данных, для примера у нас есть оценки учеников:

Ученик	Предмет	Оценка
Анна	Математика	5
Анна	Физика	4
Иван	Математика	3
Иван	Физика	5

При использовании обычной агрегации (GROUP BY), не сохраняется исходных данных и считается только средний балл каждого ученика:

Анна: 4.5
Иван: 4.0

А при использовании оконной агрегации (AVG() OVER) к каждой оценке приписывается средний балл ученика, но все оценки остаются на месте:

Анна, Математика, 5 средний 4.5
Анна, Физика, 4 средний 4.5
Иван, Математика, 3 средний 4.0
Иван, Физика, 5 средний 4.0

SUM() – сумма по окну

Код:

SELECT user_id, order_id, amount,
SUM(amount) OVER (PARTITION BY user_id) as user_total,
SUM(amount) OVER (ORDER BY order_id) as running_total
FROM orders;

Результат:

user_id	order_id	amount	user_total	running_total
1	101	1000	1600	1000
1	102	600	1600	1600
2	103	2000	2000	3600

COUNT() — количество строк в окне

Код:

SELECT 
user_id, 
order_id,
amount, 
COUNT(*) OVER (PARTITION BY user_id) as user_orders_count, 
COUNT(*) OVER () as total_orders_count
FROM orders;

Результат:

user_id	order_id	amount	user_orders_count	total_orders_count
1	101	1000	2	3
1	102	600	2	3
2	103	2000	1	3

AVG() – среднее по окну

Код:

SELECT 
user_id,
order_id,
amount,
AVG(amount) OVER (PARTITION BY user_id) as user_avg,AVG(amount) OVER () as total_avg
FROM orders;

Результат:

user_id	order_id	amount	user_avg	total_avg
1	101	1000	800	1200
1	102	600	800	1200
2	103	2000	2000	1200

MIN() / MAX() — минимум и максимум по окну

Код:

SELECT 
 user_id,
order_id,
amount,
MIN(amount) OVER (PARTITION BY user_id) as user_min,
 MAX(amount) OVER (PARTITION BY user_id) as user_max
FROM orders;

Результат:

user_id	order_id	amount	user_min	user_max
1	101	1000	600	1000
1	102	600	600	1000
2	103	2000	2000	2000

Итак, оконные функции – это важный функционал, который позволяет выполнять вычисления по группам строк без потери детализации, но помимо функций ранжирования и агрегатных еще существуют сдвиговые (LAG, LEAD) для сравнения с соседними строками, FIRST_VALUE и LAST_VALUE для поиска первого и последнего значения в окне и продвинутая конструкция фреймы окон (ROWS BETWEEN…AND… ) для скользящих расчётов, изучите их для продвинутого использования всех возможностей оконных функций в работе.

💚Еще больше про будни и задачи аналитика данных в бигтехе в моем тг канале 🌸Таня и Данные📊

📙Cтатьи для старта карьеры:
- Базовый минимум для старта в аналитике
- Как стать аналитиком с нуля (и не потратить на это много денег)
- Обзор книг
- SQL в 2026 для аналитика (с чего начать, где учиться и что реально нужно знать)
- Что реально нужно знать в Python начинающему аналитику

Cтатьи-разборы задач на SQL:
https://habr.com/ru/articles/1005262/
https://habr.com/ru/articles/1005284/