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

Зачем residuals

Residual = разница между actual и predicted. Анализ residuals — то, что отделяет «модель сошлась» от «модель работает». Если в данных есть тренд, который модель не уловила, он останется в residuals. Если variance растёт с x — heteroscedasticity. Если бимодальное распределение — нелинейность.

Формула

residual_i = y_i − y_hat_i
y_hat_i = intercept + slope × x_i

Для multi-variable: y_hat = β0 + Σ β_j × x_ji.

Residuals в SQL

WITH model AS (
    SELECT
        REGR_SLOPE(y, x) AS slope,
        REGR_INTERCEPT(y, x) AS intercept
    FROM training_data
),
predictions AS (
    SELECT
        d.user_id,
        d.x,
        d.y,
        m.intercept + m.slope * d.x AS y_hat,
        d.y - (m.intercept + m.slope * d.x) AS residual
    FROM training_data d
    CROSS JOIN model m
)
SELECT *
FROM predictions
ORDER BY ABS(residual) DESC
LIMIT 20;

Топ по |residual| — кандидаты на outliers или anomalies.

Distribution residuals

Что хочется в OLS: residuals normal с mean = 0 и постоянной variance.

SELECT
    AVG(residual) AS mean_residual,
    STDDEV_SAMP(residual) AS std_residual,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY residual) AS median_residual,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY residual) AS p95_residual,
    PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY residual) AS p5_residual
FROM predictions;

mean ≈ 0 — модель не biased. mean ≠ 0 — есть систематический сдвиг. Распределение p5/p95 покажет нормально ли.

Outliers по residuals

Studentized residuals = residual / stddev:

WITH stats AS (
    SELECT STDDEV_SAMP(residual) AS sigma FROM predictions
)
SELECT
    p.user_id,
    p.x,
    p.y,
    p.residual,
    p.residual / NULLIF(s.sigma, 0) AS studentized_residual,
    CASE
        WHEN ABS(p.residual / NULLIF(s.sigma, 0)) > 3 THEN 'outlier'
        WHEN ABS(p.residual / NULLIF(s.sigma, 0)) > 2 THEN 'borderline'
        ELSE 'normal'
    END AS verdict
FROM predictions p
CROSS JOIN stats s
WHERE ABS(p.residual / NULLIF(s.sigma, 0)) > 2;

В bivariate OLS |studentized residual| > 3 — outlier с тех же позиций, что |z| > 3 на нормальном распределении.

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

Ошибка 1. Считать residuals на in-sample. In-sample residuals искусственно занижены (модель оптимизирует именно их). Анализируйте на test set.

Ошибка 2. Игнорировать pattern в residuals. Идеально: random scatter. Видна U-образная или линейная зависимость → нелинейность.

Ошибка 3. Считать |residual| как абсолютное значение в anomaly detection. Лучше использовать studentized или standardized — relative к sigma.

Ошибка 4. AVG(residual) ≠ 0. В OLS by construction = 0 на train. Если в SQL получили != 0 — где-то баг (например, считаете на test, или formula неверна).

Ошибка 5. Использовать residual для feature importance. Residual — это «что не объяснено». Не показатель важности фичи.

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

• Как посчитать linear regression в SQL
• Как посчитать r2 в SQL
• Как посчитать heteroscedasticity в SQL
• Как посчитать z-score outliers в SQL

FAQ

Residuals vs errors?

Residual — на наблюдаемых данных. Error — теоретический «истинный» error. На train они эквивалентны в OLS.

Mean residual должен быть 0?

В OLS — да, by construction.

Residual plot — что смотреть?

Random scatter — хорошо. Pattern (U-shape, fan) — что-то не уловлено.

Studentized vs standardized?

Studentized учитывает leverage (положение точки). Standardized — просто residual / sigma.

Residual analysis для logistic?

Pearson или deviance residuals. Подходы другие.