CORR in SQL: Pearson Correlation as a Single Aggregate

Вопрос «а связаны ли вообще эти две колонки» возникает в аналитике постоянно: растёт ли средний чек с возрастом аккаунта, тянет ли скидка за собой выручку, идут ли рука об руку трафик и заказы. Можно выгрузить данные в Python и рисовать точечный график, а можно спросить базу напрямую. CORR(y, x) возвращает коэффициент корреляции Пирсона между двумя числовыми колонками одним агрегатом, за один проход по таблице. Результат всегда укладывается в диапазон от -1 до 1 и показывает, насколько дружно две величины движутся по прямой.

Что считает CORR и как читать результат

Функция оценивает именно линейную связь: насколько аккуратно облако точек (x, y) ложится на одну прямую. Читают результат по двум вещам сразу — по знаку и по модулю.

• +1 — идеальная прямая зависимость: больше x — больше y.
• -1 — идеальная обратная: больше x — меньше y.
• 0 — линейной связи нет (но нелинейная вполне может прятаться рядом).
• |r| около 0.7 и выше обычно зовут сильной связью, диапазон 0.3-0.7 — умеренной.

Порядок аргументов на сам коэффициент не влияет: CORR(a, b) равно CORR(b, a). А вот дальше, у функций регрессии, порядок уже станет принципиальным — это стоит держать в голове заранее.

-- Correlation between order amount and the user's account age in days
SELECT CORR(
         o.amount,
         EXTRACT(EPOCH FROM (o.created_at - u.created_at)) / 86400
       ) AS amount_vs_age
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

Корреляция — это не причинность

Это главная ловушка, и обходить её приходится каждому. Высокий r сообщает ровно одно: числа двигаются вместе. О том, что одно вызывает другое, он не говорит ничего. Вот откуда чаще всего берётся обманчивая корреляция:

• Скрытый общий фактор: сезонность или рекламная кампания одновременно подтягивают вверх и заказы, и трафик, а вы видите «связь» между ними.
• Эффект масштаба: крупные клиенты дают и больше заказов, и больший средний чек — корреляция тут лишь отражение размера.
• Случайность на крошечной выборке: r = 0.9 на пяти строках не значит почти ничего.

-- Per-country correlation, but only where the sample is large enough to trust
SELECT u.country,
       COUNT(*)                  AS n,
       CORR(o.amount, u.id)      AS r
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY u.country
HAVING COUNT(*) >= 30
ORDER BY r DESC;

Возьмите за правило выводить рядом COUNT(*): коэффициент без размера выборки слишком легко прочитать неверно и принять шум за закономерность.

Обработка NULL и крайние случаи

CORR берёт в расчёт только те пары, где оба значения не NULL. Стоит хотя бы одному из x или y оказаться NULL — и вся строка молча выпадает из подсчёта. Это поведение по умолчанию, а не сбой, но именно молчаливость и подводит.

• Если валидных пар меньше двух, на выходе будет NULL.
• Если x или y постоянны (нулевая дисперсия), деление на ноль даёт NULL, а не выбрасывает исключение.

-- Salary vs manager presence: rows with NULL manager_id are dropped automatically
SELECT dept,
       COUNT(*)                       AS rows_total,
       COUNT(salary)                  AS rows_with_salary,
       CORR(salary, manager_id)       AS r
FROM employees
GROUP BY dept;

И вот тут поджидают грабли: COUNT(*) и число пар, которое CORR реально взял в работу, запросто расходятся. Вы думаете, что коэффициент построен на тысяче строк, а половина из них тихо отвалилась по NULL. Чтобы знать честный размер выборки, считайте пары явно — например, через COUNT(*) FILTER (WHERE salary IS NOT NULL AND manager_id IS NOT NULL).

Связка с REGR_* для линии тренда

CORR отвечает на вопрос «насколько сильна связь», но уравнения прямой не даёт. За уравнением идём к семейству REGR_* — и считается оно тем же одним проходом, так что добавить его почти ничего не стоит:

• REGR_SLOPE(y, x) — наклон линии тренда.
• REGR_INTERCEPT(y, x) — точка пересечения с осью y.
• REGR_R2(y, x) — коэффициент детерминации, то самое r в квадрате.

-- Trend line for amount as a function of account age, plus strength of fit
SELECT REGR_SLOPE(o.amount, age_days)     AS slope,
       REGR_INTERCEPT(o.amount, age_days) AS intercept,
       CORR(o.amount, age_days)           AS r,
       REGR_R2(o.amount, age_days)        AS r_squared
FROM (
  SELECT o.amount,
         EXTRACT(EPOCH FROM (o.created_at - u.created_at)) / 86400 AS age_days
  FROM orders o
  JOIN users u ON u.id = o.user_id
) o;

Дальше прогноз собирается в одну строчку: predicted_amount = intercept + slope * age_days. Полноценную модель это не заменит, но быстро показывает, есть ли в данных линейный сигнал вообще.

Различия между СУБД

• PostgreSQL: полная поддержка CORR, COVAR_POP, COVAR_SAMP и всего набора REGR_*.
• ClickHouse: есть corr(x, y), но REGR_* нет; наклон приходится собирать вручную из covarPop и varPop.
• MySQL: функции CORR нет вовсе — формулу складывают руками из AVG, STDDEV_POP и среднего произведения либо считают на стороне приложения.

-- Portable fallback that works even without a built-in CORR
SELECT (AVG(x * y) - AVG(x) * AVG(y))
       / (STDDEV_POP(x) * STDDEV_POP(y)) AS r
FROM (SELECT amount AS x, user_id AS y FROM orders) t;

Собирая формулу руками, не забудьте отфильтровать строки так, чтобы обе величины были непустыми одновременно, — иначе разные агрегаты посчитают разные множества строк, и результат поедет. Итог простой: CORR — дешёвый способ за один проход прикинуть силу линейной связи, а стоящий рядом REGR_* превращает эту прикидку в готовое уравнение тренда. Только держите рядом размер выборки и помните, что совместное движение чисел само по себе ещё ничего не доказывает.