Lección 4.4: Agregación Condicional

La agregación condicional en SQL te permite calcular múltiples métricas en una sola consulta sin necesidad de ejecutar varias consultas separadas. La idea es simple: dentro de una función de agregación (SUM, COUNT, AVG), utilizas una expresión condicional (más a menudo CASE, pero en algunos SGBD puede ser otro operador condicional) que incluye solo las filas que coinciden con una condición en el cálculo.

Este enfoque es especialmente útil para informes, paneles y análisis donde necesitas varios indicadores a la vez: conteos, sumas, participaciones, desgloses de estado, y así sucesivamente.

En esta lección, cubriremos:

• cómo funciona la agregación condicional;
• cómo calcular conteos, sumas y promedios condicionales;
• cómo construir informes estilo pivote (transformando filas en columnas) con CASE.

Idea Central

Plantilla clásica de agregación condicional:

AGGREGATION_FUNCTION(CASE WHEN condition THEN value ELSE 0 END)

o una versión corta:

AGGREGATION_FUNCTION(CASE WHEN condition THEN 1 END)

Lo que sucede:

• CASE devuelve un valor dependiendo de la condición. En la versión corta, si la condición no se cumple, devuelve NULL;
• la función de agregación acumula el resultado por grupo;
• la salida es una métrica basada en la condición.

Suma Condicional

Ejemplo: totales de ventas por personal divididos por rangos de monto

SELECT
    staff_id,
    SUM(CASE WHEN amount < 2 THEN amount ELSE 0 END) AS low_amount_total,
    SUM(CASE WHEN amount BETWEEN 2 AND 6 THEN amount ELSE 0 END) AS medium_amount_total,
    SUM(CASE WHEN amount > 6 THEN amount ELSE 0 END) AS high_amount_total
FROM payment
GROUP BY staff_id;

Resultado: una consulta devuelve tres sumas diferentes para cada miembro del personal.

Promedio Condicional

Ejemplo: monto promedio de grandes pagos por personal

SELECT
    staff_id,
    AVG(CASE WHEN amount >= 5 THEN amount END) AS avg_big_payment
FROM payment
GROUP BY staff_id;

Resultado: para cada miembro del personal, el monto promedio se calcula solo para los pagos donde amount >= 5.

Por qué ELSE 0 generalmente no es necesario aquí:

• AVG se calcula como la suma de valores dividida por su conteo;
• si pones 0 para las filas que no cumplen con la condición, esos ceros se incluirán y disminuirán el promedio;
• por eso, el AVG condicional generalmente usa ELSE NULL o omite ELSE por completo.

Conteo Condicional

Ejemplo: número de pagos en cada rango de monto

SELECT
    customer_id,
    COUNT(CASE WHEN amount < 2 THEN 1 END) AS low_payments,
    COUNT(CASE WHEN amount BETWEEN 2 AND 6 THEN 1 END) AS medium_payments,
    COUNT(CASE WHEN amount > 6 THEN 1 END) AS high_payments
FROM payment
GROUP BY customer_id;

Resultado: para cada cliente, la consulta devuelve el número de pagos bajos, medios y altos.

Por qué ELSE no es necesario aquí:

• si la condición es verdadera, CASE devuelve 1;
• si la condición es falsa y ELSE no está especificado, CASE devuelve NULL;
• COUNT(expression) cuenta solo valores no NULL, por lo que solo se incluyen las filas donde la condición es verdadera.

Importante: no uses ELSE 0 en este patrón para COUNT, porque 0 tampoco es NULL, y entonces COUNT comienza a contar casi todas las filas.

Ejemplo: conteo de alquileres devueltos y no devueltos

SELECT
    staff_id,
    COUNT(return_date) AS returned_count,
    COUNT(CASE WHEN return_date IS NULL THEN 1 END) AS not_returned_count
FROM rental
GROUP BY staff_id;

Lo que sucede aquí:

• COUNT(return_date) cuenta solo valores no NULL, es decir, el número de alquileres devueltos;
• COUNT(CASE WHEN return_date IS NULL THEN 1 END) cuenta solo las filas donde falta la fecha de devolución, es decir, alquileres no devueltos;
• GROUP BY staff_id construye contadores separados para cada miembro del personal.

Resultado: en una consulta, obtienes ambas métricas para cada miembro del personal.

Técnica de Pivote con CASE

Qué es un pivote en SQL

Un pivote transforma filas en columnas. Normalmente, los datos de origen contienen categorías en filas, pero en un informe necesitas esas categorías como columnas separadas.

Muchos SGBD tienen un operador PIVOT dedicado, pero la forma universal y portátil es la agregación condicional con CASE.

Plantilla básica de pivote

SELECT
    group_column,
    SUM(CASE WHEN pivot_key = 'A' THEN measure ELSE 0 END) AS col_a,
    SUM(CASE WHEN pivot_key = 'B' THEN measure ELSE 0 END) AS col_b,
    SUM(CASE WHEN pivot_key = 'C' THEN measure ELSE 0 END) AS col_c
FROM source_table
GROUP BY group_column;

Ejemplo: pivote por clasificación de películas

A continuación se muestra un ejemplo donde, para cada categoría de película, contamos las películas por clasificación en columnas separadas:

SELECT
    c.name AS category,
    COUNT(CASE WHEN f.rating = 'G' THEN 1 END) AS g_films_count,
    AVG(CASE WHEN f.rating = 'G' THEN length ELSE 0 END) AS g_films_average_length,
    COUNT(CASE WHEN f.rating = 'PG' THEN 1 END) AS pg_films_count,
    AVG(CASE WHEN f.rating = 'PG' THEN length ELSE 0 END) AS pg_films_average_length,
    COUNT(CASE WHEN f.rating = 'PG-13' THEN 1 END) AS pg13_films_count,
    AVG(CASE WHEN f.rating = 'PG-13' THEN length ELSE 0 END) AS pg13_films_average_length,
    COUNT(CASE WHEN f.rating = 'R' THEN 1 END) AS r_films_count,
    AVG(CASE WHEN f.rating = 'R' THEN length ELSE 0 END) AS r_films_average_length,
    COUNT(CASE WHEN f.rating = 'NC-17' THEN 1 END) AS nc17_films_rating,
    AVG(CASE WHEN f.rating = 'NC-17' THEN length ELSE 0 END) AS nc17_films_average_length
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY c.name;

Resultado: cada fila es una categoría, y las columnas muestran el número de películas de cada clasificación y su duración promedio.

Recomendaciones Prácticas

• Para SUM, ELSE 0 se usa generalmente para que las filas fuera de la condición den una contribución cero.
• Para COUNT(CASE ...), ELSE generalmente no es necesario: COUNT ya ignora NULL.
• Para AVG(CASE ...), se usa más a menudo ELSE NULL o no se usa ELSE para que el promedio no se reduzca.
• Si hay muchas métricas condicionales, usa alias claros (*_count, *_total).
• Verifica que las condiciones de CASE no se superpongan cuando las categorías deben ser mutuamente excluyentes.
• Para consultas grandes, valida la lógica primero en un conjunto de datos pequeño o con LIMIT.

Uso Práctico

1. Pivote por día de la semana:

    SELECT
        MONTH(rental_date) AS rental_month,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Monday' THEN 1 ELSE 0 END) AS monday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Tuesday' THEN 1 ELSE 0 END) AS tuesday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Wednesday' THEN 1 ELSE 0 END) AS wednesday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Thursday' THEN 1 ELSE 0 END) AS thursday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Friday' THEN 1 ELSE 0 END) AS friday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Saturday' THEN 1 ELSE 0 END) AS saturday_rentals,
        SUM(CASE WHEN DAYNAME(rental_date) = 'Sunday' THEN 1 ELSE 0 END) AS sunday_rentals
    FROM rental
    GROUP BY MONTH(rental_date);
   

   Esta consulta muestra cuántos alquileres se realizaron en cada mes por día de la semana.

2. Cálculo de participación condicional:

   SELECT
       customer_id,
       SUM(CASE WHEN amount >= 5 THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS high_payment_share
   FROM payment
   GROUP BY customer_id;
   

Nota Rápida sobre la Sintaxis FILTER

En algunos SGBD (por ejemplo, PostgreSQL), puedes mover la condición de CASE a FILTER:

COUNT(*) FILTER (WHERE condition)
SUM(amount) FILTER (WHERE condition)

El significado es el mismo que la agregación condicional con CASE: la función de agregación procesa no todas las filas, sino solo las filas que pasan la condición en WHERE dentro de FILTER.

Esta sintaxis es a menudo más fácil de leer, especialmente si en un solo SELECT necesitas calcular varias métricas diferentes con diferentes condiciones.

Por ejemplo:

SELECT
    customer_id,
    COUNT(*) AS total_payments,
    COUNT(*) FILTER (WHERE amount >= 5) AS big_payments_count,
    SUM(amount) FILTER (WHERE amount >= 5) AS big_payments_total
FROM payment
GROUP BY customer_id;

En este ejemplo:

• COUNT(*) cuenta todos los pagos del cliente;
• COUNT(*) FILTER (WHERE amount >= 5) cuenta solo los pagos grandes;
• SUM(amount) FILTER (WHERE amount >= 5) suma solo esos pagos.

Así, FILTER realiza el mismo trabajo que CASE, pero en una forma más compacta. Al mismo tiempo, es importante recordar que esta sintaxis no es compatible con todos los SGBD.

Conclusiones Clave de Esta Lección

• La agregación condicional es una función de agregación más una expresión condicional, más a menudo CASE.
• Con SUM(CASE ...), COUNT(CASE ...), y AVG(CASE ...), puedes obtener varias métricas en una sola consulta.
• El pivote con CASE es una forma universal de transformar filas en columnas.
• Este enfoque es muy adecuado para informes analíticos y paneles.

Al dominar la agregación condicional, podrás escribir consultas SQL más compactas y expresivas para análisis de negocios.