MySQL - Capitulo 6

Una guía através del camino de las bases de datos con MySQL uno de los sistemas de gestión de bases de datos más conocidos y utilizados en el mundo de la programación.

Síguenos con el tutorial

• En QuéPasalinux MySQL - Trucos y Consultas Avanzadas un vídeo práctico sobre MySQL y Docker

Volver a Página Principal

Ver Codigo en GitLab

📚 Curso de Capacitación de MySQL

🚀 Módulo 6: Trucos y Consultas Avanzadas ✨ ¡El Gran Final!

¡Felicidades por llegar hasta aquí! 🎉 Has dominado la estructuración, manipulación y automatización de datos con procedimientos almacenados. Ahora, en nuestro último módulo, vamos a desbloquear el potencial completo de MySQL con algunos trucos finales y herramientas avanzadas.

Este módulo se enfoca en técnicas que te permitirán optimizar consultas, generar reportes complejos de forma eficiente y dominar los “pequeños grandes detalles” que marcan la diferencia entre un desarrollador junior y un profesional. Nos centraremos en una de las funcionalidades más poderosas para la agregación de datos: ROLLUP.

Prepárate para llevar tus habilidades al siguiente nivel y convertirte en un verdadero mago de las bases de datos. 🪄

6.1. Agregación con ROLLUP: El poder de los subtotales 📈

En muchas ocasiones, necesitamos generar reportes que no solo muestren datos detallados, sino también subtotales y un gran total. En lugar de ejecutar múltiples consultas separadas, MySQL nos ofrece la cláusula WITH ROLLUP.

ROLLUP es una extensión de la cláusula GROUP BY que genera filas adicionales de “super-agregación”. Estas filas muestran subtotales para cada nivel de la jerarquía de agrupación, además de un total general al final. Es perfecto para:

• Reportes de ventas por región y producto: Muestra el total de ventas para cada producto, el subtotal para cada región y el total global.
• Análisis de inventario: Agrupa por categoría y subcategoría para obtener subtotales.

---

6.2. Ejemplos prácticos: ROLLUP en acción 💻

Usaremos nuestra base de datos para ver cómo ROLLUP simplifica la creación de reportes.

Ejemplo 1: Total de ventas por cliente y producto

Imagina que quieres un reporte del total de ventas por cada cliente, detallado por producto.

SELECT
    C.nombre,
    P.nombre_producto,
    SUM(VP.subtotal) AS total_subtotal
FROM Ventas AS V
JOIN Clientes AS C ON V.id_cliente = C.id
JOIN Ventas_Productos AS VP ON V.id_venta = VP.id_venta
JOIN Products AS P ON VP.id_producto = P.id
GROUP BY C.nombre, P.nombre_producto WITH ROLLUP;

¿Qué hace WITH ROLLUP aquí?

• Genera las filas de detalle (cliente, producto).
• Añade una fila con NULL en nombre_producto para mostrar el subtotal de cada cliente.
• Añade una última fila con NULL en ambas columnas para el total general de todas las ventas.

---

6.3. Otros trucos avanzados para tus consultas ✨

Además de ROLLUP, existen otras funcionalidades que te harán más productivo y eficiente, permitiendo resolver problemas complejos de manera elegante.

---

6.3.1. Uso de variables de usuario (@)

Las variables de usuario (@nombre_variable) te permiten almacenar un valor temporalmente durante la sesión actual de MySQL. Son útiles para realizar cálculos secuenciales o para almacenar resultados intermedios que necesites en múltiples pasos de una misma consulta.

Ejemplo: Asignar un ranking a los clientes por total de compras

Imagina que quieres clasificar a tus clientes por el monto total que han gastado. Puedes hacerlo en una sola consulta usando una variable de usuario.

SET @rank_cliente = 0;

SELECT
    @rank_cliente := @rank_cliente + 1 AS ranking,
    C.nombre,
    SUM(V.monto_total) AS total_compras
FROM Clientes AS C
JOIN Ventas AS V ON C.id = V.id_cliente
GROUP BY C.id
ORDER BY total_compras DESC;

Aquí, SET @rank_cliente = 0; inicializa la variable. Luego, @rank_cliente := @rank_cliente + 1 asigna un nuevo valor a la variable en cada fila del resultado, creando un ranking dinámico.

---

6.3.2. Subconsultas correlacionadas

Una subconsulta correlacionada es una consulta interna que se ejecuta una vez por cada fila de la consulta externa. A diferencia de las subconsultas normales, que se ejecutan una sola vez, las correlacionadas dependen de los datos de la consulta principal. Son menos eficientes que los JOINs, pero a menudo son la única forma de resolver problemas complejos de filtrado o agregación.

Ejemplo: Encontrar el último pedido de cada cliente

En lugar de un JOIN y GROUP BY, puedes usar una subconsulta correlacionada para encontrar la venta más reciente de cada cliente.

SELECT
    id_venta,
    id_cliente,
    created_date
FROM Ventas AS V1
WHERE created_date = (
    SELECT MAX(created_date)
    FROM Ventas AS V2
    WHERE V2.id_cliente = V1.id_cliente
);

En este ejemplo, la subconsulta (SELECT MAX(created_date) ...) se ejecuta para cada fila de la tabla Ventas de la consulta principal (V1), buscando la fecha más reciente para el id_cliente específico de esa fila.

---

6.3.3. Funciones de ventana (Window Functions)

Las funciones de ventana permiten realizar cálculos sobre un conjunto de filas relacionadas con la fila actual, sin agrupar el resultado en una única fila. Esto significa que puedes, por ejemplo, calcular un total o un promedio para un grupo de filas mientras mantienes las filas de detalle originales.

La sintaxis clave es OVER (...), que define la “ventana” o partición de datos sobre la que operará la función.

Ejemplo 1: RANK() para clasificar productos por ventas

Imagina que quieres clasificar los productos por sus ventas, pero quieres ver su ranking dentro de cada categoría.

SELECT
    nombre_producto,
    categoria,
    monto_total,
    RANK() OVER (PARTITION BY categoria ORDER BY monto_total DESC) AS ranking_por_categoria
FROM (
    SELECT
        P.nombre_producto,
        P.categoria,
        SUM(VP.subtotal) AS monto_total
    FROM Products AS P
    JOIN Ventas_Productos AS VP ON P.id = VP.id_producto
    GROUP BY P.nombre_producto, P.categoria
) AS ventas_por_producto
ORDER BY categoria, ranking_por_categoria;

En este caso, PARTITION BY categoria divide los datos en grupos por categoría, y RANK() asigna una clasificación a cada producto dentro de su respectiva categoría, basándose en monto_total.

Ejemplo 2: LAG() para comparar ventas entre periodos

LAG() te permite acceder a una fila anterior dentro del mismo conjunto de resultados. Es ideal para comparar valores de ventas con el periodo anterior.

SELECT
    created_date,
    monto_total,
    LAG(monto_total, 1, 0) OVER (ORDER BY created_date) AS ventas_anteriores,
    monto_total - LAG(monto_total, 1, 0) OVER (ORDER BY created_date) AS diferencia
FROM Ventas
ORDER BY created_date;

Aquí, LAG(monto_total, 1, 0) recupera el monto_total de la fila anterior, facilitando el cálculo de la diferencia de ventas entre cada venta.

Estos trucos avanzados te otorgan un control granular sobre tus datos, permitiéndote resolver problemas que con sentencias SQL básicas serían muy difíciles o imposibles.

---

🚀 Tarea Práctica del Módulo 6

¡Es hora de poner a prueba tus nuevas habilidades!

1. Explora ROLLUP: Crea una consulta que muestre el total de productos vendidos agrupados por categoría y subcategoría (asume que tienes estas columnas). Utiliza ROLLUP para generar subtotales.
2. Reporte de deuda: Modifica el procedimiento ReporteDeudaClientePorID del módulo anterior para usar GROUP BY ... WITH ROLLUP y así obtener un resumen del estado de deuda por cliente y, si es posible, un total general.
3. Investiga: Busca en la documentación oficial de MySQL qué son y para qué se usan las funciones de ventana. ¿Podrías usar una para numerar las ventas de cada cliente de forma consecutiva?

---

Disculpa la confusión, aquí tienes las referencias en formato Markdown sin ninguna dependencia de Google Search en las URLs.

---

🤝 Referencias y Recursos Adicionales

• Documentación Oficial de MySQL:
  • Sentencia SELECT con GROUP BY: Aprende a agrupar filas para obtener resúmenes de datos.
  • Modificadores de GROUP BY (WITH ROLLUP): Descubre cómo generar subtotales y totales generales en tus reportes.
  • La función GROUPING(): Distingue entre NULL de datos y NULL de un resumen ROLLUP.
  • Uso de Variables de Usuario: Guía completa para usar variables de sesión en tus consultas.
  • Análisis de consultas con EXPLAIN: La herramienta esencial para optimizar y entender el rendimiento de tus consultas.

---

¡Has completado el curso de capacitación de MySQL! 🎉 Esperamos que estas herramientas te sean de gran utilidad en tu camino profesional. ¡Sigue practicando y explorando el fascinante mundo de las bases de datos!