Ejercicios Clase 04b Tema 04

Funciones agregadas y agrupación — Ejercicios fundamentales

Primera toma de contacto con las funciones agregadas aplicadas a datos reales. Usando la tabla film de la base de datos Sakila se practican los conteos con COUNT, las sumatorias con SUM, los promedios con AVG, los extremos con MIN/MAX y las combinaciones clásicas GROUP BY + COUNT y GROUP BY + AVG, hasta llegar al filtrado de grupos con HAVING.

#COUNT#SUM#AVG#MIN#MAX#GROUP BY#HAVING#Sakila

§ 01

Resumen rápido

§ 02

Conceptos clave

Sakila

Base de datos de ejemplo distribuida con MySQL que simula un videoclub: películas, actores, clientes, alquileres y pagos. Es la base de práctica estándar para ejercicios de agregación porque sus tablas tienen volumen suficiente para que los resultados sean significativos.

film (tabla)

Tabla principal de Sakila. Contiene 1000 películas con columnas como title, rating, length (minutos), rental_rate (precio de alquiler), replacement_cost (coste de reposición), special_features y release_year.

rating

Clasificación por edades de cada película en Sakila. Toma cinco valores posibles: G (todos los públicos), PG (guía parental), PG-13 (mayores de 13), R (restringido) y NC-17 (solo adultos).

replacement_cost

Coste de reposición de una película en el videoclub: lo que pagaría el cliente si pierde o daña el DVD. Es una columna numérica (DECIMAL) ideal para practicar SUM, AVG, MIN y MAX.

special_features

Características especiales de cada película: trailers, deleted scenes, behind the scenes, commentaries, o combinaciones de varias. Es una columna de texto que permite practicar GROUP BY con valores no numéricos.

Combinación clásica GROUP BY + COUNT

El patrón más habitual en análisis de datos: agrupar registros por una categoría y contar cuántos hay en cada grupo. Responde a preguntas del tipo "¿cuántas películas hay por clasificación?".

Combinación clásica GROUP BY + AVG

Patrón que permite comparar el valor medio de una magnitud entre diferentes grupos. Responde a preguntas del tipo "¿qué clasificación de película tiene mayor tarifa media de alquiler?".

§ 03

Contenido de la clase

Preparar el entorno: activar Sakila

Estos ejercicios se realizan sobre la base de datos Sakila. Si todavía no la tienes importada, descarga el archivo desde la documentación oficial de MySQL (sakila-db.zip), descomprímelo e importa ambos archivos (esquema y datos) desde MySQL Workbench → Administration → Data Import/Restore.

Una vez importada, actívala con USE y comprueba que la tabla film está disponible con un SELECT rápido.

COUNT: tres formas de contar

En la tabla film, COUNT(*) devuelve las 1000 películas. COUNT(replacement_cost) devuelve las filas donde ese campo no es nulo —en este caso también 1000 porque la columna no admite nulos—. COUNT(DISTINCT rating) devuelve 5, porque solo existen cinco clasificaciones posibles (G, PG, PG-13, R, NC-17).

Este último uso, COUNT(DISTINCT), es muy práctico cuando se quiere saber cuántos valores únicos existen en una columna sin necesidad de listarlos todos.

SUM y AVG: resúmenes numéricos

SUM(replacement_cost) devuelve el coste total de reposición de todo el catálogo: lo que costaría reponer todas las películas si se perdieran simultáneamente. SUM(rental_duration) filtrando por rating = 'PG' devuelve la suma de días de alquiler estándar para esa clasificación concreta.

AVG(length) filtrando por rating = 'NC-17' devuelve la duración media de las películas para adultos. AVG(rental_rate) agrupado por rating permite comparar qué clasificación tiene la tarifa media más alta.

MIN y MAX: los extremos de un conjunto

MIN(length) y MAX(length) aplicados sobre toda la tabla film devuelven la película más corta (46 minutos) y la más larga (185 minutos) del catálogo. Combinados con GROUP BY rating se puede saber, para cada clasificación, cuánto dura la película más corta y la más larga de ese grupo.

GROUP BY + COUNT y GROUP BY + AVG: combinaciones esenciales

La combinación GROUP BY + COUNT es la más habitual en análisis de datos. Permite responder "¿cuántos hay de cada tipo?" para cualquier dimensión: por clasificación, por año, por característica especial.

En la tabla film, GROUP BY release_year muestra un caso extremo: todas las 1000 películas pertenecen al mismo año (2006). Esto revela algo sobre la base de datos, no un error en la consulta.

GROUP BY + AVG sobre special_features permite comparar qué tipo de extras (trailers, deleted scenes, behind the scenes, commentaries) se asocia a películas más largas. Las películas con comentarios y trailers tienden a tener una duración media superior.

HAVING: quedarse solo con los grupos relevantes

HAVING filtra los grupos después de formarse. En la tabla film, HAVING COUNT(*) > 200 sobre un GROUP BY rating muestra solo las clasificaciones con representación abundante en el catálogo: PG-13 y NC-17. El resto de clasificaciones, con menos películas, se descarta del resultado.

Combinando GROUP BY special_features con HAVING AVG(length) > 115 se identifican qué tipos de características especiales están vinculadas a películas significativamente más largas que la media.

§ 04

Código y ejemplos

COUNT en sus tres variantes

SQL

USE sakila;

-- Total de películas en el catálogo
SELECT COUNT(*) AS total_peliculas
FROM film;

-- Películas cuyo coste de reposición supera los 20 €
SELECT COUNT(*) AS peliculas_costosas
FROM film
WHERE replacement_cost > 20;

-- Cuántos tipos de clasificación distintos existen
SELECT COUNT(DISTINCT rating) AS tipos_rating
FROM film;

Las tres variantes de COUNT en una sola sesión. La primera devuelve 1000, la segunda 486, la tercera 5.

SUM y AVG con filtrado y agrupación

SQL

-- Coste total de reposición de todo el catálogo
SELECT SUM(replacement_cost) AS coste_total_catalogo
FROM film;

-- Suma de días de alquiler estándar para películas PG
SELECT SUM(rental_duration) AS dias_renta_pg
FROM film
WHERE rating = 'PG';

-- Duración media de películas NC-17
SELECT AVG(length) AS duracion_media_nc17
FROM film
WHERE rating = 'NC-17';

-- Tarifa media de alquiler por clasificación
SELECT rating,
       AVG(rental_rate) AS tarifa_media
FROM film
GROUP BY rating
ORDER BY tarifa_media DESC;

AVG filtrando con WHERE trabaja sobre las filas que quedan después del filtro. GROUP BY + AVG lo calcula por separado para cada grupo.

MIN y MAX solos y agrupados

SQL

-- Película más corta y más larga del catálogo
SELECT MIN(length) AS duracion_minima,
       MAX(length) AS duracion_maxima
FROM film;

-- Coste de reposición mínimo y máximo por clasificación
SELECT rating,
       MIN(replacement_cost) AS coste_minimo,
       MAX(replacement_cost) AS coste_maximo
FROM film
GROUP BY rating
ORDER BY rating;

Sin GROUP BY, MIN y MAX actúan sobre toda la tabla y devuelven una sola fila. Con GROUP BY, calculan los extremos dentro de cada grupo.

HAVING: filtrar grupos

SQL

-- Clasificaciones con representación abundante (más de 200 películas)
SELECT rating,
       COUNT(*) AS total
FROM film
GROUP BY rating
HAVING COUNT(*) > 200
ORDER BY total DESC;

-- Tipos de special_features asociados a películas largas (media > 115 min)
SELECT special_features,
       AVG(length) AS duracion_media
FROM film
GROUP BY special_features
HAVING AVG(length) > 115
ORDER BY duracion_media DESC;

HAVING va después de GROUP BY. La condición puede referenciar la misma función agregada que aparece en SELECT o una diferente.

§ 05

Errores comunes

Usar WHERE en lugar de HAVING para filtrar por un resultado de COUNT o AVG. WHERE no tiene acceso a los valores calculados por las funciones agregadas porque se ejecuta antes de GROUP BY.

Olvidar incluir en GROUP BY todas las columnas del SELECT que no son agregados. Si seleccionas rating y COUNT(*) pero solo agrupas por otra columna, el valor de rating en cada fila será arbitrario.

Asumir que GROUP BY devuelve los grupos en un orden concreto. Sin ORDER BY el orden es indeterminado y puede cambiar entre ejecuciones.

Confundir la columna length (duración de la película en minutos) con rental_duration (días de alquiler). Aplicar AVG sobre la columna equivocada da un resultado numérico sin que MySQL avise del error.

§ 06

Buenas prácticas

Antes de escribir la consulta, identifica qué quieres medir (el agregado) y por qué dimensión quieres segmentarlo (el GROUP BY). Esas dos decisiones definen el 90% de la consulta.

Siempre añade ORDER BY al usar GROUP BY. El orden natural de MySQL no es predecible y puede cambiar con el volumen de datos.

Usa WHERE para filtrar antes de GROUP BY siempre que la condición no dependa de un agregado. Reduce las filas que se procesan y mejora el rendimiento.

Renombra con AS los resultados de funciones agregadas. Un resultado llamado "total", "media" o "coste_total" es mucho más útil que uno llamado "COUNT(*)" o "AVG(length)".

§ 07

Ejercicios

Cuenta cuántas películas tienen replacement_cost mayor a 25.
Calcula la media de length para las películas con rating 'G'. ¿Cuántos minutos duran de media?
Obtén la suma total de rental_rate de todas las películas de la tabla film.
Agrupa las películas por rating y muestra el total de películas y la duración mínima y máxima de cada grupo. Ordena de mayor a menor por total de películas.
Usa HAVING para mostrar solo los grupos de special_features que tienen más de 180 películas.
Encuentra las clasificaciones (rating) cuya media de replacement_cost supera los 20 dólares.

§ 08

Checklist final

Sé importar Sakila y activarla con USE sakila.
Puedo usar COUNT(*), COUNT(columna) y COUNT(DISTINCT columna) en contextos diferentes.
Sé calcular SUM y AVG sobre columnas numéricas, con y sin filtrado WHERE.
Puedo obtener los extremos de una columna con MIN y MAX, solos y agrupados.
Entiendo y puedo escribir GROUP BY + COUNT y GROUP BY + AVG.
Sé usar HAVING para filtrar grupos por condiciones que implican funciones agregadas.
Distingo cuándo usar WHERE (antes de GROUP BY) y cuándo HAVING (después de GROUP BY).

¿Has terminado la clase?

Márcala como repasada y vuelve cuando quieras revisarla.