Statistical functions: percentiles, medians e distribuições

1. Introdução às Funções Estatísticas em SQL

1.1. Por que estatísticas descritivas importam em bancos de dados relacionais

Em aplicações reais, não basta conhecer médias e somas. Decisões de negócio exigem entender a dispersão dos dados: qual é o salário típico de uma empresa (mediana), como os tempos de resposta de uma API se distribuem (percentis 95 e 99), ou se existem outliers que distorcem indicadores. Funções estatísticas em SQL permitem responder essas perguntas sem exportar dados para ferramentas externas.

1.2. Diferença entre funções agregadas tradicionais e funções de ordem/percentil

Função O que calcula Sensível a outliers
AVG Média aritmética Sim
SUM Soma total Sim
MEDIAN Valor central Não
PERCENTILE_CONT Percentil interpolado Parcialmente

Enquanto AVG pode ser distorcida por valores extremos, a mediana e percentis oferecem uma visão mais robusta da tendência central e da distribuição.

1.3. Visão geral das funções padrão SQL

O padrão SQL:2003 introduziu funções de agregação ordenada. As principais são:

  • PERCENTILE_CONT(p) — percentil contínuo (interpolação)
  • PERCENTILE_DISC(p) — percentil discreto (valor real do conjunto)
  • MEDIAN — atalho para PERCENTILE_CONT(0.5)
-- Sintaxe básica (PostgreSQL, Oracle)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salario) AS mediana
FROM funcionarios;

2. Mediana: Cálculo e Casos de Uso

2.1. Função MEDIAN vs. abordagem manual com PERCENTILE_CONT(0.5)

Em bancos como Oracle e PostgreSQL, MEDIAN é syntactic sugar:

-- Oracle
SELECT MEDIAN(salario) FROM funcionarios;

-- Equivalente explícito (padrão SQL)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salario) FROM funcionarios;

2.2. Mediana em dados agrupados e janelas

-- Mediana por departamento (PostgreSQL)
SELECT 
    departamento,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salario) AS mediana_salario
FROM funcionarios
GROUP BY departamento;

-- Mediana móvel (janela) — SQL Server, PostgreSQL
SELECT 
    data,
    salario,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salario) 
        OVER (PARTITION BY YEAR(data) ORDER BY data ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS mediana_movel
FROM historico_salarios;

2.3. Exemplo prático: mediana de salários ignorando outliers

-- Removendo 5% extremos de cada lado (percentil 5 a 95)
WITH filtro AS (
    SELECT salario,
           PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY salario) OVER () AS p05,
           PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salario) OVER () AS p95
    FROM funcionarios
)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salario) AS mediana_robusta
FROM filtro
WHERE salario BETWEEN p05 AND p95;

3. Percentis com PERCENTILE_CONT e PERCENTILE_DISC

3.1. PERCENTILE_CONT: interpolação contínua

Quando o percentil cai entre dois valores, PERCENTILE_CONT interpola linearmente:

-- 95º percentil de latências (PostgreSQL)
SELECT 
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY latencia_ms) AS p95_latencia
FROM metricas_api;

3.2. PERCENTILE_DISC: valor discreto real

Retorna o menor valor do conjunto que é maior ou igual ao percentil:

SELECT 
    PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY nota) AS p95_nota
FROM alunos;

3.3. Diferenças práticas e quando usar cada uma

Cenário Função recomendada Motivo
Latências de API PERCENTILE_CONT Valores contínuos, precisa de precisão
Notas de alunos PERCENTILE_DISC Notas são discretas (0-10)
Salários PERCENTILE_CONT Valores contínuos com muitos decimais
Classificação de produtos PERCENTILE_DISC Categorias discretas

4. Distribuições com Funções de Janela (Window Functions)

4.1. CUME_DIST: distribuição cumulativa relativa

Retorna a posição relativa (0 a 1) de cada linha dentro da partição:

SELECT 
    produto,
    receita,
    CUME_DIST() OVER (ORDER BY receita DESC) AS percentil_acumulado
FROM vendas_mensais
ORDER BY receita DESC;

4.2. NTILE: dividindo dados em buckets

Cria N grupos de tamanho aproximadamente igual:

-- Quartis de clientes por gasto
SELECT 
    cliente_id,
    total_gasto,
    NTILE(4) OVER (ORDER BY total_gasto DESC) AS quartil
FROM clientes;

4.3. PERCENT_RANK: ranqueamento percentual

Calcula (rank - 1) / (total_linhas - 1):

SELECT 
    funcionario,
    salario,
    PERCENT_RANK() OVER (ORDER BY salario) AS rank_percentual
FROM funcionarios;

5. Funções Estatísticas Avançadas em SGBDs Específicos

5.1. PostgreSQL: ordered-set aggregates e mode()

-- Moda (valor mais frequente)
SELECT MODE() WITHIN GROUP (ORDER BY categoria) AS categoria_mais_comum
FROM produtos;

-- Vários percentis de uma vez
SELECT 
    PERCENTILE_CONT(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY valor) AS quartis
FROM dados;

5.2. SQL Server: PERCENTILE_CONT como função de janela

SELECT 
    funcionario,
    salario,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salario) 
        OVER (PARTITION BY departamento) AS mediana_departamento
FROM funcionarios;

5.3. MySQL/MariaDB: limitações e workarounds

MySQL não possui PERCENTILE_CONT nativamente. Workaround com variáveis:

SET @row_num = 0;
SET @total = (SELECT COUNT(*) FROM alunos);

SELECT AVG(nota) AS mediana
FROM (
    SELECT 
        nota,
        @row_num := @row_num + 1 AS row_num
    FROM alunos
    ORDER BY nota
) AS sub
WHERE row_num IN (FLOOR((@total+1)/2), CEIL((@total+1)/2));

6. Padrões de Consulta para Análise de Distribuições

6.1. Construindo histogramas com CASE e NTILE

WITH buckets AS (
    SELECT 
        valor,
        NTILE(10) OVER (ORDER BY valor) AS decil
    FROM dados
)
SELECT 
    decil,
    COUNT(*) AS frequencia,
    MIN(valor) AS minimo,
    MAX(valor) AS maximo
FROM buckets
GROUP BY decil
ORDER BY decil;

6.2. Box plots via SQL

SELECT 
    MIN(valor) AS minimo,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY valor) AS q1,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY valor) AS mediana,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY valor) AS q3,
    MAX(valor) AS maximo
FROM vendas;

6.3. Identificação de outliers com IQR

WITH stats AS (
    SELECT 
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY valor) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY valor) AS q3
    FROM dados
)
SELECT *
FROM dados, stats
WHERE valor < q1 - 1.5 * (q3 - q1)
   OR valor > q3 + 1.5 * (q3 - q1);

7. Performance e Boas Práticas

7.1. Impacto de índices em funções de ordenação

Funções como PERCENTILE_CONT exigem ordenação completa dos dados. Criar índices na coluna de ORDER BY pode acelerar a consulta:

CREATE INDEX idx_salario ON funcionarios(salario);

7.2. Estratégias para grandes volumes

Para tabelas com milhões de linhas, considere:

  • Amostragem: SELECT * FROM tabela TABLESAMPLE SYSTEM(10)
  • Pré-cálculo em tabelas de agregação
  • Uso de APPROX_PERCENTILE (disponível no BigQuery, Snowflake)

7.3. Materialização de resultados para dashboards

CREATE TABLE metricas_diarias AS
SELECT 
    data,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY receita) AS mediana_receita,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY receita) AS p90_receita
FROM transacoes
GROUP BY data;

8. Conclusão e Próximos Passos

8.1. Resumo dos principais comandos

Função Propósito
PERCENTILE_CONT(p) Percentil interpolado
PERCENTILE_DISC(p) Percentil discreto
MEDIAN Atalho para p=0.5
NTILE(n) Divide em buckets
CUME_DIST() Distribuição cumulativa
PERCENT_RANK() Rank percentual

8.2. Referência cruzada com temas vizinhos

  • Time-series: combine PERCENTILE_CONT com funções de janela temporal para percentis móveis
  • Geospatial: use NTILE para criar heatmaps de densidade espacial
  • Machine Learning: percentis são usados para normalização de features (MinMaxScaler)

8.3. Sugestões de exercícios práticos

  1. Calcule a mediana de vendas por mês nos últimos 12 meses
  2. Identifique clientes no top 5% de gasto usando PERCENTILE_CONT(0.95)
  3. Construa um histograma com 20 buckets usando NTILE
  4. Encontre outliers de temperatura usando IQR em dados de sensores
  5. Compare PERCENTILE_CONT vs PERCENTILE_DISC para notas de 0 a 100

Referências