5 Exemplos de GROUP BY

Quando você começa a aprender SQL, você se depara rapidamente com a cláusula GROUP BY. O agrupamento de dados - ou agregação de dados - é um conceito importante no mundo dos bancos de dados. Neste artigo, vamos demonstrar como você pode usar a cláusula GROUP BY na prática. Reunimos cinco exemplos de GROUP BY, desde os mais fáceis até os mais complexos para que você possa ver o agrupamento de dados em um cenário da vida real. Como um bônus, você também aprenderá um pouco sobre funções agregadas e a cláusula HAVING.

SQL é uma linguagem universal para falar com bancos de dados que existe há quase 50 anos. Se você é um iniciante completo, considere fazer nosso curso SQL para Iniciantes antes de ler este artigo.

Um dos conceitos centrais por trás do SQL é o agrupamento de dados, ou agregação de dados. Se você estiver lendo este artigo, provavelmente já ouviu falar sobre a cláusula GROUP BY. Para ajudá-lo a compreendê-la melhor, apresentamos cinco problemas comerciais e mostramos como eles podem ser resolvidos em nossos exemplos GROUP BY.

Se você precisa de uma rápida introdução a GROUP BY, veja nosso vídeo de cinco minutos no YouTube.

Neste artigo, vamos ajudar um museu imaginário a analisar seus convidados. Usaremos a história das visitas ao museu para obter percepções significativas usando a cláusula GROUP BY. Vamos ao trabalho, então!

Dados de entrada

Vamos trabalhar com uma única tabela chamada visit. Cada fila representa uma única visita ao museu. Abaixo, você pode ver algumas linhas de amostra a partir desta tabela:

visit

Como você pode ver, a mesa não é muito complicada. Ela contém apenas três colunas:

• data - A data da visita ao museu.
• preço - O preço pago pelo ingresso em dólares (você verá uma variedade de preços porque o museu usa diferentes opções de preços em vários dias, juntamente com muitos tipos de ingressos reduzidos).
• duração - A duração da visita ao museu em minutos.

A propósito, se você já conhece as instruções SQL básicas e quer tentar criar tabelas em seu lugar, dê uma olhada neste curso LearnSQL.com.br O Básico de Criar Tabelas em SQL.

Por que agrupamos fileiras?

Sabemos que podemos agregar (agrupar) filas em SQL, mas por que fazemos isso? A cláusula GROUP BY é tipicamente usada junto com funções agregadas, que computam várias estatísticas sobre os grupos de linhas. As cinco funções agregadas mais básicas em SQL são:

• COUNT()-Usadas para contar o número de linhas.
• AVG()-Utilizadas para encontrar o valor médio.
• MIN() e MAX()-Usado para encontrar o valor mínimo e máximo, respectivamente.
• SUM()-Utilizado para encontrar a soma de todos os valores.

Em resumo, agrupamos as linhas para calcular várias estatísticas.

GROUP BY Exemplos

Bom. Agora que sabemos um pouco sobre funções agregadas, vamos dar uma olhada em cinco exemplos de GROUP BY.

Exemplo 1: GROUP BY Com Uma Coluna

Vamos começar com um exemplo simples. Queremos saber quantas pessoas visitaram o museu em cada dia. Em outras palavras, para cada data, vamos mostrar o número de visitas ao museu. A consulta de que precisamos será parecida com esta:

SELECT date, COUNT(*)
FROM visit
GROUP BY date;

Temos apenas duas colunas: data e contagem. COUNT(*) significa "contar tudo". Como também usamos a coluna de datas na cláusula GROUP BY, veremos uma contagem separada para cada data. Quando executamos a consulta em nosso banco de dados, devemos ver algo como isto:

Excelente. Agora sabemos quantas pessoas visitaram o museu em cada dia.

Em vez de COUNT(*), que significa "contar cada fila", poderíamos também usar um nome de coluna dentro, por exemplo, COUNT(duration). A diferença é que COUNT(*) conta todas as linhas de um determinado grupo, mesmo que algumas linhas contenham valores NULL (desconhecidos). COUNT(duration) Por outro lado, conta apenas as linhas que têm um valor não NULL na coluna duration. Em nossa tabela, entretanto, não há valores NULL, portanto, o resultado seria o mesmo.

Exemplo 2: GROUP BY Duas Colunas

Agora queremos saber o preço médio pago por um bilhete em um determinado mês. Para isso, vamos precisar de uma consulta mais complicada. Dê uma olhada:

SELECT 
  EXTRACT(YEAR FROM date) AS year, 
  EXTRACT(MONTH FROM date) AS month, 
  ROUND(AVG(price), 2) AS avg_price
FROM visit
GROUP BY
  EXTRACT(YEAR FROM date),
  EXTRACT(MONTH FROM date);

Usamos a função EXTRACT(YEAR FROM date) para obter o ano de cada data, e usamos EXTRACT(MONTH FROM date) para obter o mês (como um valor numérico, onde "1" significa "janeiro", "2" significa "fevereiro", etc.). Observe que eles são tratados como colunas separadas, assim você verá o ano em uma coluna e o mês em outra.

Como queremos ver a média para cada mês de cada ano separadamente, precisamos também agrupar por estas duas colunas. Temos que repetir as mesmas funções na cláusula GROUP BY.

A terceira coluna é uma combinação de duas funções. Dentro, temos AVG(price), que calculará o preço médio em cada grupo. Também temos ROUND(AVG(price), 2)) para arredondar o valor médio para duas casas decimais.

Quando você usar uma cláusula GROUP BY, tente lembrar a regra de ouro: Todos os nomes de colunas da cláusula SELECT devem aparecer na cláusula GROUP BY ou ser usados nas funções agregadas. Neste caso, tanto EXTRACT(YEAR FROM date) quanto EXTRACT(MONTH FROM date) devem aparecer na cláusula GROUP BY. Se você se esquecer de uma delas, provavelmente verá um erro. A terceira coluna usa uma função agregada, AVG(price), portanto não é mencionada na cláusula GROUP BY.
Há algumas exceções a esta regra, e elas podem levar a um comportamento inesperado.

Quando fizermos a consulta, veremos algo parecido com isto:

Como você pode ver, o preço médio dos ingressos diminuiu em junho, em comparação com maio. Isto poderia se traduzir em uma renda mais baixa para o museu.

Exemplo 3: GROUP BY e PEDIDO POR

Desta vez, queremos encontrar o valor médio da duração da visita para cada mês. Também queremos ter certeza de que as filas estão ordenadas cronologicamente. A consulta que precisaremos será semelhante ao exemplo anterior:

SELECT 
  EXTRACT(YEAR FROM date) AS year, 
  EXTRACT(MONTH FROM date) AS month, 
  ROUND(AVG(duration), 2)
FROM visit
GROUP BY 
  EXTRACT(YEAR FROM date), 
  EXTRACT(MONTH FROM date)
ORDER BY 
  EXTRACT(YEAR FROM date), 
  EXTRACT(MONTH FROM date);

A nova peça aqui é a cláusula ORDER BY. Uma cláusula ORDER BY nos permite especificar a ordem em que devemos ver as filas. Neste caso, queremos ver todas as linhas ordenadas primeiro pelo ano, e depois pelo mês.

Novamente, precisamos repetir as mesmas funções da cláusula SELECT para que a cláusula ORDER BY funcione. Por padrão, ORDER BY ordena as filas em ordem ascendente.

Se você quiser ver as linhas ordenadas em ordem decrescente, você precisa adicionar a palavra-chave DESC após o nome da coluna. Por exemplo, escreva ORDER BY EXTRACT(YEAR FROM date) DESC. Você pode ler mais sobre a diferença entre GROUP BY e ORDER BY neste artigo.

Quando executarmos a consulta, veremos algo como isto:

Em média, um convidado passou mais tempo no museu em junho do que em maio. Isso é uma boa notícia!

Exemplo 4: GROUP BY e TENDO

Agora, temos o seguinte problema: queremos ver o preço médio do bilhete para cada dia. Entretanto, há uma condição extra: não queremos mostrar dias com 3 ou menos visitas. Esta condição se traduz em uma nova peça em nossa consulta SQL. Dê uma olhada:

SELECT 
  date, 
  ROUND(AVG(price), 2) AS avg_price
FROM visit
GROUP BY date
HAVING COUNT(*) > 3
ORDER BY date;

A nova parte aqui é HAVING COUNT(*) > 3. HAVING é uma cláusula que podemos usar para filtrar nas filas agrupadas. Neste caso, agrupamos as linhas até a data (GROUP BY date). Quando fazemos isso, queremos ter certeza de que um determinado grupo tem mais de três linhas (HAVING COUNT(*) > 3). Se um grupo (neste caso, visitas em um determinado dia) não cumpre esta condição, não a mostramos de forma alguma.

Quando realizamos a consulta, veremos algo assim:

Exemplo 5: GROUP BY, HAVING, e ONDE

Finalmente, temos o seguinte problema a resolver: queremos mostrar a duração média da visita para cada dia. Mais uma vez, queremos mostrar apenas os dias com mais de três visitas. Entretanto, também queremos ter certeza de que as visitas de cinco minutos de duração ou menos não sejam incluídas nos cálculos. Estes são provavelmente testes realizados pelos funcionários do museu, portanto, queremos ignorá-los. Esta é a consulta que precisaremos:

SELECT 
  date, 
  ROUND(AVG(duration), 2) AS avg_duration
FROM visit
WHERE duration > 5
GROUP BY date
HAVING COUNT(*) > 3
ORDER BY date;

A nova parte aqui é a cláusula WHERE. Ela é usada para incluir apenas visitas que duraram mais de cinco minutos.

As cláusulas WHERE e HAVING podem parecer semelhantes, mas há uma diferença entre elas: WHERE é usado para filtrar filas únicas antes de serem agrupadas (ou seja, visitas únicas) enquanto HAVING é usado para filtrar grupos de filas (ou seja, visitas em um determinado dia). Leia mais neste artigo.

Quando executarmos a consulta, veremos algo como isto:

Observe como a duração média das visitas aumenta com quase todos os dias em junho. Parece que acrescentamos uma exposição interessante e nossos convidados espalharam a palavra sobre o assunto.

Resumo e Acompanhamento

Com estes cinco exemplos, passamos de casos fáceis para casos mais complexos GROUP BY. A versatilidade do SQL nos permitiu analisar as visitas ao museu e responder a várias perguntas sobre elas. Isto mostra quão eficazmente GROUP BY pode resolver problemas reais de negócios.

Se você gostaria de ler mais sobre a cláusula GROUP BY, nosso Editor Chefe de Conteúdo, Agnieszka, preparou um artigo abrangente disponível aqui.

Se você tiver vontade de aprender mais SQL, dê uma olhada em LearnSQL.com.br. LearnSQL.com.br equipe ensina SQL do zero de uma maneira completamente interativa.

Para iniciantes, temos nosso curso best-seller SQL para Iniciantes. Garantimos uma entrada tranquila no mundo da codificação para pessoas sem experiência prévia em TI. Você não precisará se preocupar com a configuração técnica - você estudará diretamente de seu navegador da web. Nós cuidamos do banco de dados enquanto você se concentra nos conceitos-chave do SQL.

Se você quiser saber mais sobre os benefícios de aprender conosco antes de comprar uma assinatura, dê uma olhada em nosso artigo: Por que fazer o curso SQL para Iniciantes em LearnSQL.com. Este artigo explica em detalhes porque construímos o curso, o que ele contém, e a filosofia por trás dele.