Материалы nkhcode

Appearance

Агрегатные функции для статистических вычислений ​

Агрегатные функции для статистических вычислений — это специальные агрегатные функции SQL, которые выполняют математико‑статистическую обработку набора строк и возвращают одно обобщённое значение: дисперсию, стандартное отклонение, ковариацию, корреляцию или параметры линейной регрессии.
В PostgreSQL эти функции реализованы на уровне СУБД и работают напрямую с данными таблиц, без необходимости выгружать данные во внешние аналитические инструменты.

Для каких задач используются ​

Статистические агрегатные функции применяются, когда необходимо:

  • анализировать разброс значений (дисперсия, стандартное отклонение)
  • сравнивать изменчивость выборок
  • измерять взаимосвязь между величинами (корреляция, ковариация)
  • строить простые линейные модели прямо в SQL (регрессионный анализ)
  • выполнять аналитику и отчётность на уровне БД

Типичные сценарии:

  • анализ выручки по дням
  • исследование зависимости суммы платежей от различных факторов
  • оценка стабильности продаж
  • подготовка данных для BI‑отчётов

Особенности статистических агрегатных функций:

  • работают по группам строк (через GROUP BY или по всей таблице)
  • игнорируют NULL значения
  • делятся на функции для генеральной совокупности (*_pop) и выборки (*_samp)
  • многие функции применимы только к числовым типам данных
  • регрессионные функции требуют двух числовых аргументов: y, x

corr ( Y double precision, X double precision ) → double precision ​

Коэффициент корреляции Пирсона.
Диапазон значений: от -1 до 1.
Интерпретация:

  • 1 — идеальная положительная связь
  • 0 — линейной связи нет
  • -1 — идеальная отрицательная связь
sql
-- Условие задачи:
-- Проверить, существует ли линейная зависимость между размером платежа и 
-- порядковым номером аренды, и определить направление и силу этой связи.
select corr(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
corr                 |
---------------------+
0.0010368499492238476|

covar_pop ( Y double precision, X double precision ) → double precision ​

Ковариация генеральной совокупности.

sql
-- Условие задачи:
-- Оценить ковариацию между суммой платежа и порядковым номером аренды,
-- рассматривая данные как полную генеральную совокупность.
select covar_pop(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
covar_pop         |
------------------+
11.350691632324873|

covar_samp ( Y double precision, X double precision ) → double precision ​

Вычисляет выборочную ковариацию между двумя величинами.
Что показывает:

  • положительное значение — величины растут вместе
  • отрицательное — одна растёт, другая падает
sql
-- Условие задачи:
-- Определить, изменяются ли сумма платежа и порядковый номер аренды
-- согласованно (в одном направлении или в противоположных)
-- в рамках выборки данных.
select covar_samp(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
covar_samp       |
-----------------+
11.35139892866288|

regr_avgx ( Y double precision, X double precision ) → double precision ​

Среднее значение независимой переменной x.

sql
-- Условие задачи:
-- Вычислить среднее значение независимой переменной (x),
-- используемой в линейной регрессионной модели.
select regr_avgx(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
regr_avgx|
---------+
   8025.0|

regr_avgy ( Y double precision, X double precision ) → double precision ​

Среднее значение зависимой переменной y.

sql
-- Условие задачи:
-- Определить среднее значение зависимой переменной (y),
-- которое используется при построении регрессионной модели.
select regr_avgy(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
regr_avgy        |
-----------------+
4.200667331297407|

regr_count ( Y double precision, X double precision ) → bigint ​

Количество пар значений (x, y), участвующих в регрессии. Полезно для проверки достаточности данных.

sql
-- Условие задачи:
-- Определить количество пар значений
-- «сумма платежа – порядковый номер аренды»,
-- которые фактически участвуют в регрессионном анализе.
select regr_count(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
regr_count|
----------+
     16049|

regr_intercept ( Y double precision, X double precision ) → double precision ​

Значение y, когда x = 0.

sql
-- Условие задачи:
-- Найти значение зависимой переменной,
-- которое прогнозирует модель при x = 0.
select regr_intercept(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
regr_intercept   |
-----------------+
4.196423553269369|

regr_r2 ( Y double precision, X double precision ) → double precision ​

Коэффициент детерминации R².
Показывает долю вариации y, объясняемую моделью.
Диапазон: от 0 до 1.

sql
-- Условие задачи:
-- Оценить, какую долю вариации суммы платежей
-- объясняет линейная регрессионная модель.
select regr_r2(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
regr_r2           |
------------------+
0.0000010750578172|

regr_slope ( Y double precision, X double precision ) → double precision ​

Коэффициент наклона линии линейной регрессии.
Интерпретация: показывает, насколько в среднем изменяется y при увеличении x на 1.

sql
-- Условие задачи:
-- Определить, на сколько в среднем изменяется сумма платежа
-- при увеличении независимой переменной на одну единицу.
select regr_slope(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
regr_slope       |
-----------------+
0.000000528819692|

regr_sxx ( Y double precision, X double precision ) → double precision ​

Сумма квадратов отклонений x от среднего.
Используется при вычислении коэффициента наклона регрессии.

sql
-- Условие задачи:
-- Рассчитать суммарный разброс независимой переменной
-- относительно её среднего значения,
-- необходимый для вычисления коэффициента наклона регрессии.
select regr_sxx(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
regr_sxx    |
------------+
344478945800|

regr_sxy ( Y double precision, X double precision ) → double precision ​

Сумма произведений отклонений x и y.

sql
-- Условие задачи:
-- Вычислить совместный разброс зависимой и независимой переменных,
-- который отражает степень их линейной связи.
select regr_sxy(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
regr_sxy         |
-----------------+
182167.2500071819|

regr_syy ( Y double precision, X double precision ) → double precision ​

Сумма квадратов отклонений y от среднего.

sql
-- Условие задачи:
-- Оценить суммарный разброс зависимой переменной
-- относительно среднего значения.
select regr_syy(amount, row_number)
from (
	select p.amount, row_number() over (order by r.rental_date)
	from payment p 
	join rental r on r.rental_id = p.rental_id);
text
regr_syy         |
-----------------+
89607.85875288144|

stddev ( числовой_тип ) → double precision для real или double precision, иначе numeric ​

Стандартное отклонение выборки (квадратный корень из дисперсии). Является синонимом stddev_samp().
Показывает среднее отклонение значений от среднего в тех же единицах измерения, что и исходные данные.

sql
-- Условие задачи:
-- Определить среднее отклонение сумм платежей от их среднего значения
select stddev(amount)
from payment;
text
stddev            |
------------------+
2.3629938536099744|

stddev_pop ( numeric_type ) → double precision для real или double precision, иначе numeric ​

Стандартное отклонение генеральной совокупности.

sql
-- Условие задачи:
-- Вычислить стандартное отклонение сумм платежей
-- для всей генеральной совокупности данных.
select stddev_pop(amount)
from payment;
text
stddev_pop        |
------------------+
2.3629202343606813|

stddev_samp ( числовой_тип ) → double precision для real или double precision, иначе numeric ​

Стандартное отклонение выборки. Наиболее часто используемая мера разброса в аналитике.

sql
-- Условие задачи:
-- Рассчитать стандартное отклонение сумм платежей как основной
-- показатель разброса значений в выборке.
select stddev_samp(amount)
from payment;
text
stddev_samp       |
------------------+
2.3629938536099744|

variance ( numeric_type ) → double precision для real или double precision, иначе numeric ​

Вычисляет дисперсию выборки. Является синонимом var_samp().
Показывает насколько сильно значения отклоняются от среднего. Чем больше значение дисперсии, тем выше разброс данных.

Когда использовать:

  • при анализе нестабильности платежей
  • для сравнения разброса значений между разными группами
sql
-- Условие задачи:
-- Оценить общий разброс сумм платежей, чтобы понять,
-- насколько сильно отдельные платежи отклоняются от среднего значения.
select variance(amount)
from payment;
text
variance          |
------------------+
5.5837399521985169|

var_pop ( numeric_type ) → double precision для real или double precision, иначе numeric ​

Дисперсия генеральной совокупности.
Отличие от var_samp: деление происходит на n, а не n - 1.
Используется если данные охватывают всю совокупность (редкий случай в реальной аналитике).

sql
-- Условие задачи:
-- Определить дисперсию сумм платежей,
-- предполагая, что все записи таблицы образуют генеральную совокупность.
select var_pop(amount)
from payment;
text
var_pop           |
------------------+
5.5833920339511371|

var_samp ( числовой_тип ) → double precision для real или double precision, иначе numeric ​

Вычисляет выборочную дисперсию.
Особенность: использует деление на n - 1, что делает оценку дисперсии статистически корректной для выборок.

sql
-- Условие задачи:
-- Рассчитать выборочную дисперсию сумм платежей
-- для статистически корректной оценки вариативности данных.
select var_samp(amount)
from payment;
text
var_samp          |
------------------+
5.5837399521985169|

Частые ошибки и подводные камни ​

Путаница между выборкой и генеральной совокупностью
Одна из самых распространённых ошибок — использовать функции для генеральной совокупности (var_pop, stddev_pop) там, где анализируется выборка, а не все возможные данные.

  • *_samp — применяются, когда данные являются выборкой
  • *_pop — когда данные описывают всю совокупность

В аналитике и отчётах чаще всего корректнее использовать именно выборочные функции.

Неочевидное игнорирование NULL
Все статистические агрегатные функции автоматически игнорируют NULL значения.
Это может привести к неожиданным результатам, если NULL используется как «0 по смыслу».

Использование нечисловых столбцов
Статистические агрегаты работают только с числовыми типами данных.
Если анализируется время или дата, их необходимо преобразовать (например, в число дней или секунд).

Ошибки в порядке аргументов регрессионных функций
У регрессионных функций всегда используется порядок: regr_*(y, x)
Где:

  • y — зависимая переменная
  • x — независимая переменная

Перепутанный порядок аргументов не вызовет ошибку, но приведёт к неверной интерпретации результата.

Корреляция ≠ причинно-следственная связь
Функция corr() показывает степень линейной связи, но:

  • не доказывает причинность
  • чувствительна к выбросам
  • отражает только линейную зависимость

Высокая корреляция не означает, что одна величина вызывает другую.

Заключение ​

Статистические агрегатные функции PostgreSQL представляют собой инструмент анализа данных, позволяющий выполнять полноценные статистические вычисления непосредственно на уровне базы данных. Использование этих функций делает возможным переход от простых агрегатов (таких как COUNT, SUM и AVG) к более глубокому анализу распределений, взаимосвязей и закономерностей в данных.

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

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