REGR_SLOPE и REGR_INTERCEPT в PostgreSQL: линия тренда прямо в SQL

Иногда в базе хочется не просто посчитать сумму, среднее или максимум, а понять направление: растёт показатель или падает. Например:

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

Для таких задач в PostgreSQL есть агрегатные функции REGR_SLOPE и REGR_INTERCEPT. Они строят простую линейную регрессию прямо внутри SQL-запроса.

Говоря проще, PostgreSQL берёт точки на графике и подбирает к ним прямую линию:

y = slope * x + intercept

Где:

• y — показатель, который мы хотим объяснить или предсказать;
• x — число, от которого этот показатель зависит;
• slope — наклон линии;
• intercept — точка пересечения с осью y.

Например, если x — это время, а y — сумма заказа, то такая линия показывает общий тренд: суммы заказов со временем растут, падают или стоят примерно на месте.

Главная польза в том, что всё считается прямо в базе. Не нужно выгружать данные в Excel, Python или BI-систему, если нужен быстрый тренд или грубый прогноз.

Что делают REGR_SLOPE и REGR_INTERCEPT

Функция REGR_SLOPE(y, x) возвращает наклон прямой.

Наклон отвечает на вопрос:

На сколько в среднем меняется y, когда x увеличивается на единицу?

Функция REGR_INTERCEPT(y, x) возвращает свободный коэффициент, то есть значение линии при x = 0.

Вместе они дают готовую формулу:

y = REGR_SLOPE(y, x) * x + REGR_INTERCEPT(y, x)

Допустим, у нас есть заказы. Мы хотим понять, как сумма оплаченного заказа меняется со временем.

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';

Здесь:

• amount — это y, сумма заказа;
• extract(epoch from created_at) — это x, дата заказа, превращённая в число;
• REGR_COUNT показывает, сколько пар значений реально попало в расчёт.

Дата сама по себе не подходит для регрессии, потому что функции нужны числовые оси. Поэтому мы превращаем дату в количество секунд с начала Unix-эпохи.

Главное правило: сначала y, потом x

Самая частая ошибка с REGR_SLOPE и REGR_INTERCEPT — перепутать порядок аргументов.

В обычной речи мы часто говорим «икс и игрек». Из школы тоже помним: сначала x, потом y.

Но в PostgreSQL порядок другой:

REGR_SLOPE(y, x)
REGR_INTERCEPT(y, x)

Сначала идёт зависимая переменная — то, что мы изучаем или предсказываем.

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

Например:

REGR_SLOPE(amount, extract(epoch from created_at))

Читается так:

Как меняется amount при изменении created_at.

А вот так писать не надо:

REGR_SLOPE(extract(epoch from created_at), amount)

Такой запрос не упадёт с ошибкой. Он просто посчитает другую зависимость: как время зависит от суммы заказа. Для нашей задачи это бессмыслица, но PostgreSQL не знает вашего бизнес-смысла и честно вернёт число.

Поэтому держите в голове простую фразу:

Сначала то, что предсказываем. Потом то, по чему предсказываем.

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

Посмотрим, растёт ли сумма оплаченных заказов со временем.

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';

Почему мы умножаем на 86400?

Потому что extract(epoch from created_at) возвращает время в секундах. Значит, сам REGR_SLOPE покажет изменение суммы за одну секунду. Это почти невозможно нормально прочитать.

В сутках 86400 секунд, поэтому после умножения мы получаем более понятный показатель:

На сколько в среднем меняется сумма заказа за день.

Если amount_per_day положительный, суммы заказов в среднем растут.

Если отрицательный — снижаются.

Если близок к нулю — выраженного линейного тренда почти нет.

Но обязательно смотрите на sample_size. Если линия построена по трём заказам, это ещё не тренд. Это просто три точки, которые могли случайно лечь как угодно.

Пример: прогноз на будущую дату

Раз у нас есть slope и intercept, можно подставить будущий x в формулу прямой.

Сначала посчитаем коэффициенты в 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;

Здесь происходит ровно то же самое, что в школьной формуле прямой:

y = slope * x + intercept

Только x — это будущая дата, превращённая в число.

Важно понимать: это не магический искусственный интеллект и не полноценная модель продаж. Это простая прямая линия. Она полезна для быстрой оценки направления, но не знает про сезонность, акции, праздники, кризисы, отпускной сезон и внезапный вирусный пост в соцсетях.

Пример: зависимость зарплаты от количества подчинённых

Ось x не обязана быть временем. Это может быть любое числовое значение.

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

Сначала посчитаем для каждого сотрудника количество подчинённых, а потом построим регрессию отдельно по отделам.

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;

Что здесь означает результат:

• salary_per_report — насколько в среднем меняется зарплата при увеличении числа подчинённых на одного человека;
• base_salary — значение линии при нуле подчинённых;
• dept — отдел, внутри которого мы строим зависимость.

Например, если в одном отделе salary_per_report заметно выше, чем в другом, это может означать, что управленческая нагрузка там сильнее связана с зарплатой. Но это не доказывает причинно-следственную связь. Регрессия показывает зависимость в данных, а не объясняет, почему именно она появилась.

Как проверить, можно ли доверять линии

REGR_SLOPE всегда пытается провести прямую, если для этого хватает данных. Но сама по себе прямая ещё не означает, что зависимость хорошая.

Представьте облако точек, разбросанных как попало. Через них всё равно можно провести линию. Вопрос в другом: описывает ли эта линия данные хоть сколько-нибудь нормально?

Для проверки в PostgreSQL есть REGR_R2 и 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_R2 показывает, насколько хорошо линейная модель объясняет разброс данных. Значение находится в диапазоне от 0 до 1.

Примерная бытовая интерпретация такая:

• значение около 0 — линия почти ничего не объясняет;
• значение ближе к 1 — точки лучше ложатся на прямую;
• значение где-то посередине — зависимость есть, но относиться к ней нужно осторожно.

CORR показывает корреляцию между двумя числовыми значениями. Она может быть от -1 до 1.

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

Например, если REGR_SLOPE показывает рост, но REGR_R2 почти равен нулю, радоваться рано. Формально наклон есть, но линия плохо описывает данные.

Что происходит с NULL

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

Например, такая строка попадёт в расчёт:

amount = 5000
x = 1770000000

А такие строки будут пропущены:

amount = NULL
x = 1770000000

amount = 5000
x = NULL

Именно поэтому рядом с регрессией полезно выводить REGR_COUNT.

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 sample_size
FROM orders
WHERE status = 'paid';

COUNT(*) покажет все строки после фильтра.

А REGR_COUNT(y, x) покажет только те строки, где одновременно есть и y, и x.

Для аналитики это важная разница. Можно думать, что вы строите тренд по десяти тысячам заказов, а на самом деле в расчёт попали только двести строк, потому что в остальных не было суммы или даты.

Когда REGR_SLOPE вернёт NULL

Иногда результатом будет NULL, и это нормально.

Например, если все значения x одинаковые, наклон посчитать невозможно.

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

В такой ситуации REGR_SLOPE вернёт NULL.

То же самое возможно, если после исключения NULL осталось слишком мало пригодных пар данных.

Поэтому в практических запросах хорошо смотреть сразу несколько показателей:

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

Так вы видите не только саму линию, но и то, насколько вообще прилично её было строить.

Линейная регрессия видит только прямую

У REGR_SLOPE и REGR_INTERCEPT есть важное ограничение: они подбирают прямую линию.

А жизнь часто устроена сложнее.

Например:

• продажи могут расти по выходным и падать в будни;
• в декабре может быть сильный сезонный всплеск;
• новый продукт может сначала расти быстро, а потом выйти на плато;
• после рекламной кампании может быть резкий скачок, который испортит общий тренд;
• старые данные могут уже не описывать текущую ситуацию.

Линейная регрессия всё это не понимает. Она берёт облако точек и проводит через него одну прямую.

Поэтому такие функции хороши для первого взгляда на данные:

Есть ли общий рост? Есть ли общее падение? Насколько примерно меняется показатель?

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

Чем REGR_SLOPE отличается от обычных агрегатов

Обычные агрегаты отвечают на простые вопросы:

SELECT
    count(*) AS orders_count,
    sum(amount) AS total_amount,
    avg(amount) AS avg_amount
FROM orders
WHERE status = 'paid';

Такой запрос говорит:

• сколько было заказов;
• какая общая сумма;
• какой средний чек.

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

А регрессионные функции отвечают уже на другой вопрос:

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

Этот запрос спрашивает:

Как сумма заказа меняется во времени?

То есть SUM, AVG и COUNT описывают состояние.

А REGR_SLOPE помогает увидеть направление.

Как это выглядит в аналитической задаче

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

Можно собрать компактный отчёт:

SELECT
    REGR_COUNT(amount, extract(epoch from created_at)) AS sample_size,
    avg(amount) AS avg_amount,
    REGR_SLOPE(amount, extract(epoch from created_at)) * 86400 AS amount_per_day,
    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';

Такой запрос даёт сразу несколько уровней понимания:

• sample_size — сколько точек участвовало в расчёте;
• avg_amount — средний размер оплаченного заказа;
• amount_per_day — примерное изменение суммы заказа за день;
• r_squared — насколько хорошо прямая описывает данные;
• correlation — насколько сильна линейная связь.

Это уже похоже не на голое число, а на маленький аналитический вывод прямо из SQL.

Отличия в других СУБД

В PostgreSQL порядок аргументов такой:

REGR_SLOPE(y, x)
REGR_INTERCEPT(y, x)

То есть сначала показатель, потом объясняющая переменная.

В других СУБД ситуация может отличаться.

В MySQL функций семейства REGR_* нет. Наклон и свободный коэффициент обычно считают вручную через SUM, AVG и формулы линейной регрессии.

Формула наклона выглядит так:

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). У неё порядок аргументов обратный по сравнению с PostgreSQL: сначала x, потом y. Функция возвращает пару значений: наклон и свободный коэффициент.

В BigQuery встроенных REGR_SLOPE и REGR_INTERCEPT нет. Там коэффициенты можно посчитать вручную через агрегаты вроде SUM, AVG, COVAR_POP и VAR_POP, либо использовать BigQuery ML и обучать модель через CREATE MODEL.

В Snowflake функции REGR_SLOPE(y, x) и REGR_INTERCEPT(y, x) поддерживаются в привычном для PostgreSQL порядке.

Главный вывод простой: при переходе между СУБД всегда проверяйте порядок аргументов. Ошибка не обязательно сломает запрос, но может полностью испортить смысл результата.

Главное из статьи

REGR_SLOPE и REGR_INTERCEPT в PostgreSQL позволяют построить простую линию тренда прямо в SQL.

Они возвращают коэффициенты для формулы:

y = slope * x + intercept

Главное правило — не перепутать аргументы:

REGR_SLOPE(y, x)
REGR_INTERCEPT(y, x)

Сначала идёт то, что мы предсказываем или изучаем. Потом — то, от чего это зависит.

Для дат и времени ось x нужно превращать в число, например через extract(epoch from created_at).

Рядом с регрессией почти всегда полезно выводить REGR_COUNT, потому что строки с NULL в y или x не участвуют в расчёте.

Чтобы понять, насколько линии можно доверять, смотрите на REGR_R2 и CORR.

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