STDDEV no SQL: STDDEV_SAMP vs STDDEV_POP e deteccao de outliers

O desvio padrao mede o quanto os valores se espalham em torno da media: pequeno significa que os dados se agrupam perto de AVG, grande significa que se dispersam. No SQL nao e uma funcao so, mas um par: STDDEV_SAMP para uma amostra e STDDEV_POP para a populacao inteira, e a diferenca importa mais do que parece.

Duas funcoes: amostra e populacao

Ambas medem a dispersao, mas dividem a soma dos desvios ao quadrado por uma contagem diferente. STDDEV_POP divide por n (a populacao completa), STDDEV_SAMP divide por n - 1 (uma amostra, correcao de Bessel). O STDDEV puro no PostgreSQL e sinonimo de 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;

Em conjuntos pequenos, dividir por n - 1 infla o resultado de forma perceptivel em relacao a n; em conjuntos grandes a diferenca tende a zero.

• Use STDDEV_SAMP (ou apenas STDDEV) quando as linhas sao uma amostra de algo maior: um mes de pedidos como representante do comportamento dos clientes.
• Use STDDEV_POP quando as linhas sao exatamente tudo o que voce estuda: a dispersao salarial entre todos os funcionarios atuais.

media +/- sd: combinacao com AVG

Um desvio sozinho e inutil; ele se le ao lado da media. O relatorio classico mostra AVG mais uma faixa de "media mais ou menos um desvio".

-- 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 nao e cosmetico: sobre uma unica linha STDDEV_SAMP retorna NULL, porque o divisor n - 1 vira zero. STDDEV_POP sobre uma unica linha retorna 0.

O coeficiente de variacao (sd / mean) e uma medida adimensional de dispersao que permite comparar grupos cujos valores vivem em escalas diferentes.

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

Deteccao de outliers via z-score

O z-score e "quantos desvios um valor esta da media": (x - mean) / sd. Linhas com |z| > 3 costumam ser tratadas como anomalias. E conveniente calcular as estatisticas com uma funcao de janela e comparar cada linha contra elas numa unica passada.

-- 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) protege contra a divisao por zero: se todos os valores de um grupo sao identicos, o desvio e 0 e, sem a protecao, a consulta quebra. O mesmo truque sinaliza salarios desproporcionais dentro de um departamento.

NULL, tipos e diferencas entre engines

• STDDEV_* ignora NULL, assim como AVG: os nulos nao entram nem na soma nem na contagem. Por isso o n da correcao de Bessel e o numero de valores NAO NULL, e nao o total de linhas.
• Se nenhuma linha sobreviver aos seus filtros, qualquer uma dessas funcoes retorna NULL, nao um erro.
• Pegadinha: confundir STDDEV_SAMP e STDDEV_POP em grupos pequenos. Em 5 linhas, dividir por 4 em vez de 5 e uma mudanca de cerca de 12% na estimativa da dispersao, e o seu limiar de outliers desliza junto. Escolha uma versao e nao as misture num mesmo relatorio.
• O tipo do resultado e double precision, ou seja, um float com erro de arredondamento. Costuma servir para dinheiro, mas nunca o compare por igualdade exata.
• O PostgreSQL oferece os tres nomes. No MySQL, STDDEV e STD sao sinonimos de STDDEV_POP (populacao!), e voce precisa escrever STDDEV_SAMP de forma explicita: o contrario do Postgres. No ClickHouse as funcoes sao stddevSamp e stddevPop.

Resumindo: escolha SAMP para uma amostra e POP para uma populacao completa, leia sempre o desvio ao lado de AVG, proteja a divisao com NULLIF e lembre que o STDDEV puro significa coisas diferentes no Postgres e no MySQL.