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

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

Корреляция — базовая статистическая метрика, показывающая связь двух числовых переменных. Аналитику часто задают вопросы в духе «связаны ли выручка и длительность сессии?», «коррелирует ли частота покупок с LTV?». В Postgres, ClickHouse, Snowflake и BigQuery есть встроенная функция CORR, в MySQL — нет, придётся считать вручную.

В статье:

• CORR в Postgres / SQL Server / BigQuery / ClickHouse;
• ручная формула Пирсона для MySQL;
• матрица корреляций для нескольких переменных;
• корреляция по сегментам и когортам;
• Spearman-корреляция через ранги;
• интерпретация и ограничения.

1. CORR в Postgres / SQL Server / BigQuery

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

Возвращает число в диапазоне от −1 до 1. NULL-значения игнорируются в паре: если хотя бы одно из двух полей NULL, строка не учитывается.

2. CORR в ClickHouse

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

ClickHouse использует нижний регистр и специализированную реализацию, которая хорошо работает на миллиардах строк.

3. Ручная формула в MySQL

В MySQL нет CORR, поэтому считаем формулу Пирсона руками:

SELECT
    (n * sum_xy - sum_x * sum_y)
    / NULLIF(
        SQRT((n * sum_x2 - sum_x * sum_x)
           * (n * sum_y2 - sum_y * sum_y)),
        0
      ) 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
    WHERE revenue IS NOT NULL
      AND session_length IS NOT NULL
) t;

Запрос заметно длиннее, но работает в любой СУБД. Полезно знать хотя бы структуру формулы: сумма, сумма произведений, сумма квадратов.

4. Корреляция по сегментам

SELECT
    country,
    CORR(revenue, session_length) AS correlation,
    COUNT(*)                       AS n
FROM user_stats
GROUP BY country
HAVING COUNT(*) >= 100      -- чтобы не считать на маленьких группах
ORDER BY correlation DESC;

Часто средняя корреляция по всему датасету скрывает разную картину в сегментах — смотрите корреляции на уровне стран, платформ, сегментов пользователей.

5. Матрица корреляций

В SQL нет готового «corr matrix», но можно собрать её руками:

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

Для полноценной матрицы проще экспортировать данные в pandas — df.corr() построит её одной строкой.

6. Корреляция по когортам

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

Когортный разрез помогает заметить, что связь между метриками меняется во времени — например, когда продукт меняется или приходит новая аудитория.

7. Spearman через ранги

CORR даёт корреляцию Пирсона — чувствительна к выбросам и измеряет именно линейную связь. Для Спирмена сначала превращаем значения в ранги:

WITH ranked 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 ranked;

Корреляция Спирмена устойчивее к выбросам и улавливает любую монотонную связь, не только линейную.

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

Знак показывает направление — плюс означает «обе метрики растут вместе», минус — «одна растёт, другая падает».

Несколько важных оговорок:

• Корреляция не означает причинность. Высокая корреляция — повод запустить A/B-тест или причинный анализ, но сама по себе она причиной не является.
• На большой выборке статистически значимой становится практически любая связь. Поэтому всегда стоит смотреть на размер эффекта, а не только на p-value.
• Корреляция не видит нелинейных связей. Y = X² даст корреляцию около нуля при идеальной зависимости.
• Выбросы сильно смещают Пирсона. В таких случаях берите Спирмена или предварительно чистите выбросы.

Типичные ошибки

1. Применять CORR к категориальным полям

-- не сработает: gender не число
CORR(gender, revenue)

Для категориальных переменных нужны другие инструменты: t-тест, χ², ANOVA.

2. Вывод без графика

Одно число без scatter plot легко обмануть. Показательный пример — Anscombe's quartet: четыре совершенно разных набора данных с одинаковой корреляцией около 0,82. В отчёт лучше добавлять хотя бы один scatter с аннотацией.

3. Корреляция вместо причинности

«Пользователи, пишущие комментарии, имеют LTV выше» не значит «заставим всех комментировать — вырастет LTV». Скорее всего, вовлечённость и LTV имеют общую причину.

4. Корреляция на маленькой выборке

При n < 30 корреляция неустойчива — небольшой шум сильно её двигает. Либо набирайте данные, либо используйте bootstrap для оценки доверительного интервала.

5. Забыть про NULL в ручной формуле

Встроенный CORR сам исключает строки с NULL, а в ручной формуле это нужно прописать в WHERE.

Корреляция в pandas

import pandas as pd
from scipy.stats import pearsonr, spearmanr

df = pd.read_sql('SELECT revenue, session_length FROM user_stats', conn)

r_pearson,  p_pearson  = pearsonr(df['revenue'], df['session_length'])
r_spearman, p_spearman = spearmanr(df['revenue'], df['session_length'])

print(df.corr())  # матрица корреляций по всему датафрейму

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

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

FAQ

CORR поддерживается везде?

Встроенно — в Postgres, SQL Server, Oracle, BigQuery, Snowflake, ClickHouse. В MySQL пока нет.

Можно ли считать корреляцию в окне?

В Postgres CORR — агрегат и может использоваться как оконная функция: CORR(x, y) OVER (...). В других СУБД возможности могут отличаться.

Нужно ли проверять значимость?

Для принятия решений — да. Проще всего — через pandas/scipy: pearsonr или spearmanr вернёт и коэффициент, и p-value.

Когда использовать Спирмена?

Когда есть выбросы, распределения скошены или связь предположительно нелинейная, но монотонная. Во всех остальных случаях Пирсон даёт более острый сигнал.