Módulo 2: Análisis de Secuencias y Comparativas

Más Allá del `GROUP BY`

Desbloqueando Análisis con Funciones de Ventana

El Concepto Clave: ¿Qué es una "Ventana"?

Una función de ventana realiza un cálculo sobre un conjunto de filas relacionadas—la "ventana"—pero, a diferencia de `GROUP BY`, **no colapsa las filas**.

Cada fila conserva su identidad y obtiene un nuevo valor calculado a partir de sus "vecinas". La ventana se define con la cláusula OVER().

  • `PARTITION BY`: Define el marco de la ventana. Agrupa las filas lógicamente (ej. por `id_producto`).
  • `ORDER BY`: Establece el orden de las filas *dentro* de cada ventana (ej. por `fecha_pedido`), lo que es crucial para saber cuál es la fila "anterior" o "siguiente".

El "Porqué": El Problema de Negocio

El equipo de producto necesita análisis más profundos que una simple suma:

1. Análisis de Crecimiento 📈

¿Cómo varían las ventas de un producto mes a mes?

2. Ranking de Productos 🏆

¿Cuál fue el producto más vendido dentro de cada categoría, cada mes?

Anti-Patrón: El `SELF-JOIN` para Crecimiento

La forma tradicional de comparar un mes con el anterior es unir una tabla consigo misma. Esto es problemático:

WITH VentasMensuales AS (
  SELECT
    p.id_producto, d.anio, d.mes,
    SUM(p.monto_total) AS ventas_mes
  FROM pedidos_silver p JOIN dim_fecha d ON p.id_fecha = d.id_fecha
  GROUP BY p.id_producto, d.anio, d.mes
)
SELECT
  actual.id_producto, actual.anio, actual.mes,
  actual.ventas_mes,
  anterior.ventas_mes AS ventas_mes_anterior
FROM VentasMensuales actual
LEFT JOIN VentasMensuales anterior 
  ON actual.id_producto = anterior.id_producto
  AND actual.anio = anterior.anio -- Lógica compleja y frágil
  AND actual.mes = anterior.mes + 1;
  • Complejo e Ineficiente: La lógica de unión es difícil de leer y no maneja bien los cambios de año.
  • Costoso en Databricks: Un `self-join` sobre una tabla grande puede provocar un **shuffle masivo de datos**, degradando el rendimiento.

Solución Elegante: La Función `LAG()`

`LAG()` nos permite acceder a datos de una fila anterior dentro de la misma ventana, sin necesidad de un `JOIN`.

WITH VentasMensuales AS (
  SELECT
    p.id_producto, d.anio, d.mes,
    SUM(p.monto_total) AS ventas_mes
  FROM pedidos_silver p JOIN dim_fecha d ON p.id_fecha = d.id_fecha
  GROUP BY p.id_producto, d.anio, d.mes
)
SELECT
  id_producto, anio, mes, ventas_mes,
  LAG(ventas_mes, 1, 0) OVER (
    PARTITION BY id_producto 
    ORDER BY anio, mes
  ) AS ventas_mes_anterior
FROM VentasMensuales;

Anatomía de la función:

LAG(columna, desfase, defecto): Obtiene el valor de la `columna`, `desfase` filas atrás, con un valor `defecto` si no existe.

OVER (PARTITION BY ... ORDER BY ...): Define la ventana donde opera la función.

Solución de Ranking: `RANK()` y `DENSE_RANK()`

Para encontrar el producto más vendido por categoría cada mes, usamos funciones de ranking.

WITH VentasProductoMes AS (
  SELECT
    pr.categoria, d.anio, d.mes, pr.nombre_producto,
    SUM(p.monto_total) AS ventas_totales
  FROM pedidos_silver p
  JOIN productos_silver pr ON p.id_producto = pr.id_producto
  JOIN dim_fecha d ON p.id_fecha = d.id_fecha
  GROUP BY pr.categoria, d.anio, d.mes, pr.nombre_producto
)
SELECT * FROM (
  SELECT
    *,
    RANK() OVER (
        PARTITION BY categoria, anio, mes 
        ORDER BY ventas_totales DESC
    ) AS ranking
  FROM VentasProductoMes
)
WHERE ranking = 1;

Diferencia Clave:

RANK(): Asigna el mismo rango a los empates, pero deja un 'hueco' (1, 2, 2, 4).

DENSE_RANK(): Asigna el mismo rango a los empates sin dejar 'huecos' (1, 2, 2, 3).

¡A Practicar! 🏋️

Ahora es tu turno. El equipo de servicio al cliente quiere entender la frecuencia de compra de los usuarios.

"Para cada pedido de un cliente, calcula cuántos **días han pasado desde su pedido inmediatamente anterior**."

Tabla de Salida Esperada:

  • `id_usuario`
  • `id_pedido`
  • `ts_pedido` (fecha actual del pedido)
  • `dias_desde_ultimo_pedido` (tu cálculo)

Pistas:

  • Necesitarás `LAG()` sobre la fecha del pedido.
  • La ventana debe ser `PARTITION BY id_usuario`.
  • Usa la función `DATEDIFF()` para calcular la diferencia de días.

Solución del Ejercicio

Aquí está la solución para calcular los días entre pedidos por cliente.

SELECT
  id_usuario,
  id_pedido,
  ts_pedido,
  DATEDIFF(
    ts_pedido,
    LAG(ts_pedido, 1) OVER (PARTITION BY id_usuario ORDER BY ts_pedido)
  ) AS dias_desde_ultimo_pedido
FROM
  curso_arquitecturas.pedidos_silver
ORDER BY
  id_usuario, ts_pedido;

Observa cómo la primera fila de cada `id_usuario` tiene un valor `NULL`, ya que no hay un pedido anterior en esa partición del cual obtener la fecha.