Скользящее среднее в SQL: rolling-окна и тренды

Через оконную функцию с рамкой по строкам: AVG(revenue) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Это среднее по текущей строке и шести предыдущим — итого 7 точек. Важно, что данные должны быть по одной строке на день без пропусков, иначе «6 PRECEDING» захватит не 6 календарных дней, а 6 строк, которые могут покрывать больший интервал. Если пропуски есть, сначала достраивают календарь через generate_series и LEFT JOIN.

ROWS BETWEEN N PRECEDING считает физические строки: ровно N предыдущих строк, независимо от значений. RANGE BETWEEN считает по значению колонки из ORDER BY: например, RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW возьмёт все строки, чья дата попадает в окно 7 календарных дней — это устойчиво к пропущенным датам и к нескольким записям в один день. На собесе именно этот нюанс любят: ROWS удобен на плотных рядах, RANGE — когда даты рваные или есть дубли.

Два пути. Первый — использовать RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW, тогда окно определяется по фактическим датам и пропуски не мешают. Второй — достроить непрерывный календарь: generate_series(min_date, max_date, interval '1 day') AS d, LEFT JOIN с данными, COALESCE(orders, 0), и уже поверх плотного ряда применить ROWS BETWEEN 29 PRECEDING. Второй способ надёжнее, если нужно, чтобы дни без данных считались нулями, а не выпадали из окна.

По умолчанию в начале ряда окно неполное: на второй день среднее берётся всего по 2 точкам. Чтобы отсекать неполные окна, добавляют условие через COUNT: CASE WHEN COUNT(*) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) = 7 THEN AVG(...) OVER (...) END. Тогда для первых шести дней вернётся NULL, и на графике сглаженная линия стартует только там, где окно набралось полностью — это ровно то, чего ждут в аккуратном дашборде.

Сырая дневная метрика сильно шумит и содержит недельную сезонность: по выходным заказов меньше, в понедельник всплеск. 7-дневное скользящее убирает эффект дня недели, потому что окно всегда покрывает полную неделю, а 28-дневное сглаживает ещё и месячные колебания. На собеседовании часто спрашивают именно «почему 7 дней, а не 5» — правильный ответ про кратность недельному циклу. Rolling-метрики — стандарт для трендов retention, выручки и DAU в продуктовой аналитике.