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.10 | 10 |
| 02.10 | 20 |
| 03.10 | 30 |
| 04.10 | нет строки |
| 05.10 | 50 |
| 06.10 | 60 |
| 07.10 | 70 |
| 08.10 | 80 |
| 09.10 | 90 |
| 10.10 | 100 |
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 7 строк |
|---|---|---|
| 2026-10-01 | 10 | 10,00 |
| 2026-10-02 | 20 | 15,00 |
| 2026-10-03 | 30 | 20,00 |
| 2026-10-05 | 50 | 27,50 |
| 2026-10-06 | 60 | 34,00 |
| 2026-10-07 | 70 | 40,00 |
| 2026-10-08 | 80 | 45,71 |
| 2026-10-09 | 90 | 57,14 |
| 2026-10-10 | 100 | 68,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-01 | 10 | 1 | NULL |
| 2026-10-02 | 20 | 2 | NULL |
| 2026-10-03 | 30 | 3 | NULL |
| 2026-10-04 | 0 | 4 | NULL |
| 2026-10-05 | 50 | 5 | NULL |
| 2026-10-06 | 60 | 6 | NULL |
| 2026-10-07 | 70 | 7 | 34,29 |
| 2026-10-08 | 80 | 7 | 44,29 |
| 2026-10-09 | 90 | 7 | 54,29 |
| 2026-10-10 | 100 | 7 | 64,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;| Дата | Наблюдаемых дней | AVG наблюдений |
|---|---|---|
| 2026-10-01 | 1 | 10,00 |
| 2026-10-02 | 2 | 15,00 |
| 2026-10-03 | 3 | 20,00 |
| 2026-10-05 | 4 | 27,50 |
| 2026-10-06 | 5 | 34,00 |
| 2026-10-07 | 6 | 40,00 |
| 2026-10-08 | 6 | 51,67 |
| 2026-10-09 | 6 | 63,33 |
| 2026-10-10 | 6 | 75,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.
Если нужна последовательная практика на MySQL 8, а не SQLite, есть отдельный курс SQL для аналитика на Stepik. Примеры этой статьи рассчитаны именно на SQLite бесплатного тренажёра.