LAG y LEAD: comparar con la fila anterior — SQL Avanzado — Iterixs

SQL Avanzado

Enroll to access all the lessons in this course.

Content

Lesson 16 of 29· Navegación entre filas y acumulados

LAG y LEAD: comparar con la fila anterior

Reading

LAG y LEAD: comparar con la fila anterior

Una de las preguntas más frecuentes en analítica de negocio es: "¿cómo nos fue este mes comparado con el mes pasado?". Suena trivial, pero con las herramientas de SQL que ya conoces (GROUP BY, JOIN) resolverla es sorprendentemente incómodo: necesitarías unir la tabla consigo misma desplazada un período, con condiciones de fecha frágiles y propensas a errores.

Las funciones de ventana LAG y LEAD convierten ese rompecabezas en una sola línea. Te dejan mirar hacia atrás (la fila anterior) o hacia adelante (la fila siguiente) dentro de una ventana ordenada, sin colapsar filas ni auto-unir nada. Son la herramienta canónica para cálculos período-contra-período: variación mes a mes, crecimiento interanual, días entre eventos consecutivos.

Al terminar esta lección sabrás calcular, en una sola consulta, la venta de cada mes junto a la del mes anterior y su variación porcentual, y visualizar la tendencia. Vamos a ejecutar todo sobre la tienda TechAndino en un PostgreSQL real dentro de tu navegador.

Primero, la ventana ordenada

Recuerda del módulo anterior: una función de ventana opera sobre un conjunto de filas relacionadas (la ventana) definido por OVER (...), sin fusionarlas en una sola. LAG y LEAD dependen críticamente de un ORDER BY dentro del OVER, porque "la fila anterior" solo tiene sentido si las filas están ordenadas.

Preparemos primero el ingrediente base: la venta total por mes de los pedidos completados. Ejecuta esta consulta y observa el resultado — será la materia prima de todo lo demás.

sql

-- Venta total por mes (solo pedidos completados)
SELECT to_char(o.order_date, 'YYYY-MM')       AS mes,
       SUM(oi.quantity * oi.unit_price)::int  AS ventas
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completado'
GROUP BY 1
ORDER BY 1;

Obtienes nueve filas, una por mes, de enero a septiembre de 2023. Cada fila es independiente: SQL no sabe que enero "viene antes" de febrero. Ahora le vamos a dar esa noción de secuencia.

LAG: traer el valor de la fila anterior

LAG toma, para cada fila, el valor de una columna en una fila anterior dentro de la ventana. Su firma completa es:

text

LAG(expresión [, offset [, valor_por_defecto]]) OVER (ORDER BY ...)

parte	significado
`expresión`	la columna cuyo valor quieres traer de otra fila
`offset`	cuántas filas hacia atrás mirar (por defecto 1, la fila inmediatamente anterior)
`valor_por_defecto`	qué devolver cuando no existe esa fila anterior (por defecto NULL)
`ORDER BY`	obligatorio: define qué significa "anterior"

Envolvamos la consulta anterior en una CTE (WITH) para reutilizar el agregado, y añadamos LAG para traer la venta del mes previo:

sql

WITH ventas_mes AS (
  SELECT to_char(o.order_date, 'YYYY-MM')      AS mes,
         SUM(oi.quantity * oi.unit_price)::int AS ventas
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completado'
  GROUP BY 1
)
SELECT mes,
       ventas,
       LAG(ventas) OVER (ORDER BY mes) AS mes_anterior
FROM ventas_mes
ORDER BY mes;

Fíjate en dos cosas del resultado:

Cada fila ahora muestra su propia venta (ventas) y la del mes anterior (mes_anterior), lado a lado. Ya no necesitas un self-join.
La primera fila (enero) tiene mes_anterior = NULL: no hay ningún mes antes de enero en nuestros datos, así que LAG devuelve su valor por defecto, NULL.

Ese NULL inicial es esperado y correcto. Pero, ¿y si prefieres tratar "sin mes anterior" como cero? Ahí entra el tercer argumento.

El offset y el valor por defecto

Los argumentos segundo y tercero te dan control fino. Compara estas tres variantes en una sola consulta:

LAG(ventas) → mes anterior (offset 1), NULL si no existe.
LAG(ventas, 2, 0) → dos meses atrás; si no existe, devuelve 0 en vez de NULL.
LEAD(ventas) → el mismo mecanismo, pero mirando hacia adelante: el mes siguiente.

sql

WITH ventas_mes AS (
  SELECT to_char(o.order_date, 'YYYY-MM')      AS mes,
         SUM(oi.quantity * oi.unit_price)::int AS ventas
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completado'
  GROUP BY 1
)
SELECT mes,
       ventas,
       LAG(ventas)        OVER (ORDER BY mes) AS mes_anterior,
       LAG(ventas, 2, 0)  OVER (ORDER BY mes) AS hace_dos_meses,
       LEAD(ventas)       OVER (ORDER BY mes) AS mes_siguiente
FROM ventas_mes
ORDER BY mes;

Observa cómo:

hace_dos_meses es 0 (no NULL) en enero y febrero, porque pusimos 0 como valor por defecto y en ambos casos falta la fila objetivo.
mes_siguiente es NULL en septiembre: es la última fila, no hay nada después.

LEAD es literalmente LAG mirando en la dirección opuesta. LEAD(x) equivale a LAG(x, -1). Úsalo cuando la pregunta natural sea "¿y el siguiente?": el próximo pedido de un cliente, el siguiente estado de un envío, el precio del día siguiente.

El uso estrella: variación porcentual mes a mes

Ya tienes todo para responder la pregunta con la que abrimos. La variación porcentual período-contra-período es:

$\text{variación}\%_t = 100 \times \frac{\text{ventas}_t - \text{ventas}_{t-1}}{\text{ventas}_{t-1}}$

Donde $\text{ventas}_{t-1}$ es exactamente lo que LAG nos da. Traducido a SQL:

sql

WITH ventas_mes AS (
  SELECT to_char(o.order_date, 'YYYY-MM')      AS mes,
         SUM(oi.quantity * oi.unit_price)::int AS ventas
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completado'
  GROUP BY 1
)
SELECT mes,
       ventas,
       LAG(ventas) OVER (ORDER BY mes) AS mes_anterior,
       ROUND(
         100.0 * (ventas - LAG(ventas) OVER (ORDER BY mes))
               / LAG(ventas) OVER (ORDER BY mes),
         1
       ) AS variacion_pct
FROM ventas_mes
ORDER BY mes;

El resultado cuenta la historia del negocio: abril crece +234,6 % frente a un marzo flojo, junio se desploma −82,0 %, y julio rebota +672,7 %. Ese es el tipo de insight que un dashboard ejecutivo quiere mostrar.

Tres detalles que marcan la diferencia entre un analista y un profesional:

100.0, no 100. Si escribes 100 * (...) con enteros, PostgreSQL hace división entera y pierdes los decimales. Multiplicar por 100.0 (o hacer un ::numeric) fuerza aritmética de punto flotante. Es el error silencioso número uno en estos cálculos.
La primera fila da NULL, porque variacion_pct depende de LAG, que es NULL en enero. Correcto: no hay variación que reportar sin un punto de comparación.
Cuidado con la división por cero. Si algún mes anterior fuera 0, la división explotaría. En producción se protege con NULLIF(LAG(ventas) OVER (...), 0) en el denominador, que convierte el 0 en NULL y hace que el cálculo devuelva NULL en vez de fallar.

Visualiza la tendencia

Una tabla de nueve filas está bien, pero la tendencia salta a la vista en un gráfico. Esta es la serie mensual de ventas que acabas de calcular — observa el bache de marzo–junio y los picos de abril y julio:

Chart

Cada punto de este gráfico es una fila de tu consulta, y cada pendiente entre dos puntos es, precisamente, la variacion_pct que calculamos con LAG. La función de ventana y la visualización cuentan la misma historia desde dos ángulos.

Errores comunes a evitar

Olvidar el ORDER BY dentro del OVER. Sin él, "la fila anterior" es indefinida y el resultado no es determinista. LAG casi siempre necesita OVER (ORDER BY algo).
Confundir el ORDER BY de la ventana con el de la consulta. Son independientes: OVER (ORDER BY mes) ordena dentro de la ventana; el ORDER BY mes final ordena la salida. Suele convenir que coincidan, pero son dos cláusulas distintas.
Comparar contra la fila anterior sin particionar. Si calculas variación mensual por región, necesitas OVER (PARTITION BY region ORDER BY mes). Sin PARTITION BY, la última fila de una región se compararía con la primera de otra — un salto sin sentido.
División entera en la variación porcentual. Ya lo vimos: usa 100.0 o un cast a numeric.

Puntos clave

LAG(x) trae x de la fila anterior; LEAD(x), de la siguiente. Ambas requieren OVER (ORDER BY ...).
El offset (2º arg) controla cuántas filas saltar; el valor_por_defecto (3er arg) reemplaza el NULL de los bordes.
El patrón período-contra-período es (actual - LAG(actual)) / LAG(actual), con 100.0 para el porcentaje y, en producción, NULLIF(..., 0) para blindar la división.

ReviewLAG y LEAD

1 of 6

Tu turno: comprobación final

Pon a prueba lo aprendido. Escribe una consulta que muestre, por mes, la venta total de pedidos completados junto a la venta del mes anterior, usando LAG. Resuélvela en la celda y pulsa Comprobar.

Exercise · SQL

Completa la consulta para que la columna venta_mes_anterior muestre, con LAG, la venta total del mes inmediatamente anterior (NULL para el primer mes). El resultado debe tener las columnas mes, ventas, venta_mes_anterior, ordenado por mes ascendente.

sql

-- Objetivo: por cada mes (formato 'YYYY-MM'), muestra
--   mes | ventas | venta_mes_anterior
-- usando LAG sobre la venta total de pedidos completados.
-- Ordena el resultado por mes ascendente.
-- Pista: reutiliza la venta mensual con una CTE y castea la suma a ::int.

WITH ventas_mes AS (
  SELECT to_char(o.order_date, 'YYYY-MM')      AS mes,
         SUM(oi.quantity * oi.unit_price)::int AS ventas
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completado'
  GROUP BY 1
)
SELECT mes,
       ventas,
       -- TODO: trae la venta del mes anterior con LAG
       ??? AS venta_mes_anterior
FROM ventas_mes
ORDER BY mes;

This is a preview. Enroll to run and check the exercise.

←→to move between lessons