Скользящее среднее за 7 дней: где потерялся день

Окно из семи строк захватило лишнюю дату. Три честных определения средней дадут разные числа.

SQL Studio / Разборы SQL / Скользящее среднее за 7 дней: где потерялся день

Отчёт показывает для 8 октября среднее 45,71 ₽ «за 7 дней». В данных нет 4 октября, и запрос с ROWS BETWEEN 6 PRECEDING AND CURRENT ROW незаметно захватил 1 октября — дату за пределами семи календарных суток. ROWS считает строки, а не дни. Если точно известно, что 4 октября продажи были нулевыми, построьте календарь, соедините его с дневными суммами и только затем применяйте AVG(...) OVER. Тогда на 8 октября выйдет 44,29 ₽. Если пропущенная строка означает сбой выгрузки, заменять её нулём нельзя.

Сначала определяем единицу ряда

Наша искусственная таблица содержит одну строку на наблюдаемый день, суммы в рублях. Даты с 1 по 10 октября, кроме четвёртого числа; никаких заказов или статусов у нас здесь нет, только подготовленные дневные суммы. Это важно: окно не исправит двойные даты, если одна строка изначально означает заказ, а не день.

Исходный разреженный ряд
ДатаСумма, ₽
01.1010
02.1020
03.1030
04.10нет строки
05.1050
06.1060
07.1070
08.1080
09.1090
10.10100

1. Правильный SQL, неправильная подпись на графике

Ниже нет синтаксической ошибки. Запрос действительно считает среднее за последние семь имеющихся строк. Ошибка появится, если подписать результат «семь календарных дней».

WITH sales(day, amount_rub) AS (
 VALUES ('2026-10-01',10),('2026-10-02',20),
        ('2026-10-03',30),('2026-10-05',50),
        ('2026-10-06',60),('2026-10-07',70),
        ('2026-10-08',80),('2026-10-09',90),
        ('2026-10-10',100)
)
SELECT day, amount_rub,
       ROUND(AVG(amount_rub) OVER (
         ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ),2) AS avg_last_7_rows
FROM sales ORDER BY day;
AVG с семью строками, а не днями
ДатаПродажиAVG 7 строк
2026-10-011010,00
2026-10-022015,00
2026-10-033020,00
2026-10-055027,50
2026-10-066034,00
2026-10-077040,00
2026-10-088045,71
2026-10-099057,14
2026-10-1010068,57

Для 8 октября семь строк — это 1, 2, 3, 5, 6, 7 и 8 октября: сумма 320 ₽, среднее 320 / 7 = 45,71 ₽. В календарном окне 2–8 октября нет 1 октября, зато есть 4 октября. Именно здесь расходятся определения.

2. Если пропущенный день — подтверждённый ноль

Предположим, загрузка полная и 4 октября действительно не было продаж. В SQLite рекурсивный CTE создаёт по одной дате с 1 по 10 октября. LEFT JOIN заполняет пропуск, а оконная рамка из семи строк теперь означает семь календарных дней. COUNT(*) OVER отдельно помечает неполные окна в начале истории.

WITH RECURSIVE
sales(day, amount_rub) AS (
 VALUES ('2026-10-01',10),('2026-10-02',20),
        ('2026-10-03',30),('2026-10-05',50),
        ('2026-10-06',60),('2026-10-07',70),
        ('2026-10-08',80),('2026-10-09',90),
        ('2026-10-10',100)
), calendar(day) AS (
 SELECT '2026-10-01'
 UNION ALL
 SELECT date(day,'+1 day') FROM calendar WHERE day < '2026-10-10'
), filled AS (
 SELECT c.day, COALESCE(s.amount_rub,0) AS amount_rub
 FROM calendar AS c LEFT JOIN sales AS s ON s.day=c.day
), rolling AS (
 SELECT day, amount_rub,
        COUNT(*) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS days_in_window,
        AVG(amount_rub) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_rub
 FROM filled
)
SELECT day, amount_rub, days_in_window,
       CASE WHEN days_in_window=7 THEN ROUND(avg_rub,2) END AS avg_7_calendar_days
FROM rolling ORDER BY day;
Календарь: среднее только для полного семидневного окна
ДатаСуммаДней в окнеAVG 7 дней
2026-10-01101 NULL
2026-10-02202 NULL
2026-10-03303 NULL
2026-10-0404 NULL
2026-10-05505 NULL
2026-10-06606 NULL
2026-10-0770734,29
2026-10-0880744,29
2026-10-0990754,29
2026-10-10100764,29

За 2–8 октября сумма равна 20 + 30 + 0 + 50 + 60 + 70 + 80 = 310 ₽, поэтому 310 / 7 = 44,29 ₽. До 7 октября полной семидневной истории нет: вместо маскирующего это частичного среднего показан NULL. В реальном отчёте начало наблюдения должно быть согласовано с бизнесом, а календарь построен на нужный диапазон и часовой пояс.

3. Когда пропуск неизвестен: среднее по наблюдаемым дням

Другой честный вопрос — средняя выручка по доступным наблюдаемым дням в последние семь календарных дней. В SQLite можно отсечь старые даты через числовой RANGE по julianday(day). Он не создаёт отсутствующую дату и оставляет в знаменателе только присутствующие строки.

WITH sales(day, amount_rub) AS (
 VALUES ('2026-10-01',10),('2026-10-02',20),
        ('2026-10-03',30),('2026-10-05',50),
        ('2026-10-06',60),('2026-10-07',70),
        ('2026-10-08',80),('2026-10-09',90),
        ('2026-10-10',100)
)
SELECT day, amount_rub,
       COUNT(*) OVER (
         ORDER BY julianday(day) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS observed_days,
       ROUND(AVG(amount_rub) OVER (
         ORDER BY julianday(day) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
       ),2) AS avg_observed_days
FROM sales ORDER BY day;
RANGE ограничивает календарный интервал, но не добавляет пропущенные дни
ДатаНаблюдаемых днейAVG наблюдений
2026-10-01110,00
2026-10-02215,00
2026-10-03320,00
2026-10-05427,50
2026-10-06534,00
2026-10-07640,00
2026-10-08651,67
2026-10-09663,33
2026-10-10675,00

На 8 октября в диапазоне 2–8 октября есть шесть наблюдений на 310 ₽: среднее 310 / 6 = 51,67 ₽. Все три цифры — 45,71, 44,29 и 51,67 — математически корректны, но отвечают на разные вопросы. Отдельный числовой RANGE здесь является SQLite-совместимым; синтаксис рамок по датам у СУБД различается.

Проверка для читателя

Как изменится результат на 10 октября, если 4 октября — достоверный ноль? Сравните «семь строк», «семь календарных дней» и «среднее по наблюдаемым дням в интервале». Контрольные числа: 68,57 ₽, 64,29 ₽ и 75,00 ₽ соответственно. Объясните расхождение знаменателей, а не только формулы.

Запустите запросы целиком в бесплатной песочнице SQLite. Для подготовки ряда из заказов полезно повторить группировку по дням; для нарастающего, а не скользящего итога — отдельный материал про SUM OVER.

По теме: фильтрация дат · накопительная сумма · основы окон.

Документация: SQLite: ROWS, RANGE и рамки окон · SQLite: date и julianday.

Закрепите выбор оконной рамки в задаче P110 «Скользящая средняя заказов». Там окно рассчитано на три последовательные строки месяцев; отдельно подумайте, что изменится при отсутствующем месяце.


Если нужна последовательная практика на MySQL 8, а не SQLite, есть отдельный курс SQL для аналитика на Stepik. Примеры этой статьи рассчитаны именно на SQLite бесплатного тренажёра.