VARIANCE no SQL: VAR_SAMP versus VAR_POP e a relacao com STDDEV

A variancia mede o quanto os valores se espalham em torno da media: quanto maior, mais "larga" e a distribuicao dos dados. E a media dos desvios ao quadrado em relacao a media, expressa nas unidades de entrada ao quadrado. O SQL oferece duas variantes, a amostral VAR_SAMP e a populacional VAR_POP, e a diferenca importa mais do que parece a primeira vista.

Duas funcoes e um atalho

O PostgreSQL expoe tres nomes, mas por tras ha apenas duas formulas:

• VAR_POP(x) e a variancia populacional: divide a soma dos desvios ao quadrado por N.
• VAR_SAMP(x) e a variancia amostral: divide por N - 1 (correcao de Bessel).
• VARIANCE(x) e apenas um apelido para 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;

Conclusao principal: no PostgreSQL, o VARIANCE puro NAO e uma escolha "neutra", ele divide por N - 1. No ClickHouse as funcoes explicitas se chamam varSamp e varPop. O MySQL e a armadilha: la VARIANCE e um sinonimo de VAR_POP (divisao por N), e nao de VAR_SAMP. Por isso uma consulta com VARIANCE puro devolve numeros diferentes no PostgreSQL e no MySQL para os mesmos dados. Para codigo portavel, escreva sempre de forma explicita a versao que voce quer.

Amostra ou populacao

Qual delas usar depende do que suas linhas representam.

• Se as linhas sao TODOS os objetos que interessam (por exemplo, todos os pedidos de um dia especifico), use VAR_POP.
• Se as linhas sao uma amostra de um conjunto maior nao observado (pedidos como amostra do comportamento geral dos clientes), use 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;

Com N grande, a diferenca entre dividir por N e por N - 1 some. Em grupos pequenos ela aparece: com N = 2, a variancia amostral e exatamente o dobro da populacional. Por isso, em segmentos esparsos ou com muitos grupos pequenos, a escolha da funcao move diretamente os numeros do relatorio e vale a pena torna-la explicita em vez de confiar em um padrao. Quando voce esta em duvida se suas linhas sao uma populacao completa ou uma amostra, trata-las como amostra e usar VAR_SAMP quase sempre e a opcao mais segura: ela da uma estimativa nao enviesada da dispersao, enquanto VAR_POP sobre uma amostra a subestima de forma sistematica.

Variancia e o desvio padrao ao quadrado

Variancia e desvio padrao sao dois lados do mesmo numero: STDDEV e a raiz quadrada de VARIANCE. STDDEV_SAMP corresponde a VAR_SAMP e STDDEV_POP a VAR_POP. Essa identidade e util para conferencias e para trocar de unidades.

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;

O desvio padrao esta nas mesmas unidades dos dados (reais, dolares), enquanto a variancia esta nessas unidades ao quadrado. Por isso os relatorios quase sempre mostram STDDEV para as pessoas e reservam a variancia para os calculos internos, onde o quadrado e conveniente (por exemplo, ao somar as variancias de variaveis independentes).

Detalhes numericos e armadilhas

• Os NULL sao ignorados em silencio: VAR_SAMP conta apenas os valores nao nulos, e N e essa contagem, nao o total de linhas.
• Em uma unica linha, VAR_SAMP retorna NULL (nao da para dividir por N - 1 = 0), enquanto VAR_POP retorna 0. Essa e a armadilha classica ao agrupar: grupos de uma so linha de repente produzem 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;

• O tipo do resultado e double precision para entradas float e numeric para entradas inteiras e numeric. Com numeros muito grandes o caminho float pode perder precisao; converta para numeric quando precisar de estabilidade.
• Nao calcule a variancia sobre medias ja agregadas, isso da um resultado errado. A variancia e tomada sobre os valores originais, sem agregacao.

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

Regra curta: no PostgreSQL use VAR_SAMP (ou VARIANCE) para amostras, VAR_POP para uma populacao completa, fique atento aos NULL em grupos de uma so linha e passe para STDDEV pela raiz quadrada sempre que quiser unidades legiveis. E lembre-se de que no MySQL VARIANCE significa VAR_POP.