Как посчитать cross-correlation в SQL

Зачем CCF

Cross-correlation function (CCF) — корреляция между двумя рядами X и Y с лагом. Используется, чтобы найти задержку, на которой X лучше всего предсказывает Y. Маркетинговые расходы → конверсии через 3 дня. Запросы саппорта → churn через 7 дней. CCF помечает причинно-временные связи.

Формула

CCF(k) = corr(X_t, Y_{t-k})  для разных k

Положительный k → Y отстаёт от X. Отрицательный → X отстаёт от Y.

CCF в SQL

WITH base AS (
    SELECT
        event_date,
        marketing_spend AS x,
        signups AS y,
        LAG(signups, 0) OVER (ORDER BY event_date) AS y_lag_0,
        LAG(signups, 1) OVER (ORDER BY event_date) AS y_lag_1,
        LAG(signups, 3) OVER (ORDER BY event_date) AS y_lag_3,
        LAG(signups, 7) OVER (ORDER BY event_date) AS y_lag_7,
        LAG(signups, 14) OVER (ORDER BY event_date) AS y_lag_14
    FROM daily_marketing
)
SELECT
    CORR(x, y_lag_0)  AS ccf_0,
    CORR(x, y_lag_1)  AS ccf_1,
    CORR(x, y_lag_3)  AS ccf_3,
    CORR(x, y_lag_7)  AS ccf_7,
    CORR(x, y_lag_14) AS ccf_14
FROM base;

Если ccf_3 максимальная — marketing spend сегодня даёт signups через 3 дня. Это и есть «эффект задержки».

Поиск optimal lag

Развёрнуто по lag k = −14..14:

WITH base AS (
    SELECT event_date, marketing_spend AS x, signups AS y FROM daily_marketing
),
lags AS (
    SELECT
        k AS lag,
        CORR(b1.x, b2.y) AS ccf
    FROM (VALUES (-14),(-7),(-3),(-1),(0),(1),(3),(7),(14)) AS t(k)
    CROSS JOIN base b1
    JOIN base b2 ON b2.event_date = b1.event_date + (k * INTERVAL '1 day')
    GROUP BY k
)
SELECT lag, ccf
FROM lags
ORDER BY ABS(ccf) DESC;

Топ-1 lag — кандидат на «задержку эффекта».

Lead и lag

lag k > 0: x_t предсказывает y_{t+k} — y "лагирует" за x
lag k < 0: y_t предсказывает x_{t-k} — x "лагирует" за y

В маркетинге: расходы → конверсии — lag > 0. В DA: support tickets → churn — lag > 0. В обратную сторону — что-то странное.

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

Ошибка 1. CCF на trended рядах. Оба ряда с трендом дадут спurious correlation на всех лагах. Detrend оба.

Ошибка 2. Spurious correlation. Marketing spend и signups оба растут осенью — CCF покажет высокую корреляцию без causal link. Holiday/seasonal control.

Ошибка 3. Outliers. Один Black Friday может перекосить CCF.

Ошибка 4. Считать на разных частотах. Daily marketing и weekly signups — несовместимы. Aggregate до одной частоты.

Ошибка 5. CCF как causal proof. Максимальный |CCF| указывает на корреляцию с лагом, не причину. Granger test или DiD для causation.

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

• Как посчитать autocorrelation в SQL
• Как посчитать correlation в SQL
• Как посчитать multi-touch attribution в SQL
• Time series forecasting для аналитика

FAQ

CCF и Granger causality?

Granger — формальный тест: «X предсказывает Y лучше своей истории». CCF — descriptive.

Какой lag искать?

В зависимости от домена. Marketing → conv: дни. Tickets → churn: недели.

Confidence band CCF?

±2/√n как для ACF.

CCF для категориальных?

Нужно бинарное / numerical encoding. Прямой CCF не работает.

Что если CCF максимальная на lag=0?

Просто корреляция, не «задержанный эффект». X и Y движутся вместе.