Cómo usar CASE WHEN en GROUP BY

Aprenda cómo puede combinar SQL CASE WHEN y GROUP BY para crear categorías personalizadas en sus consultas SQL.

Los datos en bruto, por su propia naturaleza, no siempre son legibles. Muchas veces, los datos que se consultan están en su forma menos formateada. Ejemplos de ello son los códigos de los distintos departamentos de la empresa o los SKU de productos que representan productos específicos. A simple vista, estos códigos no significan nada, por lo que incluirlos en un informe no es útil para la persona que los lee.

También hay situaciones en las que los datos sin procesar deben pasar a niveles superiores para que sean más fáciles de digerir. Por ejemplo, una lista de 50 productos podría desglosarse en 5 categorías de productos, lo que facilitaría la lectura y comprensión del informe.

En este tipo de situaciones, podemos utilizar las sentencias CASE WHEN y GROUP BY para dar formato a los datos y añadir metadatos a nuestro conjunto de datos original. A lo largo de este artículo veremos cómo funcionan estas sentencias. Si desea profundizar en estos conceptos, consulte nuestra página Cómo crear informes básicos con SQL que le guiará a través de todos los matices de CASE y GROUP BY. Es una excelente continuación de este artículo.

En primer lugar, repasemos la sentencia CASE WHEN con algunos ejemplos. Si desea obtener más información sobre la sentencia CASE, asegúrese de leer nuestro artículo Cómo utilizar CASE en SQL.

Breve revisión de CASE WHEN

Puede pensar en la sentencia CASE WHEN como una lógica if..then para su consulta. Evalúa condiciones y si encuentra que la condición es verdadera, devolverá un resultado específico definido.

Hay tres partes importantes en las sentencias CASE en SQL: CASE WHEN THEN y END. Cada una de ellas es necesaria o su consulta devolverá un error.

Comience la sentencia con CASE WHEN para definir su condición lógica. A continuación, utilice THEN para definir el valor si la condición es verdadera. Después de la última sentencia THEN, utilice END para cerrar la cláusula.

Otra cláusula opcional es la cláusula ELSE. Si todas las condiciones lógicas de la sentencia CASE WHEN fallan, puede utilizar ELSE para asignar un valor a estos datos. Se trata de una cláusula comodín. Si los datos no cumplen todas las condiciones y no se utiliza ELSE, los datos devolverán un valor NULL.

Así es como se ve la sintaxis de CASE WHEN:

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

Si el producto es una camisa, CASE WHEN lo asigna a la categoría Clothing. Si el producto es un sombrero, CASE WHEN lo asigna a la categoría Accessories. En caso contrario, CASE WHEN asigna el producto a la categoría Other.

Para ilustrarlo mejor, considere los siguientes datos de la tabla cities tabla:

Si quisiéramos clasificar la población de cada ciudad en baja, media o alta, ejecutarí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

En esta consulta, estamos añadiendo lógica condicional basada en el campo population. Si la población es inferior a 500.000 habitantes, le asignamos el valor Low. Si la población está entre 500.000 y 1.500.000 habitantes, le asignamos el valor Medium. Por último, si la población es superior a 1.500.000, asignamos el valor High. A continuación, asignamos a la columna el alias population_level. El resultado obtenido es el siguiente:

¿Y si quisiéramos averiguar la población media de cada population_level? En este escenario, podemos lograrlo incluyendo una cláusula de agregación y GROUP BY. Lo demostraremos en la siguiente sección.

Uso de CASE WHEN con GROUP BY

Ejemplo 1: Agregaciones de categorías personalizadas

Ahora que tenemos nuestra categoría personalizada de population_level, podemos calcular diferentes métricas para ella. En este ejemplo, calcularemos la población media de cada population_level.

Utilizaremos la misma sentencia CASE WHEN que arriba, añadiremos una agregación para el promedio y luego GROUP BY utilizando la misma sintaxis.

Vamos a ejecutar 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

Hemos utilizado la misma categoría de población que en la consulta anterior. Hemos añadido un cálculo para la población media. Observe que hemos repetido la misma expresión CASE WHEN en SELECT y en GROUP BY.

Cuando se ejecuta esta consulta, los datos se evalúan primero en las condiciones lógicas de la expresión CASE WHEN y se les asigna un valor para population_level. A continuación, se calcula la media de cada uno de estos niveles con GROUP BY.

Los resultados devueltos tienen este aspecto:

Si necesita un repaso de la cláusula GROUP BY, lea este artículo sobre GROUP BY en SQL.

Ejemplo 2: CASE WHEN con ELSE en GROUP BY

Otra forma de escribir esta consulta sería utilizar la cláusula ELSE. Se definirían los dos primeros niveles de población y se utilizaría ELSE para agrupar todas las demás ciudades en high. La consulta sería la siguiente:

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

No es necesario definir la lógica para las ciudades muy pobladas; si la ciudad no supera las dos primeras sentencias lógicas, la población es superior a 1.500.000 habitantes.

Ejemplo 3: Sentencias CASE WHEN dentro de agregaciones

También podemos colocar sentencias CASE WHEN dentro de funciones agregadas para contar el número de registros que cumplen ciertas condiciones. Continuando con el mismo ejemplo, consideremos cómo contaríamos el número de ciudades en cada population_level.

Necesitaremos tres sentencias CASE WHEN separadas para este ejemplo, una para cada condición que hemos definido en nuestra sentencia CASE WHEN: low medium y 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

Veamos esto línea por línea. En la primera línea, evaluamos si la ciudad tiene menos de 500.000 habitantes. Si es así, asignamos el valor 1 a esa ciudad. Si no es así, la cláusula ELSE le asigna el valor 0. A continuación, hemos envuelto toda la sentencia CASE WHEN en una SUM(). De este modo, contamos las filas de las ciudades con poca población.

A continuación repetimos este patrón en las dos líneas siguientes, utilizando las mismas condiciones lógicas que usamos antes para asignar el valor de medium y high a las ciudades en función de su población.

Esta consulta devolverá:

Ahora tenemos un recuento del número de ciudades que entran en cada categoría.

¿Quieres saber más? Echa un vistazo a cómo utilizar CASE WHEN con SUM() para ver más ejemplos.

Ejemplo 4: CASE WHEN en GROUP BY

Veamos algunos ejemplos más utilizando CASE WHEN en GROUP BY. Los datos que aparecen a continuación están contenidos en la tabla products.

Mejoremos estos datos con una nueva columna que contenga product_category. Esto nos permitirá agrupar los productos individuales en una categoría de nivel superior para poder calcular agregaciones.

Para añadir una columna para product_category, ejecutaremos:

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 utilizando el operador LIKE para buscar en el campo de descripción cada cadena entre comillas. El operador % sólo significa que puede haber cualquier cosa antes o después. De hecho, estamos buscando en cada descripción cualquiera de las palabras clave enumeradas en las sentencias WHEN. Los resultados se muestran a continuación:

Ahora que tenemos nuestro nuevo campo product_category, podemos contar el número de productos en cada categoría 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 

En este ejemplo, hemos utilizado CASE WHEN en la cláusula SELECT, pero no siempre es necesario. También puede ejecutar la consulta anterior sin ella:

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 

¿Qué sigue con CASE WHEN y GROUP BY?

Como puede ver, hay muchos escenarios diferentes donde la combinación de las sentencias CASE WHEN y GROUP BY son extremadamente útiles. Le permiten agregar lógica de negocios a sus datos y luego calcular métricas basadas en los campos de datos recién definidos. Puede utilizar las sentencias CASE WHEN tanto fuera como dentro de las agregaciones; siguen la misma sintaxis. Comience su sentencia con CASE WHEN para definir su lógica condicional y luego asigne valores con las sentencias THEN/ELSE. Finalmente, ciérrela con END.

No deje de consultar nuestro curso sobre Cómo crear informes básicos con SQL. Aprenderás todos los matices de CASE y GROUP BY y tendrás problemas prácticos de la vida real para completar.