REGR_R2 in SQL: R-squared and Goodness of Fit for Regression

Наклон без меры доверия — это половина ответа, и обычно опасная половина. REGR_SLOPE бодро сообщит, что выручка растёт, даже когда на графике сплошное облако точек без всякой закономерности. Вот тут на сцену и выходит REGR_R2: функция возвращает коэффициент детерминации (R-квадрат) линейной регрессии — число от 0 до 1, которое показывает, какую долю разброса зависимой переменной объясняет проведённая прямая. По сути это прямой ответ на вопрос «а наклону, который только что насчитал REGR_SLOPE, вообще можно верить?».

Синтаксис и базовый смысл

Порядок аргументов тот же, что у всех REGR_*-функций: сначала зависимая переменная y, потом независимая x. Перепутаете местами — запрос формально отработает, но посчитает совсем не то, что вы задумали.

SELECT REGR_R2(y, x) FROM points;

Интуиция за этой цифрой простая. R-квадрат сравнивает вашу прямую с самой ленивой моделью на свете — «всегда предсказываю среднее y, что бы ни было на входе». Если регрессия объясняет данные не лучше, чем это тупое среднее, R-квадрат жмётся к нулю. А если прямая ложится точно по точкам, он дотягивает до единицы. Несколько ориентиров, чтобы привязать число к смыслу:

• 1.0 — идеальная прямая, точки сидят на ней без единого отклонения.
• 0.0 — наклон бесполезен, x не объясняет в y ровным счётом ничего.
• 0.7 — прямая забирает на себя 70% разброса y.

Проверяем тренд выручки

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

WITH daily AS (
    SELECT
        date_trunc('day', created_at) AS day,
        SUM(amount) AS revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY 1
)
SELECT
    REGR_SLOPE(revenue, EXTRACT(EPOCH FROM day)) AS slope,
    REGR_R2(revenue, EXTRACT(EPOCH FROM day))    AS r2,
    COUNT(*)                                      AS days
FROM daily;

Если slope > 0, а r2 при этом равен, скажем, 0.04 — никакого «роста» нет, есть иллюзия: дневной разброс огромен, и линия не объясняет почти ничего. Совсем другое дело — slope > 0 в паре с r2 = 0.8: вот это уже честный, устойчивый тренд, на который не стыдно опереться в отчёте.

Связь с CORR в квадрате

Для простой регрессии (одна независимая переменная) есть красивое точное тождество: REGR_R2(y, x) ровно равен квадрату коэффициента корреляции Пирсона CORR(y, x). Удобно держать в голове как способ самопроверки — если две величины вдруг разошлись, ошибка где-то в вашем запросе, а не в арифметике.

SELECT
    REGR_R2(salary, manager_id)        AS r2,
    POWER(CORR(salary, manager_id), 2) AS corr_squared
FROM employees
WHERE manager_id IS NOT NULL;

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

Сегментируем по странам

По-настоящему R-квадрат разворачивается внутри GROUP BY: вместо одного усреднённого тренда на всех вы получаете отдельную оценку надёжности для каждого сегмента и сразу отбрасываете те, где сплошной шум.

SELECT
    u.country,
    REGR_SLOPE(o.amount, EXTRACT(EPOCH FROM o.created_at)) AS slope,
    REGR_R2(o.amount, EXTRACT(EPOCH FROM o.created_at))    AS r2,
    COUNT(*)                                                AS n
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY u.country
HAVING COUNT(*) >= 30
ORDER BY r2 DESC;

Фильтр HAVING COUNT(*) >= 30 здесь не косметика, а защита от собственной доверчивости: на двух точках R-квадрат всегда равен 1 — через две точки прямая проходит идеально по определению. Без минимального порога вы начнёте принимать крошечные сегменты со случайным совпадением за самые надёжные тренды, а они ещё и всплывут наверх из-за ORDER BY r2 DESC.

Подводные камни

• Высокий R-квадрат — это не причинность. Он говорит лишь, что прямая хорошо описывает данные, а не что x влияет на y. Классика жанра — две величины, которые синхронно растут из-за третьей, скрытой.
• NULL выбрасываются парами. Если x или y оказался NULL, строка просто выпадает из расчёта — ровно так же, как в REGR_SLOPE и CORR.
• Нет в MySQL. Ни REGR_R2, ни прочие REGR_* не входят в стандартный MySQL — придётся собирать R-квадрат руками через эквивалент CORR или через дисперсии. В ClickHouse REGR_R2 тоже нет, но из его оконных и агрегатных статистик коэффициент несложно сложить формулой.
• Только линейность. R-квадрат у нуля означает «нет линейной связи», а не «нет связи вообще». Прежде чем выносить вердикт, не поленитесь взглянуть на сам график — глаз ловит кривизну, которую цифра пропускает.

Практическое правило одно и без исключений: считайте REGR_SLOPE и REGR_R2 всегда в паре. Наклон задаёт направление и величину изменения, а R-квадрат отвечает на отдельный, не менее важный вопрос — стоит ли вообще на этот наклон смотреть или перед вами чистый шум. Поодиночке они почти всегда вводят в заблуждение: пологий наклон с высоким R-квадратом — это надёжный слабый тренд, которому можно верить, а крутой наклон с R-квадратом у нуля — всего лишь случайная прямая, лихо прочерченная сквозь облако точек.