Скользящее среднее за 7 дней в SQL: как читать тренд без скачков

Когда пригодится подход

Как сгладить дневной шум, сохранив видимые изменения продукта?

Дневная метрика скачет из-за выходных, кампаний и небольшого объёма. Команда хочет увидеть направление, но обычное среднее по всему периоду прячет момент изменения.

DAU за понедельник нужно сравнивать с предыдущими шестью календарными днями, а не с шестью строками событий. Если в таблице нет даты без событий, окно строк даст другой смысл.

Договорённости до расчёта

До оконной функции нужна дневная витрина: одна строка = одна дата и один сегмент. Окно поверх сырых событий усреднит события, а не дневные значения.

Сначала создай календарь, затем получи одну строку на день, добавь метрику и только после этого применяй AVG OVER с ROWS или RANGE. Подпиши, является ли первая неделя неполной.

Если показатель строится по событиям, отдельно проверь повторную отправку, идентичность пользователя и часовой пояс. Если данные приходят из бизнес-системы, сопоставь техническое поле с тем, как команда реально принимает решение.

Пример с данными

DAU за понедельник нужно сравнивать с предыдущими шестью календарными днями, а не с шестью строками событий. Если в таблице нет даты без событий, окно строк даст другой смысл.

Не копируй пример в рабочий отчёт вслепую. Сначала замени названия таблиц и полей, проверь кардинальность связей и запиши, какие строки должны попасть в результат. Хорошая адаптация сохраняет логику, но делает допущения видимыми.

SELECT event_date, dau, AVG(dau) OVER (ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7d\nFROM daily_dau\nORDER BY event_date;

Ограничения метода

Главные ошибки — пропущенные даты, RANGE вместо ожидаемого ROWS, сравнение неполного окна и округление до применения среднего.

Отдельно протестируй пустой результат, NULL, дубль, граничную дату и объект без связанной записи. Эти случаи не являются редкими исключениями: именно они чаще всего превращают рабочий показатель в красивую, но неверную цифру.

• Не смешивай зрелые и незрелые периоды без отдельной подписи.
• Не заменяй проверку качества тем, что запрос просто выполнился.
• Не делай причинный вывод из одного разреза.

Сравнение на графике

График здесь нужен не для украшения статьи. Он помогает увидеть форму сигнала: тренд, разрыв между сегментами, хвост распределения или этап, на котором теряется объект. Сначала прочитай оси и единицы, затем сравни с таблицей и только после этого формулируй вывод.

На визуализации «Сырая метрика и rolling average» сравнивай не только максимальное значение. Проверь, одинаковы ли базы, не скрыта ли неполная дата и не меняется ли знаменатель от категории к категории.

Самостоятельная проверка

Собери четыре недели DAU, нарочно убери один день и сравни окно по строкам с окном по календарю. Напиши, какой график пойдёт в отчёт и почему.

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

• Собери контрольный пример из пяти-десяти строк.
• Сверь итог с независимым способом расчёта.
• Проверь хотя бы один крайний случай.
• Зафиксируй дату, версию схемы и ограничение.

Следующий шаг

Показывай сырую метрику и скользящее среднее вместе. Для алерта используй правило, которое учитывает полное окно и сезонность, а не только пересечение линии.

Сильный аналитический материал не обещает абсолютной уверенности. Он показывает, как получить число, где оно может ошибиться и какое решение можно принять уже сейчас. Такой формат хорошо переносится в дашборд, SQL-тренажёр, собеседование и рабочее обсуждение с командой.