Нарастающий итог: OVER с ORDER BY

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

ORDER BY внутри OVER и неявная RANGE-рамка: почему одинаковые даты дают скачок суммы

Как только внутри OVER появляется ORDER BY, оконная функция перестаёт смотреть на весь раздел сразу. Теперь у неё есть понятие «позиция текущей строки», и по умолчанию SQLite применяет рамку RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Слово RANGE здесь ключевое: рамка определяется не количеством строк, а значением ключа сортировки.

Что это значит на практике? Если несколько строк имеют одинаковое значение ключа — например, одну и ту же дату — все они считаются «текущими» одновременно. В рамку для каждой из них входит одинаковый набор строк, и все они получают одинаковый результат.

Разберём на конкретных данных. Пусть таблица sales содержит три строки:

CREATE TABLE sales (sale_id INTEGER PRIMARY KEY, sale_date TEXT, amount INTEGER);
INSERT INTO sales VALUES
  (1, '2024-01-01', 100),
  (2, '2024-01-01', 200),
  (3, '2024-01-02', 300);

Запрос с неявной RANGE-рамкой:

SELECT sale_id, sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date, sale_id;

Результат:

sale_id	sale_date	amount	running_total
1	2024-01-01	100	300
2	2024-01-01	200	300
3	2024-01-02	300	600

Строки 1 и 2 имеют одинаковую дату. RANGE объединяет их в одну «группу» с точки зрения рамки, и обе получают сумму 100 + 200 = 300 — скачок сразу на два шага. Строка 3 получает уже 300 + 300 = 600.

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

Явная ROWS-рамка и детерминированный ORDER BY: построчное накопление без неожиданностей

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

SUM(amount) OVER (
  ORDER BY sale_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

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

Но здесь появляется другая проблема. Если ORDER BY задан только по дате, а две строки имеют одинаковую дату, СУБД вправе выдать их в любом порядке. При одном запуске строка с amount = 100 окажется первой, при другом — строка с amount = 200. Нарастающий итог будет разным, хотя запрос не менялся.

Решение — добавить уникальный столбец вторым ключом сортировки:

SUM(amount) OVER (
  ORDER BY sale_date, sale_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

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

Важный нюанс: если добавить уникальный sale_id в ORDER BY, то и неявный RANGE перестанет давать скачки — потому что полные ключи теперь различаются, и ни одна пара строк уже не попадает в одну «RANGE-группу». То есть при уникальном ORDER BY разница между RANGE и ROWS исчезает численно. Тем не менее явная ROWS-рамка остаётся предпочтительной: она точно описывает намерение (построчное накопление), не полагается на неявные правила и читается без знания деталей RANGE-семантики.

Коротко о разнице: RANGE объединяет строки по равенству значения ключа — это логическая группировка. ROWS считает строки физически — каждая строка отдельна, вне зависимости от значений. Для нарастающего итога нужен ROWS плюс уникальный ORDER BY.

Нарастающий итог SUM и AVG внутри каждой категории: полный запрос с PARTITION BY

Соберём всё вместе на таблице с категориями:

CREATE TABLE sales (
  sale_id  INTEGER PRIMARY KEY,
  category TEXT,
  sale_date TEXT,
  amount   INTEGER
);
INSERT INTO sales VALUES
  (1, 'Книги',       '2024-01-01', 500),
  (2, 'Книги',       '2024-01-02', 300),
  (3, 'Книги',       '2024-01-03', 200),
  (4, 'Электроника', '2024-01-01', 1200),
  (5, 'Электроника', '2024-01-02', 800);

SELECT
  sale_id,
  category,
  sale_date,
  amount,
  SUM(amount)  OVER (
    PARTITION BY category
    ORDER BY sale_date, sale_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  AVG(amount)  OVER (
    PARTITION BY category
    ORDER BY sale_date, sale_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_avg
FROM sales
ORDER BY category, sale_date, sale_id;

Результат:

sale_id	category	sale_date	amount	running_total	running_avg
1	Книги	2024-01-01	500	500	500.0
2	Книги	2024-01-02	300	800	400.0
3	Книги	2024-01-03	200	1000	333.33…
4	Электроника	2024-01-01	1200	1200	1200.0
5	Электроника	2024-01-02	800	2000	1000.0

PARTITION BY category сбрасывает накопитель на каждой новой категории: строка 4 начинает свой нарастающий итог с нуля, не складываясь с книжными продажами. Внутри каждой категории ORDER BY sale_date, sale_id задаёт однозначный порядок, а ROWS-рамка гарантирует, что каждая следующая строка добавляет ровно своё amount.

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

Оба столбца — running_total и running_avg — вычисляются независимо: каждая оконная функция имеет собственное определение OVER и не влияет на другую. Число строк в результате всегда равно числу строк в исходной таблице — оконные функции не схлопывают данные.