Как посчитать forecast bias в SQL

Зачем forecast bias

MAE и RMSE показывают «насколько ошиблись», но не «в какую сторону». Forecast bias — направление систематической ошибки. Если bias = +5%, прогноз постоянно завышен на 5% — модель неисправна, и MAE не подскажет. В supply chain, finance, capacity planning bias важнее точности.

Mean error

ME = Σ(actual − prediction) / n. Положительный = модель занижает, отрицательный = завышает.

SELECT
    AVG(actual - prediction) AS mean_error,
    SUM(actual - prediction) AS total_error,
    COUNT(*) AS n,
    CASE
        WHEN AVG(actual - prediction) > 0 THEN 'underforecasting'
        WHEN AVG(actual - prediction) < 0 THEN 'overforecasting'
        ELSE 'balanced'
    END AS direction
FROM forecasts_backtest
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days';

mean_error != 0 стат-значимо — bias есть. Точечная оценка может быть и шумом, нужен CI.

Percentage bias

Нормированный bias: MPE = AVG((actual - prediction) / actual). Зависит от масштаба исходного ряда.

SELECT
    AVG((actual - prediction) / NULLIF(actual, 0)) * 100 AS mpe_pct,
    AVG((actual - prediction) / NULLIF((actual + prediction) / 2.0, 0)) * 100 AS smape_signed_pct
FROM forecasts_backtest
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
  AND actual > 0;

actual > 0 — иначе деление на 0 при пустых днях.

Bias по сегментам

Где bias больше — географии, продукт-категории, типы юзеров:

SELECT
    segment,
    COUNT(*) AS n,
    AVG(actual - prediction) AS mean_error,
    AVG((actual - prediction) / NULLIF(actual, 0)) * 100 AS mpe_pct,
    CASE
        WHEN ABS(AVG((actual - prediction) / NULLIF(actual, 0))) > 0.05 THEN 'biased'
        ELSE 'ok'
    END AS verdict
FROM forecasts_backtest
WHERE DATE >= CURRENT_DATE - INTERVAL '90 days'
  AND actual > 0
GROUP BY segment
HAVING COUNT(*) >= 30
ORDER BY ABS(AVG(actual - prediction)) DESC;

Если в сегменте iOS MPE = +12%, а в Android ≈ 0% — модель «не понимает» iOS. Откатывайтесь и переобучайте.

Bias во времени

SELECT
    DATE_TRUNC('week', DATE)::DATE AS week,
    AVG(actual - prediction) AS mean_error,
    COUNT(*) AS n
FROM forecasts_backtest
WHERE DATE >= CURRENT_DATE - INTERVAL '180 days'
GROUP BY DATE_TRUNC('week', DATE)
ORDER BY week;

Если bias меняет знак — модель неустойчива. Если bias монотонно растёт — модель «устаревает».

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

Ошибка 1. Считать только MAE. Модель может иметь low MAE и high bias (например, MAE=10, ME=8). Это значит, что 80% ошибок в одну сторону — нужна коррекция.

Ошибка 2. Bias = 0 как «всё ок». Может быть «бывают равные плюсы и минусы, но огромные» — MAE плохой. Bias смотрите вместе с MAE.

Ошибка 3. MPE на actual = 0. Деление на 0. SMAPE (symmetric) лечит, но имеет свои проблемы (max 200%).

Ошибка 4. Не сегментировать. Bias overall = 0, но iOS +10%, Android -10% — взаимная компенсация. По сегменту видно явно.

Ошибка 5. Считать bias на in-sample. Bias на train неинформативен — модели обычно балансируются обучением. Только out-of-sample backtest.

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

• Как посчитать MAPE в SQL
• Как посчитать MAE в SQL
• Как посчитать RMSE в SQL
• Как посчитать tracking signal в SQL

FAQ

Какой bias допустим?

В business context: < 2% по absolute MPE — отлично. 5%+ — повод копать.

Bias 0%, MAE большая — что значит?

Модель шумит, но без направления. Ошибки в обе стороны компенсируются.

Bias положительный — это плохо?

Зависит от метрики. Inventory: положительный (underforecast) → stockouts. Capacity: положительный → перегрузка.

Как чинить bias?

Bias-correction layer поверх модели или пере-калибровка quantile.

Bias и tracking signal — это одно?

Tracking signal — нормализованный bias, отслеживается во времени. Это metric for monitoring.