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

Зачем Moving Average

Daily metric noisy (day-of-week effects, outliers). MA(7) сглаживает. Хорошо для trends, anomaly detection, dashboards. Без MA сложно увидеть direction.

Simple Moving Average

7-day MA:

SELECT
    DATE,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d
FROM daily_revenue
ORDER BY DATE;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW = current row + 6 предыдущих = 7 значений.

Trailing vs centered MA

Trailing (typically used):

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 7-day trailing

Centered:

ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING  -- 7-day centered

Centered чище для historic, но не для realtime (нужны future values).

Weighted Moving Average

Recent values weighted more:

WITH series AS (
    SELECT
        DATE,
        daily_revenue,
        ROW_NUMBER() OVER (ORDER BY DATE) AS rn
    FROM daily_revenue
)
SELECT
    DATE,
    daily_revenue,
    -- Weights: 1, 2, 3, 4, 5, 6, 7 (linear)
    SUM(daily_revenue * (7 - (rn_current - rn))) OVER (
        ORDER BY DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) / 28.0 AS wma_7d
FROM series;

(simplified — exact formula requires more nuance)

Exponential Moving Average

EMA — current heavily weighted, exp decay:

EMA_today = α × value_today + (1-α) × EMA_yesterday
α = 2 / (N + 1)  for «N-period EMA»

В SQL рекурсивный CTE:

WITH RECURSIVE ema AS (
    SELECT
        DATE,
        daily_revenue,
        daily_revenue::NUMERIC AS ema_value
    FROM daily_revenue
    WHERE DATE = (SELECT MIN(DATE) FROM daily_revenue)
    UNION ALL
    SELECT
        d.DATE,
        d.daily_revenue,
        0.286 * d.daily_revenue + 0.714 * e.ema_value  -- α = 2/(6+1) ≈ 0.286
    FROM daily_revenue d
    JOIN ema e ON d.DATE = e.DATE + INTERVAL '1 day'
)
SELECT * FROM ema ORDER BY DATE;

По группам

SELECT
    DATE,
    channel,
    daily_revenue,
    AVG(daily_revenue) OVER (
        PARTITION BY channel
        ORDER BY DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d
FROM daily_revenue
ORDER BY channel, DATE;

PARTITION BY — separate MA per group.

Common window sizes

• 7-day MA — week pattern smoothing (most popular)
• 28/30-day MA — month-level trend
• 3-day MA — quick smoothing, retains responsiveness
• 90-day MA — long-term trend

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

Ошибка 1. Edge of series. First 6 days в 7-day MA = avg of < 7 days. Mark с asterisk или filter.

Ошибка 2. ROWS BETWEEN N PRECEDING без CURRENT ROW. ROWS BETWEEN 6 PRECEDING — only previous 6, без current. Wrong.

Ошибка 3. Missing dates. Если date series sparse (no row для some days), MA wrong. Generate_series.

Ошибка 4. SMA vs EMA. EMA reactive to recent. SMA balanced. Pick один.

Ошибка 5. Centered MA для real-time. Centered MA cheats — uses future. Trailing only для live dashboards.

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

• Window functions advanced
• Cumulative distinct на собесе DE
• Как посчитать anomaly detection в SQL
• Как посчитать standard deviation в SQL

FAQ

Какой window?

7d для weekly noise. 28d для month-level trend.

SMA vs WMA vs EMA?

SMA — equal weight, simple. WMA — linearly decay. EMA — exponential decay, most reactive.

ROWS vs RANGE?

ROWS — N rows. RANGE — N units of order column (e.g. 7 days). RANGE handles gaps better.

Generate_series для missing dates?

Yes. LEFT JOIN daily data on series → MA correct.

Latency-aware MA?

For real-time dashboards: trailing only. Centered MA только для historical analytics.