Скользящее среднее в SQL: как считать три календарных дня при пропусках

На графике стоит подпись «средняя выручка за три дня». После дня без продаж линия неожиданно изменилась: запрос взял текущую запись и две предыдущие строки. Но строка за пропущенный день отсутствует, поэтому третьей оказалась продажа четырёхдневной давности. Оконная рамка ROWS BETWEEN 2 PRECEDING AND CURRENT ROW считает позиции в отсортированном наборе, а не календарные сутки.

Сначала решите, что означает пустой день

Если магазин работал, данные загружены полностью и продаж не было, сумма за этот день равна нулю. Если загрузка ещё не завершилась, нуль подменит неизвестное значение и исказит график. Этот выбор делают до SQL-вычисления. В примере ниже отсутствие строки 2 октября означает подтверждённый ноль.

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

Четыре дня, четыре заказа

1 октября получено 30, 2 октября заказов нет, 3 октября два заказа на 40 и 20, 4 октября — 90. Календарь создаётся рекурсивно. COALESCE превращает отсутствие строки в нуль только потому, что мы уже установили смысл пропуска:

WITH RECURSIVE calendar(d) AS (
  VALUES ('2026-10-01')
  UNION ALL
  SELECT date(d, '+1 day') FROM calendar WHERE d < '2026-10-04'
), sales(order_id, sold_on, amount) AS (
  VALUES (1, '2026-10-01', 30),
         (2, '2026-10-03', 40),
         (3, '2026-10-03', 20),
         (4, '2026-10-04', 90)
), daily_sales AS (
  SELECT sold_on, SUM(amount) AS total FROM sales GROUP BY sold_on
), daily AS (
  SELECT c.d, COALESCE(s.total, 0) AS revenue
  FROM calendar AS c LEFT JOIN daily_sales AS s ON s.sold_on = c.d
)
SELECT d, revenue,
       COUNT(*) OVER w AS days_in_frame,
       CASE WHEN COUNT(*) OVER w = 3
            THEN AVG(revenue) OVER w END AS avg_3_days
FROM daily
WINDOW w AS (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY d;

Запрос возвращает дневные суммы 30, 0, 60, 90. Полное среднее за три дня появляется 3 октября: (30 + 0 + 60) / 3 = 30. На 4 октября оно равно (0 + 60 + 90) / 3 = 50. Для 1 и 2 октября avg_3_days остаётся NULL, потому что в выбранном ряду пока нет трёх дней. Колонка days_in_frame показывает, сколько строк реально вошло в рамку.

Почему календарь надо построить до окна

Если просто отсортировать даты из таблицы продаж, на 3 октября предыдущей записью будет 1 октября; при более длинном ряду рамка захватит ещё более раннюю дату. После присоединения календаря каждой строке соответствует один соседний день. Теперь ROWS становится удобным способом посчитать именно календарное окно. При нескольких магазинах сначала создайте календарь для каждого магазина и добавьте PARTITION BY store_id, чтобы окно одного магазина не подхватило продажи другого.

Для отчёта, который показывается с 1 октября, но должен сразу иметь полное трёхдневное среднее, календарь и продажи надо начать с 29 сентября, а отфильтровать отображаемые даты уже после вычисления окна. Иначе две предыдущие строки будут удалены до расчёта. Если метрика относится к рабочим дням, нужен календарь рабочих дней: замена числа в ROWS не изменит смысл пропуска.

Проверяйте результат на датах вокруг пробела в данных. Для каждой такой даты рядом держите дневную сумму, число дней в рамке и само среднее. Оконная рамка SQLite определяет, какие строки видит AVG; проверка полноты загрузки определяет, можно ли нулём заполнить отсутствующую дату.

Практика после одного окна

В платном курсе «Продвинутый SQL» вы построите календарь с пропущенными датами, посчитаете скользящие показатели и разберёте, чем рамки ROWS и RANGE меняют результат. Эти приёмы затем применяются к когортам, воронкам и аналитической витрине интернет-магазина. Откройте урок «Оконные рамки ROWS» и посмотрите программу курса.

Другие ошибки расчётов разобраны в серии аналитического SQL; при работе с последовательностью событий пригодится разбор воронки.