Funciones agregadas y agrupación — Ejercicios avanzados
Análisis multidimensional agrupando por dos columnas simultáneamente, segmentación con CASE WHEN, subconsultas que calculan un agregado para usarlo como referencia en el filtrado exterior, y las funciones de reporte GROUP_CONCAT y WITH ROLLUP. Todo aplicado sobre la tabla film de Sakila.
Resumen rápido
Análisis multidimensional agrupando por dos columnas simultáneamente, segmentación con CASE WHEN, subconsultas que calculan un agregado para usarlo como referencia en el filtrado exterior, y las funciones de reporte GROUP_CONCAT y WITH ROLLUP. Todo aplicado sobre la tabla film de Sakila.
Conceptos clave
Consulta que agrupa por dos o más columnas simultáneamente, generando una tabla cruzada. Permite comparar cómo se distribuye una magnitud en la intersección de dos categorías (por ejemplo: rating × special_features).
Resultado de agrupar por múltiples columnas. Cada fila representa una combinación única de los valores de esas columnas, con sus métricas calculadas. Es el equivalente SQL de una tabla dinámica de Excel.
Expresión condicional que crea una columna calculada on-the-fly que puede usarse directamente como criterio de agrupación. Permite segmentar datos en categorías personalizadas (bajo/medio/alto) sin alterar la tabla.
Subconsulta que devuelve exactamente un valor (una fila, una columna). Se puede usar en WHERE, en SELECT o como parte de una expresión matemática. El caso más habitual es calcular un promedio global para compararlo con cada fila.
Diferencia entre el valor de un grupo y el promedio global. Se calcula restando el resultado de una subconsulta (que devuelve el promedio) del valor del grupo. Permite ver qué grupos están por encima y cuáles por debajo de la media.
Función agregada que concatena todos los valores de una columna dentro de cada grupo en una cadena de texto. Acepta SEPARATOR para definir el carácter separador y DISTINCT para eliminar duplicados antes de concatenar.
Función de texto que extrae los primeros N segmentos de una cadena separados por un delimitador. Se combina con GROUP_CONCAT para limitar el número de elementos en la lista concatenada.
Modificador de GROUP BY que añade automáticamente filas con subtotales por cada nivel de agrupación y una fila de total general. Las columnas agrupadas aparecen como NULL en las filas de subtotal; se usa IFNULL para reemplazarlos con etiquetas.
Contenido de la clase
Análisis multidimensional: agrupar por dos columnas
Hasta ahora los ejercicios agrupaban por una sola columna. Agrupar por dos columnas simultáneamente genera una tabla cruzada: cada fila representa una combinación única de los dos criterios. En la tabla film, agrupar por rating y special_features a la vez permite ver cómo se distribuyen las películas en la intersección de clasificación y tipo de extras.
El resultado muestra métricas (total de películas, duración media, coste de reposición) para cada combinación concreta: películas G con trailers, películas G con deleted scenes, películas PG con trailers, etc. Es el equivalente SQL de una tabla dinámica de Excel.
CASE WHEN: segmentar datos en categorías propias
CASE WHEN crea una nueva columna calculada en tiempo de consulta, sin modificar la tabla. Evalúa las condiciones en orden y devuelve el primer valor cuya condición se cumpla. Si ninguna condición se cumple, devuelve el valor del ELSE. La expresión siempre se cierra con END.
Un caso práctico es segmentar las películas por coste de reposición en tres grupos: Bajo (menos de 15), Medio (entre 15 y 20) y Alto (más de 20). Una vez creado el segmento con CASE WHEN, se puede usar directamente en GROUP BY para agregar métricas por segmento.
- CASE y END son obligatorios: sin ellos la expresión queda abierta y MySQL devuelve un error de sintaxis.
- El alias asignado con END AS es el nombre con el que se referencia la columna en GROUP BY y ORDER BY.
- Se pueden encadenar tantos WHEN como casos se necesiten. El ELSE captura todo lo que no encaja en ningún caso anterior.
Subconsultas con agregados: comparar contra la media global
Una subconsulta escalar es una consulta completa encerrada entre paréntesis que devuelve un único valor. Ese valor se puede usar como umbral en un WHERE o como referencia en un cálculo.
El ejemplo clásico es identificar las películas con duración superior a la media: el WHERE no puede contener "length > AVG(length)" directamente, porque AVG(length) necesita procesar todas las filas primero. La solución es calcular ese promedio en una subconsulta y comparar cada fila contra el número que devuelve.
Una variante más avanzada usa una subconsulta dentro del SELECT para calcular la tarifa promedio global y restarla de la tarifa media de cada grupo. El resultado es la desviación de cada clasificación respecto al promedio general, lo que permite ver qué ratings cobran más y cuáles menos de la media.
GROUP_CONCAT: listas compactas en un campo de texto
GROUP_CONCAT concatena todos los valores de una columna dentro de cada grupo en una única cadena de texto. Es útil para generar reportes donde se quiere ver, por ejemplo, qué títulos de película corresponden a cada clasificación, sin necesidad de múltiples filas.
SEPARATOR define el carácter que separa los valores (por defecto es una coma). DISTINCT antes del nombre de la columna elimina los duplicados antes de concatenar. Para limitar el número de elementos en la cadena, se combina con SUBSTRING_INDEX, que recorta la cadena hasta el enésimo separador.
WITH ROLLUP: subtotales automáticos
WITH ROLLUP se añade al final de GROUP BY y genera automáticamente filas de subtotales para cada nivel de agrupación, más una fila de total general al final. No requiere ningún cálculo adicional: MySQL lo hace solo.
Las filas de subtotal se identifican porque sus columnas de agrupación tienen valor NULL. IFNULL(columna, 'Subtotal') permite reemplazar esos NULL por una etiqueta legible. En un GROUP BY por rating y release_year, WITH ROLLUP añade una fila con el subtotal por rating (release_year = NULL) y una fila con el total general (rating = NULL, release_year = NULL).
Código y ejemplos
Análisis multidimensional: rating × special_features
-- Tabla cruzada: métricas por combinación de rating y special_features
SELECT rating,
special_features,
COUNT(*) AS total_peliculas,
AVG(length) AS duracion_media,
SUM(replacement_cost) AS coste_total_reposicion
FROM film
GROUP BY rating, special_features
ORDER BY rating, total_peliculas DESC;Cada fila del resultado representa la intersección de un rating y un tipo de special_features. GROUP BY con dos columnas genera tantas filas como combinaciones únicas existen.
CASE WHEN: segmentar por coste de reposición
-- Clasificar películas en tres segmentos de coste y comparar sus métricas
SELECT CASE
WHEN replacement_cost < 15 THEN 'Bajo'
WHEN replacement_cost BETWEEN 15 AND 20 THEN 'Medio'
ELSE 'Alto'
END AS segmento_coste,
COUNT(*) AS cantidad,
AVG(rental_rate) AS tarifa_media,
AVG(length) AS duracion_media
FROM film
GROUP BY segmento_coste
ORDER BY cantidad DESC;El alias "segmento_coste" asignado en END AS se usa directamente en GROUP BY y ORDER BY. Los tres segmentos muestran que el rango de tarifas medias entre ellos es pequeño.
Subconsultas con agregados
-- Películas con duración superior a la media del catálogo
SELECT title,
length
FROM film
WHERE length > (SELECT AVG(length) FROM film)
ORDER BY length DESC;
-- Desviación de la tarifa media de cada rating respecto al promedio global
SELECT rating,
AVG(rental_rate) AS tarifa_media,
(SELECT AVG(rental_rate) FROM film) AS tarifa_global,
AVG(rental_rate) - (SELECT AVG(rental_rate) FROM film) AS diferencia
FROM film
GROUP BY rating
ORDER BY diferencia DESC;La subconsulta (SELECT AVG(rental_rate) FROM film) devuelve un único número y se evalúa una sola vez. Ese número se usa como referencia en el WHERE o en el cálculo de la diferencia.
GROUP_CONCAT y WITH ROLLUP
-- Listado compacto: primeros 5 títulos por clasificación
SELECT rating,
SUBSTRING_INDEX(
GROUP_CONCAT(title SEPARATOR ' / '),
'/',
5
) AS muestra_titulos,
COUNT(*) AS total_peliculas
FROM film
GROUP BY rating
ORDER BY total_peliculas DESC;
-- Ratings únicos disponibles por tipo de special_features
SELECT special_features,
GROUP_CONCAT(DISTINCT rating SEPARATOR ', ') AS ratings_disponibles,
AVG(replacement_cost) AS coste_medio
FROM film
GROUP BY special_features
ORDER BY COUNT(*) DESC;
-- Subtotales por rating con WITH ROLLUP
SELECT IFNULL(rating, 'TOTAL') AS clasificacion,
IFNULL(release_year, 'Subtotal') AS anio,
COUNT(*) AS total,
AVG(length) AS duracion_media
FROM film
GROUP BY rating, release_year WITH ROLLUP;GROUP_CONCAT genera toda la cadena; SUBSTRING_INDEX la recorta. WITH ROLLUP añade filas NULL automáticamente; IFNULL las convierte en etiquetas legibles.
Errores comunes
Buenas prácticas
Ejercicios
- Crea una tabla cruzada que muestre, para cada combinación de rating y release_year, el total de películas y la duración media.
- Usa CASE WHEN para segmentar las películas por duración en "Corta" (< 90 min), "Media" (90–120 min) y "Larga" (> 120 min). Muestra cuántas hay en cada segmento y la tarifa media de alquiler.
- Escribe una consulta que devuelva el título y la duración de todas las películas cuya duración supera el doble de la duración mínima del catálogo (usa una subconsulta para obtener MIN(length)).
- Calcula la desviación del coste de reposición medio de cada rating respecto al promedio global. ¿Qué clasificación tiene el mayor sobrecoste?
- Usa GROUP_CONCAT con DISTINCT para mostrar, por cada tipo de special_features, qué ratings están disponibles en una sola cadena separada por barras.
- Añade WITH ROLLUP a un GROUP BY rating para obtener el total de películas por clasificación más el total general. Usa IFNULL para etiquetar las filas de subtotal.
Checklist final
- Puedo agrupar por dos columnas simultáneamente con GROUP BY col1, col2 y entiendo el resultado.
- Sé escribir una expresión CASE WHEN...THEN...ELSE...END y usarla en GROUP BY.
- Entiendo qué es una subconsulta escalar y puedo usarla en WHERE y en SELECT.
- Puedo comparar el valor de un grupo contra el promedio global usando una subconsulta.
- Sé usar GROUP_CONCAT con SEPARATOR y DISTINCT para generar listas compactas.
- Puedo añadir WITH ROLLUP a un GROUP BY y usar IFNULL para etiquetar los subtotales.
¿Has terminado la clase?
Márcala como repasada y vuelve cuando quieras revisarla.