Uma introdução ao uso das funções agregadas de SQL com JOINs

Anteriormente, discutimos o uso de funções agregadas SQL com a declaração GROUP BY. Os leitores regulares do nosso blog também se lembrarão de nosso recente tutorial sobre JOINs. Se você estiver um pouco enferrujado em qualquer um dos assuntos, eu o encorajo a revisá-los antes de continuar este artigo. Isso é porque vamos aprofundar funções agregadas emparelhando-os com JOINs. Esta dupla desencadeia todas as possibilidades das funções agregadas SQL e nos permite realizar cálculos em várias tabelas em uma única consulta.

O que as funções agregadas de SQL fazem?

Aqui está uma rápida visão geral das funções de agregação SQL mais comuns:

Também é importante lembrar que a declaração GROUP BY, quando usada com agregados, computa valores que foram agrupados por coluna. (Para mais informações, veja Um Guia para Principiantes das Funções Agregadas SQL.) Podemos usar GROUP BY com qualquer uma das funções acima. Por exemplo, utilizamos a função MIN() no exemplo abaixo:

SELECT MIN(column_name)
FROM table_name 
GROUP BY group_column

Isso recuperaria o valor mínimo encontrado em column_name para cada conjunto de valores em um grupo com base na coluna group_column. A mesma idéia se aplica às funções MAX, SUM, AVG, e COUNT.

Junção Pais-Criança

Agora vamos cavar algumas situações comuns em que você usará o grupo por meio de JOINs com funções agregadas. Se você leu o A Beginner's Guide to SQL Aggregate Functions, o seguinte diagrama já será familiar:

Se você já usou este modelo antes (por exemplo, fazendo os exemplos do artigo anterior), certifique-se de limpar quaisquer registros existentes de sua tabela. Você pode fazer isso executando os seguintes comandos:

TRUNCATE cities;
TRUNCATE users;

Vamos inserir alguns dados novos nas tabelas:

INSERT INTO `cities` VALUES
    (1,'Miami'),
    (2,'Orlando'),
    (3,'Las Vegas'),
    (4,'Coyote Springs');
INSERT INTO `users` VALUES
    (1,1,'John','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);

Portanto, temos uma tabela chamada users e outra mesa chamada cities. Estas duas tabelas têm algo em comum: um valor numérico de identificação da cidade. Este valor é armazenado na coluna id na tabela cities e na coluna city_id na users mesa. A coluna city_id contém uma referência (ou seja, uma chave estrangeira) que conecta um registro de usuário a uma cidade. Estes registros correspondentes nos permitem JOIN as duas tabelas juntas.

Em outras palavras, nós conhecemos a cidade de um usuário quando pegamos o registro da cities tabela que tem um valor id igual ao valor em users.city_id. Na consulta seguinte, podemos ver isto em ação:

SELECT  cities.*, users.*
FROM cities
JOIN users
  ON cities.id = users.city_id;

Uma vez que a users tabela se conecta a uma cidade através da chave estrangeira city_id, podemos dizer que um usuário pertence a uma cidade e, portanto, a cidade tem muitos usuários. Esta é uma relação pai-filho (cidades-usuários); a users tabela compartilha um link para o cities mesa.

Com esta relação em mente, vamos continuar e ver como podemos computar alguns dados resumidos interessantes que ligam as duas tabelas entre si.

Agregar + GROUP BY + Juntar

Agora vamos começar a abordar algumas situações práticas onde estaremos GROUPvalores de JOINed mesas.

MIN + AGRUPAR POR + ADERIR

Valores computacionais baseados em registros de crianças que são agrupados por uma coluna dos pais é bastante comum. Vamos construir uma consulta que obterá o menor valor users.age (registro infantil) para cada cityname (registro dos pais):

SELECT cities.cityname, MIN(users.age)
FROM cities
JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Isto retornará:

Há algo muito importante a apontar sobre a forma como o JOIN funciona. Será mais óbvio se olharmos para todas as cidades:

SELECT cities.cityname
FROM cities

Como você pode ver, "Coyote Springs" não foi listada antes porque não tem usuários. Se você quisesse ter essa cidade listada nos resultados resumidos, você deveria usar uma LEFT JOIN ao invés disso:

SELECT cities.cityname, MIN(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Isto retornará:

Se isto faz sentido ou não dependerá de seu caso de uso, mas é importante que você mantenha esta situação em mente ao unir-se às mesas.

MAX + GROUP BY + JUNTA-SE

Podemos encontrar a maior idade para cada cidade usando a função MAX():

SELECT cities.cityname, MAX(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

A consulta acima será recuperada:

Note que eu usei LEFT JOIN. Quero uma lista de todas as cidades, não apenas aquelas com registros de usuários associados.

SOMA + AGRUPAR POR + ADERIR

Vamos agora ver como totalizar as idades para cada cidade. Podemos usar a função SUM() para fazer isso:

SELECT cities.cityname, SUM(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Que retorna:

CONTAR + AGRUPAR POR + ADERIR

Suponha que queiramos ver o número de usuários em cada cidade. Usaríamos a função COUNT(), desta forma:

SELECT cities.cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Que retorna:

MÉDIA + GROUP BY + ADERIR

Usando o número de usuários em cada cidade (COUNT) e o SUM das idades combinadas dos usuários de cada cidade, podemos calcular a média de idade de cada cidade. Simplesmente dividimos a idade somada pelo número de usuários para cada cidade:

SELECT 
    cities.cityname,
    SUM(users.age) AS sum,
    COUNT(users.id) AS count,
    SUM(users.age) / COUNT(users.id) AS average
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Retornando:

Observe como a soma e a média calculada resulta em um valor NULL para Coyote Springs. Isto porque Coyote Springs não tem usuários e, portanto, a coluna resumida não pode computar um valor numérico.

AVG + AGRUPA POR + JUNTA

O exemplo anterior utilizou um cálculo que entramos para encontrar uma média de idade para cada cidade. Poderíamos ter usado a função AVG(), como mostrado abaixo:

SELECT cities.cityname, AVG(users.age)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Isto resulta nos mesmos valores que o exemplo anterior:

Resultados da filtragem

Às vezes será necessário filtrar filas com base em certas condições. Neste tipo de consulta, há três etapas onde você pode fazer isso: WHERE, HAVING, e JOIN.

Dependendo da situação, cada uma destas opções pode ter um resultado diferente. É importante entender qual utilizar quando se deseja um resultado específico. Vejamos alguns exemplos para ilustrar isto.

Usando o Predicado Juntos

Vamos obter o número de usuários com menos de 30 anos em cada cidade. Vamos usar LEFT JOIN para recuperar cidades sem nenhum registro de usuário:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
  AND users.age < 30
GROUP BY cities.cityname
ORDER BY cities.cityname;

A condição para incluir somente usuários com idade inferior a 30 anos é definida no predicado JOIN. Isto retorna a seguinte saída:

Todas as cidades são listadas, e somente os usuários com idades dentro do intervalo retornam um número diferente de zero. Cidades sem nenhum usuário que corresponda a nossos critérios retornam um zero.

O que teria acontecido se tivéssemos colocado a mesma condição de filtragem na cláusula WHERE?

Usando as condições WHERE

Se colocar as mesmas condições no WHERE, seria o que parece:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id 
WHERE users.age < 30
GROUP BY cities.cityname
ORDER BY cities.cityname;

O resultado será o seguinte:

Isto não é o que eu esperava; eu queria ter TODAS as cidades e uma contagem de seus respectivos usuários com menos de 30 anos. Mesmo que uma cidade não tivesse usuários, ela deveria ter sido listada com uma contagem zero, como retornado pelo JOIN exemplo.

A razão pela qual isso não devolveu esses registros é porque WHERE condições são aplicadas após o JOIN. Como a condição users.age < 30 remove todos os registros de "Coyote Springs" e "Orlando", o cálculo resumido não pode incluir estes valores. Somente "Las Vegas" e "Miami" atendem às condições WHERE, portanto somente "Las Vegas" e "Miami" são devolvidos.

Em contraste, quando a condição é aplicada no predicado JOIN, os registros de usuários sem idade correspondente são removidos antes que as duas tabelas sejam unidas. Em seguida, todas as cidades são correspondidas por colunas de usuários, como seria de se esperar ao utilizar um LEFT JOIN. Isto significa que todas as cidades farão parte dos resultados; apenas os registros de usuários que não atenderam à condição users.age < 30 são filtrados. Neste caso, o predicado do JOIN retorna o resultado desejado.

Utilização das condições de HAVING

Mencionamos este é o primeiro artigo, mas vamos repeti-lo aqui: usar a cláusula WHERE para filtrar colunas resumidas não funciona. Veja o exemplo abaixo.

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
WHERE COUNT(users.id) > 2
GROUP BY cities.cityname
ORDER BY cities.cityname;

Isto faz com que o banco de dados emita uma reclamação como esta do MySQL:

Error Code: 1111. Invalid use of group function

Ao invés disso, use a cláusula HAVING:

SELECT cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname
HAVING COUNT(users.id) > 2
ORDER BY cities.cityname;

Isto retorna os registros pretendidos (somente cidades com mais de dois usuários):

Lidando com NULLs

Além dos casos de borda já apresentados, é importante considerar algo que não é tão óbvio. Voltemos ao exemplo COUNT():

SELECT cities.cityname, COUNT(users.id)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Isto retorna:

Se eu tivesse usado COUNT(*) ao invés de COUNT(users.id), a contagem total de filas teria sido gerada. Isto nos teria dado um valor não intencional - neste caso, um falso "1" para "Coyote Springs". Este resultado é devido à natureza do LEFT JOIN. Aqui está um exemplo:

SELECT cities.cityname, COUNT(*)
FROM cities
LEFT JOIN users
  ON cities.id = users.city_id
GROUP BY cities.cityname

Isto teria retornado:

Então COUNT(*) está contando um "1" para "Coyote Springs" porque o LEFT JOIN está retornando uma linha com valores NULL. Lembre-se que em COUNT(*), uma fila com NULLs ainda conta.

Pela mesma razão, COUNT(users.id) retorna a contagem esperada de "0"; o valor da coluna users.id é nulo para Coyote Springs.

Em outras palavras, use sempre Count(column) com este tipo de consulta.

Uma dica final sobre como trabalhar com funções agregadas SQL

Finalmente, gostaria de acrescentar que trabalhar com funções agregadas sqlÂ- especialmente ao usar JOINs - requer que você compreenda SQL e os dados com os quais está trabalhando. Tente as consultas em um subconjunto menor de seus dados primeiro para confirmar que todos os cálculos estão funcionando como esperado. Se possível, verifique algumas saídas em relação a um valor de referência para validar os resultados de suas consultas.

Tenha em mente que o uso das condições no predicado JOIN (após o ON) não é o mesmo que a filtragem no WHERE (ou usando HAVING). Isto pode criar diferenças sutis (ou não tão sutis) em seus dados resumidos, o que pode resultar em erros difíceis de serem detectados. Preste atenção especial a suas escolhas de filtragem.

Como sempre, obrigado pela leitura e sinta-se à vontade para compartilhar suas próprias experiências na seção de comentários.