SQL Interview Prep · Lezione

LAG e LEAD per le righe adiacenti

Accedere ai valori della riga precedente e successiva senza usare un self-join

LAG e LEAD per le righe adiacenti è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 1 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.

La domanda degli intervistatori

Una delle domande più comuni nei colloqui per analisti è: "Confrontare ogni riga con quella precedente senza usare una self-join." Si pensi ai ricavi mese su mese, all'accesso precedente di un utente o all'evento successivo in una sequenza.

La risposta più pulita consiste nelle funzioni finestra LAG e LEAD. Consentono a una riga di leggere il valore di una riga vicina mantenendo intatti tutti i dettagli delle righe. In questa lezione costruirà un modello mentale preciso del modo in cui navigano tra le righe adiacenti.

Cosa fanno LAG e LEAD

LAG(col) restituisce il valore di col della riga precedente. LEAD(col) restituisce il valore della riga successiva. "Precedente" e "successiva" sono definiti interamente da ORDER BY all'interno della clausola OVER.

LAG guarda indietro.
LEAD guarda avanti.

Entrambe sono funzioni finestra con offset: non accorpano mai le righe, ma collegano semplicemente il valore di una riga vicina alla riga corrente.

Sintassi di base di LAG

Ecco la forma canonica. Abbiamo una tabella sales con le colonne month e revenue. Vogliamo che ogni riga mostri anche i ricavi del mese precedente.

OVER (ORDER BY month) indica al motore come definire "precedente". La prima riga non ha un predecessore, quindi in quella riga prev_revenue è NULL.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

Leggere il risultato

Per i dati 2024-01 = 100, 2024-02 = 130, 2024-03 = 120, la query restituisce:

Gen: ricavi 100, prev_revenue NULL
Feb: ricavi 130, prev_revenue 100
Mar: ricavi 120, prev_revenue 130

Ogni riga ha recuperato il valore della riga immediatamente precedente nell'insieme ordinato. Nessuna self-join, nessuna query annidata e nessuna perdita di righe.

LEAD guarda avanti

LEAD è l'immagine speculare di LAG. Lo usi quando una riga deve sapere cosa viene dopo, ad esempio la data del prossimo acquisto per calcolare il tempo tra gli ordini.

L'ultima riga dell'insieme ordinato non ha una riga successiva, quindi il risultato di LEAD è NULL.

SELECT
  month,
  revenue,
  LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM sales
ORDER BY month;

L'argomento offset

Entrambe le funzioni accettano un secondo argomento facoltativo: il numero di righe da saltare. LAG(col, 2) torna indietro di due righe, mentre LEAD(col, 3) salta tre righe in avanti.

Gli intervistatori usano questo argomento per chiedere, ad esempio, "i ricavi di due mesi fa" o "il valore tre righe più avanti". L'offset predefinito è 1.

SELECT
  month,
  revenue,
  LAG(revenue, 2) OVER (ORDER BY month) AS revenue_2_months_ago
FROM sales
ORDER BY month;

L'argomento del valore predefinito

Un terzo argomento fornisce un valore sostitutivo quando non esiste una riga vicina, invece di ottenere NULL. La firma è LAG(col, offset, default).

È utile quando un calcolo successivo non può gestire NULL, ad esempio trattando il valore precedente mancante come 0 affinché sia comunque possibile calcolare una differenza.

SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

PARTITION BY riavvia la finestra

I dati reali raramente contengono un'unica serie globale. Di solito si effettuano confronti all'interno di ogni cliente, prodotto o area geografica. PARTITION BY riavvia il calcolo di LAG/LEAD all'inizio di ogni partizione.

Di conseguenza, la prima riga di ogni partizione riceve NULL da LAG e un valore non attraversa mai il confine finendo nei dati di un altro cliente.

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_amount
FROM orders;

Esempio svolto: giorni tra gli ordini

Un'attività frequente consiste nel misurare l'intervallo tra gli ordini consecutivi di un cliente. Recuperi la data dell'ordine precedente con LAG, quindi calcoli la differenza.

Per il primo ordine di ogni cliente viene restituito NULL, perché non esiste una data precedente da sottrarre. Questo è esattamente il tipo di confronto per cliente che i selezionatori si aspettano venga risolto con le funzioni finestra.

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS days_since_prev
FROM orders;

Perché non usare un self-join?

Prima delle funzioni finestra, la soluzione era un self-join correlato: unire la tabella a sé stessa sulla riga la cui data è la più grande tra quelle precedenti a quella corrente. Funziona, ma è prolisso, soggetto a errori in presenza di valori ex aequo e spesso più lento.

LAG/LEAD esprimono l'intento in una sola riga.
Vengono valutate in un'unica scansione ordinata.
I valori ex aequo vengono risolti in modo deterministico dal Suo ORDER BY.

Dire «userei LAG invece di un self-join» dimostra padronanza dell'argomento.

Errore comune: ORDER BY mancante

Senza un ORDER BY nella clausola OVER, la «riga precedente» non è definita. Alcuni motori rifiutano la query, mentre altri restituiscono risultati imprevedibili. Ordini sempre la finestra.

Ricordi inoltre che l'ordinamento all'interno di OVER è indipendente dall'ORDER BY esterno della query. La finestra stabilisce quale riga sia adiacente; la clausola esterna stabilisce soltanto l'ordine di visualizzazione.

Verifica rapida

Verifichi la Sua comprensione delle funzioni finestra con offset.

Riepilogo

Ora conosce le funzioni finestra con offset:

LAG(col) legge la riga precedente, mentre LEAD(col) legge quella successiva, secondo l'ORDER BY della finestra.
Argomenti opzionali: LAG(col, offset, default).
PARTITION BY reimposta la navigazione per ogni gruppo, quindi le righe ai confini restituiscono NULL.
Sostituiscono i self-join macchinosi per confrontare righe adiacenti.

Ora applichiamo tutto questo alla domanda immancabile per un analista: la variazione periodo su periodo.

Domande Frequenti

La lezione «LAG e LEAD per le righe adiacenti» è gratuita?

Sì — il testo completo di «LAG e LEAD per le righe adiacenti» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.

Cosa imparerò in «LAG e LEAD per le righe adiacenti»?

Accedere ai valori della riga precedente e successiva senza usare un self-join Eserciti SQL Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Interview Prep?

Non è richiesta alcuna esperienza precedente. SQL Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 1 di 4.

Quanto tempo richiede la lezione «LAG e LEAD per le righe adiacenti»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Interview Prep?

Sì. Ogni lezione SQL Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

LAG e LEAD per le righe adiacenti
Variazione tra periodi
NTILE per creare fasce
FIRST_VALUE, LAST_VALUE e limiti del frame

← Torna a SQL Interview Prep