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

Зачем changepoint

Outlier — единичная аномалия. Changepoint — момент, когда сменился режим: среднее или дисперсия выросли надолго. Релиз ухудшил latency, дешёвый канал подключился, цена изменилась. Changepoint detection отвечает «когда именно сломалось», что важнее для root cause анализа.

CUSUM

CUSUM (Cumulative Sum) — простой и наглядный метод. Идея: суммируем отклонения от baseline. Если sum растёт стабильно — режим сдвинулся.

WITH base AS (
    SELECT
        event_date,
        value,
        AVG(value) OVER () AS overall_mean
    FROM daily_metric
    WHERE event_date >= CURRENT_DATE - INTERVAL '60 days'
),
cusum AS (
    SELECT
        event_date,
        value,
        SUM(value - overall_mean) OVER (ORDER BY event_date) AS cusum_score
    FROM base
)
SELECT
    event_date,
    value,
    cusum_score
FROM cusum
ORDER BY event_date;

cusum_score плавающий: если режим стабилен — близко к 0. Растёт — режим вырос; падает — упал. Точка перегиба (max или min) — кандидат на changepoint.

Mean shift по окнам

Считаем разность между средним за окно «до» и «после»:

WITH rolling AS (
    SELECT
        event_date,
        value,
        AVG(value) OVER (ORDER BY event_date ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING) AS pre_mean,
        AVG(value) OVER (ORDER BY event_date ROWS BETWEEN CURRENT ROW AND 14 FOLLOWING) AS post_mean
    FROM daily_metric
    WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    event_date,
    value,
    post_mean - pre_mean AS mean_shift,
    ABS(post_mean - pre_mean) AS shift_magnitude
FROM rolling
WHERE event_date BETWEEN CURRENT_DATE - INTERVAL '60 days' AND CURRENT_DATE - INTERVAL '14 days'
ORDER BY shift_magnitude DESC
LIMIT 10;

Топ-10 кандидатов на changepoint.

Pre/post t-test

Для подтверждения — проверяем стат-значимость shift через t-test:

WITH candidate_dates AS (
    SELECT DATE '2026-04-15' AS suspected_changepoint
),
pre AS (
    SELECT value
    FROM daily_metric, candidate_dates
    WHERE event_date BETWEEN suspected_changepoint - INTERVAL '14 days' AND suspected_changepoint - INTERVAL '1 day'
),
post AS (
    SELECT value
    FROM daily_metric, candidate_dates
    WHERE event_date BETWEEN suspected_changepoint AND suspected_changepoint + INTERVAL '14 days'
),
stats AS (
    SELECT
        (SELECT AVG(value) FROM pre) AS mean_pre,
        (SELECT AVG(value) FROM post) AS mean_post,
        (SELECT VAR_SAMP(value) FROM pre) AS var_pre,
        (SELECT VAR_SAMP(value) FROM post) AS var_post,
        (SELECT COUNT(*) FROM pre) AS n_pre,
        (SELECT COUNT(*) FROM post) AS n_post
)
SELECT
    mean_post - mean_pre AS diff,
    (mean_post - mean_pre) / NULLIF(SQRT(var_pre / n_pre + var_post / n_post), 0) AS t_stat,
    CASE
        WHEN ABS((mean_post - mean_pre) / NULLIF(SQRT(var_pre / n_pre + var_post / n_post), 0)) > 2
        THEN 'significant shift'
        ELSE 'NOT significant'
    END AS verdict
FROM stats;

Несколько changepoints

Простая heuristic: если CUSUM имеет несколько локальных максимумов/минимумов — несколько changepoints. Для строгого анализа — пакет ruptures в Python.

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

Ошибка 1. CUSUM на trend-данных. Если ряд растёт линейно, CUSUM всегда показывает «changepoint». Сначала detrend (вычитайте линейный тренд).

Ошибка 2. Pre/post окна слишком короткие. 14 дней — минимум. Меньше — шумит, false detections.

Ошибка 3. Не подтверждать changepoint t-тестом. CUSUM визуально показывает «здесь сдвиг», но без стат-теста это hypothesis. T-test confirms.

Ошибка 4. Один порог для всех метрик. Volatile метрики (revenue) — нужен |t| > 3. Стабильные (latency) — > 2.

Ошибка 5. Игнорировать seasonality. Зимой DAU выше, чем летом, не «changepoint». Сначала seasonal decomposition.

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

• Как посчитать z-score outliers в SQL
• Как посчитать anomaly detection в SQL
• Как посчитать seasonality в SQL
• Как посчитать t-test в SQL

FAQ

CUSUM или mean shift?

CUSUM для качественной картинки. Mean shift — для точечного поиска кандидата.

Окно pre/post?

7–14 дней для дневной метрики. Больше — медленнее реагирует.

Когда BCP, когда CUSUM?

Bayesian Change Point (BCP) даёт probability shift. CUSUM — простой и быстрый.

Changepoint vs outlier?

Outlier — одна точка. Changepoint — момент перелома режима, ряд после стабильно другой.

Можно ли в чистом SQL?

CUSUM и mean shift — да. Строгий MCMC BCP — нет, нужен Python.