Agregación y agrupación de datos en MySQL

Introducción

En este laboratorio, exploraremos la agregación y agrupación de datos en MySQL, habilidades esenciales para analizar y resumir datos en bases de datos. Aprenderás cómo utilizar funciones de agregación para realizar cálculos en varias filas, agrupar datos según los valores de las columnas y filtrar los resultados agrupados. Estas técnicas son fundamentales para generar informes, analizar tendencias y extraer información significativa de tus datos. A través de ejercicios prácticos, adquirirás experiencia práctica con estas operaciones cruciales de bases de datos y entenderás cómo analizar datos de manera efectiva en MySQL.

Funciones de agregación básicas

En este paso, exploraremos las funciones de agregación fundamentales en MySQL. Estas funciones realizan cálculos en múltiples filas y devuelven un solo valor, lo que las hace esenciales para el análisis de datos.

Primero, conectémonos a MySQL y seleccionemos nuestra base de datos:

sudo mysql -u root

Una vez conectados:

USE sales_db;

Función COUNT

Comencemos con la función COUNT, que cuenta el número de filas en un conjunto de resultados:

-- Contar el número total de ventas
SELECT COUNT(*) as total_sales
FROM sales;

Deberías ver una salida como esta:

+--------------+
| total_sales  |
+--------------+
| 12           |
+--------------+

COUNT se puede utilizar de diferentes maneras:

-- Contar los productos únicos vendidos
SELECT COUNT(DISTINCT product_name) as unique_products
FROM sales;

-- Contar las ventas por categoría
SELECT category, COUNT(*) as sales_count
FROM sales
GROUP BY category;

Explicación:

• COUNT(*) cuenta todas las filas de la tabla, incluyendo los valores NULL
• COUNT(DISTINCT column) cuenta solo los valores únicos en la columna especificada
• Cuando se utiliza con GROUP BY, COUNT calcula los totales para cada grupo por separado
• La palabra clave as crea alias para las columnas de resultados, lo que hace que la salida sea más legible

Función SUM

La función SUM calcula el total de columnas numéricas:

-- Calcular la cantidad total vendida
SELECT SUM(quantity) as total_items_sold
FROM sales;

-- Calcular el ingreso total
SELECT
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales;

Explicación:

• SUM solo se puede utilizar con columnas numéricas
• Los cálculos como quantity * unit_price se realizan antes de la agregación
• ROUND(x, 2) redondea los números a 2 decimales
• Para los cálculos de ingresos, la multiplicación se realiza antes de la suma para mantener la precisión

Función AVG

La función AVG calcula el valor promedio:

-- Calcular el precio unitario promedio
SELECT
    ROUND(AVG(unit_price), 2) as avg_price
FROM sales;

-- Calcular la cantidad promedio por venta
SELECT
    ROUND(AVG(quantity), 1) as avg_quantity
FROM sales;

Explicación:

• AVG ignora automáticamente los valores NULL en sus cálculos
• El segundo parámetro en ROUND especifica el número de decimales
• Los resultados se convierten automáticamente al tipo DECIMAL para mayor precisión
• AVG se utiliza comúnmente para encontrar valores típicos en tus datos

Funciones MAX y MIN

Estas funciones encuentran los valores más altos y más bajos:

-- Encontrar el rango de precios de los productos
SELECT
    MIN(unit_price) as lowest_price,
    MAX(unit_price) as highest_price
FROM sales;

-- Encontrar las fechas de la primera y última venta
SELECT
    MIN(sale_date) as first_sale,
    MAX(sale_date) as last_sale
FROM sales;

Explicación:

• MIN/MAX funcionan con números, cadenas y fechas
• Para fechas, MIN encuentra la fecha más temprana mientras que MAX encuentra la más reciente
• Se pueden combinar múltiples funciones de agregación en una sola declaración SELECT
• Al igual que otras funciones de agregación, ignoran automáticamente los valores NULL
• Estas funciones son útiles para encontrar rangos y límites de valores en tus datos

Agrupación de datos con GROUP BY

En este paso, aprenderemos cómo agrupar datos utilizando la cláusula GROUP BY. La agrupación nos permite realizar cálculos de agregación en subconjuntos de nuestros datos basados en valores específicos de columnas.

Agrupación básica

Comencemos con operaciones de agrupación simples:

-- Conteo de ventas y cantidad total por categoría
SELECT
    category,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
ORDER BY total_quantity DESC;

Esta consulta muestra cuántas ventas y cuántos artículos en total se vendieron en cada categoría. Deberías ver una salida como esta:

+-------------+-------------+----------------+
| category    | sales_count | total_quantity |
+-------------+-------------+----------------+
| Furniture   |           5 |             22 |
| Electronics |           5 |             21 |
| Appliances  |           2 |             10 |
+-------------+-------------+----------------+

Explicación:

• GROUP BY combina las filas con la misma categoría en una sola fila
• La cláusula ORDER BY total_quantity DESC ordena los resultados de mayor a menor cantidad
• Cada función de agregación (COUNT, SUM) opera dentro de cada grupo de forma independiente
• Las columnas no agregadas en la cláusula SELECT deben aparecer en la cláusula GROUP BY

Agrupación por múltiples columnas

Podemos agrupar por múltiples columnas para obtener información más detallada:

-- Análisis de ventas por categoría y región
SELECT
    category,
    region,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category, region
ORDER BY category, total_revenue DESC;

Explicación:

• Agrupar por múltiples columnas crea subgrupos para cada combinación única
• El orden de las columnas en la cláusula GROUP BY afecta cómo se agrupan los datos
• Los resultados se ordenan primero por categoría y luego por total_revenue dentro de cada categoría
• Este enfoque ayuda a identificar las regiones con mejor rendimiento dentro de cada categoría

Agrupación basada en fechas

MySQL proporciona funciones para extraer partes de las fechas, útiles para la agrupación basada en tiempo:

-- Resumen de ventas diarias
SELECT
    sale_date,
    COUNT(*) as transactions,
    SUM(quantity) as items_sold,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Explicación:

• Agrupa todas las transacciones que ocurrieron en la misma fecha
• La cláusula ORDER BY sale_date organiza los resultados cronológicamente
• Cuenta las transacciones por día y calcula los totales diarios
• Es útil para identificar patrones y tendencias de ventas diarias
• Puede modificarse para agrupar por mes o año utilizando funciones de fecha (DATE functions)

Filtrado de datos agrupados con HAVING

En este paso, aprenderemos sobre la cláusula HAVING, que nos permite filtrar los resultados después de la agrupación. Mientras que WHERE filtra filas individuales antes de la agrupación, HAVING filtra los grupos en sí.

Uso básico de HAVING

Encontremos las categorías con más de 15 artículos vendidos en total:

SELECT
    category,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
HAVING total_quantity > 15;

Esto muestra solo las categorías donde la cantidad total supera los 15 artículos:

+-------------+----------------+
| category    | total_quantity |
+-------------+----------------+
| Electronics |             21 |
| Furniture   |             22 |
+-------------+----------------+

Explicación:

• HAVING filtra los grupos después de que se haya aplicado GROUP BY
• Puedes hacer referencia a los resultados de las funciones de agregación en HAVING
• La condición de filtrado utiliza el alias 'total_quantity'
• Las categorías con 15 artículos o menos se excluyen de los resultados

Combinación de WHERE y HAVING

Podemos usar WHERE y HAVING juntos. WHERE filtra las filas antes de la agrupación, mientras que HAVING filtra después de la agrupación:

-- Encontrar categorías de alto volumen en la región Norte
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity
FROM sales
WHERE region = 'North'
GROUP BY category
HAVING total_quantity > 5;

Explicación:

• WHERE filtra filas individuales (region = 'North') antes de la agrupación
• Luego, los datos se agrupan por categoría
• Finalmente, HAVING filtra los resultados agrupados (total_quantity > 5)
• El orden es importante: FROM → WHERE → GROUP BY → HAVING → SELECT

Condiciones complejas de HAVING

Podemos usar múltiples condiciones en HAVING:

-- Encontrar categorías con alto volumen de ventas e ingresos
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
HAVING total_quantity > 10 AND total_revenue > 1000
ORDER BY total_revenue DESC;

Explicación:

• Se pueden combinar múltiples condiciones utilizando AND/OR
• Todos los cálculos de agregación están disponibles para las condiciones de HAVING
• Puedes usar operaciones aritméticas en HAVING
• La cláusula ORDER BY se aplica después de evaluar los filtros de HAVING
• Este tipo de consulta es útil para identificar las categorías con mejor rendimiento

Técnicas avanzadas de agregación

En este último paso, exploraremos técnicas de agregación más avanzadas combinando todo lo que hemos aprendido. Crearemos informes de ventas completos que demuestren el poder de estas herramientas en conjunto.

Tablero de rendimiento de ventas

Creemos un análisis completo de ventas:

SELECT
    category,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as total_transactions,
    SUM(quantity) as total_quantity,
    ROUND(AVG(quantity), 1) as avg_quantity_per_sale,
    ROUND(MIN(unit_price), 2) as min_price,
    ROUND(MAX(unit_price), 2) as max_price,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

Explicación:

• Crea una visión general completa del rendimiento de cada categoría
• Utiliza DISTINCT para contar los productos únicos dentro de cada categoría
• Combina múltiples funciones de agregación para un análisis completo
• Muestra los rangos de precios con MIN y MAX
• Calcula las cantidades promedio y los ingresos totales
• Los resultados se ordenan por ingresos para resaltar los mejores rendimientos

Análisis de rendimiento regional

Analicemos el rendimiento de ventas por región:

SELECT
    region,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue,
    ROUND(SUM(quantity * unit_price) / SUM(quantity), 2) as avg_price_per_unit
FROM sales
GROUP BY region
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

Explicación:

• Agrupa los datos de ventas por regiones geográficas
• Muestra la diversidad de productos con conteos DISTINCT
• Calcula los ingresos totales y las métricas de cantidad
• avg_price_per_unit se calcula dividiendo los ingresos entre la cantidad
• HAVING filtra las regiones con ingresos más bajos
• Ayuda a identificar las regiones con mejor y peor rendimiento

Análisis de tendencias diarias

Creemos un informe de tendencias de ventas diarias:

SELECT
    sale_date,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as transactions,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue,
    ROUND(AVG(quantity * unit_price), 2) as avg_transaction_value
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Explicación:

• Registra las métricas de rendimiento de ventas diarias
• Muestra la diversidad de productos y categorías por día
• Cuenta las transacciones y cantidades diarias
• Calcula los ingresos diarios y el valor promedio de las transacciones
• El orden cronológico ayuda a identificar tendencias
• Es útil para detectar patrones y efectos estacionales
• Puede ayudar en la planificación de inventario y las decisiones de personal

Resumen

En este laboratorio, hemos cubierto los aspectos esenciales de la agregación y agrupación de datos en MySQL:

1. Utilizar funciones de agregación básicas (COUNT, SUM, AVG, MAX, MIN) para resumir datos
2. Agrupar datos utilizando GROUP BY para analizar patrones y tendencias
3. Filtrar los resultados agrupados utilizando la cláusula HAVING
4. Combinar múltiples técnicas para crear análisis de datos completos

Estas habilidades son fundamentales para el análisis y reporte de datos en MySQL. Entender cómo agregar y agrupar datos de manera efectiva te permite extraer información significativa de tus bases de datos y crear informes comerciales valiosos.