LAG, LEAD, FIRST_VALUE y LAST_VALUE

Lección 7.4 · Tiempo de lectura: ~9 min · Tareas de práctica ↓

Esta lección presenta las funciones de ventana LAG, LEAD, FIRST_VALUE y LAST_VALUE. Aprenderás a obtener el valor anterior y el siguiente sin JOIN, a conseguir el primer y el último valor dentro de una ventana y por qué LAST_VALUE suele necesitar un marco explícito. Al terminar, usarás estas funciones con seguridad para comparar filas, analizar tendencias y preparar informes analíticos.

En la lección anterior vimos los marcos de ventana y cómo sus límites afectan a los cálculos. Ahora pasamos a las funciones que permiten mirar hacia atrás, hacia delante y a los valores de los extremos de una ventana.

Estas funciones son especialmente útiles en analítica: ayudan a comparar las ventas por día, identificar la acción anterior de un cliente, calcular el cambio respecto a un valor anterior y encontrar el primer o el último registro de un grupo sin self join.

Qué hacen estas funciones

Las cuatro son funciones de ventana y se usan con OVER (...).

• LAG devuelve un valor de una fila anterior de la ventana.
• LEAD devuelve un valor de una fila siguiente de la ventana.
• FIRST_VALUE devuelve el primer valor de la ventana actual.
• LAST_VALUE devuelve el último valor de la ventana actual.

La idea clave es sencilla: la fila actual se queda donde está, pero obtiene acceso a los valores de otras filas de la misma partición.

Sintaxis básica

LAG y LEAD

LAG(expression [, offset [, default_value]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)

LEAD(expression [, offset [, default_value]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)

• expression es el valor que quieres obtener de otra fila.
• offset indica cuántas filas retroceder o avanzar.
• default_value es lo que se devuelve si esa fila no existe.

FIRST_VALUE y LAST_VALUE

FIRST_VALUE(expression) OVER (
    [PARTITION BY ...]
    ORDER BY ...
    [frame_clause]
)

LAST_VALUE(expression) OVER (
    [PARTITION BY ...]
    ORDER BY ...
    [frame_clause]
)

En FIRST_VALUE y sobre todo en LAST_VALUE, el marco de la ventana importa. Sin un marco explícito, LAST_VALUE suele dar un resultado distinto del que esperan los principiantes.

Usar LAG

LAG es útil cuando necesitas comparar la fila actual con la anterior.

El pago anterior de un cliente

SELECT
    customer_id,
    payment_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS previous_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: cada fila muestra el pago actual y el importe del pago anterior del mismo cliente.

Diferencia con el pago anterior

SELECT
    customer_id,
    payment_date,
    amount,
    amount - LAG(amount, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS amount_diff
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: ves cuánto se diferencia el pago actual del anterior. En la primera fila se usa 0 como valor por defecto.

Usar LEAD

LEAD funciona de forma simétrica, pero mira hacia delante en lugar de hacia atrás.

El siguiente pago de un cliente

SELECT
    customer_id,
    payment_date,
    amount,
    LEAD(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS next_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: cada fila muestra el importe del siguiente pago de ese cliente.

Fecha del siguiente alquiler

SELECT
    customer_id,
    rental_date,
    LEAD(rental_date) OVER (
        PARTITION BY customer_id
        ORDER BY rental_date
    ) AS next_rental_date
FROM rental
WHERE customer_id = 1
ORDER BY rental_date;

Resultado: la consulta muestra cuándo hará el mismo cliente su siguiente alquiler.

Usar FIRST_VALUE

FIRST_VALUE devuelve el primer valor de la ventana. Es útil cuando quieres comparar la fila actual con un punto de partida.

El primer pago de un cliente

SELECT
    customer_id,
    payment_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_payment_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: el importe del primer pago del cliente se repite en cada fila de la ventana.

Comparar el pago actual con el primero

SELECT
    customer_id,
    payment_date,
    amount,
    amount - FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS diff_from_first
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: así puedes medir cuánto se alejan los valores actuales del primer valor de la secuencia.

Usar LAST_VALUE

LAST_VALUE parece sencillo, pero es justo aquí donde las expectativas suelen fallar.

Un matiz importante: el marco por defecto

Si escribes esto:

SELECT
    customer_id,
    payment_date,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
    ) AS last_amount_default
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

en muchos SGBD el resultado no es el último valor de toda la partición, sino el valor del final del marco actual. Muy a menudo, eso significa la propia fila actual.

Versión correcta para el último valor de la partición

SELECT
    customer_id,
    payment_date,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_payment_amount
FROM payment
WHERE customer_id = 1
ORDER BY payment_date;

Resultado: ahora cada fila ve el importe del último pago del cliente en toda la partición.

Por qué es útil

Este patrón es cómodo cuando necesitas comparar un valor actual con el último valor conocido de una serie, el estado final de un pedido o el último pago de un cliente.

Comparación de LAG, LEAD, FIRST_VALUE y LAST_VALUE

Si la tarea consiste en comparar filas vecinas, LAG y LEAD suelen ser las herramientas adecuadas. Si necesitas un punto de referencia al principio o al final de la ventana, usa FIRST_VALUE y LAST_VALUE.

Ejemplo práctico: ingresos diarios y comparación con los días vecinos

Primero agregamos los pagos por día y después aplicamos las funciones de ventana al resultado agregado:

SELECT
    pay_day,
    daily_total,
    LAG(daily_total) OVER (ORDER BY pay_day) AS previous_day_total,
    LEAD(daily_total) OVER (ORDER BY pay_day) AS next_day_total,
    FIRST_VALUE(daily_total) OVER (
        ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_day_total,
    LAST_VALUE(daily_total) OVER (
        ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_day_total
FROM (
    SELECT
        DATE(payment_date) AS pay_day,
        SUM(amount) AS daily_total
    FROM payment
    GROUP BY DATE(payment_date)
) AS daily_stats
ORDER BY pay_day;

Resultado: cada fecha obtiene los ingresos del día anterior y del siguiente, además del primer y el último valor de toda la secuencia.

Es una buena plantilla para el análisis de series temporales, la preparación de paneles y la detección de desviaciones de tendencia.

Preguntas frecuentes

¿Qué diferencia hay entre LAG y LEAD?

LAG mira hacia atrás y devuelve un valor de una fila anterior, mientras que LEAD mira hacia delante y devuelve un valor de una fila siguiente. Las dos funciones trabajan dentro de la ventana y del orden definidos.

¿Por qué LAST_VALUE devuelve a menudo la fila actual?

Porque el resultado depende del marco de la ventana. Si mantienes el marco por defecto, la última fila del marco puede ser la fila actual. Para obtener el último valor de toda la partición, normalmente necesitas ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

¿Puedo usar LAG y LEAD sin PARTITION BY?

Sí. En ese caso la función trabaja sobre todo el conjunto de resultados como si fuera una sola partición grande. Es útil cuando necesitas analizar una única secuencia global sin dividirla en grupos.

Preguntas de entrevista

¿Cuándo usar LAG y cuándo LEAD?

Usa LAG cuando necesites comparar la fila actual con la anterior, por ejemplo para encontrar el cambio respecto al pago previo. Usa LEAD cuando necesites mirar hacia delante, por ejemplo para obtener la fecha del siguiente evento o el siguiente valor de una métrica.

¿En qué se diferencia FIRST_VALUE de MIN?

MIN devuelve el valor mínimo de un conjunto de filas, mientras que FIRST_VALUE devuelve el valor de la primera fila según el orden indicado. Si el orden no coincide con el valor mínimo, los resultados serán distintos.

¿Por qué LAST_VALUE suele necesitar un marco explícito?

Porque LAST_VALUE no significa "la última fila de la partición pase lo que pase", sino la última fila del marco actual. Si el marco por defecto termina en la fila actual, la función devuelve el valor actual. Un marco explícito amplía la ventana a toda la partición.

Puntos clave de esta lección:

• LAG y LEAD permiten acceder a las filas vecinas sin self join.
• FIRST_VALUE y LAST_VALUE devuelven los valores de los extremos de una ventana, no simplemente el mínimo o el máximo.
• En todas estas funciones, la cláusula ORDER BY es crítica, porque define el orden de las filas.
• LAST_VALUE suele necesitar el marco explícito UNBOUNDED PRECEDING ... UNBOUNDED FOLLOWING.
• Estas funciones son especialmente útiles para analizar secuencias y series temporales y detectar cambios entre filas.