SQL VARIANCE: VAR_SAMP vs VAR_POP and the Link to STDDEV

VARIANCE в SQL — это агрегатная функция, которая считает дисперсию: средний квадрат отклонения значений столбца от их среднего. Берут её, когда нужно числом измерить разброс данных: насколько сильно зарплаты, суммы заказов или времена отклика «гуляют» вокруг среднего. Результат выражен в квадрате исходных единиц, и в PostgreSQL у функции есть две формы — выборочная и популяционная.

Две функции и одно сокращение

В PostgreSQL есть три имени, но две формулы:

• VAR_POP(x) делит сумму квадратов отклонений на N;
• VAR_SAMP(x) делит на N - 1 (поправка Бесселя);
• VARIANCE(x) является псевдонимом VAR_SAMP.

SELECT
  var_samp(amount) AS sample_variance,
  var_pop(amount)  AS pop_variance,
  variance(amount) AS bare_variance   -- equals var_samp
FROM orders;

Главная грабля: VARIANCE не является нейтральной «дисперсией вообще». Это выборочная версия с делением на N - 1. В ClickHouse функции называются varSamp и varPop. В MySQL VARIANCE — синоним именно VAR_POP (деление на N), а не VAR_SAMP, поэтому при переносе запроса между PostgreSQL и MySQL «голый» VARIANCE даст разные числа; для переносимого кода пишите нужную форму явно.

Выборка или совокупность

Выбор между VAR_SAMP и VAR_POP зависит от смысла строк.

• Если в запросе все интересующие объекты, например все заказы конкретного дня, берите VAR_POP.
• Если строки — наблюдение процесса или выборка из большего множества, берите VAR_SAMP.

-- Spread of order amounts per status, treating each group as a sample
SELECT
  status,
  count(*)              AS n,
  round(var_samp(amount)::numeric, 2) AS variance
FROM orders
GROUP BY status
ORDER BY variance DESC NULLS LAST;

На больших N разница между делением на N и N - 1 ничтожна. На маленьких группах она огромна: при N = 2 VAR_SAMP ровно вдвое больше VAR_POP. Поэтому на разреженных сегментах и при множестве мелких групп выбор функции напрямую двигает числа в отчёте.

Если сомневаетесь, что перед вами — полная совокупность или выборка, безопаснее явно взять VAR_SAMP и зафиксировать это в методологии: для оценки разброса по неполным данным выборочная дисперсия даёт несмещённую оценку, тогда как VAR_POP на выборке систематически занижает результат.

Дисперсия — это квадрат стандартного отклонения

STDDEV равен квадратному корню из VARIANCE: STDDEV_SAMP соответствует VAR_SAMP, а STDDEV_POP — VAR_POP. Это удобно для самопроверки запроса и для перехода от расчётной величины к человекочитаемой.

SELECT
  var_samp(salary)             AS variance_salary,
  stddev_samp(salary)          AS stddev_salary,
  sqrt(var_samp(salary))       AS stddev_via_sqrt  -- matches stddev_samp
FROM employees;

Стандартное отклонение измеряется в тех же единицах, что и данные (рубли, секунды), а VARIANCE — в их квадрате. Поэтому «рубли в квадрате» плохо смотрятся в отчёте, но удобны внутри модели — например, когда складывают дисперсии независимых величин или сравнивают разброс разных метрик. Людям в дашборде почти всегда показывают STDDEV, а саму дисперсию оставляют в промежуточных расчётах.

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

NULL игнорируются: VAR_SAMP считает только непустые значения, и N означает именно их количество, а не число строк. На одной строке VAR_SAMP возвращает NULL, потому что N - 1 = 0; VAR_POP в той же ситуации вернёт 0. Это классическая ловушка при группировке: одиночные группы внезапно дают NULL.

-- Single-row group: var_samp is NULL, var_pop is 0
SELECT
  dept,
  count(*)        AS n,
  var_samp(salary) AS vs,   -- NULL when n = 1
  var_pop(salary)  AS vp    -- 0 when n = 1
FROM employees
GROUP BY dept;

Ещё одна грабля — считать дисперсию по уже агрегированным средним. VARIANCE нужно брать от исходных наблюдений; дисперсия групповых средних — это разброс средних, а не разброс данных, и подменять одно другим нельзя.

Тип результата зависит от входа: для float-значений это double precision, для целочисленных и numeric-входов — numeric. На больших денежных суммах путь через float теряет точность, поэтому приводите вход к numeric, если важна стабильность.

-- Cast to numeric for stable variance on large monetary values
SELECT var_samp(amount::numeric) AS stable_variance
FROM orders
WHERE status = 'paid';

Вывод: VAR_SAMP и VARIANCE в PostgreSQL используйте для выборок, VAR_POP — для полной совокупности. На одиночных группах ждите NULL, для человекочитаемых отчётов переходите к STDDEV через корень, а исходные данные не заменяйте средними. И помните про разную семантику VARIANCE в MySQL.