SQL Оконные функции простым языком

Введение: Почему мы боимся оконных функций?

Каждый SQL-разработчик помнит свое первое столкновение с конструкцией OVER(). Ты сидишь, уверенно пишешь SELECT, фильтруешь через WHERE, группируешь через GROUP BY и чувствуешь, что полностью контролируешь данные. А потом прилетает банальная продуктовая задача: посчитать нарастающий итог или вытащить топ-3 записи для каждой категории.

Ты идешь на StackOverflow, копируешь решение с ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), вставляешь в IDE и смотришь на этот монолит. Синтаксис кажется перегруженным, он ломает привычную структуру запроса, и в этот момент действительно появляется желание закрыть ноутбук и пойти работать руками на свежем воздухе.

Но вот главный спойлер: оконные функции — это не закрытый клуб для избранных. Это просто инструмент. Как только вы один раз поймете их базовую механику, вы с наслаждением начнете выпиливать из своего кода километровые самоджойны (JOIN) и многоэтажные вложенные подзапросы. Окна делают код читаемым, чаще всего выполняются быстрее и, что самое главное, берегут ваши нервы.

(Кстати, если перед тем как переходить к оконным функциям, вы чувствуете, что нужно уверенно закрепить фундамент, залетайте на мой бесплатный курс на Stepik — «SQL часть 1. Основы и работа с данными». Отлично подойдет для разминки перед сложными темами!)

В этой статье мы разберем базу без академической духоты. К концу чтения вы четко поймете, как работают четыре самые популярные функции — ROW_NUMBER(), RANK(), LAG() и SUM() OVER() — и начнете применять их на автомате. Погнали.

2. В чем суть: GROUP BY

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

Как работает классический GROUP BY: безжалостное сжатие Допустим, вам нужно посчитать суммарный урон каждой команды за матч. Вы пишете SUM(damage) и добавляете GROUP BY team. Что в этот момент делает SQL? Он работает как гидравлический пресс. База данных берет десятки строк с детальной статистикой каждого игрока и безжалостно сплющивает их в одну строку для каждой команды.

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

Как работает оконная функция: аналитика без потерь А теперь представьте, что вам нужна и статистика каждого игрока, и суммарный урон его команды в одной таблице. С GROUP BY вам придется писать подзапрос с группировкой, а потом прикручивать его через JOIN к исходной таблице. Это долго, громоздко и медленно работает.

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

**Наш полигон: таблица cs2_matches**

Чтобы примеры дальше не выглядели как абстрактный код в вакууме, мы будем работать с понятным датасетом. Это лог матча по CS2, где фиксируются результаты каждого игрока в каждом раунде. Команды и никнеймы выдуманные, но суть абсолютно реальна.

Вот структура нашей таблицы cs2_matches и пара строк для наглядности:

Здесь есть всё необходимое для тестов:

• Текстовые категории, по которым мы будем бить данные на независимые окна (team).

• Хронология, по которой мы будем выстраивать логику внутри окна (round_number).

• Метрики, которые мы будем считать и анализировать (damage и frags).

Именно на этих данных мы сейчас начнем разбирать работу конструкции OVER().

3. ROW_NUMBER(): Раздаем талончики в очередь

Задача: Вывести топ-3 лучших игроков в каждой команде по суммарному количеству фрагов за турнир.

Если попытаться решить эту задачу инструментами классического SQL без окон, придется сильно пострадать. Вы не можете просто написать LIMIT 3, потому что лимит сработает на весь запрос, а нам нужен топ-3 для каждой команды отдельно. В старых версиях СУБД для этого приходилось использовать костыли с пользовательскими переменными (сохранять предыдущую команду и увеличивать счетчик) или писать тяжелые коррелированные подзапросы, которые заставляют базу сканировать саму себя для каждой строки, намертво убивая производительность.

Решение: Оконная функция ROW_NUMBER() справляется с этим элегантно и быстро. Она делает ровно то, что обещает ее название — просто нумерует строки по заданным правилам.

Давайте сначала посчитаем общие фраги каждого игрока, а затем пронумеруем их внутри команд:

WITH PlayerStats AS (    SELECT         player_id,         team,         SUM(frags) AS total_frags    FROM cs2_matches    GROUP BY player_id, team)SELECT     player_id,    team,    total_frags,    ROW_NUMBER() OVER (PARTITION BY team ORDER BY total_frags DESC) AS rank_in_teamFROM PlayerStats;

Анатомия функции: Вся суть работы окна скрыта в конструкции OVER (...). Давайте разберем ее по частям:

• OVER() — ключевое слово, которое сообщает базе данных, что функция ROW_NUMBER() должна применяться не ко всему набору данных разом, а работать как оконная.

• PARTITION BY team — правило изоляции. Мы говорим: «разбей всех игроков на группы по названию команды». База данных начнет нумеровать игроков NeonDragons с единицы, а как только перейдет к записям команды VoidWalkers, счетчик сбросится и снова начнется с 1.

• ORDER BY total_frags DESC — правило сортировки внутри созданной группы. Кто получит номер 1? Тот, у кого больше всего фрагов. Остальные выстроятся по убыванию.

Результат этого запроса будет выглядеть так:

Чтобы окончательно решить исходную задачу и оставить только топ-3, нам остается лишь обернуть этот запрос в еще один WITH (или подзапрос) и добавить фильтр WHERE rank_in_team <= 3. Никакой боли, все читается на уровне простого английского языка.

4. RANK(): Честное разделение пьедестала

Задача: Представьте классическую ситуацию: турнир окончен, вы считаете MVP (самого ценного игрока) команды, но два киберспортсмена набрали абсолютно одинаковое количество фрагов. Кому отдать первое место?

Если мы натравим на такие данные изученный выше ROW_NUMBER(). Увидев двух игроков с результатом в 45 фрагов, он отдаст первое место тому, кого база данных первым прочитала с диска (или по внутреннему системному алгоритму), а второму повесит обидный ярлык №2. В аналитике и бизнес-отчетах такая случайность недопустима — она искажает реальную картину.

Разница между ROW_NUMBER() и RANK() Для справедливого распределения мест существует функция RANK(). Ее логика полностью совпадает со спортивными правилами:

• ROW_NUMBER() — это жесткий конвейер: 1, 2, 3, 4. Никаких дубликатов, счетчик всегда увеличивается на единицу.

• RANK() — это классический пьедестал. Если два человека выдали одинаковый результат, они оба получают ранг 1. Следующий за ними получает ранг 3 (потому что второе место фактически «съедено» дублем). Последовательность выглядит так: 1, 1, 3, 4.

Наглядный пример: Давайте немного изменим статистику из предыдущего шага. Допустим, Pixel собрался в последних раундах и догнал CyberWolf — у обоих теперь по 45 фрагов.

Выведем обе оконные функции рядом в одном запросе, чтобы кристально ясно увидеть разницу.

WITH PlayerStats AS (    -- Смоделируем ситуацию с ничьей    SELECT 'CyberWolf' AS player_id, 'NeonDragons' AS team, 45 AS total_frags UNION ALL    SELECT 'Pixel', 'NeonDragons', 45 UNION ALL    SELECT 'Glitch', 'NeonDragons', 21)SELECT     player_id,    total_frags,    ROW_NUMBER() OVER (PARTITION BY team ORDER BY total_frags DESC) AS strict_row,    RANK() OVER (PARTITION BY team ORDER BY total_frags DESC) AS fair_rankFROM PlayerStats;

Результат выполнения:

Обратите внимание на Glitch. Он третий по списку, поэтому RANK() присваивает ему математически точное третье место, пропуская двойку. ROW_NUMBER() же просто пронумеровал строки по порядку, проигнорировав ничью.

Полезный нюанс: Если специфика задачи требует, чтобы нумерация после одинаковых результатов не прерывалась (то есть шла 1, 1, 2, 3), в SQL есть функция DENSE_RANK(). Она работает точно так же, но «уплотняет» выдачу, не оставляя пропусков (дыр) в рангах.

5. LAG()

Задача: Мы хотим проанализировать стабильность киберспортсмена: сравнить его нанесенный урон в текущем раунде с уроном в предыдущем. Разогревается он по ходу матча или, наоборот, проседает по фокусу?

Реляционные базы данных мыслят неупорядоченными множествами, а не последовательностями. Для SQL таблица — это просто мешок со строками. Строка из второго раунда ничего не знает о существовании строки из первого раунда.

Если решать эту задачу через классический JOIN, вам придется джойнить таблицу саму на себя, прописывая громоздкое условие связывания: t1.player_id = t2.player_id AND t1.round_number = t2.round_number + 1. А если игрок пропустил раунд из-за дисконнекта? Логика сразу усложняется, код пухнет, а планировщик запросов начинает грустить.

Решение с помощью LAG() Функция LAG() создана специально для того, чтобы обращаться к предыдущим строкам без всяких самоджойнов. Вы буквально говорите базе: «оставаясь в текущей строке, загляни на шаг назад и принеси мне значение из колонки X».

Синтаксис выглядит максимально прозрачно. Давайте рассчитаем дельту урона для нашего игрока CyberWolf:

SELECT     player_id,    round_number,    damage AS current_damage,    LAG(damage) OVER (PARTITION BY player_id ORDER BY round_number) AS prev_damage,    damage - LAG(damage) OVER (PARTITION BY player_id ORDER BY round_number) AS damage_deltaFROM cs2_matchesWHERE player_id = 'CyberWolf';

Разбор механики:

• PARTITION BY player_id — мы ограничиваем окно просмотра историей только одного конкретного игрока. Заглядывать в чужие раунды нельзя.

• ORDER BY round_number — мы выстраиваем хронологию. Без сортировки база не поймет, какая строка считается «предыдущей».

• LAG(damage) — берет значение из колонки damage из строки, стоящей прямо перед текущей.

Результат выполнения:

Обратите внимание на первый раунд: prev_damage возвращает NULL. Это абсолютно логично, ведь первого раунда не было «нулевого», поэтому функции некуда заглядывать. Со второго раунда база начинает исправно подтягивать прошлые значения и считать дельту: мы сразу видим, что во втором раунде игрок просел на 60 единиц урона, зато в третьем собрался и выдал на 130 больше, чем во втором.

Полезный нюанс: У LAG() есть брат-близнец — функция LEAD(). Она делает абсолютно то же самое, но заглядывает не в прошлое (назад), а в будущее (в следующую строку). Обе функции по умолчанию шагают на 1 строку, но вторым аргументом можно передать любой отступ: например, LAG(damage, 2) заглянет на два раунда назад.

6. SUM() OVER(): Накопительный итог (Running Total)

Задача: Мы хотим нарисовать красивый график для трансляции матча, который покажет, как рос общий урон каждой команды от первого раунда к последнему. Нам нужен классический накопительный итог (running total).

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

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

WITH TeamRoundDamage AS (    -- Шаг 1: Считаем суммарный урон команды внутри каждого конкретного раунда    SELECT         team,        round_number,        SUM(damage) AS round_damage    FROM cs2_matches    GROUP BY team, round_number)-- Шаг 2: Считаем накопительный итогSELECT     team,    round_number,    round_damage,    SUM(round_damage) OVER (PARTITION BY team ORDER BY round_number) AS running_total_damageFROM TeamRoundDamageORDER BY team, round_number;

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

Как это работает под капотом? Главный секрет скрыт в сортировке ORDER BY round_number внутри окна.

Когда вы добавляете ORDER BY в агрегатную оконную функцию, SQL неявно включает правило скользящего окна (frame). База данных читает это так: «для текущей строки возьми сумму всех значений от самого начала группы (PARTITION) до текущей строки включительно».

• В первом раунде сумма равна урону первого раунда (345).

• Во втором раунде окно расширяется: берется урон первого раунда + второго (345 + 410 = 755).

• В третьем раунде окно снова растет, включая уже три строки, и так далее.

Если бы мы забыли написать ORDER BY round_number внутри OVER(...), база данных просто посчитала бы общий урон за всю игру и вывела бы одно и то же финальное значение (1535) во всех строках. Именно хронологическая сортировка превращает обычную сумму в динамически растущий накопительный итог, создавая ту самую кривую прогресса.

7. Заключение: Ваш новый SQL-арсенал

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

Краткая шпаргалка для закрепления:

• ROW_NUMBER() — жесткая нумерация строк по порядку (1, 2, 3). Ваш лучший друг для вывода топ-N записей в каждой категории.

• RANK() — справедливое распределение мест с учетом «ничьих» (1, 1, 3). Идеально для спортивной аналитики и рейтингов, где важна точность позиций.

• LAG() (и его брат LEAD()) — машина времени для ваших данных. Позволяет вытаскивать значения из предыдущих или следующих строк без тяжелых самоджойнов. Мастхэв для расчета дельт и конверсий.

• SUM() OVER(ORDER BY ...) — превращает классическую агрегацию в динамический накопительный итог (running total). Незаменим для построения графиков роста и когортного анализа.

Уверенное владение оконными функциями — это не просто очередная строчка в резюме. Это тот самый невидимый рубикон, который во многих командах отличает Junior-разработчика от Middle. Джун попытается решить задачу через боль, временные таблицы и циклы в коде приложения. Мидл напишет один красивый SELECT с OVER() и пойдет пить кофе.

Анонсы новых статей, полезные материалы, а так же если в процессе у вас возникнут сложности, обсудить их или задать вопрос по этой статье можно в моём Telegram‑сообществе. Смело заходите, если что‑то пойдет не так, — постараемся разобраться вместе.