Как посчитать exponential smoothing в SQL

Зачем exponential smoothing

Exponential smoothing (ES, EWMA) — самый простой baseline для прогноза временного ряда без тренда и сезонности. Подходит, когда нужно быстро дать «честный прогноз на завтра», не вытаскивая статистический пакет. По производительности обычно лучше moving average, потому что не игнорирует свежие данные.

Формула

S_t = α * Y_t + (1 − α) * S_{t-1}
S_1 = Y_1

α ∈ (0, 1). Большая α — больше веса свежим данным. Прогноз на t+1: Y_hat = S_t.

Recursive CTE

В Postgres удобно через WITH RECURSIVE:

WITH RECURSIVE base AS (
    SELECT
        DATE,
        value,
        ROW_NUMBER() OVER (ORDER BY DATE) AS rn
    FROM daily_metrics
    WHERE metric_name = 'dau'
),
smoothed AS (
    SELECT
        DATE,
        value,
        rn,
        value::NUMERIC AS s_t
    FROM base
    WHERE rn = 1

    UNION ALL

    SELECT
        b.DATE,
        b.value,
        b.rn,
        0.3 * b.value + 0.7 * s.s_t AS s_t
    FROM base b
    JOIN smoothed s ON b.rn = s.rn + 1
)
SELECT DATE, value, s_t AS smoothed
FROM smoothed
ORDER BY DATE;

alpha = 0.3 — стандартный старт. Меньше → ряд сглаживается сильнее, лагает.

Подбор alpha

Считаем MAE на разных α и выбираем лучший:

WITH alphas AS (
    SELECT generate_series(0.05, 0.95, 0.05)::NUMERIC AS alpha
),
forecasts AS (
    -- Для каждого α прогоняем ES и считаем MAE
    -- Здесь упрощённо: outer loop через PL/pgSQL или несколько CTE.
    -- В SQL без stored procedure обычно делают batch через несколько recursive CTE.
    SELECT alpha, AVG(ABS(actual - prediction)) AS mae
    FROM (
        SELECT 0.3 AS alpha, 100 AS actual, 95 AS prediction
        UNION ALL SELECT 0.5, 100, 90
    ) example
    GROUP BY alpha
)
SELECT *
FROM forecasts
ORDER BY mae ASC
LIMIT 1;

В практике подбор alpha делают вне SQL — Python scipy.optimize.minimize_scalar на MAE-функции.

Прогноз вперёд

ES даёт flat-прогноз на любой горизонт: Y_{T+h} = S_T для всех h.

WITH last_smoothed AS (
    SELECT s_t AS last_state
    FROM smoothed
    ORDER BY DATE DESC
    LIMIT 1
),
forecast AS (
    SELECT
        CURRENT_DATE + i AS forecast_date,
        last_state AS prediction
    FROM last_smoothed
    CROSS JOIN generate_series(1, 7) i
)
SELECT * FROM forecast;

Если в данных есть тренд — flat прогноз будет недооценивать или переоценивать. Используйте Holt's linear для trend, Holt-Winters для seasonality.

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

Ошибка 1. Большой alpha на шумных данных. α=0.9 — почти naive (вчерашнее значение). На шумах прогноз скачет.

Ошибка 2. Маленький alpha на резких сдвигах. α=0.05 — ряд лагает 20 точек. После события (релиза, сезонного скачка) ES не успевает.

Ошибка 3. Initialization через 0. S_0 = 0 искажает первые точки. Стандарт — S_1 = Y_1 или среднее первых N.

Ошибка 4. Применять к ряду с trend/seasonality. ES не моделирует ни тренд, ни сезонность. Holt's linear для тренда, Holt-Winters для сезонности.

Ошибка 5. RECURSIVE без LIMIT на длинных рядах. Postgres WITH RECURSIVE на 10000+ точек медленный. Лучше выгрузить в Python.

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

• Как посчитать moving average в SQL
• Как посчитать MAPE в SQL
• Holt-Winters на собесе DS
• Time series forecasting для аналитика

FAQ

Какой alpha по умолчанию?

0.2–0.3 для стабильных рядов, 0.4–0.5 для динамичных.

ES vs moving average?

ES даёт больше веса свежим — реагирует быстрее. MA — равные веса в окне.

Можно ли ES в Postgres без RECURSIVE?

Нет, ES — рекурсивная формула. Альтернатива — материализованная view или Python.

ES для месячных данных?

Да, но обычно не нужен — мало точек.

ES для прогноза на год?

Нет. Flat прогноз на горизонт > 7 — плохая идея. Минимум Holt-Winters.