7 Comum GROUP BY Erros

Você está cometendo esses erros com o GROUP BY em SQL? Descubra o que eles são, como evitá-los e como corrigi-los.

A declaração GROUP BY da SQL pode rapidamente desvendar poderosas informações de dados. No início, usar GROUP BY pode parecer fácil - por exemplo, ao criar relatórios SQL básicos que você apresentará aos tomadores de decisão de negócios. Mas enquanto aprende este poderoso recurso, você pode ficar preso a erros estranhos ou obter resultados incorretos causados por declarações GROUP BY mal escritas. Se você estiver se sentindo como se as coisas não estivessem se somando ao seu uso do GROUP BY, continue lendo. Neste artigo, explicarei os erros mais comuns do GROUP BY e como você pode evitá-los.

Pare de cometer estes 7 erros comuns do GROUP BY

1. Esquecimento de GROUP BY Funções Agregadas

Você usa SELECT declarações com a cláusula GROUP BY quando deseja agrupar e organizar linhas em grupos específicos e depois realizar um cálculo específico de cada grupo.

O erro mais comum GROUP BY é esquecer de escrever GROUP BY dentro da declaração SELECT.

Aqui está um exemplo. Imagine que você tem a tabela receitasque contém 100 registros e seis colunas. Esta tabela armazena o número de visualizações (no_of_views) por cada receita publicada em um famoso site de culinária:

tabela de receitas

Aqui está uma breve descrição das colunas da tabela:

• meal_category - A categoria da receita (sopa, bebidas, sobremesas, etc.).
• name - O nome da receita.
• author - O nome do autor.
• no_of_views - O número de visualizações (total de páginas/receitas visualizadas) no mês corrente.
• no_of_views_lst_mth - O número de visualizações (total de páginas/receitas visualizadas) no mês anterior.
• author_id - O número de identificação único do autor.

Digamos que você queira contar o número de receitas em cada categoria de refeição. Se você escrever a declaração como esta (sem GROUP BY no final) ...

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes ;

... a maioria dos motores SQL lhe dará um erro. Alguns motores, no entanto, produzirão resultados estranhos e indesejados. Estou usando o MySQL e quando executo esta declaração, eu recebo isto:

Resultado sem GROUP BY

100 é a contagem total de todas as receitas do conjunto de dados e a categoria de refeições "Aperitivo frio" é apenas uma categoria em cada dez. Para corrigir este tipo de erro, é necessário adicionar um GROUP BY meal_category no final da declaração. (Caso contrário, seu resultado no MySQL simplesmente não faz sentido).

O SELECT correto se parece com isto:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Aqui está uma breve explicação do que está acontecendo:

• Os registros são fundidos com base na categoria de refeição. Por exemplo, as sobremesas são um grupo, as sopas outro, os pratos principais ainda outro, etc. A coluna meal_category é especificada após GROUP BY; ela também é listada em SELECT.
• Para cada grupo, estamos usando COUNT(*) para contar o número total de receitas nesse grupo.

Eu não vou mergulhar profundamente na sintaxe aqui, mas eu definitivamente sugeriria que você lesse GROUP BY in SQL Explained ou Using GROUP BY in SQL para mais detalhes.

Como você pode ver, o resultado é como esperávamos:

Resultado válido de GROUP BY

2. Confundindo ONDE e HAVING

Talvez você gostaria de ver apenas aquelas categorias de refeições que têm mais de 10 receitas. Muitos iniciantes escreveriam esta pergunta:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

Esta declaração retornará um erro porque você não pode usar funções agregadas em uma cláusula WHERE. WHERE é usado com GROUP BY quando você quer filtrar as linhas antes de agrupá-las.

Em nosso exemplo, queremos filtrar as linhas após o agrupamento; em casos como este, precisamos usar a cláusula HAVING:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

Este mal entendido sobre a diferença entre HAVING e WHERE é o segundo erro mais comum com GROUP BY.

Vamos esclarecer esta diferença com mais dois exemplos.

Exemplo 1 - Como exibir categorias de refeições com vistas de 1M+

Uma declaração que exibe apenas categorias com mais de 1 milhão de páginas vistas no total pode ser escrita assim:

SELECT 
  meal_category,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category
HAVING sum(no_of_views) >1000000;

Aqui estamos usando HAVING porque queremos filtrar os registros após terem sido agrupados. O resultado é apresentado abaixo:

Exemplo com HAVING

Exemplo 2 - O desempenho de John em cada categoria de refeição

Esta consulta extrai apenas as receitas de John e calcula seu desempenho:

SELECT 
  meal_category, 
  sum(no_of_views) AS total 
FROM recipes 
WHERE author = ‘John’ 
GROUP BY meal_category;

Estamos usando WHERE porque precisamos filtrar os registros (portanto, só obtemos os dados de John) antes de colocarmos os registros em grupos por categoria de refeição. Veja como fica o resultado:

Os KPIs de John

HAVING e WHERE estão bem descritos em nossos artigos Qual é a diferença entre WHERE e HAVING Clauses in SQL? e 5 Exemplos de GROUP BY. Se você gostaria de ver mais exemplos sobre este tópico, sugiro que comece por aí.

3. Listagem de uma coluna dentro do SELECT mas não no GROUP BY

Agora suponha que você queira ver o número total de visualizações por meal_category e author. Podemos fazer isso? - ?só precisamos acrescentar a coluna do autor à nossa consulta anterior:

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

Isto lhe parece bem? É claro que não; ele vai lançar um erro na maioria dos motores SQL. Por exemplo, a Oracle lhe dirá "erro: Não é uma expressão "GROUP BY". Por que este erro confuso? O que está faltando aqui?

Bem, o motor SQL não sabe como calcular o total para cada autor porque não o incluímos na cláusula GROUP BY; o autor do atributo não está listado dentro da cláusula GROUP BY. Este é outro erro comum com a cláusula GROUP BY.

Vamos corrigir esta consulta e executá-la mais uma vez:

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

O resultado é:

Agora isto parece estar bem. Lembre-se, as colunas não agregadas que estão listadas em SELECT também devem ser listadas em GROUP BYEm nosso caso, as colunas não agregadas são meal_category e author, que agora estão em SELECT e GROUP BY.

Você não lista colunas que estão dentro de funções agregadas em GROUP BY. Em nosso exemplo, a coluna no_of_views é usada na função agregada SUM() e, portanto, não está listada na cláusula GROUP BY.

Se você gostaria de saber mais sobre este tópico, confira nosso artigo GROUP BY Cláusula: Você conhece bem? Ele explica porque as colunas SELECTed precisam aparecer na cláusula GROUP BY. Além disso, Como corrigir um erro "Não é um erro de GROUP BY Expressão" dá mais exemplos relacionados a este tipo de erro.

4. Não Agrupar por uma Chave Exclusiva

Agora vamos tentar algo mais. Suponha que queiramos obter o número médio de páginas vistas para cada autor de receita. A consulta seguinte calcula o número médio total de page views para cada autor usando o nome do autor:

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

Ao olhar para o resultado, você notará que Lisa tem uma média de 116101,5 page views:

GROUP BY autor - mas os nomes não são únicos

No entanto, na verdade, temos dois autores chamados Lisa em nossa mesa. Quando agrupamos os resultados pela coluna do autor, os dois Lisas são calculados em média juntos. Por quê? Porque estamos usando uma coluna não única no GROUP BY. Isto significa que nem todos os valores de agrupamento têm que ser únicos. Se quisermos ver a média de cada Lisa separadamente, devemos adicionar author_id (uma coluna única) à lista GROUP BY:

SELECT 
  author, author_id
  avg(no_of_views) 
FROM recipes 
GROUP BY author, author_id;

Agora vemos como as receitas de Lisa(id=11) são muito mais vistas do que as receitas de Lisa(id=5):

GROUP BY com autor e autor_id

É importante pensar sempre em agrupar chaves. Os valores de agrupamento devem ser únicos e devem representar cada grupo da maneira desejada. Caso contrário, você obterá resultados imprecisos, confusos e possivelmente um GROUP BY erro.

5. CONTA(distinta) e COUNT(*) confusas

Se você estiver curioso para ver o número total de autores para cada categoria de refeição, você pode escrever uma declaração GROUP BY para calcular isso. Vamos usar COUNT(*) e recuperar o número de autores em cada categoria:

SELECT 
  meal_category, 
  count(*) 
FROM recipes 
GROUP BY meal_category;

Aqui está o resultado - mas não é o que você esperava, não é?

Este é o número total de receitas em cada categoria, não o número total de autores. Por que isso acontece? Bem, COUNT(*) conta todas as fileiras de cada grupo. A receita da tabela contém informações sobre uma recipe nível - cada registro é uma receita. Esta consulta conta as receitas (linhas) em cada categoria, não os autores da receita.

Um autor pode ter muitas receitas em cada categoria, então para obter as informações que você deseja, você deve contar autores distintos (usando COUNT(distinct author) ao invés de COUNT(*)) dentro de cada grupo. Este é um erro muito comum no GROUP BY.

Então, quando você deve usar COUNT(*), COUNT(expression) e COUNT(distinct expression)?

Vamos dar uma olhada em um exemplo:

SELECT 
  meal_category, 
  count(distinct author), 
  count(author),
  count(*) 
FROM recipes 
GROUP BY meal_category;

A diferença entre COUNT(*) e COUNT(expression) é visível se estivermos fazendo cálculos em uma coluna que tem alguns valores em falta. Quando valores faltantes estão presentes, COUNT(*) contará todos os registros de um grupo e COUNT(expression) contará apenas valores não-nulos.

No exemplo acima, COUNT(*) e COUNT(author) dão exatamente o mesmo resultado porque a coluna do autor não tem nenhum valor NULL.

COUNT(distinct author) nos dá o número de autores distintos para cada categoria, que não é o mesmo que COUNT(*). Por exemplo, a categoria de refeição aperitivo fria contém seis receitas de dois autores distintos. COUNT(*) conta o número de receitas (registros) em cada categoria, enquanto COUNT(distinct author) conta o número de autores distintos.

Portanto, se você gostaria de exibir o número total de autores distintos por cada categoria de refeição, use COUNT(distinct author). Aqui está a consulta correta:

SELECT 
  meal_category, 
  count(distinct author)
FROM recipes 
GROUP BY meal_category;
GROUP BY meal_category;

Para uma explicação mais detalhada, veja Qual é a diferença entre COUNT(*), COUNT(1), COUNT(nome da coluna), e COUNT(nome da coluna DISTINCT)?

6. Problemas na utilização de funções agregadas com NULLs

Este é outro problema de "valor em falta". Digamos que você queira calcular o número médio total de pontos de vista do mês anterior para cada categoria. Seu colega calculou esses números, mas eles gostariam que você verificasse o resultado duas vezes.

Aqui está sua pergunta:

SELECT
      meal_category,
      avg(no_of_views_lst_mth) as average,
  FROM recipes 
GROUP BY meal_category;

E o que você recebe é ...

O resultado parece bom e você está confiante quando se trata da exatidão de sua consulta. Entretanto, seu colega obteve números ligeiramente diferentes:

O que acabou de acontecer? Por que os resultados diferentes?

Em poucas palavras, os diferentes resultados surgem de diferentes interpretações dos valores em falta.

A coluna no_of_views_lst_mth representa o número total de page views no mês anterior. Se uma receita foi criada no mês atual, esta coluna será NULL para aquela linha.

Por exemplo, a receita de Cheesecake de Banana da Ally foi escrita no mês corrente, portanto não há estatísticas para o mês anterior:

O cheesecake de banana foi publicado no mês corrente

Agora, vamos voltar a essas médias e seus diferentes resultados. As médias são calculadas como a soma total de no_of_views_lst_mth dividida pelo número total de registros. Se você usar a função AVG() e as NULLs estiverem presentes, o motor simplesmente ignora as NULLs e faz cálculos sem elas. Isto é o que aconteceu quando você executou sua consulta - os NULLs foram omitidos. Em alguns casos, você vai querer substituir os NULLs por 0 (porque a lógica comercial dita); foi o que seu colega fez, o que produziu números ligeiramente diferentes. Aqui está a pergunta de seu colega:

SELECT
      meal_category,
           avg(CASE WHEN no_of_views_lst_mth is null 
            THEN 0 
            ELSE no_of_views_lst_mth END) AS average
FROM recipes 
GROUP BY meal_category;

Observe como as médias destas duas consultas tratam os NULLs de forma diferente. Por exemplo, a categoria "sobremesas" contém NULLs. Assim, a primeira consulta omite estas linhas e não as conta para o número total de linhas; isto dá o valor 144349,72. A segunda consulta substitui todos os NULLs por zero e conta estas linhas na média, dando um valor menor de 129914,75.

Eu diria que ambas as consultas poderiam ser válidas, dependendo de como você quer calcular as médias.

7. Usando COUNT(*) com GROUP BY e um LEFT JOIN

Usar GROUP BY com uma declaração LEFT JOIN pode ser bastante confuso - especialmente com COUNT(). Vamos ver como COUNT(*) e COUNT(expression) funcionam em um LEFT JOIN.

Vamos supor que alguém no marketing tem a seguinte tabela, recipes_campaigns. Ela contém informações sobre o número de campanhas realizadas em cada categoria de refeição no mês corrente:

receitas_campaign

Além dos dados em recipes_campaignsO comerciante também quer ver o número de receitas para cada categoria de refeição. Para isso, precisaremos das informações do recipes mesa. Portanto, vamos juntar estas duas tabelas e calcular o número de receitas usando COUNT(*), desta forma:

SELECT 
      a.meal_category,
      count(*),
     FROM recipes_campaigns a 
LEFT JOIN recipes b ON a.meal_category=b.meal_category
GROUP BY a.meal_category;

Aqui está o resultado:

Isto não é o que esperávamos. A tabela recipe não contém nenhuma receita na categoria "brunch", então por que então conseguimos essa 1 no resultado? Isto acontece porque COUNT() é aplicado ao resultado LEFT JOIN! Quando você LEFT JOIN duas tabelas, a categoria 'brunch' estará presente na saída - mesmo que não haja receitas ou categorias correspondentes no recipe mesa.

Como podemos consertar isso? Se usarmos COUNT(expression) em vez de COUNT(*), teremos o resultado que queremos:

SELECT 
      a.meal_category,
      count(author_id),
FROM recipes_campaigns a 
LEFT JOIN recipes b ON a.meal_category=b.meal_category
GROUP BY a.meal_category;

Uma vez que você administre isto, você obtém:

Aqui, COUNT(author_id) conta apenas os valores não-NULL em author_id após a execução do LEFT JOIN. Não há nenhum valor author_id para a categoria 'brunch'; em outras palavras, é NULL e o resultado para essa categoria é 0.

Você Pode Resolver GROUP BY Erros!

Através de vários exemplos, exploramos GROUP BY e os erros mais comuns que os iniciantes freqüentemente cometem. Espero que agora você tenha uma melhor noção de como GROUP BY funciona e o que está causando esses erros estranhos ou resultados confusos.

GROUP BY é realmente muito importante na criação de relatórios. Se você quiser aprender como construir bons relatórios, recomendo nossa Como Criar Relatórios Básicos em SQL curso. Ele tem muitos exercícios interativos que lhe permitem ganhar experiência escrevendo GROUP BY consultas para relatórios. E mais experiência certamente reduz a possibilidade de erros!