Почему `SUM() OVER (ORDER BY ...)` иногда считает «неправильно»: разбираем оконные фреймы в SQL

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

Именно поэтому они так быстро входят в рабочий набор: ROW_NUMBER(), RANK(), SUM() OVER (...), AVG() OVER (...) — и кажется, что всё уже понятно. Запросы выполняются, результаты выглядят правдоподобно, значит всё под контролем.

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

Классический пример: пишем SUM(amount) OVER (ORDER BY amount), ожидаем обычный накопительный итог, а получаем скачок сразу на несколько строк. Ошибки нет. SQL отработал корректно. Но сработал не так, как предполагалось.

Чтобы увидеть это максимально явно, удобно сделать мини-пример прямо на VALUES:

WITH t(amount) AS (
    VALUES
        (10.00),
        (11.99),
        (11.99),
        (12.50)
)
SELECT
    amount,
    SUM(amount) OVER (
        ORDER BY amount
    ) AS rolling_sum
FROM t
ORDER BY amount;

|--------|-------------|
| amount | rolling_sum |
|--------|-------------|
| 10.00  | 10.00       |
| 11.99  | 33.98       |
| 11.99  | 33.98       |
| 12.50  | 46.48       |

Попробуйте этот код: SQLize.online

Пока оставим этот запрос как «неправильный» и не будем сразу чинить. Сначала разберём, как работает оконный фрейм, а затем вернёмся к этому примеру и исправим его.

Что здесь происходит:

• rolling_sum использует фрейм по умолчанию (обычно RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

На первой строке 11.99 вы часто ожидаете сумму 21.99 (10.00 + 11.99).
Но rolling_sum уже даст 33.98, потому что во фрейм сразу попадут обе строки с 11.99.

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

Причина почти всегда одна: оконный фрейм.

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

Для меня понимание фреймов стало моментом, когда оконные функции перестали быть набором удобных трюков и сложились в цельную, управляемую модель.

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

• что такое оконный фрейм;

• чем отличаются ROWS, RANGE и GROUPS;

• как работают границы UNBOUNDED PRECEDING, CURRENT ROW, n FOLLOWING;

• почему фрейм по умолчанию может удивить;

• как писать накопительные итоги, скользящие средние и другие аналитические запросы без сюрпризов.

Что такое оконный фрейм

Когда мы пишем оконную функцию, обычно видим что-то вроде:

SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY payment_date
)

Я сам поначалу воспринимал это так: «функция считается по всей секции customer_id в порядке payment_date».

Это не совсем так.

PARTITION BY определяет, внутри какой секции работает окно.
ORDER BY определяет, в каком порядке рассматриваются строки.
А оконный фрейм определяет, какой поднабор строк из этой секции берётся для текущей строки.

Полная форма выглядит так:

function_name() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS | RANGE | GROUPS BETWEEN ... AND ...]
)

То есть окно можно представить как три уровня:

1. Сначала выбирается секция.

2. Затем внутри секции задаётся порядок.

3. Потом для каждой строки вычисляется фрейм: откуда начать и где закончить.

Почему это важно

Одна и та же функция SUM() может означать совершенно разное в зависимости от фрейма:

• накопительный итог от начала секции до текущей строки;

• скользящее окно из трёх строк;

• среднее по текущей и следующим строкам;

• агрегат по всей секции без схлопывания строк.

Снаружи запросы выглядят похожими. Поведение отличается радикально. Именно поэтому тема кажется простой до первого действительно странного результата в боевом запросе.

Границы фрейма

Фрейм обычно задаётся через BETWEEN ... AND ....

Варианты границ такие:

• UNBOUNDED PRECEDING — от самой первой строки секции;

• n PRECEDING — на n строк или логических шагов назад;

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

• n FOLLOWING — на n строк или шагов вперёд;

• UNBOUNDED FOLLOWING — до самой последней строки секции.

Пример:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Это классический накопительный итог: от начала секции до текущей строки.

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

Вот здесь начинается самое важное.

ROWS

ROWS работает с физическими строками.

Если написать:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

это всегда означает ровно три строки:

• две предыдущие;

• текущая.

Неважно, одинаковые у них значения в ORDER BY или нет. Счёт идёт именно по строкам.

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

RANGE

RANGE работает не с физическими строками, а с логическим диапазоном значений.

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

Именно из-за этого RANGE часто ведёт себя не так, как ожидают начинающие.

Самое важное: если вы указали ORDER BY, но не указали фрейм явно, во многих СУБД по умолчанию используется:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Это значит, что в расчёт попадут не просто все строки до текущей, а ещё и все строки с тем же значением сортировки, что и у текущей строки.

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

GROUPS

GROUPS работает с группами равных значений ORDER BY.

Если ROWS считает строки, а RANGE мыслит диапазонами значений, то GROUPS считает именно группы одинаковых значений.

Например:

GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW

означает: взять текущую группу равных значений и одну предыдущую группу целиком.

Это полезный режим, когда нужно мыслить не строками, а именно «ступенями» или «группами равных». Но поддерживается он не везде. В PostgreSQL он есть, в MySQL/MariaDB поддержка ограничена или отсутствует в зависимости от версии.

Исправляем стартовый пример

Теперь вернёмся к примеру из начала статьи и исправим его явным фреймом ROWS:

WITH t(amount) AS (
    VALUES
        (10.00),
        (11.99),
        (11.99),
        (12.50)
)
SELECT
    amount,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM t
ORDER BY amount;

Теперь сумма растёт построчно, как обычно и ожидается для накопительного итога:

|--------|-------------|
| amount | rolling_sum |
|--------|-------------|
| 10.00  | 10.00       |
| 11.99  | 21.99       |
| 11.99  | 33.98       |
| 12.50  | 46.48       |

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

Пример 1. Накопительный итог

Начнём с самой частой задачи.

SELECT
    customer_id,
    payment_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Что происходит:

• данные разделены по customer_id;

• внутри секции строки упорядочены по payment_date;

• для каждой строки сумма считается от первой строки секции до текущей.

Это и есть правильный, явный накопительный итог.

В своей практике я почти всегда стараюсь писать такой фрейм явно, даже если СУБД и так вернёт ожидаемый результат по умолчанию. Именно явное указание фрейма делает поведение очевидным и защищает от сюрпризов при дубликатах в сортировке.

Пример 2. Скользящее среднее

Теперь возьмём окно фиксированной ширины:

SELECT
    customer_id,
    payment_date,
    amount,
    ROUND(
        AVG(amount) OVER (
            PARTITION BY customer_id
            ORDER BY payment_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_3
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Здесь для каждой строки берутся:

• текущая строка;

• две предыдущие.

Итого максимум три строки.

Это типичный пример, где нужен именно ROWS, а не RANGE. Мы хотим фиксированное число строк, а не логическое расширение по равным значениям.

Пример 3. Окно, смотрящее вперёд

Оконные функции умеют анализировать не только прошлое, но и будущее относительно текущей строки:

SELECT
    customer_id,
    payment_date,
    amount,
    ROUND(
        AVG(amount) OVER (
            PARTITION BY customer_id
            ORDER BY payment_date
            ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
        ),
        2
    ) AS forward_avg
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Такой фрейм удобен, например, для:

• сглаживания;

• поиска локальных трендов;

• анализа ближайших событий вперёд.

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

Пример 4. Агрегат по всей секции без GROUP BY

Иногда нужно показать рядом со строкой агрегат по всей секции:

SELECT
    customer_id,
    payment_date,
    amount,
    ROUND(
        AVG(amount) OVER (
            PARTITION BY customer_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ),
        2
    ) AS customer_avg
FROM payment
WHERE customer_id IN (1, 2)
ORDER BY customer_id, payment_date;

Это похоже на GROUP BY customer_id, но строки не схлопываются. Мы по-прежнему видим каждую запись, просто рядом добавляется среднее по клиенту.

Такой шаблон полезен, когда нужно сравнить строку с общим контекстом:

• отклонение от среднего;

• доля от общего;

• сравнение с максимумом по группе.

Главная ловушка: ROWS и RANGE дают разные результаты

Допустим, у нас есть несколько строк с одинаковым amount.

Сравним:

SELECT
    customer_id,
    amount,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM payment
WHERE customer_id IN (1, 2, 3)
ORDER BY amount;

Если значение amount = 11.99 встречается несколько раз, поведение будет разным:

• ROWS считает строки по одной;

• RANGE включает сразу все строки с тем же значением amount.

Именно поэтому при дубликатах в сортировке накопительная сумма на RANGE может резко увеличиться на несколько строк сразу.

Это, пожалуй, самая частая причина путаницы: разработчик ожидает пошаговый рост, а получает скачок. Формально всё работает правильно. Проблема в неверном ожидании.

Когда использовать ROWS, а когда RANGE

Для себя я держу очень простое правило.

Используйте ROWS, когда нужны:

• накопительные итоги по строкам;

• скользящие средние по фиксированному числу строк;

• предсказуемое пошаговое поведение;

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

Используйте RANGE, когда нужны:

• расчёты по логическим диапазонам значений;

• объединение строк с одинаковым значением сортировки;

• поведение, завязанное именно на значение ORDER BY, а не на количество строк.

Используйте GROUPS, когда нужно работать с группами равных значений как с отдельными единицами анализа.

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

Именованные окна

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

SELECT
    customer_id,
    payment_date,
    amount,
    SUM(amount)   OVER w AS running_total,
    AVG(amount)   OVER w AS running_avg,
    COUNT(amount) OVER w AS payment_count
FROM payment
WHERE customer_id = 1
WINDOW w AS (
    PARTITION BY customer_id
    ORDER BY payment_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY payment_date;

Плюсы такого подхода:

• меньше дублирования;

• ниже риск опечатки;

• проще поддерживать;

• логика окна описана в одном месте.

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

Практический сценарий: дневные продажи

Один из самых полезных паттернов для отчётности и дашбордов:

SELECT
    DATE(payment_date) AS payment_day,
    SUM(amount) AS daily_total,
    SUM(SUM(amount)) OVER (
        ORDER BY DATE(payment_date)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_total,
    ROUND(
        AVG(SUM(amount)) OVER (
            ORDER BY DATE(payment_date)
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS rolling_7day_avg
FROM payment
GROUP BY DATE(payment_date)
ORDER BY payment_day;

Здесь сразу две полезные метрики:

• cumulative_total — накопительный итог;

• rolling_7day_avg — скользящее среднее за 7 дней.

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

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

Что у меня обычно ломалось на этой теме

Если свести мой опы�� к короткому списку, путаница почти всегда возникала в трёх местах:

• я мысленно подставлял «вся секция» там, где реально работал только текущий фрейм;

• я забывал, что RANGE при одинаковых значениях ORDER BY ведёт себя не как ROWS;

• я слишком долго полагался на поведение по умолчанию вместо явного описания окна.

После того как начинаешь явно задавать фрейм и отдельно спрашивать себя, что именно должно входить в расчёт для текущей строки, большая часть магии и непредсказуемости исчезает.

Что стоит запомнить

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

Коротко:

• PARTITION BY делит данные на секции;

• ORDER BY задаёт порядок внутри секции;

• фрейм определяет, какие строки вокруг текущей попадают в расчёт.

Самые полезные шаблоны:

• накопительный итог:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

• полная секция:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

• скользящее окно из трёх строк:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Самая важная ловушка:

• если у вас есть ORDER BY, но нет явного фрейма, часто используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

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

Вывод

Для меня оконные функции стали действительно понятными только тогда, когда я начал мыслить не просто «секциями» и «порядком», а именно фреймами.

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

• не полагаться на фрейм по умолчанию;

• указывать его явно;

• осознанно выбирать между ROWS и RANGE;

• помнить, что одинаковые значения ORDER BY меняют поведение окна.

С этого момента SQL перестаёт быть набором удобных трюков и превращается в точный инструмент аналитики.

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

Если захотите после статьи не просто прочитать примеры, а сразу руками проверить поведение ROWS, RANGE и GROUPS, удобнее всего открыть запросы в sqlize.online. А если нужен более последовательный формат изучения SQL с уроками и практикой, я веду это направление на sqltest.online.