Нарастающий итог: 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 этого не даёт: промежуточные значения пропадают, и результат выглядит как «прыжок».