Как посчитать Rolling Average в SQL

Зачем Rolling Average

DAU в среду 95K, в субботу 60K. Это weekly seasonality. Без smoothing график волатильный. Rolling 7d average убирает day-of-week noise → видно trend.

Формула

Rolling 7-day avg(t) = AVG(value) for t in [t-6 .. t]

Базовый расчёт 7d

SELECT
    DATE,
    value,
    AVG(value) OVER (
        ORDER BY DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM daily_metrics
ORDER BY DATE;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — окно 7 строк включая текущую.

Rolling 30d

SELECT
    DATE,
    value,
    AVG(value) OVER (
        ORDER BY DATE
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS rolling_30d_avg
FROM daily_metrics
ORDER BY DATE;

Exponential smoothing

EMA даёт more weight to recent data:

WITH RECURSIVE ema_calc AS (
    SELECT
        DATE,
        value,
        value::NUMERIC AS ema,
        ROW_NUMBER() OVER (ORDER BY DATE) AS rn
    FROM daily_metrics
    ORDER BY DATE
    LIMIT 1
    UNION ALL
    SELECT
        d.DATE,
        d.value,
        0.3 * d.value + 0.7 * e.ema AS ema,
        e.rn + 1
    FROM ema_calc e
    JOIN daily_metrics d ON ROW_NUMBER() OVER (ORDER BY d.DATE) = e.rn + 1
)
SELECT * FROM ema_calc;

α = 0.3 — smoothing factor. Higher = более reactive to changes.

В SQL exponential smoothing сложно — обычно делается в Python/Pandas.

Частые ошибки

Ошибка 1. ROWS vs RANGE. ROWS BETWEEN 6 PRECEDING — exactly 7 rows. RANGE BETWEEN 6 PRECEDING — 6 calendar days (works only when dates contiguous).

Ошибка 2. Holes in dates. Если в данных нет 12 января (нет events) — окно скользит по 6 предыдущим существующим, не 6 calendar days. Use generate_series для density.

Ошибка 3. Centered vs trailing. ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING — centered 7-day. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — trailing.

Ошибка 4. Не учитывать window edge. Первые 6 дней rolling 7d — based on фewer points → less reliable.

Ошибка 5. Smoothing хides actual numbers. Используйте rolling для trends, raw для актуального состояния.

Связанные темы

• Как посчитать DAU в SQL
• Как посчитать накопительный итог в SQL
• LAG и LEAD в SQL
• Оконные функции в SQL — шпаргалка

FAQ

7d или 30d rolling?

7d — short-term trends. 30d — medium-term. Зависит от частоты данных.

Trailing или centered?

Trailing — для real-time dashboards. Centered — для historical analysis.

Rolling average vs Moving average?

Синонимы. Иногда «rolling» = «moving».

Когда EMA лучше?

Когда recent data matters больше. Например, alerting on spike.

Rolling average для weekend?

Без normalization — Sunday rolling 7d includes 1 weekend = норма. По дням недели — отдельный график.