STDDEV en SQL: STDDEV_SAMP vs STDDEV_POP y deteccion de outliers

La desviacion estandar mide cuanto se dispersan los valores alrededor de la media: pequena significa que los datos se agrupan cerca de AVG, grande significa que se esparcen. En SQL no es una sola funcion sino un par: STDDEV_SAMP para una muestra y STDDEV_POP para toda la poblacion, y la diferencia importa mas de lo que parece.

Dos funciones: muestra y poblacion

Ambas miden la dispersion, pero dividen la suma de desviaciones al cuadrado entre un conteo distinto. STDDEV_POP divide entre n (la poblacion completa), STDDEV_SAMP divide entre n - 1 (una muestra, correccion de Bessel). El STDDEV a secas en PostgreSQL es 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;

En conjuntos pequenos, dividir entre n - 1 infla el resultado de forma notable frente a n; en conjuntos grandes la brecha tiende a cero.

• Usa STDDEV_SAMP (o simplemente STDDEV) cuando las filas son una muestra de algo mayor: los pedidos de un mes como reflejo del comportamiento de los clientes.
• Usa STDDEV_POP cuando las filas son justamente todo lo que estudias: la dispersion salarial entre todos los empleados actuales.

media +/- sd: combinacion con AVG

Una desviacion por si sola no sirve; se lee junto a la media. El informe clasico muestra AVG mas una banda de "media mas o menos una desviacion".

-- 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 no es cosmetica: sobre una sola fila STDDEV_SAMP devuelve NULL, porque el divisor n - 1 se vuelve cero. STDDEV_POP sobre una sola fila devuelve 0.

El coeficiente de variacion (sd / mean) es una medida adimensional de dispersion que permite comparar grupos cuyos importes viven en escalas distintas.

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

Deteccion de outliers con z-score

El z-score es "cuantas desviaciones separan un valor de la media": (x - mean) / sd. Las filas con |z| > 3 suelen tratarse como anomalias. Conviene calcular las estadisticas con una funcion de ventana y comparar cada fila contra ellas en una sola pasada.

-- 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 frente a la division por cero: si todos los importes de un grupo son identicos, la desviacion es 0 y, sin el resguardo, la consulta falla. El mismo truco senala salarios desproporcionados dentro de un departamento.

NULL, tipos y diferencias entre motores

• STDDEV_* ignora NULL, igual que AVG: los nulos no entran ni en la suma ni en el conteo. Por eso la n de la correccion de Bessel es el numero de valores NO NULL, no el total de filas.
• Si no sobrevive ninguna fila a tus filtros, cualquiera de estas funciones devuelve NULL, no un error.
• Trampa: confundir STDDEV_SAMP y STDDEV_POP en grupos pequenos. Con 5 filas, dividir entre 4 en lugar de 5 es un cambio de cerca del 12% en la estimacion de la dispersion, y tu umbral de outliers se desplaza con ella. Elige una version y no las mezcles en un mismo informe.
• El tipo del resultado es double precision, es decir un float con error de redondeo. Suele estar bien para dinero, pero nunca lo compares por igualdad exacta.
• PostgreSQL ofrece los tres nombres. En MySQL, STDDEV y STD son sinonimos de STDDEV_POP (poblacion!), y debes escribir STDDEV_SAMP de forma explicita: lo contrario que en Postgres. En ClickHouse las funciones son stddevSamp y stddevPop.

En resumen: elige SAMP para una muestra y POP para una poblacion completa, lee siempre la desviacion junto a AVG, protege la division con NULLIF y recuerda que el STDDEV a secas significa cosas distintas en Postgres y en MySQL.