Почему накопительная сумма в SQL врёт

Отчёт по выручке за январь. Итог за месяц сходится с бухгалтерией до копейки, аналитик выдыхает, дашборд уезжает в продакшен. Через неделю приходит вопрос от продукта: почему первого января накопительная сумма уже 300, если продали в тот день на 100 и на 200 двумя чеками?

Ответ неприятный: накопительная сумма врёт во всех строках, кроме последней, и врёт не из-за данных.

select d, amount, sum(amount) over (order by d) as running
from sales order by d;

Такой запрос пишут все. Он есть в каждом туториале и в каждом ответе про running total. Выдаёт он вот что:

    d      amount  running
2026-01-01    100      300
2026-01-01    200      300
2026-01-02     50      350
2026-01-03     10      410
2026-01-03     20      410
2026-01-03     30      410

Три строки третьего января — и у всех трёх 410. Последняя строка, 410, правильная, и расхождение с итогом не всплывает никогда.

Рамка есть всегда, даже когда вы её не написали

Рамка решает, какие строки окна попадут в расчёт. Пишете её не вы — значит её подставит СУБД, и подставит не ту, о которой вы думали. В документации это записано так:

The default framing option is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With ORDER BY, this sets the frame to be all rows from the partition start up through the current row's last ORDER BY peer.

Всё держится на слове peer. В режиме RANGE «текущая строка» — это вся группа строк, которые ORDER BY считает равными между собой. Все три записи от третьего января по order by d равны между собой, значит для каждой из них рамка тянется до последней из них. Каждая видит всю тройку.

Стоит написать рамку руками, и сумма становится той, которую ждали:

sum(amount) over (order by d rows between unbounded preceding and current row)

    d      amount  по умолчанию  rows
2026-01-01    100           300   100
2026-01-01    200           300   300
2026-01-02     50           350   350
2026-01-03     10           410   360
2026-01-03     20           410   380
2026-01-03     30           410   410

Правый столбец растёт по строкам, левый прыгает по дням. И оба заканчиваются на 410.

Почему в стандарте именно так

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

Строки внутри группы равных ничем не упорядочены, и если order by d не различает две записи от первого января, то какая из них окажется первой, решает движок — план запроса, порядок чтения страниц, наличие параллельного скана. Сегодня 100, потом 200, завтра наоборот.

Теперь посчитайте по такой паре накопительную сумму в режиме ROWS. Первая строка получит 100, вторая 300 — либо, если движок переставит их местами, первая получит 200, а вторая те же 300. Значение в отдельной строке становится случайным, притом что запрос не менялся и в таблице ничего не поменялось.

Рамка по умолчанию от этого и защищает. Она выдаёт всем равным строкам одно и то же число, и результат перестаёт зависеть от того, как движок разложил группу. Стандарт выбрал воспроизводимость вместо интуитивности: sum(amount) over (order by d) отвечает не на вопрос «сколько накопилось к этой строке», а на вопрос «сколько накопилось к концу этого дня», и на него в любом прогоне отвечает одинаково.

Так что запрос из первого раздела не сломан. Он отвечает на другой вопрос, а совпадение последней строки с итогом маскирует подмену вопроса.

ORDER BY в окне меняет не порядок, а рамку

Без ORDER BY рамка по умолчанию — это всё окно целиком, потому что без сортировки все строки друг другу равны. Добавили ORDER BY — и рамка молча стала другой:

    d      amount  без order by  с order by
2026-01-01    100           410         300
2026-01-01    200           410         300
2026-01-02     50           410         350
2026-01-03     10           410         410

Получается, sum(x) over () считает сумму по всему окну, а sum(x) over (order by d) уже накопительную. Одна дописанная строчка меняет не оформление результата, а то, что вообще считается. Если вы когда-нибудь добавляли ORDER BY в окно, чтобы «просто отсортировать вывод», проверьте те запросы.

last_value возвращает последнего равного, а не последнего

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

    d      amount  first_v  last_v  last_v с полной рамкой
2026-01-01    100      100     200                      30
2026-01-01    200      100     200                      30
2026-01-02     50      100      50                      30
2026-01-03     10      100      30                      30
2026-01-03     20      100      30                      30
2026-01-03     30      100      30                      30

first_value ведёт себя понятно: рамка начинается в начале окна, первая строка там для всех одна. А last_value упирается в конец рамки, в последнего равного, и на второй строке отдаёт 200 вместо 30. Чтобы получить настоящее последнее значение, рамку приходится раскрывать до конца окна:

last_value(amount) over (order by d rows between unbounded preceding and unbounded following)

А row_number, rank, dense_rank, lag и lead рамкой не управляются вообще, они смотрят только на окно и на сортировку. В одном запросе rank() считает правильно, а sum() рядом неправильно, и выглядит это чистой мистикой.

Самый дешёвый способ починить — не рамка

Тут ожидается совет «всегда пишите rows». Совет рабочий, но есть короче.

Равные строки появились из-за того, что по order by d они равны. Дайте сортировке уникальный хвост, и равных не останется, а рамка по умолчанию начнёт работать как надо:

id     d      amount  order by d  order by d, id
 1 2026-01-01    100         300             100
 2 2026-01-01    200         300             300
 3 2026-01-02     50         350             350

Дописали , id — и накопительная сумма поехала по строкам. Заодно результат стал детерминированным: пока в сортировке были равные, порядок строк внутри дня решал движок, и при следующем прогоне те же 100 и 200 могли встать в обратном порядке. Уникальный хвост в ORDER BY окна закрывает сразу две проблемы.

А теперь наоборот: место, где врёт ROWS

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

    d      v   rows 2 preceding  range 2 days
2026-01-01 10               10.0          10.0
2026-01-02 20               15.0          15.0
2026-01-03 30               20.0          20.0
2026-01-06 40               30.0          40.0
2026-01-07 50               40.0          45.0
2026-01-08 60               50.0          50.0

На шестое января rows 2 preceding взял три предыдущие строки таблицы — это третье, второе и первое января, окно шириной в неделю вместо трёх дней. А range between interval '2 days' preceding and current row посчитал по календарю, увидел, что предыдущих двух дней в данных нет, и вернул 40, значение одного дня.

Тут ROWS врёт, а вот RANGE считает правильно, то есть всё наоборот к накопительной сумме.

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

Что в стандарте есть с 2018 года

И ROWS с RANGE — это ещё не весь набор. В SQL:2011 описаны третий режим и правила исключения строк из рамки, а в PostgreSQL они лежат с 11-й версии, то есть целых 8 лет. В релизных заметках это записано так: «Window functions now support all framing options shown in the SQL:2011 standard, including RANGE distance PRECEDING/FOLLOWING, GROUPS mode, and frame exclusion options».

Про них почти не пишут, а ведь закрывают они как раз те случаи, которые обычно и имели в виду:

    d      amount  groups 1 preceding  exclude group  exclude ties
2026-01-01    100                 300                         100
2026-01-01    200                 300                         200
2026-01-02     50                 350            300           350
2026-01-03     10                 110            350           360
2026-01-03     20                 110            350           370
2026-01-03     30                 110            350           380

GROUPS считает не строки и не значения, а группы равных: groups 1 preceding даёт сумму текущего дня и предыдущего, и на третье января это 110, а не 410. Для отчёта «сегодня и вчера» это то, что нужно, и писать самому ничего не приходится.

EXCLUDE GROUP выбрасывает из рамки всю группу текущей строки — получается «сумма всего, что было строго до моего дня». В первых двух строках там пусто. У первого дня «всего, что было раньше» не существует, и вместо нуля приходит NULL, который дальше надо обрабатывать руками.

EXCLUDE TIES выбрасывает равных, но оставляет саму строку. Штука специфическая, но именно она отвечает на вопрос «сколько было до меня плюс я, не считая соседей по дню».

В плане этого не видно

Первым делом в такой ситуации мы все лезем в EXPLAIN, но тут он не вообще никак не поможет:

WindowAgg
  Output: d, sum(amount) OVER (?)
  ->  Sort
        Sort Key: sales.d
        ->  Seq Scan on public.sales

Это план с рамкой по умолчанию. А вот план с явной rows between unbounded preceding and current row:

WindowAgg
  Output: d, sum(amount) OVER (?)
  ->  Sort
        Sort Key: sales.d
        ->  Seq Scan on public.sales

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

Если запросы живут во вьюхах, база найдёт их сама:

select schemaname, viewname
from pg_views
where schemaname not in ('pg_catalog','information_schema')
  and definition ~* 'over[[:space:]]*\([^)]*order by'
  and definition !~* 'rows between|range between|groups between';

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

Секционирование, на которое тут обычно надеются, не спасает тоже. Равные строки живут внутри секции точно так же, как жили в целой таблице.

client     d      amount  по умолчанию  rows
A      2026-01-01    100           300   100
A      2026-01-01    200           300   300
A      2026-01-02     50           350   350
B      2026-01-01      7            15     7
B      2026-01-01      8            15    15

Каждый клиент получил свою порцию неправды, независимо от соседа. Отсюда, кстати, и требование к уникальному хвосту: различать он должен строки внутри секции, а не по всей таблице, потому что равными считаются только соседи по одной и той же секции, так что глобально уникальный id тут просто перестраховка, хотя и безвредная.

Цена рамки — это цена сортировки

Осталось посмотреть, сколько всё это стоит. Подозрение «RANGE же ищет равных, значит он медленнее» возникает сразу, и проверить его недолго. Таблица на 2 миллиона строк, сортировка не влезает в work_mem по умолчанию и уходит на диск:

рамка по умолчанию (range)                 1789 мс,  сортировка 35 МБ на диске
rows between unbounded preceding и current 1222 мс,  сортировка 35 МБ
order by d, id                             1576 мс,  сортировка 43 МБ

Похоже, подозрение подтвердилось. Но если поднять work_mem до 256 МБ, чтобы сортировка осталась в памяти, картина меняется:

range  118 мс и 112 мс
rows   125 мс и 120 мс
order by d, id   138 мс и 132 мс

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

Вывод: режим рамки выбирают по смыслу. По скорости выбирать тут нечего. А если оконный запрос тормозит, смотреть надо на сортировку и на work_mem.

Рамка это часть условия задачи

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

Начинать при этом надо не с рамок, а с сортировки. Равные строки в ORDER BY окна — источник и неверных чисел, и невоспроизводимого порядка, и уникальный хвост чинит сразу оба. Явную рамку после этого всё равно стоит писать, даже там, где по умолчанию получается верно: лишние семь слов в запросе стоят дешевле разговора с продуктом про то, почему первого января уже 300. А last_value в существующем коде можно смотреть даже не разбираясь — он почти всегда написан с полной рамкой наоборот.

Работа с SQL часто упирается не в написание самого запроса, а в то, как сохранить его понятным и удобным для дальнейших изменений. На бесплатных вебинарах в Otus будут разбираться практические приёмы, которые помогают работать со сложными запросами и эффективнее искать нужные данные в базе: от структуры SQL-кода до обработки текстовых значений. Присоединяйтесь:

• 30 сентября в 20:00. «Подзапросы или CTE — как сделать сложный запрос понятным». Записаться

• 15 октября в 20:00. «SQL против бардака в данных: поиск по шаблону и регулярные выражения». Записаться