Lesión 7.4 · Tiempo de lectura: ~9 minutos

Esta lección introduce las funciones de ventana LAG, LEAD, FIRST_VALUE y LAST_VALUE. Aprenderás cómo recuperar valores anteriores y siguientes sin JOIN, cómo obtener el primer y último valor dentro de una ventana, y por qué LAST_VALUE a menudo requiere un marco explícito. Al final de la lección, podrás usar estas funciones con confianza para la comparación de filas, análisis de tendencias e informes analíticos.

LAG, LEAD, FIRST_VALUE y LAST_VALUE

En la lección anterior, cubrimos los marcos de ventana y vimos cómo los límites del marco afectan los cálculos. Ahora pasamos a funciones que nos permiten mirar hacia atrás, hacia adelante y a los valores extremos dentro de una ventana.

Estas funciones son especialmente útiles en análisis: ayudan a comparar ventas diarias, identificar la acción anterior de un cliente, calcular cambios respecto a un valor anterior y encontrar el primer o último registro en un grupo sin una auto unión.

Qué Hacen Estas Funciones

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

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

La idea clave es simple: la fila actual permanece en su lugar pero obtiene acceso a valores de otras filas en la misma partición.

Sintaxis Básica

LAG y LEAD

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

LEAD(expresión [, desplazamiento [, valor_por_defecto]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)

• expresión es el valor que deseas recuperar de otra fila.
• desplazamiento es cuántas filas hacia atrás o hacia adelante moverse.
• valor_por_defecto es lo que se devuelve si esa fila no existe.

FIRST_VALUE y LAST_VALUE

FIRST_VALUE(expresión) OVER (
    [PARTITION BY ...]
    ORDER BY ...
    [cláusula_de_marca]
)

LAST_VALUE(expresión) OVER (
    [PARTITION BY ...]
    ORDER BY ...
    [cláusula_de_marca]
)

Para FIRST_VALUE y especialmente LAST_VALUE, el marco de ventana es importante. Sin un marco explícito, LAST_VALUE a menudo produce un resultado diferente de lo que los principiantes esperan.

Usando 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 monto del pago anterior para el 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: puedes ver cuánto difiere el pago actual del anterior. Para la primera fila, se usa 0 como valor por defecto.

Usando LEAD

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

El próximo 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 monto del próximo pago para ese cliente.

Próxima fecha de 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 el mismo cliente hará el próximo alquiler.

Usando FIRST_VALUE

FIRST_VALUE devuelve el primer valor en la ventana. Esto es útil cuando deseas comparar la fila actual con un punto de inicio.

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 monto del primer pago del cliente se repite en cada fila de la ventana.

Comparando 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: esto ayuda a medir cuánto se alejan los valores actuales del primer valor en la secuencia.

Usando LAST_VALUE

LAST_VALUE parece sencillo, pero aquí es donde las expectativas a menudo se rompen.

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;

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

Versión correcta para el último valor en 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: cada fila ahora puede ver el monto del último pago del cliente en la partición completa.

Por qué esto es útil

Este patrón es conveniente cuando necesitas comparar un valor actual con el último valor conocido en una serie, el estado final del pedido o el último pago realizado por un cliente.

Comparando LAG, LEAD, FIRST_VALUE y LAST_VALUE

Si la tarea se trata de comparar filas vecinas, LAG y LEAD son generalmente las herramientas adecuadas. Si necesitas un punto de referencia al inicio o al final de la ventana, usa FIRST_VALUE y LAST_VALUE.

Ejemplo Práctico: Ingresos Diarios y Comparación con Días Vecinos

Primero, agrega los pagos por día, luego aplica 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 tiene acceso a los ingresos del día anterior, los ingresos del día siguiente, y los primeros y últimos valores en la secuencia completa.

Este es un fuerte template para análisis de series temporales, preparación de paneles y identificación de desviaciones de tendencias.

Preguntas Frecuentes

¿Cuál es la diferencia entre LAG y LEAD?

LAG mira hacia atrás y devuelve un valor de una fila anterior, mientras que LEAD mira hacia adelante y devuelve un valor de una fila siguiente. Ambas funciones operan dentro de la ventana y el orden de clasificación definidos.

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

Porque el resultado depende del marco de ventana. Si mantienes el marco por defecto, la última fila del marco puede ser la fila actual. Para obtener el último valor de la partición completa, generalmente 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 una gran partición. Esto es útil cuando necesitas analizar una única secuencia general sin dividirla en grupos.

Preguntas de Entrevista

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

Usa LAG cuando necesites comparar la fila actual con la anterior, por ejemplo, para encontrar el cambio del pago anterior. Usa LEAD cuando necesites mirar hacia adelante, por ejemplo, para obtener la próxima fecha de evento o el próximo valor métrico.

¿Cómo es FIRST_VALUE diferente de MIN?

MIN devuelve el valor mínimo a través de un conjunto de filas, mientras que FIRST_VALUE devuelve el valor de la primera fila según el orden especificado. Si el orden de clasificación no coincide con el valor mínimo, los resultados diferirán.

¿Por qué LAST_VALUE a menudo requiere un marco explícito?

Porque LAST_VALUE no significa "la última fila de la partición sin importar qué". Significa 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 expande la ventana a toda la partición.

Puntos clave de esta lección:

• LAG y LEAD te permiten acceder a filas vecinas sin una auto unión.
• FIRST_VALUE y LAST_VALUE devuelven valores extremos dentro de una ventana, no simplemente el mínimo o máximo.
• Para todas estas funciones, la cláusula ORDER BY es crítica porque define la secuencia de filas.
• LAST_VALUE a menudo requiere el marco explícito UNBOUNDED PRECEDING ... UNBOUNDED FOLLOWING.
• Estas funciones son especialmente útiles para el análisis de secuencias, trabajo con series temporales y detección de cambios entre filas.

En la próxima lección, aplicaremos funciones de ventana a totales acumulativos y promedios móviles.