Como utilizar o CASO QUANDO EM GROUP BY

Aprenda como você pode combinar SQL CASE QUANDO e GROUP BY para criar categorias personalizadas em suas consultas SQL.

Dados brutos, por sua própria natureza, nem sempre são legíveis por humanos. Muitas vezes, os dados que você está consultando estão em sua forma mais informal. Exemplos disto incluem códigos para diferentes departamentos comerciais ou SKUs de produtos que representam produtos específicos. A olho nu, estes códigos não significam nada, portanto, puxá-los para um relatório não é útil para a pessoa que os lê.

Há também situações em que os dados brutos devem ser enrolados até níveis mais altos para torná-los mais facilmente digeríveis. Por exemplo, uma lista de 50 produtos poderia ser enrolada até 5 categorias de produtos, tornando seu relatório muito mais fácil de ler e entender.

Nestes tipos de situações, podemos usar as declarações CASE WHEN e GROUP BY para formatar os dados e adicionar metadados ao nosso conjunto de dados original. Vamos explorar como estas declarações funcionam ao longo deste artigo. Para uma análise mais profunda destes conceitos, leve nosso Como Criar Relatórios Básicos em SQLCASE GROUP BYO artigo é um grande seguimento deste artigo.

Primeiro, vamos rever a declaração de CASE WHEN com alguns exemplos. Se você quiser mais informações sobre a declaração CASE, não deixe de ler nosso artigo Como utilizar o CASO em SQL.

Uma Breve Revisão do CASO QUANDO

Você pode pensar na declaração CASE WHEN como se...então lógica para sua consulta. Ela avalia as condições e se achar que a condição é verdadeira, retornará um resultado específico definido.

Há três peças importantes para as declarações CASE em SQL: CASE WHEN THEN , e END. Cada uma delas é necessária ou sua consulta retornará um erro.

Você começa a declaração com CASE WHEN para definir sua condição lógica. Depois disso, você usa THEN para definir o valor se essa condição for verdadeira. Após sua última declaração em THEN, você usa END para fechar a cláusula.

Outra cláusula opcional é a cláusula ELSE. Se todas as condições lógicas na declaração CASE WHEN falharem, você pode usar ELSE para atribuir um valor a estes dados. É, efetivamente, um "catch-all". Se seus dados falharem todas as condições e você não usar ELSE, os dados retornarão um valor NULL.

Este é o aspecto da sintaxe CASE WHEN:

CASE WHEN product = ‘Shirt’ THEN ‘Clothing’ 
     WHEN product = ‘Hat’ THEN ‘Accessories’ 
     ELSE ‘Other’
END 

Se o produto for uma camisa, o CASE WHEN a atribui à categoria Clothing. Se o produto for um chapéu, o CASE WHEN o atribui à categoria Accessories. Caso contrário, o CASE WHEN atribui o produto à categoria Other.

Para ilustrar melhor, considere os seguintes dados encontrados no cities mesa:

Se quiséssemos categorizar a população de cada cidade em baixa, média ou alta, correríamos:

SELECT 
  city,
  population, 
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END as population_level
FROM cities

Nesta consulta, estamos adicionando a lógica condicional baseada no campo population. Se a população for inferior a 500.000, então atribuímos o valor de Low. Se a população estiver entre 500.000 e 1.500.000, então atribuímos o valor Medium. Finalmente, se a população for maior que 1.500.000, então atribuímos o valor High. A coluna é então aliada a population_level. O resultado retornado se parece com isto:

E se quiséssemos descobrir a população média para cada population_level? Neste cenário, podemos conseguir isso incluindo uma agregação e uma cláusula GROUP BY. Demonstraremos isto na próxima seção.

Usando o CASO QUANDO com o GROUP BY

Exemplo 1: Agregações de categorias personalizadas

Agora que temos nossa categoria personalizada de population_level, podemos calcular diferentes métricas para isso. Neste exemplo, vamos calcular a população média para cada population_level.

Usaremos a mesma declaração CASE WHEN como acima, adicionaremos uma agregação para a média, e depois GROUP BY usando a mesma sintaxe.

Vamos fazer esta consulta:

SELECT  
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END as population_level, 
  AVG(population) as average_population
FROM cities
GROUP BY
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END

Usamos a mesma categoria de população da consulta acima. Acrescentamos um cálculo para a população média. Note que repetimos a mesma expressão CASE WHEN em SELECT e em GROUP BY.

Quando esta consulta é executada, os dados são primeiramente avaliados nas condições lógicas da declaração CASE WHEN e é atribuído um valor para population_level. Em seguida, a média é computada em cada um desses níveis com o GROUP BY.

Os resultados retornados parecem ser os seguintes:

Se você precisar de uma atualização sobre a cláusula GROUP BY, leia este artigo sobre GROUP BY em SQL.

Exemplo 2: CASO QUANDO COM ELSE em GROUP BY

Outra maneira de escrever esta consulta seria utilizar a cláusula ELSE. Você definiria os dois primeiros níveis de população e depois usaria ELSE para baldear todas as outras cidades em high. Isto se pareceria com a seguinte consulta:

SELECT  
  CASE 
    WHEN population < 500000 THEN ‘Low’ 
    WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
    ELSE ‘High’ 
  END as population_level, 
  AVG(population) as average_population
FROM cities
GROUP BY 
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END

Não precisamos necessariamente definir a lógica para cidades de alta população; se a cidade falhar as duas primeiras declarações lógicas, então a população é maior do que 1.500.000.

Exemplo 3: CASO QUANDO as afirmações dentro das agregações

Também podemos colocar CASE WHEN declarações dentro de funções agregadas para contar o número de registros que atendem a determinadas condições. Continuando com o mesmo exemplo, vamos considerar como contaríamos o número de cidades em cada population_level.

Precisaremos de três declarações CASE WHEN separadas para este exemplo, uma para cada condição que definimos em nossa declaração CASE WHEN: low, medium, e high.

SELECT  
  SUM(CASE 
      WHEN population < 500000 
      THEN 1 
      ELSE 0 END) as low_pop_ct,
  SUM(CASE 
      WHEN population >= 500000 and population < 1500000 
      THEN 1   
      ELSE 0 END) as medium_pop_ct, 
  SUM(CASE 
      WHEN population >= 1500000 
      THEN 1 
      ELSE 0 END) as high_pop_ct
FROM cities

Vamos caminhar nesta linha por linha. Na primeira linha, estamos avaliando se a cidade tem uma população inferior a 500.000 habitantes. Se tiver, estaremos atribuindo o valor de 1 a essa cidade. Se não tiver, é atribuído 0 pela cláusula ELSE. Em seguida, envolvemos toda essa declaração CASE WHEN em um SUM(). Isto conta efetivamente as linhas para as cidades com baixa população.

Repetimos então este padrão nas duas linhas seguintes, usando as mesmas condições lógicas que usamos anteriormente para atribuir o valor de medium e high às cidades com base em sua população.

Esta consulta retornará:

Agora temos uma contagem do número de cidades que se enquadram em cada categoria.

Interessado em saber mais? Verifique como usar CASO QUANDO com SUM() para mais exemplos.

Exemplo 4: CASO QUANDO EM GROUP BY

Vamos dar uma olhada em mais alguns exemplos usando CASE WHEN em GROUP BY. Os dados abaixo estão contidos na tabela products.

Vamos melhorar estes dados com uma nova coluna que contém um product_category. Isto nos permitirá agrupar os produtos individuais em uma categoria de nível superior para que possamos então computar as agregações.

A fim de adicionar uma coluna para product_category, vamos executar:

SELECT *, 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END as product_category 
FROM products

Estamos usando o operador LIKE para pesquisar o campo de descrição de cada string em citações. O operador % apenas significa que pode haver qualquer coisa antes ou depois. Estamos efetivamente pesquisando cada descrição para qualquer uma das palavras-chave listadas nas declarações WHEN. Os resultados são mostrados abaixo:

Agora que temos nosso novo campo product_category, podemos contar o número de produtos em cada categoria, utilizando GROUP BY:

SELECT
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
   END as product_category, 
   COUNT(DISTINCT description) as number_of_products
FROM products 
GROUP BY 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END 

Neste exemplo, utilizamos CASE WHEN na cláusula SELECT, mas isto nem sempre é necessário. Você também poderia simplesmente executar a consulta acima sem ela:

SELECT COUNT(DISTINCT description) as number_of_products
FROM products 
GROUP BY 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END 

O que vem a seguir com CASO QUANDO e GROUP BY?

Como você pode ver, há muitos cenários diferentes onde a combinação das declarações CASE WHEN e GROUP BY é extremamente útil. Elas permitem adicionar lógica comercial aos seus dados e depois calcular métricas com base nos campos de dados recém-definidos. Você pode usar as declarações CASE WHEN tanto fora como dentro das agregações; elas seguem a mesma sintaxe. Comece sua declaração com CASE WHEN para definir sua lógica condicional e depois atribua valores com as declarações THEN/ELSE. Finalmente, feche-a com END.

Não deixe de verificar nosso curso sobre Como Criar Relatórios Básicos em SQL. Você aprenderá todas as nuances de CASE e GROUP BY e terá problemas de prática na vida real para completar!