STDDEV в SQL: как измерить разброс значений вокруг среднего

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

Среднее значение отвечает на вопрос:

«Какая сумма, зарплата или длительность у нас получается в среднем?»

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

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

Представьте две группы заказов.

Первая группа:

order_id	amount
1	900
2	1000
3	1100

Вторая группа:

order_id	amount
1	100
2	1000
3	1900

В обеих группах среднее значение одинаковое — 1000. Но данные ведут себя по-разному. В первой группе суммы почти рядом. Во второй — значения сильно разъехались.

Вот это «разъехались» и помогает измерить стандартное отклонение.

Где пригодится STDDEV

STDDEV используют, когда одного среднего мало.

Например:

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

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

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

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

Простой пример

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

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  user_id bigint,
  status text NOT NULL,
  amount numeric NOT NULL
);

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

SELECT
  avg(amount) AS avg_amount,
  stddev(amount) AS amount_stddev
FROM orders;

Результат может быть таким:

avg_amount	amount_stddev
1000	80

Это значит: средняя сумма заказа — 1000, а типичное отклонение от среднего — примерно 80.

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

А если результат такой:

avg_amount	amount_stddev
1000	900

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

STDDEV удобнее дисперсии

Стандартное отклонение связано с дисперсией.

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

В SQL это можно проверить так:

SELECT
  var_samp(amount) AS amount_variance,
  stddev_samp(amount) AS amount_stddev,
  sqrt(var_samp(amount)) AS amount_stddev_check
FROM orders;

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

Почему в отчётах чаще показывают именно STDDEV, а не VARIANCE?

Потому что стандартное отклонение измеряется в тех же единицах, что и исходные данные.

Если amount — это рубли, то STDDEV тоже будет в рублях.

А дисперсия будет в «рублях в квадрате», что для обычного человека звучит странно и плохо читается в дашборде.

Поэтому для аналитического отчёта обычно показывают:

SELECT
  avg(amount) AS avg_amount,
  stddev_samp(amount) AS amount_stddev
FROM orders;

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

Две версии STDDEV: SAMP и POP

В SQL есть две основные версии стандартного отклонения:

Функция	Что считает	Когда использовать
STDDEV_SAMP	выборочное стандартное отклонение	когда строки — выборка из большего процесса
STDDEV_POP	стандартное отклонение полной совокупности	когда строки — все данные, которые нас интересуют

В PostgreSQL есть ещё короткая запись:

STDDEV(x)

В PostgreSQL STDDEV — это синоним STDDEV_SAMP.

То есть эти две записи дают одинаковый результат:

SELECT
  stddev_samp(amount) AS sd_sample,
  stddev(amount) AS sd_bare
FROM orders;

В чём разница между SAMP и POP

Разница внутри формулы.

STDDEV_POP считает так, будто перед нами вся совокупность. Поэтому он делит на n.

STDDEV_SAMP считает так, будто перед нами выборка из большего множества. Поэтому он использует поправку Бесселя и делит на n - 1.

Звучит математически, но практический смысл простой.

Если у вас есть все интересующие строки, берите STDDEV_POP.

Например:

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

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

Например:

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

Для продуктовой аналитики, A/B-тестов, логов и наблюдений чаще подходит STDDEV_SAMP.

Для фиксированного полного списка — STDDEV_POP.

Почему это важно на маленьких группах

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

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

Но если строк мало, разница становится заметной.

Например, в группе 5 строк:

• STDDEV_POP делит на 5;
• STDDEV_SAMP делит на 4.

Это уже ощутимо меняет результат.

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

Плохая ситуация: в одном блоке отчёта используется выборочная версия, в другом — популяционная, а потом цифры сравнивают между собой. Формально SQL всё посчитал, но смысл сравнения становится мутным.

Пример: средний чек и стандартное отклонение по странам

Допустим, у нас есть таблицы orders и users.

CREATE TABLE users (
  id bigint PRIMARY KEY,
  country text NOT NULL
);

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  status text NOT NULL,
  amount numeric NOT NULL
);

Посчитаем средний чек и стандартное отклонение по странам:

SELECT
  u.country,
  count(*) AS orders_count,
  avg(o.amount) AS avg_amount,
  stddev_samp(o.amount) AS amount_stddev
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country
HAVING count(*) >= 2
ORDER BY amount_stddev DESC;

Здесь важно условие:

HAVING count(*) >= 2

Для STDDEV_SAMP нужна минимум пара значений. Если в группе всего одна строка, выборочное стандартное отклонение будет NULL, потому что делитель n - 1 станет равен нулю.

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

Среднее плюс-минус стандартное отклонение

Часто STDDEV читают рядом с AVG.

Можно построить простой диапазон:

среднее минус одно стандартное отклонение
среднее плюс одно стандартное отклонение

В статистике это часто называют диапазоном в одну сигму.

SELECT
  u.country,
  count(*) AS orders_count,
  avg(o.amount) AS avg_amount,
  stddev_samp(o.amount) AS amount_stddev,
  avg(o.amount) - stddev_samp(o.amount) AS low_border,
  avg(o.amount) + stddev_samp(o.amount) AS high_border
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country
HAVING count(*) >= 2
ORDER BY amount_stddev DESC;

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

Например:

country	avg_amount	amount_stddev	low_border	high_border
AR	1000	100	900	1100
BR	1000	800	200	1800

Среднее одинаковое, но во второй стране разброс намного шире.

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

Коэффициент вариации: сравниваем разброс разных масштабов

Иногда стандартного отклонения недостаточно.

Допустим, в одном отделе средняя зарплата 50 000, а стандартное отклонение 10 000.

В другом отделе средняя зарплата 300 000, а стандартное отклонение 30 000.

Абсолютно 30 000 больше, чем 10 000. Но относительно среднего в первом отделе разброс сильнее:

• 10 000 / 50 000 = 0.2;
• 30 000 / 300 000 = 0.1.

Для таких случаев используют коэффициент вариации: стандартное отклонение делят на среднее.

SELECT
  dept,
  avg(salary) AS avg_salary,
  stddev_pop(salary) AS salary_stddev,
  stddev_pop(salary) / nullif(avg(salary), 0) AS variation_ratio
FROM employees
GROUP BY dept
ORDER BY variation_ratio DESC;

NULLIF здесь защищает от деления на ноль. Если среднее вдруг равно 0, выражение не упадёт с ошибкой деления на ноль, а вернёт NULL.

Коэффициент вариации полезен, когда нужно сравнить нестабильность групп с разным масштабом: зарплаты, чеки, выручку, длительности, количество событий.

Поиск выбросов через z-score

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

Для этого считают z-score — насколько далеко значение находится от среднего в единицах стандартного отклонения.

Формула такая:

z = (x - mean) / sd

Если z близок к 0, значение рядом со средним.

Если z большой по модулю, значение далеко от среднего.

Часто как грубый ориентир используют правило:

Если абсолютное значение z больше 3, точка может быть выбросом.

Пример: найдём заказы, которые сильно отличаются от среднего заказа в своей стране.

SELECT
  id,
  country,
  amount,
  z_score
FROM (
  SELECT
    o.id,
    u.country,
    o.amount,
    (o.amount - avg(o.amount) OVER w)
      / nullif(stddev_samp(o.amount) OVER w, 0) AS z_score
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE o.status = 'paid'
  WINDOW w AS (PARTITION BY u.country)
) t
WHERE abs(z_score) > 3
ORDER BY abs(z_score) DESC;

Здесь оконные функции считают среднее и стандартное отклонение отдельно внутри каждой страны.

NULLIF снова важен:

nullif(stddev_samp(o.amount) OVER w, 0)

Если все суммы в группе одинаковые, стандартное отклонение будет 0. Делить на ноль нельзя. NULLIF превращает ноль в NULL, и расчёт безопасно возвращает NULL вместо ошибки.

STDDEV и NULL

STDDEV_SAMP, STDDEV_POP и STDDEV игнорируют NULL.

Так же работает и AVG.

Например, если в таблице такие данные:

id	amount
1	100
2	200
3	NULL
4	300

Стандартное отклонение будет считаться только по значениям 100, 200 и 300.

Проверить количество строк и количество значений можно так:

SELECT
  count(*) AS rows_count,
  count(amount) AS values_count,
  avg(amount) AS avg_amount,
  stddev_samp(amount) AS amount_stddev
FROM orders;

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

count(amount) считает только непустые значения.

Именно непустые значения участвуют в расчёте AVG и STDDEV.

Если после фильтров не осталось ни одного непустого значения, результат STDDEV будет NULL.

Ловушка одной строки в группе

Представим таблицу сотрудников:

CREATE TABLE employees (
  id bigint PRIMARY KEY,
  dept text NOT NULL,
  salary numeric NOT NULL
);

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

SELECT
  dept,
  count(salary) AS salary_count,
  stddev_samp(salary) AS salary_stddev_sample,
  stddev_pop(salary) AS salary_stddev_population
FROM employees
GROUP BY dept
ORDER BY dept;

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

dept	salary_count	salary_stddev_sample	salary_stddev_population
support	1	NULL	0

Почему так?

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

Для STDDEV_POP одна строка — это вся совокупность. Разброса внутри одного значения нет, поэтому результат 0.

Если для отчёта нужны только группы, где выборочное отклонение имеет смысл, добавляйте HAVING:

SELECT
  dept,
  count(salary) AS salary_count,
  stddev_samp(salary) AS salary_stddev
FROM employees
GROUP BY dept
HAVING count(salary) >= 2
ORDER BY salary_stddev DESC;

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

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

В PostgreSQL:

SELECT
  stddev(amount) AS amount_stddev
FROM orders;

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

SELECT
  stddev_samp(amount) AS amount_stddev
FROM orders;

То есть PostgreSQL считает выборочное стандартное отклонение.

В MySQL ситуация другая: STDDEV и STD означают популяционное стандартное отклонение, то есть соответствуют STDDEV_POP.

Выборочную версию в MySQL нужно писать явно:

SELECT
  stddev_samp(amount) AS amount_stddev
FROM orders;

В ClickHouse функции называются иначе:

SELECT
  stddevSamp(amount) AS sd_sample,
  stddevPop(amount) AS sd_population
FROM orders;

Поэтому хороший стиль — не полагаться на короткое STDDEV, если запрос может переезжать между СУБД.

Лучше писать явно:

SELECT
  stddev_samp(amount) AS sd_sample,
  stddev_pop(amount) AS sd_population
FROM orders;

Так сразу видно, какую именно формулу вы выбрали.

Тип результата и сравнение float

В PostgreSQL результат STDDEV для вещественных чисел обычно имеет тип double precision.

Это число с плавающей точкой. Такие значения не стоит сравнивать на точное равенство.

Плохая идея:

SELECT
  *
FROM metrics
WHERE stddev_value = 10.0;

Лучше сравнивать через диапазон или округление, в зависимости от задачи:

SELECT
  *
FROM metrics
WHERE stddev_value BETWEEN 9.99 AND 10.01;

Для денежных значений часто удобно работать с numeric, особенно если важна аккуратная точность в отчётах:

SELECT
  round(stddev_samp(amount::numeric), 2) AS amount_stddev
FROM orders
WHERE status = 'paid';

STDDEV не заменяет медиану и перцентили

Стандартное отклонение полезно, но его нельзя читать в одиночку.

Особенно осторожно нужно быть с такими данными:

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

Например, почти все запросы к API отвечают за 100 ms, но один запрос из тысячи зависает на 10000 ms. Среднее и стандартное отклонение могут резко измениться из-за таких редких выбросов.

Поэтому для latency и денежных сумм рядом со STDDEV часто смотрят:

• медиану;
• перцентили;
• минимум и максимум;
• количество наблюдений;
• долю выбросов.

Например, для PostgreSQL можно посчитать медиану через percentile_cont:

SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount,
  avg(amount) AS avg_amount,
  stddev_samp(amount) AS amount_stddev
FROM orders
WHERE status = 'paid';

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

Пример полного отчёта

Соберём отчёт по странам: количество заказов, средний чек, стандартное отклонение, коэффициент вариации и медиану.

SELECT
  u.country,
  count(o.amount) AS orders_count,
  round(avg(o.amount)::numeric, 2) AS avg_amount,
  round(stddev_samp(o.amount)::numeric, 2) AS amount_stddev,
  round(
    (stddev_samp(o.amount) / nullif(avg(o.amount), 0))::numeric,
    4
  ) AS variation_ratio,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY o.amount) AS median_amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country
HAVING count(o.amount) >= 2
ORDER BY variation_ratio DESC;

Такой отчёт уже намного полезнее простого среднего.

Он показывает:

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

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

Главное

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

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

В PostgreSQL STDDEV — это синоним STDDEV_SAMP, то есть выборочное стандартное отклонение. Для полной совокупности используйте STDDEV_POP.

Практическое правило простое:

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

STDDEV игнорирует NULL. Для одной строки STDDEV_SAMP возвращает NULL, а STDDEV_POP возвращает 0.

В z-score и коэффициенте вариации защищайте деление через NULLIF, чтобы не упасть на нулевом среднем или нулевом стандартном отклонении.

И не забывайте: в разных СУБД короткое имя STDDEV может означать разные вещи. В PostgreSQL это выборочная версия, в MySQL — популяционная. Поэтому в серьёзных запросах лучше писать явно: STDDEV_SAMP или STDDEV_POP.