Tema 12: Window functions: ranking, LAG, LEAD y promedios móviles

Identificador: PF-012 | XP: 200 | Insignia: 🥇 Oro | Tier: Aprendiz

Tiempo estimado: 90-120 min | Pre-requisitos: PF-009, PF-011

🖼️ Imagen de inicio del tema

[Moka con telescopio mexica mirando pirámide escalonada (window). Sofía, Don Carlos, Citlalli viendo rankings de productos.]

🎯 Aprenderás

ROW_NUMBER, RANK, DENSE_RANK. LAG y LEAD para ver filas anteriores/siguientes. SUM, AVG con OVER (PARTITION BY ...). Patrones de reportes avanzados.

🏛️ Visión mexica — Por Citlalli

"Los calpixqui organizaban los altépetl tributarios por jerarquía: el primero en llegar al huey tlatoani era el más importante. Esa 'jerarquía dinámica' es el equivalente de una WINDOW FUNCTION en SQL: calcular un valor (ranking, suma acumulada) sobre un grupo de filas sin perder la fila individual.

El tonalpohualli (calendario mexica de 260 días) era un sistema de 'ventanas temporales': cada día era único pero formaba parte de una trecena, un mes, un año. Lo mismo hace una window function: cada fila es única pero forma parte de un grupo."

📚 Bloque 1: ROW_NUMBER, RANK, DENSE_RANK

SELECT
    nombre,
    precio,
    ROW_NUMBER() OVER (ORDER BY precio DESC) AS fila,
    RANK() OVER (ORDER BY precio DESC) AS ranking,
    DENSE_RANK() OVER (ORDER BY precio DESC) AS ranking_denso
FROM productos;

• `ROW_NUMBER`: número único, no salta.
• `RANK`: si hay empates, salta el siguiente número.
• `DENSE_RANK`: si hay empates, no salta.

📚 Bloque 2: LAG y LEAD

-- Comparar ventas de cada mes con el mes anterior
SELECT
    DATE_FORMAT(fecha_pedido, '%Y-%m') AS mes,
    SUM(total) AS ventas_mes,
    LAG(SUM(total), 1) OVER (ORDER BY DATE_FORMAT(fecha_pedido, '%Y-%m')) AS mes_anterior,
    SUM(total) - LAG(SUM(total), 1) OVER (ORDER BY DATE_FORMAT(fecha_pedido, '%Y-%m')) AS diferencia
FROM pedidos
GROUP BY DATE_FORMAT(fecha_pedido, '%Y-%m');

📚 Bloque 3: SUM OVER PARTITION BY — suma acumulada por grupo

-- Para cada cliente, sus pedidos con total acumulado
SELECT
    c.nombre,
    pe.fecha_pedido,
    pe.total,
    SUM(pe.total) OVER (PARTITION BY c.id ORDER BY pe.fecha_pedido) AS acumulado
FROM clientes c
INNER JOIN pedidos pe ON c.id = pe.cliente_id
ORDER BY c.nombre, pe.fecha_pedido;

📚 Bloque 4: NTILE — dividir en grupos iguales

-- Dividir productos en 4 grupos de precio (cuartiles)
SELECT
    nombre,
    precio,
    NTILE(4) OVER (ORDER BY precio) AS cuartil
FROM productos;

📚 Bloque 5: ROW_NUMBER para "top N por grupo"

-- Top 3 productos más vendidos por categoría
WITH ranked AS (
    SELECT
        categoria_id,
        nombre,
        total_vendido,
        ROW_NUMBER() OVER (PARTITION BY categoria_id ORDER BY total_vendido DESC) AS ranking
    FROM (
        SELECT pr.categoria_id, pr.nombre, SUM(pi.cantidad) AS total_vendido
        FROM productos pr
        INNER JOIN pedido_items pi ON pr.id = pi.producto_id
        GROUP BY pr.categoria_id, pr.nombre
    ) sub
)
SELECT  FROM ranked WHERE ranking <= 3;

🤖 AI Mission

Pídele: "Dame 5 ejemplos de uso real de window functions en un e-commerce. Para cada uno, escribe la query y explica el patrón."*

⚠️ Errores típicos

Olvidar `PARTITION BY` cuando se necesita agrupar. Usar `RANK` y esperar numeración única. Window functions con demasiadas filas (rendimiento). Confundir `OVER()` con `GROUP BY`.

🧪 LAB 12.1 — "5 reportes con window functions"

Ranking de clientes por gasto total. Top 3 productos más vendidos por categoría. Diferencia de ventas mes a mes. Cuartiles de productos por precio. Acumulado de pedidos por cliente.

📝 Quest 12

¿Qué hace ROW_NUMBER? A) Ranking con saltos. B) Número único. C) Cuenta. D) Suma. ¿RANK vs DENSE_RANK? A) Iguales. B) RANK salta, DENSE_RANK no. C) DENSE_RANK salta. D) Solo en PostgreSQL. ¿LAG devuelve? A) Fila actual. B) Fila anterior. C) Siguiente. D) Última. ¿PARTITION BY en window? A) Agrupa dentro de la window. B) Filtra. C) Ordena. D) Suma. ¿OVER() sin nada? A) Error. B) Window global. C) Una sola fila. D) Igual a GROUP BY. ¿NTILE(10)? A) 10 filas. B) Deciles. C) Percentiles. D) Error. ¿Window function vs GROUP BY? A) Iguales. B) Window conserva filas. C) GROUP BY conserva filas. D) Son sinónimos. ¿FIRST_VALUE? A) Primer valor de la window. B) Última fila. C) Suma. D) Promedio. ¿Rendimiento de window functions? A) Rápidas siempre. B) Pueden ser lentas en tablas grandes. C) Igual a subconsultas. D) Peor que GROUP BY. ¿Frame ROWS BETWEEN UNBOUNDED PRECEDING? A) Solo fila actual. B) Todas las anteriores. C) Solo la primera. D) Error.

¿Cuándo RANK vs DENSE_RANK? Da un ejemplo de uso de LAG para detectar anomalías. ¿Cuándo NO usar window functions?

✅ Respuestas

1-B, 2-B, 3-B, 4-A, 5-B, 6-B, 7-B, 8-A, 9-B, 10-B.

🏛️ Tras las huellas — Por Citlalli

"Los calpixqui organizaban altépetl por jerarquía dinámica: el primero en llegar al huey tlatoani era el más importante. Esa es una WINDOW FUNCTION: calcular un valor (ranking, suma acumulada) sobre un grupo sin perder la fila individual."

📓 Cierre

1. La window function más útil fue:

2. La query más compleja:

3. Una decisión de negocio con LAG/LEAD:

4. Cómo voy a usar window functions:

🚀 ¿Qué sigue?

Tema 13 — Vistas, índices y constraints: el kit de durabilidad.