Como Agrupar por Múltiplas Colunas em SQL

Ao analisar grandes conjuntos de dados, você freqüentemente cria agrupamentos e aplica funções agregadas para encontrar totais ou médias. Nestes casos, o uso da cláusula GROUP BY com múltiplas colunas desdobra todo o seu potencial.

GROUP BY é uma cláusula do comando SELECT. Ela permite calcular várias estatísticas para um grupo de linhas. Por exemplo, você pode usar GROUP BY com uma tabela de funcionários para saber quantos funcionários são de cada sexo. Ou você pode agrupar por várias colunas para determinar a idade média dos veículos para cada marca e modelo em uma tabela de veículos_frota. Neste artigo, examinaremos em detalhes como funciona o agrupamento por múltiplas colunas.

Este artigo assume que você já sabe como usar GROUP BY em uma consulta SQL. Não está familiarizado com GROUP BY? A melhor maneira de aprender esta e outras construções SQL básicas é com nosso curso interativo SQL para Iniciantes. Ele contém 129 exercícios práticos e práticos. Em cada exercício, você recebe uma breve explicação e tarefa para resolver. Com cada exercício concluído, você aumenta a confiança em suas habilidades SQL. Este curso também é uma ótima maneira de rever as características SQL básicas se seu conhecimento estiver um pouco enferrujado.

Ok, vamos começar com uma atualização sobre um simples caso de uso para o GROUP BY.

GROUP BY 1 Coluna

Cada combinação de valores de coluna(s) especificada(s) na cláusula GROUP BY constitui um grupo; o comando SELECT com uma cláusula GROUP BY exibe uma única linha para cada grupo. Também é bom notar que GROUP BY permite aplicar funções agregadas em colunas não incluídas no subconjunto pendente.

Vamos ver um exemplo. Eu criei uma tabela chamada WorldWideFriends que armazena dados sobre meus amigos em diferentes partes do mundo:

Gostaria de utilizar as informações desta tabela para fazer algumas pesquisas - por exemplo, para obter uma lista dos países onde vivem meus amigos, incluindo o número de amigos que vivem em cada país.

Se eu quisesse saber quantos amigos eu tenho em cada país, eu usaria GROUP BY junto com a função agregada COUNT():

SELECT 
  Country, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country;

Esta consulta me dá um conjunto de resultados que condensa as filas com o mesmo país em apenas uma fila, enquanto COUNT(*) me diz quantas filas repetidas existem para cada país

A consulta acima me dá as informações que eu precisaria se, por exemplo, eu precisasse escolher para qual país viajar a fim de encontrar o maior número possível de meus amigos. Se você gostaria de ler mais sobre o uso básico de GROUP BY, recomendo nossos artigos sobre O que é GROUP BY em SQL e Como Usar GROUP BY.

Entretanto, mesmo se eu viajar para um país onde muitos de meus amigos vivem, esses amigos podem estar localizados em diferentes estados. Posso não ter tempo de viajar de um estado a outro para visitá-los a todos. Portanto, preciso refinar um pouco minha busca para encontrar a localização geográfica onde há uma maior concentração de meus amigos.

GROUP BY 2 Colunas

Portanto, agora preciso saber como meus amigos são distribuídos por estado e por país. Posso descobrir isso adicionando a coluna State ao meu anterior GROUP BY País (separando-os com vírgulas) e na cláusula SELECT. A consulta é parecida com esta:

SELECT 
  Country, 
  State, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State;

Olhando os resultados desta consulta, podemos ver alguns dos países que antes apareciam em apenas uma fila, agora aparecem em várias filas. A razão é que quando adicionamos o campo State, a consulta deve montar os grupos com as linhas que têm o mesmo valor tanto em Country quanto em State.

Na consulta anterior, a linha correspondente a "Colômbia" tinha um 3 no campo HowMany. Neste caso, 'Colômbia' aparece em duas linhas com valores diferentes para o Estado: uma para 'Antioquia' e a outra para 'Cundinamarca'. No campo HowMany, a linha correspondente a 'Antioquia' indica 1, enquanto a linha correspondente a 'Cundinamarca' indica 2. Isto significa que, na lista desagregada, há duas linhas com Country = 'Colômbia' e State = 'Cundinamarca', e apenas uma com Country = 'Colômbia' e State = 'Antioquia'.

A soma dos valores HowMany dessas duas linhas corresponde logicamente ao valor anterior HowMany para a linha correspondente a 'Colômbia'. O mesmo será válido para qualquer um dos outros países que estão divididos em várias fileiras com estados diferentes.

GROUP BY Múltiplas Colunas

Finalmente, se minha intenção é tornar minha viagem a mais curta possível e ainda visitar o maior número possível de amigos, só preciso adicionar a coluna City à minha consulta - tanto no SELECT como no GROUP BY - para ver quais cidades têm o maior número de amigos:

SELECT 
  Country, 
  State, 
  City, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State, City;

Quando adicionamos colunas a GROUP BY, o número de filas no resultado aumenta. Isto ocorre porque o número de combinações de valores possíveis aumenta. Quando adiciono a coluna City ao SQL GROUP BY, o tamanho do resultado aumenta consideravelmente:

Neste caso, acho que seria melhor ver apenas aquelas cidades onde há mais de um de meus amigos. Assim, para resumir os resultados, vou utilizar a cláusula HAVING. Esta cláusula me permite estabelecer uma condição sobre os resultados das funções agregadas ao utilizar GROUP BY. Aqui, a condição a ser aplicada será que a contagem de amigos seja maior que 1 (COUNT(*) > 1). Depois de incorporar a cláusula HAVING, a consulta se parece com isto:

SELECT Country, State, City, COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State, City
HAVING COUNT(*) > 1;

E assim, o resultado da consulta é reduzido a uma única fila que me mostra a única cidade onde há mais de um de meus amigos:

Outras formas de usar o GROUP BY com várias colunas

É comum usar GROUP BY várias colunas quando duas ou mais das colunas em uma consulta resultam em uma hierarquia de classificações com vários níveis. Tais hierarquias são encontradas em muitas áreas, como por exemplo:

• Dados detalhados das vendas com a data da venda dividida em ano, trimestre e mês.
• Catálogo de produtos de um fabricante organizado por família, marca, linha, modelo.
• A folha de pagamento dos funcionários de uma empresa organizada por gerência, setor, departamento.

Em todos esses casos, diferentes subconjuntos de colunas podem ser usados no GROUP BY para ir do geral ao particular.

Utilização de GROUP BY Múltiplas Colunas: Agrupando uma Hierarquia

Vejamos um conjunto de exemplos de dados de resultados de vendas. Suponha que você tenha uma visão chamada ViewSales que retorna as seguintes informações:

É fácil ver que os primeiros campos desta tabela formam uma hierarquia, com o ano como o nível mais alto e a data como o nível mais baixo. Usando GROUP BY e a função SUM(), podemos obter os valores totais de vendas por Year, por Quarter, por Month ou por Date. Se você quiser obter o total de unidades vendidas e o preço unitário médio por Year e Quarter, você precisa especificar essas duas colunas no SELECT e no GROUP BY:

SELECT 
  Year, 
  Quarter, 
  SUM(Quantity) AS TotalQty, 
  AVG(Unit_Price) as AvgUnit_Prc
FROM ViewSales
GROUP BY Year, Quarter;

O resultado será:

Observe que, embora exista uma ordem hierárquica, os dados nas diferentes colunas de agrupamento são independentes uns dos outros. Isto significa que se você agrupar apenas por Quarter ao invés de Year mais Quarter, os cálculos agregados combinarão as informações do mesmo trimestre para todos os anos (ou seja, todos os Q2s terão uma linha)

SELECT 
  Quarter, 
  SUM(Quantity) AS TotalQty,
  AVG(Unit_Price) as AvgUnit_Prc
FROM ViewSales
GROUP BY Quarter;

Isto não é um erro; você só precisa entender que os resultados expressam percepções diferentes. A última consulta permite comparar o desempenho das vendas entre diferentes trimestres independentemente do ano (por exemplo, para detectar fatores sazonais que afetam as vendas na mesma época de cada ano), enquanto a primeira compara as vendas para cada ano e trimestre em particular.

Utilização de GROUP BY VÁRIAS Colunas: Agrupamento Não Hierárquico

No exemplo anterior, vimos que o agrupamento por várias colunas nos permite passar do geral para o particular quando temos conjuntos de dados com colunas que formam uma hierarquia de dados. Mas em situações em que um conjunto de resultados é composto de colunas que não formam uma hierarquia, o uso do GROUP BY com múltiplas colunas nos permite descobrir verdades ocultas em grandes conjuntos de dados; ele combina atributos que à primeira vista não estão relacionados entre si.

Por exemplo, imaginemos que temos uma tabela chamada Downloads que armazena informações sobre pessoas que baixaram filmes de um serviço de streaming nos últimos dois anos. Essa tabela tem uma linha para cada download, e cada linha inclui as seguintes informações sobre cada pessoa que baixou um filme:

• Age
• Gender
• Nationality

Cada fila também captura estes atributos sobre cada filme baixado:

• Genre
• Year
• Country

Usando GROUP BY com várias destas colunas e a função COUNT(*), podemos detectar correlações entre as colunas. Por exemplo, para descobrir as preferências do gênero cinematográfico por idade, nós digitaríamos:

SELECT 
  Age, 
  Genre, 
  COUNT(*) AS Downloads
FROM Downloads
GROUP BY Age, Genre

Como resultado, obteríamos algo como isto:

Também poderíamos usar GROUP BY 3 colunas, para descobrir (por exemplo) as preferências de gênero por gênero e nacionalidade

SELECT 
  Gender, 
  Nationality, 
  Genre, 
  COUNT(*) AS Downloads
FROM Downloads
GROUP BY Gender, Nationality, Genre

E conseguiríamos algo assim:

GROUP BY com múltiplas colunas Retorna Informações Facetadas

GROUP BY é uma ferramenta poderosa para extrair insights de grandes conjuntos de dados que são difíceis de manipular de qualquer outra forma. Usando GROUP BY várias colunas, você pode aproveitar todo o seu potencial para expor as verdades de um conjunto de dados, permitindo que você veja as diferentes facetas do mesmo. Para fazer isto com sucesso, é fundamental que você compreenda - e saiba como explicar - o que representa um conjunto de resultados SQL agrupados por múltiplas colunas.

Se você está planejando fazer algum trabalho sério de análise de dados, então você deve fazer nosso curso interativo SQL para Iniciantes para aprender sobre todas as ferramentas que o SQL pode oferecer. Além disso, siga estes links se você precisar de mais explicações sobre GROUP BY ou quiser ver mais exemplos de GROUP BY em SQL.