Αυτό το άρθρο είναι προς το παρόν στα ρωσικά — η αγγλική μετάφραση βρίσκεται σε εξέλιξη.
Вопрос «а связаны ли вообще эти две колонки» возникает в аналитике постоянно: растёт ли средний чек с возрастом аккаунта, тянет ли скидка за собой выручку, идут ли рука об руку трафик и заказы. Можно выгрузить данные в 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). А вот дальше, у функций регрессии, порядок уже станет принципиальным — это стоит держать в голове заранее.
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 на пяти строках не значит почти ничего.
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, а не выбрасывает исключение.
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 в квадрате.
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 и среднего произведения либо считают на стороне приложения.
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_* превращает эту прикидку в готовое уравнение тренда. Только держите рядом размер выборки и помните, что совместное движение чисел само по себе ещё ничего не доказывает.
Вопрос «а связаны ли вообще эти две колонки» возникает в аналитике постоянно: растёт ли средний чек с возрастом аккаунта, тянет ли скидка за собой выручку, идут ли рука об руку трафик и заказы. Можно выгрузить данные в 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. Полноценную модель это не заменит, но быстро показывает, есть ли в данных линейный сигнал вообще.Различия между СУБД
CORR,COVAR_POP,COVAR_SAMPи всего набораREGR_*.corr(x, y), ноREGR_*нет; наклон приходится собирать вручную изcovarPopиvarPop.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_*превращает эту прикидку в готовое уравнение тренда. Только держите рядом размер выборки и помните, что совместное движение чисел само по себе ещё ничего не доказывает.