Como combinar duas funções agregadas em SQL

Está tendo problemas ao usar duas funções agregadas em uma consulta? Este artigo mostrará como fazê-lo da maneira correta - na verdade, as duas maneiras corretas.

Na análise dos dados e nos relatórios, muitas vezes precisamos contar o número de registros ou somá-los e depois calcular a média desta contagem ou soma.

Traduzido para a lógica SQL, esta é a agregação de dados agregados, ou agregação multinível. Para fins de agregação, existem as funções de agregação SQL. E para a agregação multinível, seriam usadas (pelo menos) duas funções de agregação ao mesmo tempo.

Se você estiver interessado em relatórios de qualidade, você precisará de muito mais do que as funções agregadas do SQL. No entanto, elas são certamente a base de um bom relatório. A melhor maneira de construir a partir dessa base é tomar nossas Como Criar Relatórios Básicos em SQL curso. Ele oferece uma seção inteira dedicada exclusivamente à computação de agregações multiníveis em SQL e ao resumo de dados usando funções agregadas. Também cobre a classificação de dados usando o CASE WHEN e GROUP BY, calculando múltiplas métricas em um relatório, e comparando grupos dentro de um mesmo relatório. Com 97 exercícios interativos, este curso ajudará você a dominar os relatórios em SQL!

Para facilitar ainda mais, vou mostrar aqui como combinar duas funções de agregação em SQL. Se seu conhecimento das funções agregadas estiver enferrujado, este guia de funções agregadas SQL pode ajudá-lo a seguir este artigo. Você também pode praticar sobre estes exemplos de funções agregadas.

Exemplos de dados

Temos uma tabela chamada new_users. Ele coleta dados sobre os novos usuários de um aplicativo no mercado sul-americano. As colunas são:

• id - A identificação para cada registro individual.
• date - A data em que os usuários aderiram.
• number_of_new_users - O número de novos usuários por data.
• city - A cidade dos usuários.
• country - O país de localização dos usuários.

Veja abaixo há duas datas no total, com cada data tendo um número diferente de usuários de dois países e duas cidades em cada país.

Como você utilizaria as funções AVG() e SUM() para calcular o número médio diário de novos usuários por país?

A solução ingênua

A lógica por trás da abordagem ingênua é, na verdade, bastante sólida. É uma pena que esta solução ingênua não seja solução alguma. Você logo perceberá o porquê.

Se você pensar apenas na abordagem matemática, é simples: primeiro você tem que somar o número de novos usuários por país todos os dias, depois calcular a média dessa soma.

Transferido para funções SQL, parece lógico escrever algo como isto:

SELECT country,
 	 AVG(SUM(number_of_new_users)) AS average_new_daily_users
FROM new_users
GROUP BY country;

Por que não, certo? Há uma razão muito simples para não escrever: SQL não permite o aninhamento de funções agregadas. Em outras palavras, não se pode usar uma função agregada dentro de uma função agregada. Bem, você pode, mas a consulta retornará um erro dizendo exatamente o que eu acabei de dizer:

Como você corrige esta consulta para combinar duas funções agregadas em SQL?

Há duas opções: usando uma subconsulta ou usando Expressões de Tabela Comum (CTEs).

Solução Real 1: Subconsulta

A primeira opção é combinar duas funções agregadas usando uma subconsulta. A subconsulta é uma consulta dentro da consulta principal. Ao criar relatórios, eles são normalmente encontrados nas cláusulas SELECT, FROM, ou WHERE.

Neste exemplo, vou colocar a subconsulta na cláusula FROM.

SELECT country,
	 AVG(ds.sum_new_users) AS average_daily_new_users
FROM (SELECT date,
	  	 country,
	 	 SUM(number_of_new_users) AS sum_new_users
	  FROM new_users
	  GROUP BY date, country) AS ds
GROUP BY country;

O princípio ao combinar duas funções agregadas é usar a subconsulta para calcular a estatística 'interna'. Em seguida, o resultado é usado nas funções agregadas da consulta externa.

O código acima seleciona a data e o país e calcula a soma da coluna number_of_new_users. Isto retorna o número total diário de novos usuários. Eu dei à subconsulta o pseudônimo ds, que é a abreviação de 'soma diária'.

Uma vez obtida a soma diária, referenciei isto na consulta externa, calculando a média da coluna ds.sum_new_users - ou seja, a média dos novos usuários diários. Quero que esta média seja por país; é por isso que agrupei os dados pela coluna do país.

Solução Real 2: CTE

A outra opção para combinar funções agregadas em SQL é usar um CTE em vez de uma subconsulta. Um CTE é uma versão mais arrumada e "mais próxima da lógica matemática" de uma subconsulta. É uma expressão que lhe permite criar um resultado temporário, que pode ser referenciado em outra declaração SELECT. Você pode usar o resultado de um CTE como usaria em qualquer outra tabela. A diferença é que o resultado do CTE só existe quando um CTE é executado em conjunto com a consulta usando o CTE. Uma explicação mais detalhada sobre os CTEs pode ser encontrada no artigo O que é um CTE?

A consulta abaixo lhe dará o mesmo resultado que a solução da subconsulta:

WITH ds AS (
  SELECT date, 
   country,
         SUM(number_of_new_users) AS sum_new_users
  FROM new_users
  GROUP BY date, country)

SELECT country,
       AVG(ds.sum_new_users) AS average_daily_new_users
FROM ds
GROUP BY country;

Cada CTE é introduzido usando a palavra-chave WITH. O mesmo se aplica ao meu CTE chamado ds. Após a palavra-chave AS vem a definição de CTE. Neste caso, é a mesma declaração SELECT como na subconsulta do exemplo anterior. Aqui vem o sum(): ele retornará, novamente, a soma de novos usuários por país e data.

A segunda declaração SELECT faz referência ao CTE e calcula a média da soma devolvida pelo CTE.

Ao contrário da subconsulta, a utilização do CTE permite utilizar as funções agregadas na ordem lógica: primeiro SUM(), depois AVG(). A função 'interna' é usada no CTE, enquanto que a segunda declaração SELECT é para a função agregada 'externa'.

O resultado será o mesmo que com a subconsulta; não é necessário que eu a mostre novamente. Entretanto, é sempre bom aprender mais sobre as diferenças entre uma subconsulta e um CTE e depois praticar usando CTEs em exemplos da vida real.

Torne-se um Mestre em Agregação de Dados e Relatórios

O problema de usar duas funções agregadas em SQL é onipresente. Quanto mais complexos os relatórios se tornarem, mais você usará agregações multiníveis. Agora você sabe que as subconsultas e os CTEs são as duas soluções para isso.

Todas as outras nuances de relatórios podem ser encontradas e praticadas em nosso Como Criar Relatórios Básicos em SQL curso. Os CTEs podem tornar seus relatórios mais fáceis e mais sofisticados, portanto, tomando o Consultas Recursivas curso seria uma idéia sábia.