Введение в оконные функции (Window Functions) для Data Analyst

1. Основы оконных функций: OVER, PARTITION BY, ORDER BY

Основы оконных функций: OVER, PARTITION BY, ORDER BY

Оконные функции (window functions) — один из самых важных инструментов SQL для аналитика данных. Они позволяют считать метрики по строкам, не «схлопывая» результат в одну строку на группу, как это делает GROUP BY.

В этой статье разберём базовый синтаксис и смысл трёх ключевых элементов:

OVER — «включает режим окна»

PARTITION BY — задаёт группы строк внутри окна

ORDER BY — задаёт порядок строк внутри окна

В следующих материалах курса обычно переходят к рамкам окна (frame: ROWS/RANGE) и более сложным приёмам.

Что такое «окно» в SQL

Окно — это набор строк, которые связаны с текущей строкой и доступны для вычисления.

Главная идея:

GROUP BY уменьшает число строк (агрегация возвращает по строке на группу)

оконная функция возвращает значение для каждой строки исходного набора, но считает его на «окне» (подмножестве строк)

Пример на уровне смысла:

GROUP BY department даст по одной строке на отдел

SUM(salary) OVER (PARTITION BY department) оставит каждую строку сотрудника, но добавит колонку с суммой зарплат по отделу

!Визуально показывает, что PARTITION BY делит строки на группы, а оконная функция считает значение для каждой строки внутри группы

Базовый синтаксис

Типовой шаблон выглядит так:

Важные замечания:

OVER (...) — обязательная часть, которая говорит SQL: «считай как оконную функцию»

PARTITION BY и ORDER BY внутри OVER — опциональны (могут отсутствовать)

оконные функции вычисляются после FROM/WHERE и до финального ORDER BY запроса (это важно для корректного понимания результата)

Официальные справки (для сверки синтаксиса в вашем диалекте SQL):

PostgreSQL Documentation: Window Functions

SQL Server Documentation: OVER Clause

BigQuery Documentation: Window function calls

Учебный пример данных

Будем использовать такую «мысленную» таблицу sales:

| order_id | dt | manager | region | amount | |---------:|------------|---------|--------|-------:| | 1 | 2025-01-01 | Anna | East | 100 | | 2 | 2025-01-03 | Anna | East | 50 | | 3 | 2025-01-02 | Boris | East | 80 | | 4 | 2025-01-01 | Boris | West | 40 | | 5 | 2025-01-04 | Anna | East | 120 |

OVER: что меняется, когда он появляется

OVER задаёт контекст вычисления для функции.

Сравним два запроса.

Агрегация с GROUP BY (результат станет короче):

Оконная сумма (результат останется построчным):

Интерпретация:

каждая строка заказа остаётся на месте

manager_sum повторяется для всех заказов одного менеджера

PARTITION BY: как задаются группы внутри окна

PARTITION BY делит строки на независимые «корзины» (partition). Оконная функция считается отдельно внутри каждой корзины.

Примеры:

PARTITION BY manager — расчёты отдельно по каждому менеджеру

PARTITION BY region — отдельно по каждому региону

PARTITION BY manager, region — отдельно по каждой паре «менеджер-регион»

Частая аналитическая задача: доля строки в группе

Например, хотим получить долю каждой продажи в сумме продаж менеджера:

Замечания:

умножение на 1.0 часто используют, чтобы избежать целочисленного деления в некоторых СУБД

в реальных задачах стоит аккуратно обрабатывать возможное деление на ноль (например, через NULLIF), но в данном наборе данных суммы не нулевые

Если PARTITION BY не указан

Если написать SUM(amount) OVER (), то окно — это все строки результата запроса.

total_sum будет одинаковым для каждой строки.

ORDER BY внутри OVER: порядок строк внутри окна

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

Ключевое следствие:

многие функции (например, ROW_NUMBER, LAG, LEAD) требуют осмысленного порядка

для агрегатов (SUM, AVG) наличие ORDER BY часто превращает метрику в «накопительную» (подробнее и строго — через рамку окна, но базовую интуицию можно получить уже здесь)

ROW_NUMBER: нумерация строк внутри группы

Нумеруем заказы каждого менеджера по дате:

Смысл rn:

для каждого manager нумерация начинается с 1

порядок задаётся dt

Рейтинг: чем отличается от ROW_NUMBER

В аналитике часто нужен «ранг» с учётом одинаковых значений. Для этого обычно используют RANK() или DENSE_RANK().

Пример: ранжируем продажи менеджера по сумме заказа (от большего к меньшему):

Интуитивное различие:

RANK() может «пропускать» номера после одинаковых значений

DENSE_RANK() не пропускает номера

Как вместе работают PARTITION BY и ORDER BY

Комбинация PARTITION BY + ORDER BY — самая частая в аналитике:

PARTITION BY отвечает на вопрос: «внутри каких групп мы считаем?»

ORDER BY отвечает на вопрос: «в каком порядке мы смотрим строки внутри группы?»

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

Как это читать:

внутри каждого manager строки упорядочены по dt

running_sum растёт по мере продвижения по датам

финальный ORDER BY manager, dt добавлен только для удобного отображения результата (он не задаёт логику окна)

> В разных СУБД детали «накопительной суммы» с SUM(...) OVER (ORDER BY ...) могут зависеть от настроек рамки окна по умолчанию. На практике для воспроизводимого результата рамку явно задают через ROWS BETWEEN .... Это будет отдельной темой следующих статей.

Типичные ошибки начинающих

Путать ORDER BY внутри OVER и финальный ORDER BY запроса.

Ожидать, что оконная функция «схлопнет» строки как GROUP BY.

Использовать ROW_NUMBER() без понятного ORDER BY (тогда нумерация может быть нестабильной).

Забывать PARTITION BY, когда нужна независимая логика «по клиенту / по менеджеру / по товару».

Практический чек-лист аналитика

Перед тем как написать оконную функцию, проговорите:

Какая сущность должна быть «группой»? Тогда это кандидат на PARTITION BY.

Нужен ли порядок внутри группы? Тогда добавляйте ORDER BY в OVER.

Должен ли результат остаться построчным? Если да — оконные функции подходят лучше, чем GROUP BY.

Что дальше по курсу

Дальнейшие темы обычно строятся на этой базе:

рамка окна (frame): ROWS и RANGE, UNBOUNDED PRECEDING, CURRENT ROW

функции доступа к соседним строкам: LAG, LEAD, FIRST_VALUE, LAST_VALUE

шаблоны: поиск «первой/последней» покупки, расчёт retention, дедупликация, сегментации, скользящие средние

2. Ранжирование: ROW_NUMBER, RANK, DENSE_RANK, NTILE

Ранжирование: ROW_NUMBER, RANK, DENSE_RANK, NTILE

Ранжирование — один из самых частых сценариев использования оконных функций в аналитике: топ-N товаров, лучшие менеджеры, первый заказ клиента, разбиение клиентов на сегменты по выручке.

В прошлой статье курса мы разобрали базовые кирпичики оконных функций: OVER, PARTITION BY, ORDER BY. В этой статье применим их к четырём ключевым функциям ранжирования:

ROW_NUMBER()

RANK()

DENSE_RANK()

NTILE(n)

Зачем ранжирование, если есть GROUP BY

GROUP BY агрегирует и уменьшает число строк. Ранжирование чаще нужно на уровне строк, чтобы:

пометить строку как первая/последняя в группе

выбрать топ-N строк внутри группы

посчитать место строки в отсортированном списке

разбить строки на квантили (например, на 4 группы по выручке)

Оконные функции позволяют сделать это без схлопывания данных.

Учебные данные

Продолжим использовать таблицу sales из предыдущей статьи:

Для примеров с одинаковыми значениями (ties) иногда полезно мысленно добавить ситуации, где amount совпадает у нескольких строк.

Общий шаблон ранжирования

Все функции ниже работают как оконные и почти всегда требуют ORDER BY внутри OVER:

PARTITION BY отвечает за вопрос внутри каких групп ранжируем.

ORDER BY отвечает за вопрос по какому ключу и в какую сторону сортируем.

> Финальный ORDER BY запроса (в конце) влияет только на отображение результата, но не задаёт логику ранжирования.

ROW_NUMBER

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

Пример: порядок заказов менеджера по дате

Что важно:

мы добавили order_id как дополнительный ключ сортировки, чтобы порядок был стабильным

внутри каждого manager нумерация начинается с 1

Типичный кейс: дедупликация

Если у вас есть несколько записей на одну сущность и нужно оставить, например, самую свежую:

Логика: оставляем по одной строке на order_id с максимальной dt.

RANK

RANK() присваивает ранг с учётом одинаковых значений:

если значения равны, ранг одинаковый

после группы одинаковых значений возможен пропуск рангов

Пример: ранжируем заказы менеджера по сумме (больше — лучше)

Интерпретация:

если у менеджера два заказа с одинаковым amount, они получат один и тот же rnk

следующий ранг может перескочить, например: 1, 1, 3

DENSE_RANK

DENSE_RANK() похож на RANK(), но не пропускает номера рангов:

равные значения получают одинаковый ранг

следующий ранг увеличивается на 1, например: 1, 1, 2

Когда выбирать DENSE_RANK вместо RANK

DENSE_RANK() удобнее, когда вы интерпретируете ранги как количество уникальных уровней.

Например:

выделить топ-3 уникальных значений (а не топ-3 строк)

посчитать, какой по счёту уникальный уровень у строки

Сравнение ROW_NUMBER, RANK, DENSE_RANK на примере ties

Представим внутри одного менеджера суммы заказов (по убыванию):

| amount | ROW_NUMBER | RANK | DENSE_RANK | |------:|-----------:|-----:|-----------:| | 120 | 1 | 1 | 1 | | 100 | 2 | 2 | 2 | | 100 | 3 | 2 | 2 | | 50 | 4 | 4 | 3 |

Смысл:

ROW_NUMBER всегда уникален

RANK даёт одинаковый ранг и пропускает следующий номер

DENSE_RANK даёт одинаковый ранг и не пропускает следующий номер

!Наглядное отличие поведения функций при одинаковых значениях

NTILE

NTILE(n) разбивает упорядоченные строки внутри окна на n групп ("корзин") с максимально равным количеством строк.

результат — номер группы от 1 до n

если строки не делятся поровну, первые группы обычно получают на 1 строку больше

Пример: делим заказы каждого менеджера на 3 группы по сумме

Как это читать:

tile_3 = 1 — верхняя часть распределения (более крупные суммы)

tile_3 = 3 — нижняя часть

Частые аналитические применения NTILE

RFM-подобные сегментации: разделить клиентов на квинтили/децили по выручке

построение децилей для оценочных моделей и аналитики воронок

грубая нормализация: сравнивать не абсолютные значения, а группу (1..10)

Практические правила выбора функции

Если вам нужно выбрать ровно одну строку в каждой группе (первая/последняя/самая большая) — чаще всего ROW_NUMBER().

Если нужно ранжирование с учётом одинаковых значений и вам важны "места" как в соревновании (с пропусками) — RANK().

Если нужно ранжирование по уникальным уровням без пропусков — DENSE_RANK().

Если нужно разделить строки на примерно равные сегменты — NTILE(n).

Две частые ошибки в ранжировании

Нестабильный порядок при одинаковых значениях

Если вы сортируете только по amount, а значений amount много одинаковых, то порядок строк внутри ties может быть неопределённым.

Практика:

добавляйте вторичный ключ сортировки: ORDER BY amount DESC, order_id

Путаница ORDER BY в окне и ORDER BY в конце запроса

Это разные уровни сортировки и разные задачи.

Ссылки на документацию

PostgreSQL: Window Functions

Microsoft SQL Server: OVER Clause

Google BigQuery: Window function calls

Что дальше по курсу

Следующий логичный шаг после ранжирования — научиться точно управлять тем, какие строки попадают в расчёт при наличии ORDER BY: рамки окна (ROWS и RANGE). Это критично для корректных накопительных итогов и скользящих метрик.

3. Аналитические функции: LAG, LEAD, FIRST_VALUE, LAST_VALUE

Аналитические функции: LAG, LEAD, FIRST_VALUE, LAST_VALUE

Аналитические оконные функции доступа к строкам отвечают на очень практичный вопрос: как сравнить текущую строку с соседней или взять значение с края группы, не делая self-join и не теряя построчность результата.

В прошлых статьях курса мы разобрали:

как работает OVER, PARTITION BY, ORDER BY

как ранжировать строки через ROW_NUMBER, RANK, DENSE_RANK, NTILE

Теперь добавим ещё один фундаментальный класс оконных функций:

LAG и LEAD для доступа к предыдущей и следующей строке

FIRST_VALUE и LAST_VALUE для доступа к первому и последнему значению в окне

Учебные данные

Продолжим использовать таблицу sales:

Во всех примерах ниже важно помнить:

PARTITION BY задаёт группы, внутри которых логика независима

ORDER BY внутри OVER задаёт порядок, по которому определяются предыдущая, следующая, первая и последняя строки

!Схема показывает, какие строки считаются предыдущей и следующей, а также что означает первое и последнее значение в окне

LAG

LAG(expr, offset, default) возвращает значение expr из предыдущей строки в окне.

expr — что вернуть (колонка или выражение)

offset — насколько строк назад (по умолчанию 1)

default — что вернуть, если предыдущей строки нет (по умолчанию NULL)

Пример

Покажем предыдущую сумму заказа для каждого менеджера по времени:

Что важно:

добавлен order_id как вторичный ключ сортировки, чтобы порядок был стабильным при одинаковых dt

для самой ранней продажи менеджера prev_amount будет NULL

Частый кейс

Посчитать изменение относительно предыдущего события:

Интерпретация delta_vs_prev:

положительное значение означает рост суммы заказа относительно предыдущего

NULL означает, что сравнивать не с чем (это первое событие менеджера)

default-значение

Если в отчёте удобнее видеть 0 вместо NULL для первой строки, используйте третий аргумент:

LEAD

LEAD(expr, offset, default) возвращает значение expr из следующей строки в окне.

Пример

Покажем следующую сумму заказа для каждого менеджера:

Типичные применения LEAD:

узнать, что произойдёт “после” текущего события

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

FIRST_VALUE

FIRST_VALUE(expr) возвращает expr из первой строки окна (с учётом ORDER BY внутри OVER).

Пример

Для каждого заказа менеджера показать его первую сумму заказа во времени:

Как это читать:

внутри каждого manager берётся самое раннее событие

first_amount повторяется на всех строках менеджера

Частый кейс

Показать стартовую точку и текущую (например, рост от первого значения):

LAST_VALUE

LAST_VALUE(expr) возвращает expr из последней строки окна. Но у этой функции есть важная особенность: результат сильно зависит от рамки окна.

Если в OVER есть ORDER BY, то во многих СУБД рамка по умолчанию устроена так, что окно заканчивается на текущей строке. В таком случае LAST_VALUE(amount) часто будет равен amount текущей строки, что для аналитика выглядит как ошибка.

Демонстрация типичной ловушки

Почему это может быть “не тем последним значением”:

без явного указания рамки, “последняя строка” может пониматься как последняя в рамке до текущей строки

значит, на каждой строке “последняя” будет часто совпадать с текущей

Как получить последнее значение по всей группе

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

Как это читать:

UNBOUNDED PRECEDING означает “с самого начала группы”

UNBOUNDED FOLLOWING означает “до самого конца группы”

рамка охватывает все строки менеджера, поэтому “последнее значение” действительно последнее по ORDER BY dt, order_id

Важные практические правила

Всегда задавайте стабильный порядок в окне, если возможны одинаковые значения ключа сортировки: ORDER BY dt, order_id обычно надёжнее, чем только ORDER BY dt.

Для сравнений “с соседней строкой” почти всегда нужны LAG и LEAD, а не RANK-функции.

FIRST_VALUE обычно работает так, как ожидается, а LAST_VALUE часто требует явной рамки ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

LAG и LEAD могут возвращать NULL на границах окна, поэтому заранее решите, что удобнее в отчёте: NULL или default.

Мини-шаблоны для аналитика

Разница с предыдущим периодом: metric - LAG(metric) OVER (PARTITION BY entity ORDER BY dt)

Флажок первого события: CASE WHEN ROW_NUMBER() OVER (...) = 1 THEN 1 END или сравнение с FIRST_VALUE

Значение на конец периода (по группе): LAST_VALUE(metric) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Ссылки на документацию

PostgreSQL: Window Functions

PostgreSQL: LAG

PostgreSQL: LEAD

PostgreSQL: FIRST_VALUE

PostgreSQL: LAST_VALUE

BigQuery: Navigation functions

SQL Server: Analytic functions

Что дальше по курсу

Следующий шаг после LAG/LEAD/FIRST_VALUE/LAST_VALUE обычно такой:

подробно разобрать рамки окна ROWS и RANGE

научиться строить скользящие метрики, корректные накопительные итоги и окна “за последние N дней/строк”

Это напрямую решает типичные аналитические задачи: moving average, rolling retention, rolling sum и корректные “значения на конец периода”.

4. Агрегаты по окну: суммы, средние, скользящие окна и кумулятивы

Агрегаты по окну: суммы, средние, скользящие окна и кумулятивы

Оконные функции полезны не только для ранжирования (ROW_NUMBER, RANK) и доступа к соседним строкам (LAG, LEAD), но и для расчёта агрегатов по окну — сумм, средних, минимумов/максимумов — так, чтобы результат оставался построчным.

В прошлых статьях мы разобрали базовый синтаксис OVER (PARTITION BY ... ORDER BY ...) и навигационные функции. Теперь добавим ключевой навык аналитика: управлять тем, какие строки участвуют в агрегате с помощью рамки окна (window frame).

Когда агрегаты по окну лучше, чем GROUP BY

GROUP BY “схлопывает” строки до одной на группу. Агрегат по окну считает по группе, но возвращает значение на каждой строке.

Типовые задачи, где оконные агрегаты удобнее:

накопительный итог по времени

скользящая сумма/среднее за последние строк

скользящая метрика за последние дней

доля строки в сумме группы (не теряя детализацию)

сравнение текущего значения с агрегатом по группе (например, средним по клиенту)

Учебные данные

Будем использовать таблицу sales (как в предыдущих материалах):

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

Агрегаты как оконные функции: SUM, AVG, MIN, MAX

Любой стандартный агрегат можно вызвать как оконную функцию:

Интерпретация:

окно задаётся PARTITION BY manager

строки не схлопываются: каждая продажа остаётся отдельной строкой

агрегаты повторяются на всех строках менеджера

Кумулятивы (накопительные итоги)

Кумулятив — это показатель, который нарастает по мере движения по упорядоченным строкам.

Самый частый пример: накопительная сумма продаж по времени внутри менеджера.

Ключевой момент: ORDER BY внутри OVER задаёт порядок расчёта. Финальный ORDER BY запроса нужен только, чтобы красиво показать результат.

!Наглядно показывает, что накопительная сумма считается независимо внутри каждой группы и растёт сверху вниз по порядку.

Важное уточнение про рамку окна “по умолчанию”

Во многих СУБД, когда вы пишете агрегат с ORDER BY внутри окна, рамка (то есть точный набор строк для расчёта) по умолчанию становится “от начала до текущей строки”. Именно поэтому кумулятив обычно “получается сам”.

Но полагаться на неявные правила не лучшая привычка: для воспроизводимости сложных расчётов рамку лучше задавать явно.

Рамка окна (frame): как управлять тем, какие строки участвуют в расчёте

Внутри OVER после ORDER BY можно указать рамку:

ROWS — рамка в терминах количества строк

RANGE — рамка в терминах диапазона значений ключа сортировки (например, диапазона дат)

Типовой шаблон:

Границы рамки:

UNBOUNDED PRECEDING — с самого начала группы

CURRENT ROW — текущая строка

UNBOUNDED FOLLOWING — до самого конца группы

N FOLLOWING — N строк назад/вперёд (для ROWS)

Документация для сверки синтаксиса:

PostgreSQL: Window Function Calls

PostgreSQL: Window Functions

BigQuery: Window function calls

Скользящее окно по последним N строкам (rolling по строкам)

Частая метрика: скользящая сумма или среднее по последним 3 событиям пользователя/менеджера.

Пример: скользящее среднее суммы заказа по последним 3 заказам менеджера.

Как читать ROWS BETWEEN 2 PRECEDING AND CURRENT ROW:

берём текущую строку и две предыдущие строки в этом порядке

если строк в начале группы меньше трёх, окно будет меньше (например, 1 или 2 строки)

!Показывает механику скользящего окна по последним N строкам.

Почему “по строкам” иногда опасно

Если у менеджера в один день 10 заказов, то “последние 3 строки” будут означать “последние 3 заказа”, а не “последние 3 дня”. Это нормально, если вы так и задумали, но может быть ошибкой, если вы хотели окно по времени.

Скользящее окно по времени (rolling по датам)

Окно “за последние 7 дней” обычно хотят в терминах дат, а не количества строк. Это чаще делают через RANGE (если диалект SQL поддерживает интервалы в рамке) или через предварительную агрегацию по дням.

Подход, который часто проще и надёжнее: сначала получить дневную выручку, затем считать rolling по дням.

Шаг 1: дневная выручка менеджера

Шаг 2: скользящая сумма за последние 7 дней (включая текущий)

В PostgreSQL можно написать так:

Как читать INTERVAL '6 days' PRECEDING:

рамка берёт строки, у которых dt попадает в диапазон от dt - 6 дней до текущего dt

вместе с текущим днём это даёт “7 дней” календарного окна

Замечание:

точный синтаксис RANGE ... INTERVAL зависит от СУБД

если ваш диалект не поддерживает такой RANGE, альтернативы: self-join по диапазону дат, или использование специализированных функций/тайм-серийных таблиц

“Одно и то же” ORDER BY, но разные результаты: ROWS vs RANGE

Важно понимать смысл:

ROWS работает по позициям строк: “2 строки назад”

RANGE работает по значениям сортировки: “все строки с датой в диапазоне”

Типовая ловушка: одинаковые значения ключа сортировки.

Если сортировка только по dt, и в один день много строк:

при RANGE ... CURRENT ROW “текущая строка” по смыслу часто означает “все строки с той же датой”

при ROWS ... CURRENT ROW это строго “до текущей строки включительно”, то есть зависит от стабильности порядка

Практическое правило:

если вы считаете “последние N событий” — чаще нужен ROWS

если вы считаете “за последние N дней/месяцев” — чаще нужен RANGE (или дневная агрегация + rolling)

если ключ сортировки может повторяться — добавляйте вторичный ключ (ORDER BY dt, order_id) для стабильности

Кумулятив “до конца группы”: когда нужна полная рамка

Иногда нужно на каждой строке видеть итог по всей группе (например, общий объём за период) и сравнивать с накопительным итогом.

Обратите внимание:

для total_sum порядок не обязателен, но рамку “на всю группу” можно указать явно

для running_sum рамка “от начала до текущей” записана явно, чтобы не зависеть от поведения по умолчанию

Доли и нормализация: строка относительно окна

Оконные агрегаты часто используются для расчёта долей.

Пример: доля заказа в сумме продаж менеджера.

Здесь NULLIF(..., 0) защищает от деления на ноль (полезно в реальных данных).

Практические ошибки и как их избежать

Путать ORDER BY в OVER и финальный ORDER BY запроса.

Не добавлять вторичный ключ сортировки при возможных совпадениях (например, dt, order_id).

Использовать ROWS там, где нужен период времени.

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

Итог

Оконные агрегаты расширяют базовые оконные конструкции из предыдущих статей:

PARTITION BY задаёт независимые группы расчёта

ORDER BY задаёт направление “движения” по строкам

рамка ROWS/RANGE задаёт точный набор строк, который попадает в расчёт

С этим набором вы закрываете большую часть практических аналитических метрик: кумулятивы, moving average, rolling sum, доли и сравнения с групповыми бенчмарками.

5. Практика и типичные ошибки: фреймы, NULL, производительность

Практика и типичные ошибки: фреймы, NULL, производительность

Эта статья завершает базовую часть курса: мы уже умеем задавать окно через OVER (PARTITION BY ... ORDER BY ...), ранжировать строки, доставать соседние значения (LAG/LEAD) и считать кумулятивы и скользящие метрики агрегатами по окну.

Теперь закрепим практику и разберём три источника большинства ошибок в оконных функциях:

фреймы окна (ROWS/RANGE) и их значения по умолчанию

NULL и “края окна” (первая/последняя строка)

производительность и как не сделать оконные функции главным тормозом отчёта

Фрейм окна: что именно попадает в расчёт

PARTITION BY отвечает на вопрос внутри каких групп считаем, ORDER BY — в каком порядке идём по строкам. Но даже с ORDER BY остаётся ещё один вопрос: какие строки из упорядоченного окна участвуют в вычислении на текущей строке? За это отвечает рамка окна (window frame).

Самая частая ошибка

Писать агрегат с ORDER BY, но не осознавать, какая рамка будет выбрана по умолчанию в вашей СУБД.

Результат:

кумулятив “вроде работает”, но на повторяющихся значениях сортировки начинает вести себя неожиданно

LAST_VALUE возвращает “текущее значение”, а не “последнее в группе”

!Наглядно показывает разницу между ROWS и RANGE при повторяющихся значениях ORDER BY

ROWS и RANGE: в чём разница

| Параметр | ROWS | RANGE | |---|---|---| | Единица измерения | количество строк | диапазон значений ключа сортировки | | “Текущая строка” при повторяющихся значениях ORDER BY | одна конкретная строка | часто означает все строки с тем же значением сортировки | | Типичные задачи | последние событий, кумулятив по строкам | окна “по времени” и диапазонам значений |

Практическое правило:

если вы хотите “последние 3 заказа” — чаще нужен ROWS

если вы хотите “последние 7 дней” — чаще нужен RANGE (или предварительная агрегация по дням)

Делайте рамку явной, когда от неё зависит смысл

Кумулятив “с начала до текущей строки” лучше писать так, чтобы не зависеть от дефолтов:

Здесь:

PARTITION BY manager отделяет менеджеров друг от друга

ORDER BY dt, order_id задаёт стабильный порядок

ROWS BETWEEN ... фиксирует смысл “кумулятива” независимо от диалекта

`LAST_VALUE`: классическая ловушка фреймов

Если вы пишете:

то очень часто получаете amount текущей строки. Причина: при ORDER BY рамка по умолчанию нередко заканчивается на текущей строке.

Надёжный вариант “последнее значение по всей группе”:

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

NULL: как он ведёт себя в окнах

NULL в оконных функциях чаще всего появляется из трёх источников:

края окна у LAG/LEAD (когда “предыдущей” строки нет)

реальные пропуски в данных (например, amount может быть NULL)

деление и расчёт долей (делитель может быть 0 или NULL)

LAG и LEAD: NULL на границах — это нормально

Для первой строки менеджера prev_amount будет NULL.

Если для отчёта нужен не NULL, а значение по умолчанию, задайте третий аргумент:

Практика:

для разницы с предыдущим часто лучше оставить NULL, чтобы не выдавать искусственные скачки

для витрин и дашбордов иногда удобнее подставить 0 или текущее значение, но это должно быть осознанным решением

Агрегаты и NULL: NULL обычно игнорируется

Во многих СУБД агрегаты (SUM, AVG, MIN, MAX, COUNT(expr)) игнорируют NULL.

Опасный момент: AVG(amount) при пропусках — это “среднее по непустым”, а не по всем строкам.

Если бизнес-смысл требует считать пропуск как 0, используйте COALESCE:

Но помните: это меняет смысл метрики.

Доли и деление на ноль

Типовой шаблон доли:

Здесь NULLIF(x, 0) вернёт NULL, если сумма равна 0, и защитит от ошибки деления на ноль.

NULL и порядок: `ORDER BY ... NULLS FIRST/LAST`

Если ключ сортировки может быть NULL (например, дата события не заполнена), порядок окна станет неочевидным.

В PostgreSQL можно задать явно:

Практика:

для аналитики чаще безопаснее отправлять NULL в конец (NULLS LAST), чтобы “неопределённые даты” не становились “самыми ранними”

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

Оконные функции часто требуют:

переразбиения данных на партиции (PARTITION BY)

сортировки внутри каждой партиции (ORDER BY)

На больших таблицах это может стать самым дорогим шагом.

Симптомы проблем

запрос “вроде простой”, но работает значительно дольше, чем GROUP BY

добавление одного ORDER BY в окно резко увеличивает время

несколько оконных функций с разными PARTITION BY/ORDER BY замедляют запрос непропорционально

Приёмы, которые почти всегда помогают

Сокращайте данные до оконных функций: фильтры в WHERE, нужные колонки в SELECT.

Предварительно агрегируйте, если вы считаете rolling по дням: транзакции дневные суммы окно по дням.

Давайте стабильный и минимальный ORDER BY в окне: лишние поля в сортировке удорожают план.

Если вам нужно несколько метрик с одним и тем же окном, старайтесь использовать одно и то же определение окна.

Пример, где одно окно переиспользуется через WINDOW (PostgreSQL, SQL standard):

Плюс: код проще поддерживать, а оптимизатору проще распознать одинаковые окна.

Не делайте “топ-N по группе” через лишние уровни, если есть QUALIFY

В BigQuery и Snowflake можно фильтровать по оконной функции без подзапроса через QUALIFY.

Пример для BigQuery:

Если QUALIFY нет (PostgreSQL, SQL Server), используйте CTE/подзапрос и фильтрацию по rn.

Индексы и физическая модель

Это зависит от СУБД, но общая логика такая:

если часто используете PARTITION BY manager ORDER BY dt, то индекс по (manager, dt) часто помогает уменьшить стоимость сортировки или чтения

при очень больших объёмах выигрывает кластеризация/сортировка хранения по ключам окна (если ваша платформа это поддерживает)

Всегда проверяйте план выполнения:

PostgreSQL: EXPLAIN

SQL Server: Execution Plans

Мини-чек-лист перед тем, как писать оконную функцию

Проверьте, нужен ли вам ROWS или RANGE, и не полагаетесь ли вы на “магическую” рамку по умолчанию.

Добавьте стабильный вторичный ключ сортировки (например, order_id), если ORDER BY может иметь повторы.

Явно решите, что делать с NULL на краях (LAG/LEAD) и с пропусками в метриках (COALESCE или “игнорировать”).

Для долей используйте NULLIF в делителе.

Если оконных функций много, постарайтесь переиспользовать одинаковые окна через WINDOW.

Если запрос медленный, сначала уменьшайте входные данные и количество сортировок, затем смотрите план.

Полезные ссылки

PostgreSQL: Window Functions

PostgreSQL: Window function calls

BigQuery: Window function calls

SQL Server: OVER Clause