ROWS и RANGE в SQL: почему накопительный итог прыгает на одинаковой дате

Бухгалтер открывает список платежей с накопительной суммой. После первого платежа 2 июня итог сразу увеличивается на 100, хотя сама строка содержит 40. Второй платёж за тот же день содержит 60 и показывает тот же итог. Сумма правильная для конца дня, но неправильная для строки отдельного платежа. Причина в рамке оконной функции.

Оконная сумма считает строки в определённой рамке. В SQLite у агрегата с ORDER BY paid_on рамка по умолчанию эквивалентна RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. При одинаковой дате обе строки считаются равными по ключу сортировки, или peers. Рамка для каждой из них включает сразу всю группу этой даты. Именно поэтому итог меняется ступенькой.

Два разумных ответа на один журнал

Для отчёта «сколько поступило к концу каждого дня» ступенька полезна. Для журнала «какой баланс после конкретного платежа» нужна отдельная позиция каждой строки. Здесь используют ROWS и детерминированный порядок: после даты добавляют уникальный payment_id. Одна только дата при ROWS оставила бы порядок двух платежей 2 июня неопределённым.

CREATE TABLE payments (payment_id INTEGER, paid_on TEXT, amount INTEGER);
INSERT INTO payments VALUES
  (1, '2025-06-01', 100),
  (2, '2025-06-02', 40),
  (3, '2025-06-02', 60),
  (4, '2025-06-03', 30);
SELECT payment_id, paid_on, amount,
       SUM(amount) OVER (
         ORDER BY paid_on
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS by_date,
       SUM(amount) OVER (
         ORDER BY paid_on, payment_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS by_payment
FROM payments
ORDER BY paid_on, payment_id;

Запрос вернёт по платежам 1–4 пары итогов 100/100, 200/140, 200/200, 230/230, где слева by_date, справа by_payment. У платежа 2 дневной итог уже 200, потому что в него вошёл платёж 3. Построчный итог равен 140 и становится 200 лишь на следующей строке.

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

Не путать размер рамки с временным расстоянием

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW возьмёт три строки, даже если между их датами прошёл месяц. RANGE с числовым выражением может задать диапазон значений ключа, но для дат в SQLite их надо преобразовать в подходящее числовое представление. При обычном накопительном итоге, как здесь, различие связано прежде всего с равными ключами и тем, включает ли рамка всю их группу.

При проверке своего отчёта возьмите дату с двумя операциями и вручную подпишите итог после каждой. Сравните эти четыре числа с запросом. Если результат нужен для контроля остатка после транзакции, используйте уникальный порядок операций; если для дневного графика, лучше явно сгруппировать платежи по дню и считать накопление по дням.

Бывает и третий вопрос: показать сумму всех платежей в пределах последних семи календарных дней для каждой операции. Здесь ROWS 6 PRECEDING не подойдёт: семь строк могут уместиться в один день или растянуться на месяц. Нужно задать рамку по числовой дате либо заранее построить дневной ряд. Название «скользящая неделя» само по себе не определяет, считаем мы события или календарное время.

Обратите внимание на внешний ORDER BY в конце запроса. Он управляет отображением результата, а порядок внутри OVER (...) управляет составом рамки. Один не заменяет другой. Запрос с верно вычисленным балансом, но без финальной сортировки может показать строки в неожиданном порядке; запрос с красивой сортировкой снаружи, но ошибочным ключом окна посчитает неверные суммы.

При переходе на другую СУБД проверьте её правила рамки по умолчанию и поведение RANGE для типа даты. Явная запись рамки в запросе помогает следующему читателю увидеть намерение: нужна группа равных дат или строгая последовательность операций.

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

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