SQL STDDEV: STDDEV_SAMP vs STDDEV_POP and Outlier Detection

STDDEV в SQL — это агрегат, который считает стандартное отклонение: насколько значения разъезжаются вокруг среднего AVG. Применяют его к денежным суммам, зарплатам, длительностям и latency, когда нужно одним числом понять, перед вами плотная группа или распределение с широким хвостом. Но STDDEV — не одна функция, а пара: STDDEV_SAMP для выборки и STDDEV_POP для всей совокупности, и выбрать между ними нужно сразу.

Две функции: выборка и популяция

Разница между функциями — в делителе. STDDEV_POP делит на n, как если бы строки были всей совокупностью. STDDEV_SAMP делит на n - 1, применяя поправку Бесселя для выборки. В PostgreSQL голое STDDEV — синоним STDDEV_SAMP.

-- sample vs population spread of order amounts
SELECT STDDEV_SAMP(amount) AS sd_sample,
       STDDEV_POP(amount)  AS sd_population,
       STDDEV(amount)      AS sd_bare      -- equals STDDEV_SAMP
FROM orders;

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

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

Для продуктовой аналитики чаще подходит выборочная версия; для фиксированного списка сотрудников на конкретную дату — популяционная.

mean +/- sd: связка с AVG

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

-- mean and a one-sigma band per country
SELECT u.country,
       COUNT(*)                          AS n,
       AVG(o.amount)                     AS mean,
       STDDEV_SAMP(o.amount)             AS sd,
       AVG(o.amount) - STDDEV_SAMP(o.amount) AS low,
       AVG(o.amount) + STDDEV_SAMP(o.amount) AS high
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country
HAVING COUNT(*) >= 2;

HAVING COUNT(*) >= 2 здесь не украшение. Для одной строки STDDEV_SAMP возвращает NULL, потому что делитель n - 1 равен нулю. STDDEV_POP на одной строке вернёт 0.

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

SELECT dept,
       AVG(salary) AS mean,
       STDDEV_POP(salary) / NULLIF(AVG(salary), 0) AS cv
FROM employees
GROUP BY dept
ORDER BY cv DESC;

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

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

Z-оценка показывает, на сколько стандартных отклонений точка отстоит от среднего: (x - mean) / sd. Часто за грубый порог берут |z| > 3.

-- flag order amounts more than 3 sd from the per-country mean
SELECT *
FROM (
    SELECT o.id,
           o.amount,
           u.country,
           (o.amount - AVG(o.amount) OVER w)
             / NULLIF(STDDEV_SAMP(o.amount) OVER w, 0) AS z
    FROM orders o
    JOIN users u ON u.id = o.user_id
    WINDOW w AS (PARTITION BY u.country)
) t
WHERE ABS(z) > 3;

NULLIF(..., 0) защищает от деления на ноль. Если все суммы в группе одинаковые, стандартное отклонение равно 0, и без защиты расчёт z-score сломается. Для поиска выбросов это не формальность, а обязательная страховка.

NULL, типы и различия СУБД

STDDEV_* игнорирует NULL, как и AVG: они не входят ни в сумму, ни в счётчик. Поэтому n — это число непустых значений, а не общее число строк. Если после фильтров не осталось данных, результат будет NULL.

Грабли — смешивать STDDEV_SAMP и STDDEV_POP в одном отчёте. На пяти строках разница между делением на 4 и на 5 меняет оценку примерно на двузначные проценты, а вместе с ней и границы выбросов. Зафиксируйте одну версию в методологии.

Результат STDDEV_* для вещественных входов имеет тип double precision, то есть float; на равенство его сравнивать не стоит. В PostgreSQL доступны все три имени. В MySQL STDDEV и синоним STD означают STDDEV_POP, а выборочную версию приходится писать явно как STDDEV_SAMP — то есть голое STDDEV в Postgres и MySQL даёт разный делитель. В ClickHouse функции называются stddevSamp и stddevPop.

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

Коротко: STDDEV_SAMP для выборки, STDDEV_POP для полной совокупности, отклонение читаем рядом с AVG, деление в z-score защищаем через NULLIF, а голое STDDEV в разных СУБД означает разный делитель.