Как посчитать linear regression в SQL

Зачем regression в SQL

Чаще всего регрессия живёт в Python / R. Но если нужен быстрый sanity check «есть ли тренд» прямо в дашборде, или подогнать line of best fit в metabase-чарт — SQL справляется без выгрузки. Простая OLS на двух переменных — несколько строк через COVAR_POP / VAR_POP или встроенные REGR_* функции Postgres.

Формула OLS

slope = cov(x, y) / var(x)
intercept = mean(y) − slope × mean(x)
y_hat = intercept + slope × x

Это и есть line of best fit для пары (x, y).

Slope и intercept

SELECT
    COVAR_POP(amount, hours_since_signup) / NULLIF(VAR_POP(hours_since_signup), 0) AS slope,
    AVG(amount) - COVAR_POP(amount, hours_since_signup) / NULLIF(VAR_POP(hours_since_signup), 0) * AVG(hours_since_signup) AS intercept
FROM users_engagement
WHERE created_at >= CURRENT_DATE - INTERVAL '90 days';

Если slope = 0.5, intercept = 10 — «через час дополнительного использования юзер тратит +0.5 amount, baseline 10».

Прогноз для новой точки

WITH model AS (
    SELECT
        COVAR_POP(y, x) / NULLIF(VAR_POP(x), 0) AS slope,
        AVG(y) - COVAR_POP(y, x) / NULLIF(VAR_POP(x), 0) * AVG(x) AS intercept
    FROM training_data
)
SELECT
    new_x.x_value,
    m.intercept + m.slope * new_x.x_value AS predicted_y
FROM (VALUES (10), (20), (50), (100)) AS new_x(x_value)
CROSS JOIN model m;

Postgres REGR функции

Postgres имеет встроенные:

SELECT
    REGR_SLOPE(amount, hours_since_signup) AS slope,
    REGR_INTERCEPT(amount, hours_since_signup) AS intercept,
    REGR_R2(amount, hours_since_signup) AS r_squared,
    REGR_COUNT(amount, hours_since_signup) AS n_pairs
FROM users_engagement;

REGR_* функции пропускают NULL и быстрее, чем CTE с COVAR/VAR. Используйте, если они доступны в вашей СУБД.

Частые ошибки

Ошибка 1. Деление на 0 для константной x. Если VAR_POP(x) = 0 (все x одинаковые), slope undefined. NULLIF лечит.

Ошибка 2. OLS без проверки assumptions. Linear regression предполагает linearity, normal residuals, homoscedasticity. На violation — biased estimates.

Ошибка 3. Slope коэффициент = «эффект». В bivariate без contols это correlation, не causal effect.

Ошибка 4. Multiple predictors в чистом SQL. Для multivariate OLS нужны матричные операции — лучше Python.

Ошибка 5. Использовать VAR_SAMP вместо VAR_POP. Для slope формула с population variance. SAMP даст идентичный результат (сократится), но если смешать с COVAR_SAMP — будет ошибка.

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

• Как посчитать r2 в SQL
• Как посчитать correlation в SQL
• Как посчитать residuals в SQL
• Linear regression для аналитика

FAQ

Multivariate в SQL?

В чистом SQL — мучительно. Используйте Python statsmodels / R lm.

Какой R² хорош?

В bivariate 0.5+ — заметный сигнал. В социальных данных 0.1-0.3 норма.

CI для slope?

Стандартная формула через SE. В SQL громоздко — лучше bootstrap.

REGR_R2 vs ручной?

REGR_R2 — coefficient of determination. Совпадает с corr(x,y)² в bivariate.

Можно ли predict с разными units?

Нет, единицы фиксированы в обучающих данных.