Агрегаты-окна: нарастающий итог и скользящее среднее

Агрегаты с ORDER BY дают нарастающий итог, а рамка ROWS BETWEEN — скользящее среднее.

Добавив ORDER BY в окно агрегата, вы получаете нарастающий итог (сумма от начала до текущей строки). Рамка ROWS BETWEEN ... AND ... ограничивает окно фиксированным числом соседних строк — так строят скользящее среднее.

Нарастающий итог (running total)

Когда у оконного SUM появляется ORDER BY, окно неявно становится «все строки от начала до текущей». Сумма накапливается.

CREATE TABLE tx(day INTEGER, amount INTEGER);
INSERT INTO tx VALUES (1,50),(2,30),(3,80),(4,20);

SELECT day, amount,
       SUM(amount)   OVER (ORDER BY day) AS running_total,
       COUNT(*)      OVER (ORDER BY day) AS days_so_far
FROM tx;

Вывод:

day  amount  running_total  days_so_far
---  ------  -------------  -----------
1    50      50             1
2    30      80             2
3    80      160            3
4    20      180            4

running_total растёт: 50 → 80 → 160 → 180. Это и есть накопительный баланс. COUNT(*) с тем же окном показывает, сколько строк уже «накоплено».

Скользящее среднее: рамка ROWS BETWEEN

По умолчанию окно с ORDER BY тянется «от начала до текущей строки». Чтобы взять только последние N строк, задают рамку явно. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — это текущая строка плюс две предыдущие, то есть окно из 3 строк.

CREATE TABLE t(day INTEGER, val INTEGER);
INSERT INTO t VALUES (1,10),(2,20),(3,30),(4,40),(5,50);

SELECT day, val,
       AVG(val) OVER (ORDER BY day
                      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM t;

Вывод:

day  val  moving_avg
---  ---  ----------
1    10   10.0
2    20   15.0
3    30   20.0
4    40   30.0
5    50   40.0

Считаем по шагам. День 1: в окне только сам он → среднее 10. День 2: строки {10,20} → 15. День 3: {10,20,30} → 20. День 4: окно «съезжает», берёт {20,30,40} → 30. День 5: {30,40,50} → 40. Окно из трёх строк скользит вдоль данных — отсюда «скользящее среднее», классический способ сгладить шум во временном ряде.

Виды рамок

Рамка	Что входит в окно
`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`	текущая и 2 предыдущие (скользящее окно из 3)
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	от начала до текущей (нарастающий итог — это поведение по умолчанию при наличии ORDER BY)
`ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`	от текущей до конца

Тонкость: ORDER BY без рамки

Запомните правило по умолчанию: агрегатная оконная функция с ORDER BY, но без явной рамки, ведёт себя как нарастающий итог (UNBOUNDED PRECEDING ... CURRENT ROW). Если же ORDER BY вообще нет, агрегат считается по всему окну сразу (как в первом уроке с OVER ()). Понимание этого избавит от загадочных результатов.

Итог

SUM(...) OVER (ORDER BY ...) без рамки = нарастающий итог.
ROWS BETWEEN N PRECEDING AND CURRENT ROW = скользящее окно из N+1 строк (скользящее среднее).
Нет ORDER BY → агрегат по всему окну; есть ORDER BY, но нет рамки → накопление от начала.