Funciones de ventana para datos temporales: LEAD(), LAG()

Ahora nuestra misión es ir un paso más allá y aprender a usar funciones de ventana para analizar datos temporales. ¿Preparadx? Espero que tengas una taza de café a mano, porque esto se va a poner interesante.

Entonces, como siempre, primero respondemos a la pregunta principal: ¿para qué necesitamos funciones de ventana (LEAD(), LAG())? Imagina que trabajas con datos temporales, ya sean logs de eventos, horas de trabajo, series temporales o cualquier cosa donde el orden de los eventos importa.

Por ejemplo, quieres:

• Saber cuándo ocurrió el siguiente evento después del actual.
• Calcular la diferencia de tiempo entre el evento actual y el anterior.
• Ordenar los datos y calcular la diferencia entre registros.

Aquí es donde entran en juego dos funciones geniales: LEAD() y LAG(). Te permiten sacar datos de la fila anterior o siguiente dentro de una ventana definida. Es como si tuvieras un libro mágico donde puedes mirar la siguiente página sin pasar la actual.

LEAD() y LAG(): sintaxis y principios básicos

Ambas funciones usan una sintaxis parecida:

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name — la columna de la que queremos sacar los datos.
• offset (opcional) — el desplazamiento respecto a la fila actual. Por defecto es 1.
• default_value (opcional) — el valor que se devuelve si no existe la fila con el desplazamiento requerido (por ejemplo, cuando estás en la última fila).
• OVER() — aquí defines la "ventana" sobre la que se hace el cálculo. Normalmente es ORDER BY, a veces se usa PARTITION BY para separar los datos en grupos.

Ejemplo: Simplemente LEAD() y LAG()

Vamos a crear una tabla sencilla events para nuestros experimentos:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('Evento A', '2023-10-01 10:00:00'),
    ('Evento B', '2023-10-01 11:00:00'),
    ('Evento C', '2023-10-01 12:00:00'),
    ('Evento D', '2023-10-01 13:00:00');

Ahora queremos ver cuándo ocurrieron los eventos anteriores y siguientes respecto a cada evento:

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS evento_anterior,
    LEAD(event_date) OVER (ORDER BY event_date) AS evento_siguiente
FROM events;

El resultado será así:

Aquí LAG() saca los datos de la fila anterior y LEAD() de la siguiente. El primer evento no tiene a quién mirar atrás, y el último no tiene a quién adelantar, así que reciben NULL.

Ejemplo: diferencia entre eventos

A veces necesitamos saber cuánto tiempo ha pasado entre eventos. Para eso, simplemente restamos un tiempo de otro:

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS tiempo_desde_evento_anterior
FROM events;

Resultado:

Ejemplo: usando PARTITION BY

Supón que tenemos varios usuarios, cada uno con sus propios eventos. Queremos encontrar la diferencia entre eventos para cada usuario.

Actualizamos la tabla y añadimos la columna user_id:

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

Ahora tenemos dos usuarios. Usamos PARTITION BY para calcular dentro de cada grupo:

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS tiempo_desde_evento_anterior
FROM events;

Resultado:

Ejemplos de uso en tareas reales

1. Logs de eventos: analizar el tiempo entre eventos, como login y logout de usuario.
2. Time-tracking: calcular el tiempo dedicado a tareas concretas.
3. Analítica de comportamiento: analizar la secuencia de acciones de clientes en una tienda online.
4. Cálculo de métricas acumulativas: usar funciones de ventana para trabajar con series temporales.

Errores típicos

Al trabajar con LEAD() y LAG() los problemas clave pueden ser:

• Olvidar ORDER BY en OVER(). Sin eso la función no puede saber el orden de las filas.
• Problemas con intervalos de tiempo o tipos de datos (TIMESTAMP vs DATE).
• Ignorar los valores NULL que pueden aparecer al principio y al final de la ventana.

Para evitar estos errores, revisa siempre tus datos y asegúrate de definir bien la ventana para las operaciones.