Comparar periodos con LAG y LEAD

Objetivos

Al terminar podrás traer un valor del periodo vecino y calcular una diferencia sin borrar filas del resumen.

Concepto

LAG(valor) lee una fila anterior en el orden de la ventana. LEAD(valor) lee una posterior. Si no existe vecindad en un extremo, el resultado es NULL de forma predeterminada.

El orden debe representar la secuencia que quieres comparar. Este ejemplo ordena meses de forma cronológica. Como los meses sin pagos no aparecen en el resumen, “anterior” significa el periodo observado anterior, no necesariamente el mes calendario contiguo.

Ejemplo

WITH monthly AS (
  SELECT
    DATE_FORMAT(payment_date, '%Y-%m') AS payment_month,
    SUM(amount) AS total_amount
  FROM payment
  GROUP BY DATE_FORMAT(payment_date, '%Y-%m')
)
SELECT
  payment_month,
  ROUND(total_amount, 2) AS total_amount,
  ROUND(LAG(total_amount) OVER (ORDER BY payment_month), 2) AS previous_observed_total,
  ROUND(LEAD(total_amount) OVER (ORDER BY payment_month), 2) AS next_observed_total
FROM monthly
ORDER BY payment_month;

El primer periodo no tiene valor anterior y el último no tiene siguiente; ambos extremos muestran NULL. No los reemplaces con cero sin definir esa regla.

Práctica guiada

Añade una columna con total_amount - LAG(total_amount) ....
Invierte el orden en OVER y explica qué cambia.
Describe cómo necesitarías una tabla calendario para comparar meses contiguos aunque no tengan pagos.

Reto

Calcula la diferencia y el cambio porcentual entre el total del mes observado y el anterior. Protege la división por cero y deja el primer periodo como NULL.

Ver respuesta al reto

WITH monthly AS (
  SELECT DATE_FORMAT(payment_date, '%Y-%m') AS payment_month,
         SUM(amount) AS total_amount
  FROM sakila.payment
  GROUP BY DATE_FORMAT(payment_date, '%Y-%m')
), compared AS (
  SELECT payment_month, total_amount,
         LAG(total_amount) OVER (ORDER BY payment_month) AS prior_total
  FROM monthly
)
SELECT payment_month, ROUND(total_amount, 2) AS total_amount,
       ROUND(total_amount - prior_total, 2) AS amount_change,
       ROUND(100 * (total_amount - prior_total) / NULLIF(prior_total, 0), 2) AS percent_change
FROM compared
ORDER BY payment_month;

El primer prior_total queda NULL; NULLIF evita dividir entre cero.

Quiz

1. ¿Qué devuelve LAG para la primera fila de una secuencia si no se indica un valor por defecto?

A.El total de la última fila.B.NULL, porque no hay una fila anterior.C.Cero en todos los motores.

2. ¿Qué define ORDER BY dentro de OVER?

A.La secuencia usada por la ventanaB.Las columnas de la tabla originalC.Los permisos del usuario

3. ¿Qué función asigna una posición única a cada fila?

A.ROW_NUMBERB.COUNTC.COALESCE

Comprobación

Ya comparas cada periodo con su vecino. La siguiente lección construye un total acumulado con un marco explícito.