Как посчитать корреляцию в SQL

Зачем это нужно

Корреляция — базовая статистическая метрика, показывающая связь двух числовых переменных. Чаще всего аналитика просят: «связаны ли revenue и session length?», «коррелирует ли frequency покупок с LTV?». Можно выгрузить данные в pandas и посчитать, но SQL позволяет делать это прямо в запросе / дашборде.

На собесе часто спрашивают: «как посчитать корреляцию в SQL»? Postgres, SQL Server и ClickHouse имеют встроенную CORR(). MySQL — нет, нужен manual расчёт. Знание обоих вариантов — middle уровень.

В статье:

• CORR() в Postgres / SQL Server / ClickHouse
• Manual формула в MySQL
• Correlation matrix для нескольких колонок
• Интерпретация (от -1 до 1)
• Когда использовать нельзя

1. CORR в Postgres / SQL Server / BigQuery

SELECT CORR(revenue, session_length) AS correlation
FROM user_stats;

Возвращает число от -1 до 1.

С фильтром

SELECT CORR(revenue, session_length) AS correlation
FROM user_stats
WHERE revenue IS NOT NULL
  AND session_length IS NOT NULL;

2. В ClickHouse

SELECT corr(revenue, session_length) FROM user_stats;

Нижний регистр. Быстрый на больших таблицах.

3. В MySQL (manual)

MySQL не имеет CORR. Считаем формулу Пирсона:

SELECT
    (n * sum_xy - sum_x * sum_y) /
    SQRT((n * sum_x2 - sum_x * sum_x) * (n * sum_y2 - sum_y * sum_y)) AS correlation
FROM (
    SELECT
        COUNT(*) AS n,
        SUM(revenue) AS sum_x,
        SUM(session_length) AS sum_y,
        SUM(revenue * session_length) AS sum_xy,
        SUM(revenue * revenue) AS sum_x2,
        SUM(session_length * session_length) AS sum_y2
    FROM user_stats
) t;

4. По сегментам

SELECT
    country,
    CORR(revenue, session_length) AS correlation
FROM user_stats
GROUP BY country
ORDER BY correlation DESC;

5. Correlation matrix (несколько пар)

SELECT
    'revenue vs sessions' AS pair,
    CORR(revenue, session_length) AS corr
UNION ALL
SELECT 'revenue vs age', CORR(revenue, age)
UNION ALL
SELECT 'sessions vs age', CORR(session_length, age);

Или через dynamic pivot (сложнее, но чище).

6. По когортам

SELECT
    DATE_TRUNC('month', signup_at) AS cohort,
    CORR(ltv, referrals_count) AS correlation
FROM user_stats
GROUP BY 1
ORDER BY 1;

Полезно — связь между метриками меняется во времени.

7. Spearman vs Pearson

CORR в SQL даёт Pearson (линейная связь).

Для Spearman (монотонная, ранговая) нужно сначала преобразовать в ранги:

WITH ranks AS (
    SELECT
        RANK() OVER (ORDER BY revenue) AS rank_x,
        RANK() OVER (ORDER BY session_length) AS rank_y
    FROM user_stats
)
SELECT CORR(rank_x, rank_y) AS spearman_correlation
FROM ranks;

8. Интерпретация

Значение	Связь
0.9 - 1.0	очень сильная положительная
0.7 - 0.9	сильная положительная
0.4 - 0.7	умеренная положительная
0.1 - 0.4	слабая положительная
-0.1 - 0.1	нет связи
-0.4 - -0.1	слабая отрицательная
-0.7 - -0.4	умеренная отрицательная
и т.д.

НО: correlation ≠ causation. Связаны ≠ одно причина другого.

9. Statistical significance

Для большого N почти любая correlation будет значима. Проверяйте p-value:

-- для N = 10000, correlation 0.02 будет p<0.05, но практически бессмысленна

Применяйте common sense.

10. Ограничения

Нелинейные связи

Pearson измеряет только линейную. Y = X² — correlation ≈ 0, но связь идеальная.

Выбросы

Один outlier может изменить correlation на 0.3-0.5. Обрабатывайте.

Small sample

На N < 30 correlation нестабильна.

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

1. Использовать CORR для категорий

CORR(gender, revenue) — не работает. Gender не число. Нужны specific тесты (t-test, chi-square).

2. Correlation = causation

Высокая correlation → надо A/B или causal analysis для причинности.

3. Single number без plots

Без scatter plot correlation обманчива. Anscombe's quartet: 4 набора с correlation 0.82, выглядящие совершенно разно.

4. MySQL без готовой функции

Можно забыть. Знайте manual формулу.

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

• Корреляция простыми словами
• Корреляция vs причинность
• Correlation vs covariance

FAQ

CORR в ClickHouse для миллиардов?

Быстрый. Специализированный engine.

Можно ли correlation в aggregated window?

Да, CORR — oconная функция (в Postgres): CORR(x, y) OVER (...).

Как экспортировать в pandas для глубокого анализа?

pd.read_sql + df.corr() для matrix.

Sign значимости?

Python: scipy.stats.pearsonr(x, y) возвращает и corr, и p-value.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.