Aprende SQL · lección gratuita

Lección 32 · Acceso entre filas: LAG y LEAD

Resumen

LAG y LEAD permiten leer el valor de una columna en una fila anterior o posterior dentro de la ventana, sin necesidad de auto-joins. Son la herramienta natural para análisis de series temporales: calcular la variación mes a mes, el crecimiento porcentual, la diferencia con el registro previo o la comparación con el siguiente. El orden lo define ORDER BY dentro de OVER; el "anterior" y "siguiente" se entienden respecto a ese orden.

• LAG(col): devuelve el valor de col en la fila anterior según el ORDER BY de la ventana. En la primera fila no hay anterior → NULL.
• LEAD(col): devuelve el valor de la fila siguiente. En la última fila → NULL.
• Desplazamiento y default: LAG(col, n, default) retrocede n filas (por defecto 1) y usa default cuando no existe fila (en vez de NULL). Igual para LEAD.
• ORDER BY define el sentido: sin un orden claro, "anterior/siguiente" no tiene significado. Ordena por fecha, mes o la clave temporal relevante.
• Patrón series temporales: agrega primero con una CTE (GROUP BY strftime('%Y-%m', fecha)) y aplica LAG/LEAD sobre el resultado mensual, no sobre las filas crudas.

Sintaxis / Conceptos

LAG(columna [, offset [, default]])  OVER ([PARTITION BY ...] ORDER BY ...)
LEAD(columna [, offset [, default]]) OVER ([PARTITION BY ...] ORDER BY ...)

Por qué una CTE para series mensuales. Las filas de detalle_pedidos no son mensuales: cada pedido tiene varias líneas. Para comparar "mes contra mes" primero hay que agregar el revenue por mes y luego aplicar LAG. Si aplicaras LAG directamente sobre el detalle, compararías líneas individuales, no meses. El patrón es:

WITH por_mes AS (
  SELECT strftime('%Y-%m', fecha) AS mes, SUM(...) AS revenue
  FROM ... GROUP BY mes
)
SELECT mes, revenue, LAG(revenue) OVER (ORDER BY mes) AS mes_anterior
FROM por_mes;

El NULL de los bordes. La primera fila no tiene anterior y la última no tiene siguiente. Si vas a dividir (crecimiento %), maneja el NULL: usa LAG(revenue, 1, 0) para un default, o filtra/COALESCE según convenga. Dividir entre NULL da NULL; dividir entre 0 da error o NULL en SQLite, así que ten cuidado al calcular porcentajes en el primer período.

SELECT fecha, estado, LAG(fecha) OVER (ORDER BY fecha) AS pedido_anterior FROM pedidos LIMIT 6

Ejemplos

-- Revenue mensual con el mes anterior, el siguiente y la variación absoluta.
-- Se agrega por mes en la CTE; LAG/LEAD operan sobre los meses ya sumados.
WITH rev_mes AS (
  SELECT strftime('%Y-%m', p.fecha) AS mes,
         ROUND(SUM(d.cantidad * d.precio_unitario * (1 - d.descuento)), 2) AS revenue
  FROM pedidos p
  JOIN detalle_pedidos d ON d.pedido_id = p.id
  GROUP BY mes
)
SELECT mes,
       revenue,
       LAG(revenue)  OVER (ORDER BY mes) AS mes_anterior,
       LEAD(revenue) OVER (ORDER BY mes) AS mes_siguiente,
       revenue - LAG(revenue) OVER (ORDER BY mes) AS variacion
FROM rev_mes
ORDER BY mes;

-- Crecimiento porcentual mes a mes. Usamos LAG(...,1,0) para el default,
-- pero solo dividimos cuando hay mes anterior (>0) para evitar /0.
WITH rev_mes AS (
  SELECT strftime('%Y-%m', p.fecha) AS mes,
         ROUND(SUM(d.cantidad * d.precio_unitario * (1 - d.descuento)), 2) AS revenue
  FROM pedidos p
  JOIN detalle_pedidos d ON d.pedido_id = p.id
  GROUP BY mes
)
SELECT mes,
       revenue,
       LAG(revenue) OVER (ORDER BY mes) AS prev,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY mes)) * 100.0
             / LAG(revenue) OVER (ORDER BY mes), 1) AS crecimiento_pct
FROM rev_mes
ORDER BY mes;

-- LAG dentro de cada departamento: salario del empleado contratado
-- justo antes (por fecha de contratación). PARTITION reinicia por depto.
SELECT departamento_id,
       nombre,
       fecha_contratacion,
       salario,
       LAG(salario) OVER (PARTITION BY departamento_id
                          ORDER BY fecha_contratacion) AS salario_previo_en_depto
FROM empleados
ORDER BY departamento_id, fecha_contratacion;

💡 LAG/LEAD reemplazan a los auto-joins "fila con su predecesora", que son verbosos y lentos. Recuerda manejar el NULL del borde: una variación o un crecimiento % en el primer período no existe, no es cero.

Cheatsheet

Función	Qué devuelve
LAG(col)	Valor de col en la fila anterior (NULL en la primera)
LEAD(col)	Valor de col en la fila siguiente (NULL en la última)
LAG(col, n)	Retrocede n filas (default 1)
LAG(col, n, d)	Usa d en vez de NULL cuando no hay fila
LEAD(col, n, d)	Igual hacia adelante
val - LAG(val) OVER (ORDER BY t)	Variación respecto al período anterior

---

← Ranking: ROW_NUMBER, RANK, DENSE_RANKTotales acumulados y marcos de ventana (frames) →

Ver todas las lecciones de Aprende SQL →