CORR в SQL: как измерить связь между двумя колонками прямо в базе

В аналитике постоянно возникает вопрос: связаны ли между собой две величины?

Например:

• растёт ли средний чек вместе с возрастом аккаунта;
• увеличивается ли выручка после скидок;
• связаны ли трафик и количество заказов;
• покупают ли чаще пользователи, которые дольше зарегистрированы;
• зависит ли сумма заказа от количества товаров в корзине.

Можно выгрузить данные в Python, построить точечный график и смотреть на облако точек глазами. А можно сначала быстро спросить саму базу.

В PostgreSQL для этого есть агрегатная функция CORR(y, x). Она считает коэффициент корреляции Пирсона между двумя числовыми выражениями.

Проще говоря, CORR отвечает на вопрос:

насколько две величины дружно движутся вместе по прямой?

Результат всегда находится в диапазоне от -1 до 1.

Что показывает CORR

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

Каждая строка таблицы превращается в точку:

x = account age
y = order amount

Функция CORR(y, x) смотрит на все эти точки и оценивает, насколько они похожи на прямую линию.

Если точки идут снизу вверх — связь положительная.

Если точки идут сверху вниз — связь отрицательная.

Если точки разбросаны как попало — линейной связи почти нет.

Как читать результат

Значение корреляции часто называют r.

Значение	Как понимать
1	идеальная положительная связь: больше x — больше y
-1	идеальная отрицательная связь: больше x — меньше y
0	линейной связи не видно
около 0.7 и выше	обычно считают сильной положительной связью
около -0.7 и ниже	обычно считают сильной отрицательной связью
от 0.3 до 0.7 по модулю	умеренная связь
около 0	слабая линейная связь или её отсутствие

Важно слово «линейная». CORR ищет именно связь, похожую на прямую линию. Если зависимость сложная, дугой или ступеньками, коэффициент может быть близок к нулю, хотя связь в данных на самом деле есть.

Первый пример: сумма заказа и возраст аккаунта

Допустим, есть таблицы users и orders.

У пользователя есть дата регистрации:

SELECT id, created_at
FROM users;

У заказа есть сумма и дата создания:

SELECT id, user_id, amount, created_at, status
FROM orders;

Хотим понять: становятся ли заказы дороже у пользователей, которые давно зарегистрированы?

Посчитаем возраст аккаунта на момент заказа в днях и сравним его с суммой заказа:

SELECT CORR(
           o.amount,
           EXTRACT(EPOCH FROM (o.created_at - u.created_at)) / 86400
       ) AS amount_vs_account_age
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

Здесь:

• o.amount — сумма заказа;
• выражение с EXTRACT — возраст аккаунта в днях;
• CORR считает связь между этими двумя числами;
• фильтр оставляет только оплаченные заказы.

Если результат получился, например:

0.62

это означает умеренную положительную связь: чем старше аккаунт, тем в среднем выше сумма заказа.

Если результат:

-0.45

это означает умеренную обратную связь: чем старше аккаунт, тем ниже сумма заказа.

Если результат:

0.03

линейной связи почти не видно.

Порядок аргументов в CORR

Для самой корреляции порядок аргументов не важен:

SELECT CORR(amount, quantity) AS r1,
       CORR(quantity, amount) AS r2
FROM orders;

r1 и r2 будут одинаковыми.

Но лучше сразу привыкать писать в порядке y, x:

CORR(y, x)

Почему так? Потому что у функций линейной регрессии порядок уже важен. Например, REGR_SLOPE(y, x) означает: как меняется y, когда растёт x.

Так что хорошая привычка такая:

сначала пишем то, что хотим объяснить, потом то, чем объясняем.

Например:

CORR(order_amount, account_age_days)

Читается так: смотрим связь суммы заказа с возрастом аккаунта.

Корреляция не доказывает причину

Это самое важное правило всей статьи.

Высокая корреляция не означает, что одно число вызывает другое.

CORR говорит только:

эти величины двигаются вместе.

Но он не говорит:

первая величина является причиной второй.

Например, вы можете увидеть, что в дни с большим трафиком больше заказов. Кажется очевидным: больше людей пришло — больше купили.

Но может быть третий фактор: рекламная кампания. Она одновременно подняла и трафик, и заказы.

Или сезонность: перед Новым годом растут и посещения сайта, и продажи.

Или размер клиента: крупные клиенты делают больше заказов и чаще имеют высокий средний чек. Корреляция есть, но причина может быть не в количестве заказов, а в масштабе клиента.

Пример обманчивой корреляции

Допустим, мы считаем связь между суммой заказа и идентификатором пользователя:

SELECT CORR(o.amount, u.id) AS r
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

Технически база посчитает результат.

Но аналитически такой запрос почти бессмысленный. u.id — это не настоящая характеристика пользователя, а технический номер. Если корреляция окажется высокой, это ещё не значит, что номер пользователя влияет на сумму заказа.

Возможно, новые пользователи получают большие скидки. Возможно, старые пользователи имеют меньшие id. Возможно, изменилась ценовая политика. Нужно смотреть контекст.

Хорошая аналитика начинается не с функции, а с нормального вопроса:

что именно мы сравниваем и почему это может быть связано?

Всегда выводите размер выборки

Коэффициент без размера выборки легко обманет.

Корреляция 0.9 на пяти строках почти ничего не доказывает. Пять точек могут случайно лечь красиво. А вот 0.45 на десяти тысячах строк уже может быть важным сигналом.

Поэтому рядом с CORR почти всегда полезно выводить COUNT.

Например, посчитаем корреляцию по странам:

SELECT u.country,
       COUNT(*) AS n,
       CORR(o.amount, EXTRACT(EPOCH FROM (o.created_at - u.created_at)) / 86400) AS r
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country
HAVING COUNT(*) >= 30
ORDER BY r DESC;

Здесь:

• n показывает количество строк в группе;
• r показывает корреляцию;
• HAVING COUNT(*) >= 30 отсекает слишком маленькие группы.

Это важная привычка: не доверять красивому коэффициенту, пока не видно, на скольких строках он посчитан.

Как CORR работает с NULL

CORR считает только пары, где оба значения не равны NULL.

Если в строке одно значение есть, а второго нет, строка не участвует в расчёте.

Например:

SELECT CORR(salary, manager_id) AS r
FROM employees;

Если у сотрудника есть salary, но manager_id равен NULL, эта строка выпадет из расчёта.

Если manager_id есть, но salary равен NULL, строка тоже выпадет.

Для корреляции нужна полная пара:

x is not null
y is not null

Почему COUNT(*) может обмануть

Посмотрим на такой запрос:

SELECT dept,
       COUNT(*) AS rows_total,
       COUNT(salary) AS rows_with_salary,
       CORR(salary, manager_id) AS r
FROM employees
GROUP BY dept;

На первый взгляд кажется, что корреляция считается по rows_total.

Но это не обязательно так.

COUNT(*) считает все строки.

CORR(salary, manager_id) считает только строки, где одновременно заполнены и salary, и manager_id.

Поэтому лучше явно вывести честное количество пар:

SELECT dept,
       COUNT(*) AS rows_total,
       COUNT(*) FILTER (
           WHERE salary IS NOT NULL
             AND manager_id IS NOT NULL
       ) AS pairs_used,
       CORR(salary, manager_id) AS r
FROM employees
GROUP BY dept;

Теперь видно:

• сколько строк было всего;
• сколько строк реально попало в корреляцию;
• какой коэффициент получился.

Это особенно важно, если в данных много пропусков.

Крайние случаи

У CORR есть несколько ситуаций, где результатом будет NULL.

Первая ситуация: валидных пар меньше двух.

SELECT CORR(x, y) AS r
FROM measurements;

Если после удаления строк с NULL осталась только одна пара, корреляцию считать не из чего. Одна точка не показывает связь.

Вторая ситуация: одна из величин постоянна.

Например, у всех строк x одинаковый:

x = 10
x = 10
x = 10
x = 10

Тогда у x нет разброса. А корреляция измеряет совместное изменение двух величин. Если одна величина вообще не меняется, считать связь по прямой невозможно.

В таком случае PostgreSQL вернёт NULL, а не ошибку.

CORR и фильтры

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

Например, только по оплаченным заказам:

SELECT CORR(amount, quantity) AS r
FROM orders
WHERE status = 'paid';

Или только за последние 90 дней:

SELECT CORR(amount, quantity) AS r
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '90 days';

Или отдельно по месяцам:

SELECT date_trunc('month', created_at) AS month,
       COUNT(*) AS n,
       CORR(amount, quantity) AS r
FROM orders
WHERE status = 'paid'
GROUP BY date_trunc('month', created_at)
HAVING COUNT(*) >= 30
ORDER BY month;

Так можно увидеть, меняется ли связь со временем. Например, раньше количество товаров в заказе сильно влияло на сумму, а после изменения цен связь стала слабее.

CORR вместе с REGR_*: от связи к линии тренда

CORR отвечает на вопрос:

насколько сильна линейная связь?

Но он не даёт уравнение прямой.

Если нужно построить простую линию тренда, в PostgreSQL есть семейство агрегатов REGR_*.

Самые полезные:

Функция	Что показывает
REGR_SLOPE(y, x)	наклон линии
REGR_INTERCEPT(y, x)	пересечение с осью y
REGR_R2(y, x)	доля объяснённого разброса, то есть r в квадрате

Например, хотим описать сумму заказа через возраст аккаунта:

WITH order_points AS (
    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
    WHERE o.status = 'paid'
)
SELECT REGR_SLOPE(amount, age_days) AS slope,
       REGR_INTERCEPT(amount, age_days) AS intercept,
       CORR(amount, age_days) AS r,
       REGR_R2(amount, age_days) AS r_squared,
       COUNT(*) AS n
FROM order_points;

Если результат такой:

slope = 1.8
intercept = 420
r = 0.64
r_squared = 0.41

это можно прочитать так:

• при увеличении возраста аккаунта на один день ожидаемая сумма заказа растёт примерно на 1.8;
• базовый уровень линии около 420;
• связь умеренно сильная;
• линейная модель объясняет около 41% разброса.

Прогноз по такой линии считается формулой:

predicted_y = intercept + slope * x

Для нашего примера:

predicted_amount = intercept + slope * age_days

Это не полноценная машинная модель и не магический прогноз будущего. Но как быстрый способ понять, есть ли в данных линейный сигнал, связка CORR и REGR_* очень удобна.

Практический пример: скидка и выручка

Допустим, в таблице orders есть:

• discount_percent — процент скидки;
• amount — сумма заказа;
• status — статус заказа.

Хотим понять: чем выше скидка, тем выше сумма заказа или ниже?

SELECT COUNT(*) AS n,
       CORR(discount_percent, amount) AS discount_vs_amount
FROM orders
WHERE status = 'paid'
  AND discount_percent IS NOT NULL
  AND amount IS NOT NULL;

Если результат положительный, например 0.35, то большие скидки чаще встречаются рядом с большими заказами.

Но не спешите говорить: «скидка увеличивает выручку».

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

Корреляция подсказывает, где копать. Но не заменяет проверку гипотезы.

Практический пример: трафик и заказы по дням

Допустим, есть таблица дневной статистики:

SELECT day, visits, orders_count
FROM daily_stats;

Посчитаем связь между посещениями и заказами:

SELECT COUNT(*) AS n,
       CORR(visits, orders_count) AS visits_vs_orders
FROM daily_stats
WHERE visits IS NOT NULL
  AND orders_count IS NOT NULL;

Если корреляция высокая, например 0.88, это ожидаемо: больше посетителей — больше заказов.

Но и здесь нужно помнить про контекст. В праздники, дни распродаж и после рекламных рассылок обе величины могут расти одновременно из-за общего внешнего фактора.

Как написать CORR вручную

В PostgreSQL встроенная функция есть, поэтому вручную формулу обычно писать не нужно.

Но полезно понимать идею.

Корреляция — это ковариация, делённая на произведение стандартных отклонений:

r = covar_pop(x, y) / (stddev_pop(x) * stddev_pop(y))

В SQL это можно записать так:

SELECT (AVG(x * y) - AVG(x) * AVG(y))
       / (STDDEV_POP(x) * STDDEV_POP(y)) AS r
FROM measurements
WHERE x IS NOT NULL
  AND y IS NOT NULL;

Главное — обязательно фильтровать строки так, чтобы оба значения были заполнены одновременно.

Иначе разные агрегаты могут посчитать разные наборы строк, и результат получится некорректным.

Различия между PostgreSQL, MySQL и ClickHouse

В PostgreSQL есть встроенный агрегат CORR, а также функции для регрессии:

• CORR;
• COVAR_POP;
• COVAR_SAMP;
• REGR_SLOPE;
• REGR_INTERCEPT;
• REGR_R2;
• другие функции семейства REGR_*.

В ClickHouse есть функция corr. Но привычного набора REGR_* как в PostgreSQL нет, поэтому наклон линии тренда обычно собирают через ковариацию и дисперсию.

В 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,
           quantity AS y
    FROM orders
    WHERE amount IS NOT NULL
      AND quantity IS NOT NULL
) t;

Такой подход менее удобен, чем встроенный CORR, зато помогает в базах, где отдельной функции для корреляции нет.

Частые ошибки при работе с CORR

Первая ошибка — принимать корреляцию за причинность.

Если скидка и выручка растут вместе, это не доказывает, что скидка вызвала рост выручки.

Вторая ошибка — смотреть только на коэффициент и не смотреть на размер выборки.

SELECT country,
       CORR(amount, discount_percent) AS r
FROM orders
GROUP BY country;

Так лучше не делать. Добавьте COUNT и отфильтруйте маленькие группы:

SELECT country,
       COUNT(*) AS n,
       CORR(amount, discount_percent) AS r
FROM orders
WHERE amount IS NOT NULL
  AND discount_percent IS NOT NULL
GROUP BY country
HAVING COUNT(*) >= 30;

Третья ошибка — забывать про NULL.

COUNT(*) может показывать тысячу строк, а CORR реально посчитаться по двумстам парам.

Четвёртая ошибка — искать линейную связь там, где зависимость нелинейная.

Например, выручка может расти с трафиком только до определённого предела, а потом упираться в складские остатки или лимит обработки заказов. CORR такую форму зависимости описывает грубо.

Когда CORR особенно полезен

CORR хорош как быстрый аналитический фонарик.

Им удобно подсветить данные и понять, есть ли смысл копать глубже.

Полезные вопросы:

• связаны ли сумма заказа и количество товаров;
• растёт ли выручка вместе с трафиком;
• есть ли связь между скидкой и суммой заказа;
• меняется ли средний чек с возрастом аккаунта;
• похожи ли динамики двух метрик по дням;
• есть ли связь между длительностью подписки и активностью.

Но после результата всегда задавайте второй вопрос:

почему это может происходить?

И третий:

не объясняется ли это третьим фактором?

Главное

CORR(y, x) в PostgreSQL считает коэффициент корреляции Пирсона между двумя числовыми выражениями.

Он возвращает число от -1 до 1:

• ближе к 1 — сильная положительная линейная связь;
• ближе к -1 — сильная отрицательная линейная связь;
• около 0 — линейной связи почти не видно.

Базовый пример:

SELECT CORR(amount, quantity) AS r
FROM orders
WHERE status = 'paid';

Рядом с корреляцией почти всегда стоит выводить размер выборки:

SELECT COUNT(*) AS n,
       CORR(amount, quantity) AS r
FROM orders
WHERE status = 'paid'
  AND amount IS NOT NULL
  AND quantity IS NOT NULL;

CORR автоматически игнорирует строки, где хотя бы одно из двух значений равно NULL. Если валидных пар меньше двух или одна из величин не меняется, результатом будет NULL.

Для линии тренда используйте функции REGR_*:

SELECT REGR_SLOPE(amount, quantity) AS slope,
       REGR_INTERCEPT(amount, quantity) AS intercept,
       CORR(amount, quantity) AS r,
       REGR_R2(amount, quantity) AS r_squared
FROM orders
WHERE status = 'paid';

И самое важное: корреляция — это не доказательство причины. Она говорит, что числа движутся вместе, но не объясняет, почему. Хороший аналитик использует CORR как первый быстрый сигнал, а не как окончательный приговор.