SQL Funções Agregadas

Uma função agregada junta muitas linhas num valor só: quantos clientes, a soma do frete, o preço médio, o produto mais caro. Sozinha, ela resume a tabela inteira; com GROUP BY, resume cada grupo. Esta página reúne todas as funções agregadas do SQLite 3.50, com o resultado real de cada uma na Loja Aurora. As explicações passo a passo estão em O que são Funções Agregadas e nas aulas de cada função.

As funções

Três jeitos de contar, na mesma consulta. A diferença entre count(*) e count(email) são justamente os 3 clientes sem email:

SELECT count(*) AS todos,
       count(email) AS com_email,
       count(DISTINCT cidade) AS cidades
FROM clientes;

Dentro dos parênteses: DISTINCT, ORDER BY e FILTER

SELECT count(*) FILTER (WHERE vip = 1) AS vips,
       count(*) FILTER (WHERE vip = 0) AS comuns,
       count(*) AS todos
FROM clientes;

Com GROUP BY

Sem GROUP BY, a agregada resume a tabela inteira numa linha. Com GROUP BY, ela resume cada grupo: uma linha por grupo. Os fornecedores, por estado (os dois estrangeiros têm estado NULL e formam um grupo próprio):

SELECT estado,
       count(*) AS fornecedores,
       group_concat(cidade, ', ') AS cidades
FROM fornecedores
GROUP BY estado;

O que cada uma faz com NULL e com "nenhuma linha"

SELECT count(*), sum(frete), total(frete), avg(frete), max(frete)
FROM pedidos
WHERE id > 1000;

SELECT avg(nome) FROM clientes;

Mediana e percentis

As funções median(x), percentile(x, p), percentile_cont(x, p) e percentile_disc(x, p) entraram no SQLite principal só na versão 3.51. No 3.50 do guia, elas existem no programa sqlite3 do terminal (ele as traz como extensão embutida), mas não no editor do guia, onde dão no such function: median. No terminal da Ana:

sqlite> SELECT median(preco) FROM produtos;
32.5
sqlite> SELECT percentile(preco, 90), percentile_cont(preco, 0.9), percentile_disc(preco, 0.9) FROM produtos;
95.396|95.396|89.99

median(preco)

A mediana: o preço do meio, quando todos estão em ordem. Metade dos produtos custa menos de 32,50, metade custa mais. Repare como é bem menor que a média (48,21): uns poucos produtos caros puxam a média para cima, mas não a mediana.

percentile(preco, 90)

O percentil 90, com p de 0 a 100: 90% dos preços estão abaixo dele.

percentile_cont(preco, 0.9)

O mesmo, com p de 0 a 1. Pode dar um valor "entre" dois preços (aqui, 95,396 não é preço de nenhum produto).

percentile_disc(preco, 0.9)

Devolve sempre um valor que existe na coluna (89,99).

Nos outros bancos

Experimente

SELECT categoria_id, preco
FROM produtos;

SELECT categoria_id,
       count(*) AS produtos,
       max(preco) AS mais_caro
FROM produtos
GROUP BY categoria_id;

Resumo

• count(*) conta linhas; count(x) conta valores não nulos; count(DISTINCT x) conta valores diferentes.
• sum (NULL quando não há o que somar), total (sempre real, nunca NULL), avg (sempre real), min, max.
• group_concat(x, sep) = string_agg(x, sep); use ORDER BY dentro dos parênteses para escolher a ordem.
• FILTER (WHERE …) faz várias contagens diferentes numa consulta só.
• Agregadas vão no SELECT, no HAVING e no ORDER BY; nunca no WHERE.
• median e os percentis: no terminal sim, no editor do guia (SQLite 3.50) não.