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

Зачем tracking signal

Tracking signal (TS) — индикатор того, что forecasting-модель «сломалась» и систематически ошибается в одну сторону. Используют в supply chain, capacity, inventory: если TS выходит за ±4 — пора пересмотреть модель. Не путать с MAE/MAPE — TS отслеживает направление ошибки, не размер.

Формула

TS = (Σ (actual − prediction)) / MAD
MAD = Σ |actual − prediction| / n

Числитель — кумулятивная сумма ошибок (cumulative forecast error). Знаменатель — Mean Absolute Deviation. Если ошибки сбалансированы, числитель близок к 0 → TS ~ 0. Если модель занижает — TS растёт положительный, и наоборот.

Tracking signal в SQL

На всей backtest-выборке:

WITH errors AS (
    SELECT
        DATE,
        actual,
        prediction,
        actual - prediction AS error,
        ABS(actual - prediction) AS abs_error
    FROM forecasts_backtest
    WHERE DATE >= CURRENT_DATE - INTERVAL '90 days'
),
agg AS (
    SELECT
        SUM(error) AS cumulative_error,
        AVG(abs_error) AS mad,
        COUNT(*) AS n
    FROM errors
)
SELECT
    cumulative_error,
    mad,
    n,
    cumulative_error / NULLIF(mad, 0) AS tracking_signal,
    CASE
        WHEN ABS(cumulative_error / NULLIF(mad, 0)) > n * 0.5 THEN 'critical bias'
        WHEN ABS(cumulative_error / NULLIF(mad, 0)) > n * 0.3 THEN 'warning bias'
        ELSE 'ok'
    END AS verdict
FROM agg;

Пороги — функция от n. Классический «±4 MAD» используют для коротких рядов. Для длинных надо нормировать.

Rolling tracking signal

Окно последних 30 точек, чтобы видеть динамику:

WITH errors AS (
    SELECT
        DATE,
        actual - prediction AS error,
        ABS(actual - prediction) AS abs_error
    FROM forecasts_backtest
    WHERE DATE >= CURRENT_DATE - INTERVAL '180 days'
)
SELECT
    DATE,
    SUM(error) OVER w AS cumulative_error_30d,
    AVG(abs_error) OVER w AS mad_30d,
    SUM(error) OVER w / NULLIF(AVG(abs_error) OVER w, 0) AS tracking_signal_30d
FROM errors
WINDOW w AS (ORDER BY DATE ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
ORDER BY DATE DESC
LIMIT 60;

ROWS BETWEEN 29 PRECEDING AND CURRENT ROW — 30-дневное окно. Динамика TS говорит, когда модель начала ломаться.

Действие по пороговым

WITH ts AS (
    SELECT
        DATE,
        SUM(error) OVER w / NULLIF(AVG(abs_error) OVER w, 0) AS ts
    FROM errors
    WINDOW w AS (ORDER BY DATE ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
)
SELECT
    DATE,
    ts,
    CASE
        WHEN ABS(ts) > 6 THEN 'retrain immediately'
        WHEN ABS(ts) > 4 THEN 'investigate'
        WHEN ABS(ts) > 3 THEN 'flag for review'
        ELSE 'normal'
    END AS action
FROM ts
WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY DATE DESC;

Пороги — стандартные для inventory. Для своего домена — калибруйте через бэктест.

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

Ошибка 1. Cumulative без окна. Если суммируете ошибки с начала жизни модели — старые ошибки доминируют. Rolling window.

Ошибка 2. MAD = 0. В первые точки backtest. NULLIF лечит, но интерпретация тогда «недостаточно данных».

Ошибка 3. Игнорировать сезонность. TS на ряду с сезонностью покажет «осциллирующий» паттерн. Применяйте на residuals после сезонной декомпозиции.

Ошибка 4. Один порог для всех моделей. Stable модели — ±2, шумные — ±4. Калибруйте по бэктесту.

Ошибка 5. Реагировать на single breach. TS должен оставаться > threshold несколько подряд — иначе шум.

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

• Как посчитать forecast bias в SQL
• Как посчитать MAE в SQL
• Как посчитать MAPE в SQL
• Как посчитать anomaly detection в SQL

FAQ

Какой порог TS?

±4 — классика для inventory. ±2-±3 для стабильных моделей. Калибровка по бэктесту.

TS vs forecast bias?

Bias — текущее направление ошибок. TS — нормированный cumulative bias, чувствителен к серии ошибок в одну сторону.

Размер окна?

20–50 точек типично. Меньше — шум, больше — медленно реагирует.

Когда retrain?

TS > |6| 3 дня подряд — точно retrain. TS > |4| — investigate.

Tracking signal для бинарных прогнозов?

Нет, для непрерывных. Для бинарных — calibration plot и Brier score.