Um Guia para Principiantes das Funções Agregadas de SQL

Funções agregadas são poderosos SQL ferramentas que computam cálculos numéricos sobre os dados, permitindo que a consulta retorne informações resumidas sobre uma determinada coluna ou conjunto de resultados. Estas funções podem ser usadas em conjunto com a declaração GROUP BY. Vamos ver como elas funcionam usando algumas funções fáceis exemplos.

Funções Agregadas SQL

Suponha que tenhamos usuários residentes em uma cidade, e armazenamos suas informações em duas tabelas. Estas tabelas e seu relacionamento são mostradas abaixo:

Vamos colocar alguns dados neste modelo:

INSERT INTO `cities` VALUES (1,'Miami'),(2,'Orlando'),
(3,'Las Vegas'),(4,'Coyote Springs');
INSERT INTO `users` VALUES (1,1,'Jhon','Doe',22),
(2,1,'Albert','Thomson',15),(3,2,'Robert','Ford',65),(4,3,'Samantha','Simpson',9),(5,2,'Carlos','Bennet',42),
(6,2,'Mirtha','Lebrand',81),(7,3,'Alex','Gomez',31);

Agora temos material suficiente para explicar o uso básico das funções agregadas SQL. Vamos começar com uma simples.

MIN

Isto retorna o menor valor em uma determinada coluna. Por exemplo, vamos obter a idade mínima do nosso grupo de usuários:

SELECT MIN(age) FROM users;

Isto retorna um "9".

Você pode usar esta função para encontrar informações alfabéticas, também. Vamos tentar com a coluna "last_name":

SELECT MIN(last_name) FROM users;

Isto retorna "Bennet", que está em primeiro lugar em ordem alfabética.

Nota: A colação usada para classificar seus dados terá impacto nos resultados desta consulta. Por exemplo, na colação dinamarquesa, "A" é tratado como "A" - a última letra do alfabeto. A colação latina, naturalmente, trata "A" como a primeira letra do alfabeto.

MAX

De forma semelhante, porém oposta a MIN, MAX retorna o maior valor em uma coluna. Vamos obter a idade máxima da nossa lista de usuários:

SELECT MAX(age) FROM users;

Isto retornará um "81".

Vamos tentar o mesmo com a coluna de sobrenome:

SELECT MAX(last_name) FROM users;

Ela retorna "Thomson", que é o último em ordem alfabética. Lembre-se, isto pode mudar dependendo da colação que você estiver usando.

SUM

Esta função calcula a soma de todos os valores numéricos em uma coluna. Vamos usá-la para recuperar a soma de todas as idades na tabela:

SELECT SUM(age) FROM users;

Ela retornará "265".

AVG

Isto é usado para calcular o valor médio de uma coluna. Vamos vê-lo em ação, recuperando a média de idade de nossos usuários:

SELECT AVG(age) FROM users;

Ele retorna um "27,75".

COUNT (coluna)

Isto retorna o número de valores não-NULL em uma determinada coluna. Se quiséssemos saber quantos usuários nos disseram sua idade, nós escreveríamos:

SELECT COUNT(age) FROM users;

Ele retornará um "7". Todos os registros no "users" tabela têm um valor de idade. Se um registro não tivesse um valor de idade, ele teria sido NULL (e não incluído no resultado do COUNT). Se você quiser contar o número real de linhas da tabela independentemente do valor da coluna, então a função COUNT(*) é o que você precisa. Em vez de especificar um nome de coluna como o argumento da função, usamos um asterisco:

SELECT COUNT(*) FROM users;

No nosso caso, isto ainda retorna um "7" porque a tabela tem sete registros.

Os valores NULL podem ser confusos, mas não se preocupe. Mostraremos como você pode lidar com os valores NULL em funções agregadas SQL mais adiante neste artigo.

Agora que você sabe o que estas funções agregadas fazem, vamos ver como torná-las ainda mais úteis.

Você quer saber mais sobre Cláusulas JOIN em SQL? Assista a um episódio de nossa série We Learn SQL no Youtube. Verifique se você já sabe tudo sobre os diferentes tipos de JOINs.

Usando GROUP BY com Funções Agregadas

A declaração GROUP BY nos permite realizar agregações sobre um grupo de valores com base em determinadas colunas. Você usará frequentemente GROUP BY com funções agregadas, portanto, estes exemplos serão um pouco mais complicados e realistas do que os simples exemplos que usamos anteriormente.

Conceitualmente, "GROUP BY (column_x)" significa "colocar todos os registros que compartilham o mesmo valor em "column_x" em um único grupo". Vamos ver como isto funciona com cada uma das funções que já discutimos.

MIN + GROUP BY

Suponhamos que queremos saber a idade de nosso usuário mais jovem em cada cidade. Podemos ver que o "users"A tabela tem uma coluna chamada "city_id" que identifica a cidade onde cada usuário vive. Podemos usar esta coluna com uma declaração GROUP BY para ver a idade mais jovem em cada cidade:

SELECT 
    city_id, MIN(age)
FROM
    users
GROUP BY city_id;

Para entender melhor o que está acontecendo, veja os dados brutos na tabela "Dados brutos".users" mesa:

Usando MIN() com GROUP BY agrupará os registros pelos valores na coluna "city_id" antes de calcular o agregado para cada grupo. Se você pudesse ver os valores do GRUPO, seria algo parecido com isto:

Então, a consulta agarrará as idades mais baixas dentro de cada grupo. Se pudéssemos ver este passo em ação, pareceria assim:

Os valores destacados representam os valores computados em MIN() para cada grupo.

Finalmente, a consulta mostrará os seguintes resultados:

MAX + GROUP BY

Como você já deve ter adivinhado, usar MAX combinado com GROUP BY funciona da mesma forma que MIN. Ele simplesmente retorna o maior valor para cada grupo. Poderíamos calcular a idade máxima para cada cidade de forma semelhante:

SELECT 
    city_id, MAX(age)
FROM
    users
GROUP BY city_id;

Esta consulta agrupará os usuários com base em seu campo "city_id" e então obterá o valor máximo de idade para cada grupo. Ela nos dá os seguintes resultados:

SUM + GROUP BY

Também poderíamos calcular a soma das idades dos usuários em cada cidade. Para isso, podemos fazer a seguinte consulta...

SELECT 
    city_id, SUM(age)
FROM
    users
GROUP BY city_id;

... que recuperará:

CONTAR + AGRUPAR POR

Também podemos querer calcular o número de usuários em cada cidade. É fácil fazer isto usando a função COUNT:

SELECT 
    city_id, COUNT(age)
FROM
    users
GROUP BY city_id;

Com base nos dois últimos exemplos, poderíamos calcular a idade média em cada cidade dividindo a soma de todas as idades em um grupo de cidades pelo número de usuários para aquela cidade. Eis como fazemos isso:

SELECT 
    city_id,
    SUM(age),
    COUNT(age),
    SUM(age) / COUNT(age) as average
FROM
    users
GROUP BY city_id;

Nota: Poderíamos também ter usado COUNT(*) aqui, pois não há registros com valores NULL na coluna "age". Neste caso, COUNT(age) funciona da mesma forma que COUNT(*). Caso contrário o valor seria diferente, como explicaremos mais adiante na seção "Lidando com NULLs".

AVG + GROUP BY

No exemplo anterior, calculamos "manualmente" a idade média para cada cidade. Poderíamos usar a função AVG() para realizar esta operação para nós, como mostrado abaixo:

SELECT 
    city_id,
    AVG(age)
FROM
    users
GROUP BY city_id;

O valor retornado por AVG(age) é idêntico ao resultado da operação matemática que realizamos anteriormente.

Filtragem de resultados agrupados

Há momentos em que é preciso filtrar mais resultados com base nas condições geradas pelos resultados agregados. A adição de quaisquer condições no WHERE falharia. Não acredita em mim? Veja o que acontece quando tentamos recuperar o número de usuários somente em cidades onde a idade média do usuário é maior que 20 anos:

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
WHERE AVG(age) >= 20
GROUP BY city_id;

Isto fará o motor (no meu caso, o MySQL) reclamar. Ele dirá algo assim:

Código de erro: 1111. Uso inválido da função de grupo

Para filtrar resultados desta forma, precisamos usar a cláusula HAVING. HAVING filtrará os resultados resumidos de GROUP BY; a cláusula WHERE se aplica apenas aos registros individuais. Se qualquer grupo não atender aos critérios da cláusula HAVING, eles não serão devolvidos.

Portanto, se quiséssemos obter o COUNT para cada cidade com uma idade média de pelo menos 20 anos, a maneira adequada de lidar com a filtragem seria assim:

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
GROUP BY city_id
HAVING AVG(age) >= 20

Nota: O grupo com city_id = 1 é descartado, pois seu AVG(age) tem 18,5 anos.

Finalmente, WHERE e HAVING podem ser usados simultaneamente sem nenhum problema (se fizer sentido em sua consulta, é claro). Observe como isto funciona no próximo exemplo. Faremos o cálculo novamente, mas desta vez excluiremos qualquer usuário cujo sobrenome seja "Simpson":

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
WHERE last_name <> 'Simpson'
GROUP BY city_id
HAVING AVG(age) >= 20

Observe que os cálculos em COUNT e AVG são diferentes para city_id = 3. Há um usuário com o sobrenome "Simpson" para city_id = 3, e esse registro foi descartado devido à condição WHERE last_name <> 'Simpson’.

Lidando com NULLs

Até agora todas as nossas colunas foram preenchidas com dados, mas isso pode não ser sempre o caso. Vamos inserir alguns registros com a idade NULL para explicar alguns casos de borda. Os dois INSERTs seguintes acrescentarão dois novos usuários, com um ID de 8 e 9 respectivamente, que têm um NULL na coluna de idade:

INSERT INTO `users` (`id`, `city_id`, `first_name`, `last_name`, `age`)
VALUES ('8', '2', 'Frederic', 'Scott',NULL);

INSERT INTO `users` (`id`, `city_id`, `first_name`, `last_name`, `age`) 
VALUES ('9', '4', 'Stacy', 'Roberts',NULL);

Registros com valores nulos não são considerados em MIN, MAX, SUM, AVG e COUNT(column). Esses registros são simplesmente ignorados no cálculo. Uma regra geral é se você não espera ter valores nulos ou se espera tratar os NULLs como "0", então você deve definir as colunas como NÃO NULL e definir as colunas NULL como "0" ou qualquer valor que faça sentido para seu caso de uso.

COUNT(coluna) vs COUNT(*)

A função COUNT(column) não contará nenhum registro com valores de idade NULL. Vamos ver isso em ação:

SELECT 
    COUNT(age)
FROM
    users;

Esta consulta retorna um "7" novamente; os dois registros que adicionamos têm valores NULL na coluna "age", portanto são ignorados. Se quisermos contar todos os registros independentemente do valor, usamos o valor COUNT(*) function:


SELECT 
    COUNT(*)
FROM
    users;

Isto retorna o resultado esperado de "9".

Vamos ver como as NULLs impactam a função AVG().

AVG + NULL

Devido ao fato de que AVG irá ignorar os registros com NULLs na coluna especificada, o valor resultante pode não fazer sentido. Eis o porquê.

A função AVG(age) adicionará somente os usuários com um valor não-nulo na coluna "age" e dividirá esse número contra o COUNT dos usuários que também têm um valor não-nulo "age". Este é o 7 em nosso exemplo. Se você considerar que os dois usuários que acabamos de adicionar com idade NULL devem ser considerados para o cálculo da idade média, então o valor que você obterá com AVG(age) estaria errado. A consulta abaixo mostrará a diferença nos cálculos:

SELECT 
    SUM(age),
    COUNT(age),
    AVG(age),
    SUM(age) / COUNT(age),
    COUNT(*),
    SUM(age) / COUNT(*)
FROM
    users;

Observe como o valor AVG(age) corresponde ao valor calculado usando SUM(age)/COUNT(age); ambas as funções não consideram registros com valores NULL. Mas veja como o valor médio muda ao utilizar COUNT(*), que inclui todos os registros.

Vimos que os valores NULL não serão computados em MIN, MAX, AVG, SUM e COUNT(column_name) funções. Se você vai usar estas funções e espera alguns campos NULL, certifique-se de definir os valores NULL como algum valor específico.

Além disso, defina os tipos de coluna consistentemente para o valor que você está armazenando. Por exemplo, os números armazenados em uma coluna VARCHAR não serão tratados como numéricos e podem causar resultados indesejados em seus cálculos de funções agregadas.

Finalmente, eu gostaria de enfatizar a importância de colocar suas condições de filtragem nos lugares certos dentro de suas consultas. Use HAVING se você precisar de condições baseadas em valores agregados.

O que você acha das funções agregadas do SQL? Você tem alguma experiência ou exemplos sobre seu uso? Por favor, compartilhe suas idéias com nossa comunidade. E não perca a segunda parte deste artigo, onde explicarei como integrar funções agregadas SQL com JOINs. Publicaremos no blog Vertabelo em breve!