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

Зачем MAPE

Когда аналитик строит прогноз — продаж, нагрузки, заказов — нужно понимать, насколько он точен. MAPE отвечает: «в среднем мой прогноз ошибается на X% от факта». Это понятная метрика, которую можно показать продакту или CEO без объяснения, что такое RMSE.

MAPE популярен в командах прогнозирования в ритейле, логистике, телекоме: «прогноз заявок ошибается на 8% — это хороший результат для квартального планирования». В статье — формула, нюансы с нулями и братские метрики SMAPE / WAPE.

Формула MAPE

MAPE = (1/n) × Σ |actual - forecast| / |actual| × 100%

Где:

• actual — фактическое значение
• forecast — прогнозное значение
• сумма идёт по всем точкам

MAPE — это среднее по точкам, выраженное в процентах. Чем меньше — тем лучше.

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

Данные: forecasts(date, actual, forecast).

SELECT
    AVG(ABS(actual - forecast) / NULLIF(ABS(actual), 0)) * 100 AS mape_pct
FROM forecasts
WHERE actual IS NOT NULL
  AND forecast IS NOT NULL;

Важно: NULLIF(ABS(actual), 0) — защита от деления на ноль. Если в данных есть actual = 0, нужна стратегия (см. ниже).

Проблема нулевых значений

MAPE ломается, когда actual = 0 — деление невозможно. Стратегии:

1. Игнорировать нули

SELECT
    AVG(ABS(actual - forecast) / ABS(actual)) * 100 AS mape_pct
FROM forecasts
WHERE actual <> 0;

Проблема: теряем те точки, где прогноз заявил «будет ноль», а на деле что-то было.

2. Заменить ноль маленьким числом

SELECT
    AVG(ABS(actual - forecast) / GREATEST(ABS(actual), 1)) * 100 AS mape_pct
FROM forecasts;

Стабильнее, но искусственно.

3. Использовать SMAPE / WAPE

Эти метрики не падают на нулях — см. следующий раздел.

SMAPE и WAPE

SMAPE (Symmetric MAPE)

SMAPE = (1/n) × Σ |actual - forecast| / ((|actual| + |forecast|) / 2) × 100%

В знаменателе — среднее actual и forecast. Не ломается на нулях.

SELECT
    AVG(
        2 * ABS(actual - forecast)
        / NULLIF(ABS(actual) + ABS(forecast), 0)
    ) * 100 AS smape_pct
FROM forecasts;

Диапазон: 0-200%. Симметричен (одинаково штрафует за переоценку и недооценку).

WAPE (Weighted Average Percentage Error)

WAPE = Σ |actual - forecast| / Σ |actual| × 100%

Это «общая ошибка / общая фактическая величина». Эквивалент MAPE, взвешенный по размеру actual.

SELECT
    SUM(ABS(actual - forecast)) * 100.0
    / NULLIF(SUM(ABS(actual)), 0) AS wape_pct
FROM forecasts;

WAPE стабилен на нулях и устойчив к выбросам. В ритейле часто предпочитают WAPE.

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

Сравнить точность прогноза по категориям / регионам:

SELECT
    category,
    COUNT(*) AS points,
    AVG(ABS(actual - forecast) / NULLIF(ABS(actual), 0)) * 100 AS mape_pct,
    SUM(ABS(actual - forecast)) * 100.0
        / NULLIF(SUM(ABS(actual)), 0) AS wape_pct
FROM forecasts
WHERE actual <> 0
GROUP BY category
ORDER BY mape_pct DESC;

WAPE рядом с MAPE сразу покажет, где разница из-за «маленьких» точек.

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

Ошибка 1. NULL вместо 0

AVG пропускает NULL. Если есть NULL actual — они исключаются из расчёта. Решите явно: пропустить (WHERE actual IS NOT NULL) или считать как 0.

Ошибка 2. Integer division

5 / 20 = 0 в Postgres для integer типов. Используйте 5::NUMERIC / 20 или 5.0 / 20.

Ошибка 3. ABS forgotten

Без ABS ошибки разных знаков компенсируются → метрика обнуляется. actual=100, forecast=110 (+10) и actual=200, forecast=190 (-10) дадут 0% средней ошибки без ABS. С ABS — корректные 5% и 5%.

Ошибка 4. MAPE на маленьких числах

Прогноз 1 → факт 2. Ошибка 100%. Прогноз 100 → факт 101. Ошибка 1%. MAPE равно их среднему — 50,5%. На малых числах любые отклонения дают огромные проценты. Решение: WAPE, или фильтр WHERE actual > threshold.

Ошибка 5. MAPE > 100%

Когда forecast >> actual — MAPE может быть и 500%, и 1000%. Это валидный, но настораживающий сигнал. SMAPE ограничен 200%.

Ошибка 6. Игнорировать знак ошибки

MAPE / SMAPE / WAPE не различают переоценку и недооценку прогноза. Если стратегически важно (планирование запасов) — добавьте отдельную метрику смещения: AVG(forecast - actual).

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

• Accuracy vs F1 — что когда
• Time series CV и features на собесе DS
• ARIMA — прогнозирование
• Time series decomposition

FAQ

Какой MAPE считается хорошим?

В ритейле / SaaS — 10-15% обычно ok. <5% — отличный прогноз. >25% — модель плохая или данные шумные.

MAPE или RMSE — что лучше?

Разные задачи. MAPE интуитивно понятен бизнесу (проценты). RMSE — про абсолютные ошибки и наказывает большие отклонения. Часто отчёт идёт с обеими.

Почему MAPE асимметричен?

Прогноз 100 при факте 50 → ошибка 100%. Прогноз 50 при факте 100 → ошибка 50%. MAPE наказывает over-forecast сильнее. SMAPE решает эту проблему.

MAPE на forecast уровня день / неделя / месяц?

Чем агрегированнее — тем меньше MAPE (шум сглаживается). На уровне дня MAPE может быть 30%, на уровне недели — 12%, на уровне месяца — 6%. Сравнивайте только сопоставимые уровни.

SMAPE = 200% — что это значит?

Худший случай. Например, actual = 100, forecast = -100. Знаменатель (100 + 100)/2 = 100, числитель |100 - (-100)| = 200. SMAPE = 200%.