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

Зачем ACF

Autocorrelation function (ACF) — корреляция ряда с самим собой со сдвигом на k шагов. Помогает увидеть:

• Сезонность: ACF(7) высокая для дневных данных с недельным паттерном.
• Тренд: ACF медленно затухает (вместо быстрого спада).
• Random walk vs stationary process.

ACF — first thing для time series. До любого forecast.

Формула

ACF(k) = corr(y_t, y_{t-k})
       = Σ (y_t − mean) × (y_{t-k} − mean) / Σ (y_t − mean)²

Range: [-1, 1]. 1 — серия точно повторяет себя со сдвигом k.

ACF для одного лага

В Postgres через LAG:

WITH lagged AS (
    SELECT
        event_date,
        value AS y,
        LAG(value, 7) OVER (ORDER BY event_date) AS y_lag_7
    FROM daily_metrics
    WHERE metric_name = 'dau'
)
SELECT
    CORR(y, y_lag_7) AS acf_7
FROM lagged
WHERE y_lag_7 IS NOT NULL;

CORR(...) встроенный в Postgres. Если ACF(7) высокая — недельная сезонность.

ACF-таблица

Считаем для лагов 1..21:

WITH base AS (
    SELECT event_date, value AS y FROM daily_metrics WHERE metric_name = 'dau'
),
lags AS (
    SELECT event_date, y,
        LAG(y, 1)  OVER (ORDER BY event_date) AS y1,
        LAG(y, 7)  OVER (ORDER BY event_date) AS y7,
        LAG(y, 14) OVER (ORDER BY event_date) AS y14,
        LAG(y, 21) OVER (ORDER BY event_date) AS y21,
        LAG(y, 30) OVER (ORDER BY event_date) AS y30
    FROM base
)
SELECT
    CORR(y, y1)  AS acf_1,
    CORR(y, y7)  AS acf_7,
    CORR(y, y14) AS acf_14,
    CORR(y, y21) AS acf_21,
    CORR(y, y30) AS acf_30
FROM lags;

В дашборде строится как столбчатая диаграмма от lag = 1 до lag = 30.

Что показывает ACF

• ACF(1) высокая (0.9+): сильная memory, ряд медленно меняется (revenue, DAU).
• ACF медленно затухает: trend в данных. Detrend перед forecasting.
• ACF(7) спайк для дневных данных: недельная сезонность.
• ACF(12) спайк для месячных: годовая сезонность.
• ACF на random series: около 0 для всех лагов.

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

Ошибка 1. Считать ACF на коротком ряду. < 50 точек — ACF шумит. Минимум 50, желательно 100+.

Ошибка 2. Trend не убрали. ACF on trended series почти всегда высокая — это эффект тренда, не структуры. Сначала detrend.

Ошибка 3. NULL в лагах. LAG в первых k точках даёт NULL. CORR игнорирует, но COUNT(*) уменьшится. Считайте n.

Ошибка 4. Сравнивать ACF разной длины рядов. ACF на 100 точках имеет другой confidence band, чем на 1000.

Ошибка 5. Игнорировать confidence band. ACF(k) считается значимым, если |ACF| > 2/√n. Иначе это шум.

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

• Как посчитать correlation в SQL
• Как посчитать seasonality в SQL
• Как посчитать partial autocorrelation в SQL
• Time series forecasting для аналитика

FAQ

Какой ACF значим?

|ACF| > 2/√n (примерно 0.2 для n=100). Меньше — шум.

ACF vs PACF?

ACF — total correlation с лагом. PACF — после удаления промежуточных лагов.

ACF на ratio-метриках?

Можно, но сначала log-transform для стабильности variance.

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

Нет, LAG нужен. Альтернатива — self-join с offset, но медленнее.

Какие лаги смотреть?

1, 7, 14, 21, 30 для дневной метрики. 1, 6, 12 для месячной.