VARIANCE в SQL: как посчитать разброс данных

VARIANCE в SQL считает дисперсию — число, которое показывает, насколько сильно значения отклоняются от среднего.

Проще говоря, среднее отвечает на вопрос:

«Какое значение типичное?»

А дисперсия отвечает на другой вопрос:

«Насколько значения разбросаны вокруг этого среднего?»

Представьте две команды с одинаковой средней зарплатой — 100 000 рублей.

В первой команде зарплаты такие:

employee	salary
A	95 000
B	100 000
C	105 000

Во второй такие:

employee	salary
A	30 000
B	100 000
C	170 000

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

В SQL дисперсию используют, когда нужно понять, насколько «гуляют» данные: суммы заказов, зарплаты, оценки пользователей, время ответа сервера, длительность доставки, количество покупок и любые другие числовые метрики.

Что такое дисперсия простыми словами

Дисперсия считается так:

1. SQL берёт среднее значение.
2. Для каждой строки считает отклонение от среднего.
3. Каждое отклонение возводит в квадрат.
4. Находит среднее по этим квадратам.

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

Например, если одно значение на 10 меньше среднего, а другое на 10 больше, простая сумма отклонений даст ноль. Но разброс-то есть. Поэтому отклонения сначала превращают в квадраты.

Допустим, у нас есть три заказа:

order_id	amount
1	100
2	200
3	300

Среднее значение — 200.

Отклонения:

amount	deviation
100	-100
200	0
300	100

Квадраты отклонений:

amount	squared_deviation
100	10 000
200	0
300	10 000

Дальше SQL усредняет эти квадраты. Получается число, которое описывает разброс.

Важно: дисперсия измеряется в квадрате исходных единиц. Если суммы заказов были в рублях, дисперсия будет в «рублях в квадрате». Звучит не очень удобно для отчёта, но полезно для расчётов.

Базовый пример

Допустим, есть таблица orders:

id	status	amount
1	paid	100
2	paid	200
3	paid	300
4	cancelled	50
5	cancelled	500

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

SELECT
  variance(amount) AS amount_variance
FROM orders;

Запрос вернёт одно число: общий разброс значений в столбце amount.

Но чаще дисперсию считают не по всей таблице, а по группам:

SELECT
  status,
  count(*) AS orders_count,
  variance(amount) AS amount_variance
FROM orders
GROUP BY status
ORDER BY amount_variance DESC NULLS LAST;

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

В PostgreSQL есть две дисперсии

Главное, что нужно запомнить: в PostgreSQL есть не одна, а две основные функции для дисперсии.

Функция	Что считает	Деление
VAR_POP	дисперсия генеральной совокупности	на N
VAR_SAMP	выборочная дисперсия	на N - 1
VARIANCE	псевдоним для VAR_SAMP	на N - 1

То есть в PostgreSQL VARIANCE — это не «просто дисперсия вообще». Это именно выборочная дисперсия.

SELECT
  var_samp(amount) AS sample_variance,
  var_pop(amount) AS population_variance,
  variance(amount) AS variance_alias
FROM orders;

В PostgreSQL variance(amount) даёт тот же результат, что и var_samp(amount).

Это важная деталь, потому что в разных СУБД поведение может отличаться. Например, в MySQL VARIANCE является синонимом VAR_POP, то есть делит на N, а не на N - 1. В ClickHouse похожие функции называются varSamp и varPop.

Поэтому для понятного и переносимого кода лучше не писать «голый» VARIANCE, если важна точная методология. Лучше явно выбрать нужную функцию: VAR_SAMP или VAR_POP.

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

VAR_POP используют, когда строки в запросе — это вся интересующая нас совокупность.

Например:

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

То есть вы не оцениваете большой мир по маленькому кусочку данных. Вы прямо говорите: «Вот все данные, которые меня интересуют».

Пример:

SELECT
  status,
  count(*) AS orders_count,
  round(var_pop(amount)::numeric, 2) AS amount_variance
FROM orders
GROUP BY status
ORDER BY amount_variance DESC NULLS LAST;

Такой запрос считает разброс сумм заказов внутри каждого статуса, считая каждую группу полной совокупностью.

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

VAR_SAMP используют, когда строки в запросе — это выборка из большего множества.

Например:

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

В таких случаях обычно берут выборочную дисперсию:

SELECT
  status,
  count(*) AS orders_count,
  round(var_samp(amount)::numeric, 2) AS amount_variance
FROM orders
GROUP BY status
ORDER BY amount_variance DESC NULLS LAST;

Почему там деление на N - 1, а не на N?

Это называется поправкой Бесселя. Идея такая: если у нас есть только выборка, она часто немного недооценивает настоящий разброс в полной совокупности. Деление на N - 1 помогает исправить это смещение.

Для новичка достаточно запомнить практическое правило:

Если перед вами не все возможные данные, а только наблюдения или часть данных, чаще всего нужен VAR_SAMP.

Почему разница особенно важна на маленьких группах

На больших объёмах данных разница между N и N - 1 становится почти незаметной.

Например, если строк 100 000, делить на 100 000 или на 99 999 — почти одно и то же.

А вот на маленьких группах разница может быть огромной.

Если в группе всего две строки, то:

• VAR_POP делит на 2;
• VAR_SAMP делит на 1.

То есть VAR_SAMP получится ровно в два раза больше VAR_POP.

Это особенно важно в отчётах с большим количеством маленьких сегментов: по городам, категориям, менеджерам, статусам, тарифам. Если в некоторых группах мало строк, выбор функции может заметно изменить результат.

VARIANCE и STDDEV: в чём связь

Дисперсия тесно связана со стандартным отклонением.

Стандартное отклонение — это квадратный корень из дисперсии.

В SQL это выглядит так:

SELECT
  var_samp(salary) AS salary_variance,
  stddev_samp(salary) AS salary_stddev,
  sqrt(var_samp(salary)) AS salary_stddev_check
FROM employees;

stddev_samp(salary) и sqrt(var_samp(salary)) должны дать одинаковый результат.

Есть такая же пара для полной совокупности:

SELECT
  var_pop(salary) AS salary_variance,
  stddev_pop(salary) AS salary_stddev,
  sqrt(var_pop(salary)) AS salary_stddev_check
FROM employees;

Разница между ними простая:

Дисперсия	Стандартное отклонение
VAR_SAMP	STDDEV_SAMP
VAR_POP	STDDEV_POP

Почему в отчётах чаще показывают STDDEV

Допустим, мы считаем разброс зарплат.

Зарплаты измеряются в рублях. А дисперсия — в рублях в квадрате. Для человека это неудобно.

Если дисперсия зарплаты равна 2500000000, это плохо читается. А если стандартное отклонение равно 50000, сразу понятнее: типичное отклонение от среднего примерно 50 000 рублей.

Поэтому в дашбордах, таблицах и аналитических отчётах людям чаще показывают стандартное отклонение:

SELECT
  department,
  round(avg(salary)::numeric, 2) AS avg_salary,
  round(stddev_samp(salary)::numeric, 2) AS salary_stddev
FROM employees
GROUP BY department
ORDER BY salary_stddev DESC NULLS LAST;

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

Как VARIANCE работает с NULL

VARIANCE, VAR_SAMP и VAR_POP игнорируют NULL.

Это значит, что SQL считает дисперсию только по непустым значениям.

Например:

id	amount
1	100
2	200
3	NULL
4	300

Для дисперсии здесь будет не четыре значения, а три: 100, 200, 300.

SELECT
  count(*) AS rows_count,
  count(amount) AS values_count,
  var_samp(amount) AS amount_variance
FROM orders;

count(*) посчитает все строки.

count(amount) посчитает только строки, где amount не равен NULL.

И именно count(amount) ближе к тому количеству значений, которое участвует в расчёте дисперсии.

Ловушка одиночных групп

У VAR_SAMP есть важная особенность: если в группе всего одно непустое значение, функция вернёт NULL.

Почему?

Потому что выборочная дисперсия делит на N - 1. Если значение одно, получается деление на ноль.

А вот VAR_POP в такой ситуации вернёт 0, потому что в полной совокупности из одного значения разброса нет.

SELECT
  department,
  count(salary) AS salary_count,
  var_samp(salary) AS sample_variance,
  var_pop(salary) AS population_variance
FROM employees
GROUP BY department
ORDER BY department;

Если в каком-то отделе только один сотрудник с заполненной зарплатой, результат будет таким:

department	salary_count	sample_variance	population_variance
support	1	NULL	0

Это не ошибка SQL. Это математический смысл функции.

Если вы строите отчёт, где одиночные группы не должны попадать в анализ, можно отфильтровать их через HAVING:

SELECT
  department,
  count(salary) AS salary_count,
  var_samp(salary) AS salary_variance
FROM employees
GROUP BY department
HAVING count(salary) > 1
ORDER BY salary_variance DESC;

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

Не считайте дисперсию по средним без необходимости

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

Иногда это нужно, но важно понимать: это уже другая метрика.

Например, такой запрос считает не разброс заказов, а разброс средних значений по пользователям:

WITH user_avg_orders AS (
  SELECT
    user_id,
    avg(amount) AS avg_amount
  FROM orders
  GROUP BY user_id
)
SELECT
  var_samp(avg_amount) AS variance_of_user_averages
FROM user_avg_orders;

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

Но это не то же самое, что дисперсия всех заказов:

SELECT
  var_samp(amount) AS variance_of_orders
FROM orders;

Первый запрос отвечает на вопрос:

«Насколько отличаются средние чеки пользователей?»

Второй отвечает на вопрос:

«Насколько отличаются суммы отдельных заказов?»

Это разные вопросы. Подменять один другим нельзя.

Пример: разброс среднего чека по статусам

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

SELECT
  status,
  count(amount) AS orders_count,
  round(avg(amount)::numeric, 2) AS avg_amount,
  round(stddev_samp(amount)::numeric, 2) AS amount_stddev,
  round(var_samp(amount)::numeric, 2) AS amount_variance
FROM orders
GROUP BY status
HAVING count(amount) > 1
ORDER BY amount_stddev DESC;

Здесь мы выводим сразу несколько показателей:

• orders_count — сколько непустых сумм попало в расчёт;
• avg_amount — средняя сумма заказа;
• amount_stddev — стандартное отклонение, удобное для чтения;
• amount_variance — дисперсия, полезная как математическая величина.

Для бизнес-отчёта чаще всего достаточно avg_amount и amount_stddev. Дисперсию можно оставить для проверки или более глубокого анализа.

Пример: стабильность времени ответа сервера

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

id	endpoint	response_ms
1	/api/orders	120
2	/api/orders	130
3	/api/orders	800
4	/api/users	90
5	/api/users	95

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

Посмотрим, где время ответа менее стабильное:

SELECT
  endpoint,
  count(response_ms) AS requests_count,
  round(avg(response_ms)::numeric, 2) AS avg_response_ms,
  round(stddev_samp(response_ms)::numeric, 2) AS response_stddev_ms,
  round(var_samp(response_ms)::numeric, 2) AS response_variance
FROM api_logs
GROUP BY endpoint
HAVING count(response_ms) > 1
ORDER BY response_stddev_ms DESC;

Если у одного эндпоинта среднее 150 ms, но большое стандартное отклонение, это сигнал: иногда он отвечает быстро, а иногда резко тормозит. Среднее такое поведение может скрыть, а разброс — показать.

Тип результата и точность

В PostgreSQL тип результата зависит от типа входных данных.

Если вы считаете дисперсию по значениям с плавающей точкой, результат будет типа double precision.

Если входные данные целочисленные или numeric, результат будет numeric.

Для обычной аналитики это часто не проблема. Но если вы работаете с большими денежными суммами и вам важна стабильная точность, лучше явно привести значение к numeric:

SELECT
  var_samp(amount::numeric) AS stable_variance
FROM orders
WHERE status = 'paid';

Так вы снижаете риск неприятных эффектов, связанных с вычислениями через числа с плавающей точкой.

VARIANCE в разных СУБД

С названием VARIANCE нужно быть аккуратным.

В PostgreSQL:

SELECT
  variance(amount) AS amount_variance
FROM orders;

Это то же самое, что:

SELECT
  var_samp(amount) AS amount_variance
FROM orders;

А в MySQL VARIANCE соответствует популяционной дисперсии, то есть ближе к VAR_POP.

Из-за этого один и тот же запрос с VARIANCE может дать разные числа в PostgreSQL и MySQL.

Поэтому хорошая привычка — писать явно:

SELECT
  var_samp(amount) AS sample_variance,
  var_pop(amount) AS population_variance
FROM orders;

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

Когда VARIANCE действительно полезна

VARIANCE помогает там, где среднего недостаточно.

Среднее может сказать:

«В среднем всё хорошо».

А разброс может показать:

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

Примеры:

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

Дисперсия и стандартное отклонение помогают увидеть эту нестабильность.

Главное

VARIANCE в SQL считает дисперсию — меру разброса значений вокруг среднего.

В PostgreSQL VARIANCE является псевдонимом VAR_SAMP, то есть считает выборочную дисперсию с делением на N - 1.

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

NULL игнорируются. На одной непустой строке VAR_SAMP возвращает NULL, а VAR_POP возвращает 0.

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

И главное: не используйте VARIANCE вслепую при переносе запросов между СУБД. В PostgreSQL и MySQL это название означает разные формулы. Лучше явно писать VAR_SAMP или VAR_POP, чтобы смысл запроса был понятен сразу.