Funzioni window per dati temporali: LEAD(), LAG()

Adesso il nostro obiettivo è andare ancora oltre e imparare a usare le funzioni window per analizzare dati temporali. Pronti? Spero che tu abbia preso una tazza di caffè, perché questa roba è davvero interessante.

Allora, come sempre, prima rispondiamo alla domanda principale: perché ci servono le funzioni window (LEAD(), LAG())? Immagina di lavorare con dati temporali, che siano log di eventi, orari di lavoro, serie temporali o qualsiasi cosa dove la sequenza degli eventi conta.

Per esempio, vuoi:

• Scoprire quando è avvenuto il prossimo evento dopo quello attuale.
• Calcolare la differenza di tempo tra l’evento attuale e quello precedente.
• Ordinare i dati e calcolare la differenza tra i record.

Ed è qui che entrano in gioco due funzioni top: LEAD() e LAG(). Ti permettono di prendere dati dalla riga precedente o successiva all’interno di una certa finestra. È come avere un libro magico dove puoi sbirciare la pagina dopo senza dover girare quella attuale.

LEAD() e LAG(): sintassi e principi base

Entrambe le funzioni usano una sintassi simile:

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 colonna da cui vogliamo prendere i dati.
• offset (opzionale) — lo spostamento rispetto alla riga attuale. Di default è 1.
• default_value (opzionale) — il valore che viene restituito se non c’è una riga con lo spostamento richiesto (tipo quando sei sull’ultima riga).
• OVER() — qui si definisce la "finestra" su cui fare il calcolo. Di solito c’è ORDER BY, a volte si usa PARTITION BY per dividere i dati in gruppi.

Esempio: Semplice LEAD() e LAG()

Creiamo una tabella semplice events per i nostri esperimenti:

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');

Ora vogliamo vedere quando sono avvenuti gli eventi precedenti e successivi rispetto a ciascun evento:

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

Il risultato sarà questo:

Qui LAG() prende i dati dalla riga precedente, mentre LEAD() dalla successiva. Il primo evento non ha nulla da guardare indietro, l’ultimo non ha nessuno da superare, quindi ottengono NULL.

Esempio: differenza tra eventi

A volte ci serve sapere quanto tempo è passato tra un evento e l’altro. Per farlo basta sottrarre un timestamp dall’altro:

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

Risultato:

Esempio: uso di PARTITION BY

Supponiamo di avere più utenti, ognuno con i propri eventi. Vogliamo trovare la differenza tra eventi per ogni utente.

Aggiorniamo la tabella e aggiungiamo la colonna 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;

Ora abbiamo due utenti. Usiamo PARTITION BY per calcolare all’interno di ogni gruppo:

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

Risultato:

Esempi di utilizzo in casi reali

1. Log di eventi: analisi del tempo tra eventi, come login e logout dell’utente.
2. Time-tracking: calcolo del tempo passato su task specifici.
3. Analisi comportamentale: analisi della sequenza di azioni dei clienti in un e-commerce.
4. Calcolo di metriche cumulative: uso delle funzioni window per lavorare con serie temporali.

Errori tipici

Lavorando con LEAD() e LAG() i problemi principali possono essere:

• Dimenticare ORDER BY in OVER(). Senza di esso la funzione non può capire la sequenza delle righe.
• Problemi con intervalli temporali o tipi di dato (TIMESTAMP vs DATE).
• Ignorare i valori NULL che possono comparire all’inizio o alla fine della finestra.

Per evitare questi errori, controlla sempre i tuoi dati e assicurati di aver definito la finestra giusta per le operazioni.