REGR_SLOPE and REGR_INTERCEPT in PostgreSQL: A Trend Line and Forecast in One Query

REGR_SLOPE и REGR_INTERCEPT — это агрегатные функции PostgreSQL, которые за один проход по таблице подгоняют прямую методом наименьших квадратов и возвращают её коэффициенты: наклон и точку пересечения с осью y. Вместе они дают уравнение y = slope * x + intercept — то есть сразу и линию тренда, и готовую формулу прогноза, не выходя из запроса и не выгружая данные в Excel, Python или BI-дашборд.

Синтаксис и порядок аргументов

Обе функции берут ровно два аргумента — и именно тут спрятаны главные грабли. Порядок строгий: сначала идёт зависимая переменная y (то, что мы предсказываем), и только потом независимая x (то, на чём строится прогноз). Школьная привычка писать «x, y» здесь играет против вас.

SELECT
    REGR_SLOPE(amount, extract(epoch from created_at)) AS slope,
    REGR_INTERCEPT(amount, extract(epoch from created_at)) AS intercept,
    REGR_COUNT(amount, extract(epoch from created_at)) AS n
FROM orders
WHERE status = 'paid';

Разберём по частям:

• REGR_SLOPE(y, x) — наклон: на сколько в среднем сдвигается y, когда x подрастает на единицу.
• REGR_INTERCEPT(y, x) — точка, где прямая пересекает ось y, то есть её значение при x = 0.
• REGR_COUNT(y, x) — сколько пар, в которых оба значения не NULL. Это и есть размер вашей выборки.

Грабли: порядок — (y, x), а не (x, y). Стоит переставить аргументы — и вы подгоните обратную зависимость, после чего прогноз тихо поедет, причём без всякой ошибки, на которую можно было бы опереться. Держите в голове смысл: «что предсказываем» всегда стоит первым.

Тренд выручки по дням

От синтаксиса — к делу. REGR_SLOPE требует числовых осей, поэтому дату превращаем в число (секунды эпохи) и смотрим, растёт ли средний чек со временем. Наклон удобнее пересчитать на сутки: «прибавка за день» читается куда нагляднее, чем неуловимая прибавка за секунду.

SELECT
    REGR_SLOPE(amount, extract(epoch from created_at)) * 86400 AS amount_per_day,
    REGR_COUNT(amount, extract(epoch from created_at)) AS sample_size
FROM orders
WHERE status = 'paid';

Положительный amount_per_day говорит, что суммы заказов в среднем растут изо дня в день; отрицательный — что сжимаются. И не выбрасывайте REGR_COUNT: наклон, построенный по трём точкам, — это не тренд, а случайный шум, который завтра качнётся в другую сторону.

Прогноз без внешнего пакета

Раз REGR_SLOPE и REGR_INTERCEPT уже отдали нам slope и intercept, прогноз сводится к школьной подстановке: берём нужный x, подставляем в уравнение прямой — и получаем y. Коэффициенты разумно посчитать один раз в CTE, а затем спроецировать выручку на будущую дату.

WITH model AS (
    SELECT
        REGR_SLOPE(amount, extract(epoch from created_at)) AS slope,
        REGR_INTERCEPT(amount, extract(epoch from created_at)) AS intercept
    FROM orders
    WHERE status = 'paid'
)
SELECT
    slope * extract(epoch from TIMESTAMP '2026-12-31') + intercept AS forecast_amount
FROM model;

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

SELECT
    dept,
    REGR_SLOPE(salary, reports) AS salary_per_report,
    REGR_INTERCEPT(salary, reports) AS base_salary
FROM (
    SELECT
        e.dept,
        e.salary,
        count(r.id) AS reports
    FROM employees e
    LEFT JOIN employees r ON r.manager_id = e.id
    GROUP BY e.id, e.dept, e.salary
) s
GROUP BY dept;

Качество подгонки и подводные камни

Сам по себе наклон, который вернул REGR_SLOPE, не скажет ни слова о том, насколько хорошо прямая ложится на данные. Линию можно протянуть сквозь любое облако точек — вопрос лишь в том, описывает ли она их вообще. Для этой проверки есть REGR_R2 (коэффициент детерминации, от 0 до 1) и CORR (корреляция):

SELECT
    REGR_R2(amount, extract(epoch from created_at)) AS r_squared,
    CORR(amount, extract(epoch from created_at)) AS correlation
FROM orders
WHERE status = 'paid';

О чём стоит помнить, прежде чем доверять числам от REGR_SLOPE и REGR_INTERCEPT:

• R2 около нуля — прямая почти бесполезна, и прогноз по ней доверия не заслуживает.
• Если все x одинаковы (нулевая дисперсия), наклон не определён, и REGR_SLOPE вернёт NULL, а не упадёт с ошибкой.
• Строки, где y или x равны NULL, в расчёт не попадают вовсе — ровно поэтому всегда сверяйтесь с REGR_COUNT.
• Линейная регрессия видит только прямую. Сезонность и экспоненциальный рост она безжалостно «спрямит» — и тихо введёт вас в заблуждение.

Различия в других СУБД

• MySQL функций REGR_* не знает вовсе. Наклон собирают вручную: slope = (n*SUM(x*y) - SUM(x)*SUM(y)) / (n*SUM(x*x) - SUM(x)*SUM(x)), а intercept = (SUM(y) - slope*SUM(x)) / n.
• ClickHouse предлагает simpleLinearRegression(x, y), и вот тут порядок аргументов обратный — x идёт первым. Функция отдаёт кортеж (slope, intercept).
• BigQuery встроенных REGR_SLOPE и REGR_INTERCEPT не имеет — наклон и сдвиг там считают вручную через SUM, AVG и COVAR_POP/VAR_POP, либо обучают модель CREATE MODEL ... OPTIONS(model_type='linear_reg') в BigQuery ML. А вот Snowflake поддерживает стандартные REGR_SLOPE(y, x) и REGR_INTERCEPT(y, x) — ровно как PostgreSQL.

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