La funzione LAG e la funzione LEAD in SQL

LAG() e LEAD() sono funzioni posizionali. Una funzione posizionale è un tipo di window function o funzione finestra. Se non sapete quando e come usarle, qual è la sintassi di ciascuna funzione, perché usarle e quali sono le differenze, continuate a leggere!

LAG() e LEAD() sono funzioni posizionali. Sono window functions e sono molto utili nella creazione di report, perché possono fare riferimento a dati di righe superiori o inferiori a quella corrente. In questo articolo analizzeremo in dettaglio queste due funzioni.

Per imparare cosa fanno le funzioni finestra, quali sono e come si applicano ai problemi del mondo reale, è meglio seguire il nostro corso Window Functions. Potete trovare tutte le informazioni sul corso qui. È interattivo, ci sono 218 esercizi e sono sufficienti un browser web e alcune conoscenze di base di SQL.

Sintassi della funzione LAG

La funzione LAG() consente di accedere a un valore memorizzato in una riga diversa, più in alto rispetto a quella corrente. La riga può essere adiacente o distanziata di un certo numero di righe, ordinata in base a una colonna o a un set di colonne specifiche.

Vediamo la sua sintassi:

LAG(espressione [,offset[,valore_predefinito]]) OVER(ORDER BY colonne)

LAG() richiede tre argomenti: il nome della colonna o di un'espressione da cui si ottiene il valore, il numero di righe da saltare (offset) e il valore predefinito da restituire se il valore memorizzato ottenuto dalla riga precedente è vuoto. Solo il primo argomento è necessario. Il terzo argomento (valore predefinito) è ammesso solo se si specifica il secondo argomento, l'offset.

Come per le altre window functions, LAG() richiede la clausola OVER. Può accettare parametri opzionali, che verranno spiegati in seguito. Con LAG(), è necessario specificare un'istruzione ORDER BY nella clausola OVER, con una colonna o un elenco di colonne in base alle quali ordinare le righe.

Consideriamo la seguente tabella, vendite:

E la seguente query con una funzione LAG():

SELECT nome_del_venditore, valore_di_vendita,
  LAG(valore_di_vendita) OVER(ORDER BY valore_di_vendita) as valore_di_vendita_precedente
FROM vendita;

Ecco il risultato:

Questo uso molto semplice di LAG() visualizza il valore della riga adiacente in alto. Per esempio, il secondo record visualizza l'importo della vendita di Alice (12.000 dollari) e di quella di Stef (7.000 dollari) dalla riga precedente, rispettivamente nelle colonne valore_di_vendita e valore_di_vendita_precedente. Notate che la prima riga non ha una riga adiacente che la precede e di conseguenza il campo valore_di_vendita_precedente è vuoto (NULL), poiché la riga da cui si dovrebbe ottenere il valore di valore_di_vendita non esiste.

Se specifichiamo solo l'argomento richiesto (il nome della colonna o un'altra espressione), come in questo esempio, l'argomento offset è predefinito a 1 e il terzo argomento è predefinito come NULL. Nel nostro esempio, la prima riga del set di risultati ha NULL in valore_di_vendita_precedente e nelle altre righe sono presenti i valori delle rispettive righe immediatamente precedenti, poiché l'offset è 1.

Utilizzando LAG(), è possibile vedere il valore della riga corrente e quello della riga adiacente. È possibile utilizzare questa funzione, per esempio, per verificare l'importo delle vendite di una determinata riga rispetto a quello della riga precedente, con l'importo delle vendite ordinato dal più basso al più alto.

L'illustrazione seguente mostra come il valore della riga adiacente viene aggiunto a quella corrente.

La funzione LAG() è inclusa nella nostra pratica "Scheda riassuntiva sulle Window Functions di SQL".

La sintassi della funzione LEAD

LEAD() è simile a LAG(). Mentre LAG() accede a un valore memorizzato in una riga superiore, LEAD() accede a un valore memorizzato in una riga inferiore.

La sintassi di LEAD() è simile a quella di LAG():

LEAD(espressione [,offset[,valore_predefinito]]) OVER(ORDER BY colonne)

Come LAG(), la funzione LEAD() accetta tre argomenti: il nome di una colonna o di un'espressione, l'offset da saltare in basso e il valore predefinito da restituire se il valore memorizzato ottenuto dalla riga sottostante è vuoto. Solo il primo argomento è necessario. Il terzo argomento, il valore predefinito, può essere specificato solo se si specifica il secondo argomento, l'offset.

Proprio come LAG(), LEAD() è una funzione finestra e richiede una clausola OVER. E come per LAG(), LEAD() deve essere accompagnata da un'istruzione ORDER BY nella clausola OVER.

Osserviamo nuovamente la tabella, vendite:

Ecco una query con una funzione LEAD():

SELECT nome_del_venditore, valore_di_vendita,
  LEAD(valore_di_vendita) OVER(ORDER BY valore_di_vendita) as valore_di_vendita_successivo
FROM vendita;

Ecco il set di risultati:

Le righe sono ordinate in base alla colonna specificata in ORDER BY (valore_di_vendita). La funzione LEAD() prende l'importo della vendita dalla riga sottostante. Per esempio, l'importo della vendita di Stef è di 7.000 dollari nella colonna valore_di_vendita, mentre la colonna valore_di_vendita_successivo nello stesso record contiene 12.000 dollari. Quest'ultima proviene dalla colonna valore_di_vendita di Alice, il venditore della riga successiva. Si noti che l'ultima riga non ha una riga successiva, quindi il campo valore_di_vendita_successivo è vuoto (NULL) per l'ultima riga.

Se si specifica solo l'argomento richiesto, vale a dire solo il nome della colonna o un'altra espressione, l'offset è predefinito a 1 e il terzo argomento è predefinito come NULL. Nel nostro esempio, il valore della colonna valore_di_vendita_successivo di Alice proviene dalla colonna valore_di_vendita della riga adiacente sottostante, poiché l'offset predefinito è 1.

Utilizzando LEAD(), è possibile confrontare i valori tra le righe. L'illustrazione seguente mostra come l'importo restituito da LEAD() viene aggiunto alla riga corrente.

Uso di LAG() e LEAD() per confrontare i valori

Un uso importante di LAG() e LEAD() nei report è quello di confrontare i valori della riga corrente con quelli della stessa colonna, ma di una riga superiore o inferiore.

Consideriamo la seguente tabella, vendite_annuali, mostrata di seguito:

Come si può vedere, questa tabella contiene l'importo totale delle vendite per anno. Utilizzando LAG() e LEAD(), è possibile confrontare gli importi delle vendite annuali tra gli anni.

Esaminiamo questa query:

SELECT  anno, vendita_totale AS vendita_totale_corrente,
   LAG(vendita_totale) OVER(ORDER BY anno) AS vendita_totale_precedente,
   vendita_totale - LAG(vendita_totale) OVER(ORDER BY anno) AS differenza
FROM vendite_annuali;

Ecco il set di risultati:

Questa query prende l'importo delle vendite dell'anno precedente e lo inserisce nella colonna vendita_totale_precedente utilizzando la funzione LAG(). ORDER BY nella clausola OVER ordina i record per anno, assicurandosi che la riga adiacente rappresenti l'anno precedente. Quindi prende l'importo dalla colonna vendita_totale della riga precedente e lo riporta alla riga corrente.

Questa query calcola anche la differenza dell'importo delle vendite tra l'anno in corso e l'anno precedente. Questo ci aiuta a capire se c'è stato un aumento (differenza positiva) o una diminuzione (differenza negativa) delle vendite da un anno all'altro.

Per il 2015 non abbiamo informazioni sull'anno precedente. Pertanto, il valore restituito dalla funzione LAG() è NULL così come la differenza. La vendita totale nel 2018 è stata di 32.000 dollari, ma nel 2017 (l'anno precedente) era di 34.000 dollari, come mostrato nella colonna vendita_totale_precedente. La differenza è di -2.000 $, il che indica che nel 2018 c'è stata una diminuzione delle vendite di 2.000 $ rispetto all'anno 2017.

Utilizzo di LAG() e LEAD() con un offset specifico

È possibile utilizzare le funzioni LAG() e LEAD() con due argomenti: il nome della colonna e l'offset.

Consideriamo la seguente tabella, dipendenti:

La query seguente seleziona il bonus per il dipendente con id=1 per ogni trimestre di ogni anno. Quindi identifica i bonus per il trimestre corrispondente dell'anno precedente e dell'anno successivo.

SELECT anno, trimestre,
  LAG(bonus,4) OVER(ORDER BY anno,trimestre) AS bonus_precedente,
 bonus AS bonus_corrente,
  LEAD(bonus,4) OVER(ORDER BY anno,trimestre) AS bonus_successivo 
FROM dipendenti
WHERE id_dipendente=1;

La query restituisce il seguente set di risultati:

Le righe evidenziate in verde sono i record del primo trimestre di ogni anno, quelle in bianco il secondo trimestre di ogni anno, ecc. In ogni riga, l'importo del bonus precedente e quello successivo sono presi dal trimestre corrispondente dell'anno precedente e dell'anno successivo e sono assegnati alle colonne bonus_precedente e bonus_successivo, rispettivamente.

Per esempio, il dipendente id=1 ha ricevuto un bonus di 80 dollari nel primo trimestre del 2018. Per lo stesso dipendente, il bonus del primo trimestre del 2017 era di 100 dollari e quello del 2019 di 0 dollari. ORDER BY specifica che le righe devono essere ordinate per anno e trimestre. Un offset di 4 indica a LEAD() e LAG() di saltare rispettivamente 4 righe prima e dopo quella corrente. Con questo offset, è possibile confrontare i valori dello stesso trimestre di anni diversi, poiché ci sono 4 trimestri in un anno. L'immagine seguente illustra questa idea.

Per saperne di più sulle funzioni LAG() e LEAD(), consultate i nostri articoli "Window Functions comuni in SQL: funzioni posizionali " di Aldo Zelen e "Quando devo usare le Window Functions in SQL?" di Tihomir Babic.

Uso di LAG() e LEAD() con un valore predefinito

Nella sezione precedente abbiamo discusso come utilizzare l'argomento offset in LAG() e LEAD(). Ora consideriamo i casi con un terzo argomento: il valore predefinito da assegnare quando il valore ottenuto è NULL. Per specificare questo argomento, è necessario indicare anche il secondo, l'offset. L'offset predefinito è 1, quindi inserite 1 per mantenere l'offset predefinito o un altro valore appropriato per il vostro caso.

Vediamo un altro esempio. La tabella seguente, vendite_prodotti contiene gli id dei prodotti, il mese (1 = gennaio, 2 = febbraio, ecc.) e il conteggio delle vendite per mese.

Ecco i record per i quali l'id del prodotto è 1.

La query:

SELECT id_prodotto, mese,
  LAG(conteggio,1,0) OVER(ORDER BY mese) AS conteggio_precedente,
  conteggio AS conteggio_corrente,
  conteggio - LAG(conteggio,1,0) OVER(ORDER BY mese) AS differenza
FROM vendite_prodotti
WHERE id_prodotto=1;

restituisce il risultato:

Per il prodotto con id=1, selezioniamo il mese di vendita, il conteggio delle vendite per questo mese (conteggio_corrente) e il conteggio delle vendite del mese precedente (il valore della riga precedente restituito da LAG()).

Vorremmo mostrare zero al posto di NULL quando LAG() cerca di ottenere valori da righe diverse da quelle esistenti nel nostro set di dati. Sia per LAG() che per LEAD(), questo risultato si ottiene specificando un terzo argomento, il valore predefinito. Ricordate che l'argomento offset è necessario per specificare l'argomento valore predefinito; in questo caso, indichiamo un offset di 1 per osservare la riga precedente. Quindi specifichiamo 0 come terzo argomento. In questo modo, si azzera qualsiasi tentativo di ottenere valori da righe che non esistono, come nel caso della prima riga (non esiste una riga sopra la prima).

Notate che il valore predefinito zero viene assegnato solo alle righe inesistenti; le righe adiacenti, ma con valori NULL in conteggio_corrente, restano NULL invece di essere sostituite da 0. Un esempio di questo risultato è visibile nella riga relativa ad aprile (mese=4): sebbene conteggio_corrente per la riga precedente (mese=3) sia NULL, il valore non viene sostituito con uno zero, poiché la riga precedente esiste e contiene solo un NULL in conteggio_corrente.

Utilizzo di LAG() e LEAD() con le partizioni

Continuiamo con lo stesso esempio, ma ora esaminiamo il caso in cui abbiamo bisogno di PARTITION BY nella clausola OVER. Di seguito è riportata la parte successiva della tabella vendite_prodotti con un altro prodotto con id=2.

La query:

SELECT id_prodotto, mese,
  LAG(conteggio,1,0) OVER(PARTITION BY id_prodotto ORDER BY mese) AS conteggio_precedente,
  conteggio AS conteggio_corrente,
  conteggio - LAG(conteggio,1,0) OVER(PARTITION BY id_prodotto ORDER BY mese) AS differenza
FROM vendite_prodotti;

restituisce il risultato:

Abbiamo più prodotti in questa tabella. Per calcolare le differenze tra le vendite attuali e le vendite precedenti separatamente per ciascun prodotto, è necessario specificare PARTITION BY prima di ORDER BY nella clausola OVER.

In PARTITION BY possiamo inserire il nome di una colonna o un elenco di colonne. In questo caso, abbiamo usato la colonna id_prodotto per dividere i record in partizioni e ordinare i risultati per mese all'interno di ogni partizione. L'esito è che ogni partizione inizia con il mese 1 e termina con il mese 4.

PARTITION BY è simile a GROUP BY in quanto raggruppa valori simili. A differenza di GROUP BY, tuttavia, PARTITION BY non comprime le righe originali in una sola; le righe originali restano accessibili. Per ulteriori informazioni sulla differenza tra PARTITION BY e GROUP BY, consultate l'articolo "Che differenza c'è tra GROUP BY e PARTITION BY?".

Le funzioni LAG e LEAD sono molto utili!

Le funzioni di posizionamento come LAG() e LEAD() sono utili in molte situazioni. Spesso vengono utilizzate nella creazione di report, perché possono fare riferimento alle righe superiori o inferiori, come abbiamo visto in questi esempi. Spero che questo articolo vi aiuti ad ampliare le vostre conoscenze di SQL sulle window functions. Per saperne di più sulle funzioni posizionali, leggete gli articoli "Window Functions comuni in SQL: funzioni posizionali " di Aldo Zelen e "Quando devo usare le Window Functions in SQL?" di Tihomir Babic. Se siete interessati a saperne di più sulle funzioni finestra, provate il nostro corso interattivo "Window Functions" sulla piattaforma LearnSQL.it.