Come funziona LAG() in SQL Server

LAG() è una funzione indispensabile per l’analisi dei dati in SQL, specialmente quando si lavora con serie temporali o dati che hanno un ordine logico. Ricorda sempre che la clausola ORDER BY all’interno della OVER è fondamentale per definire correttamente la sequenza delle righe e quindi la riga “precedente” da cui recuperare il valore.

In coppia con la sua controparte LEAD() (che ti permette di accedere a una riga successiva), LAG() ti offre un controllo potente sull’analisi sequenziale dei tuoi dati.

Cos’è la Funzione LAG() in SQL?

La funzione LAG() ti permette di accedere a una riga precedente all’interno di un set di risultati, senza dover ricorrere a complessi self-join. È particolarmente utile quando devi confrontare il valore di una riga con il valore della riga che la precede logicamente, all’interno di un gruppo o di un ordine specifico.

Immagina di avere una serie di eventi temporali (come vendite giornaliere, letture di sensori, o stati di un ordine) e di voler calcolare la differenza tra il valore attuale e quello del giorno/momento precedente. LAG() è lo strumento perfetto per questo.

Sintassi di Base

La sintassi di LAG() è la seguente:

SQL

LAG (scalar_expression [, offset [, default]]) OVER ( [ PARTITION BY value_expression , ... n ] ORDER BY order_by_expression [ ASC | DESC ] , ... n )

Vediamo i suoi componenti:

  • scalar_expression: L’espressione o la colonna da cui vuoi recuperare il valore dalla riga precedente.
  • offset (opzionale): Un numero intero positivo che specifica quante righe “indietro” (precedenti) rispetto alla riga corrente vuoi cercare. Se omesso, il valore predefinito è 1 (cioè la riga immediatamente precedente).
  • default (opzionale): Il valore che LAG() dovrebbe restituire se la riga precedente (all’offset specificato) non esiste (ad esempio, per la prima riga del set o della partizione). Se omesso, il valore predefinito è NULL.
  • OVER Clausola (Fondamentale!): Questa è la parte che definisce la “finestra” su cui opera la funzione.
    • PARTITION BY (opzionale): Divide il set di risultati in partizioni (gruppi logici) a cui la funzione LAG() viene applicata indipendentemente. Per esempio, se vuoi confrontare le vendite del giorno precedente per ogni prodotto, partizionerai per ProductID.
    • ORDER BY: Obbligatorio. Definisce l’ordinamento logico delle righe all’interno di ogni partizione (o dell’intero set di risultati se non c’è PARTITION BY). Questo ordine determina quale riga è “precedente”.

Esempi Pratici di LAG()

Vediamo come LAG() può risolvere problemi comuni con esempi testabili.

Esempio 1: Confrontare il Valore Corrente con il Precedente

Scenario: Hai una serie di vendite giornaliere e vuoi calcolare la differenza tra le vendite di oggi e quelle di ieri.

SQL

-- Dati di esempio
CREATE TABLE VenditeGiornaliere (
    DataVendita DATE PRIMARY KEY,
    ImportoVenduto DECIMAL(10, 2)
);

INSERT INTO VenditeGiornaliere (DataVendita, ImportoVenduto) VALUES
('2024-01-01', 100.00),
('2024-01-02', 120.00),
('2024-01-03', 110.00),
('2024-01-04', 150.00),
('2024-01-05', 90.00);

-- Query con LAG()
SELECT
    DataVendita,
    ImportoVenduto,
    LAG(ImportoVenduto, 1, 0.00) OVER (ORDER BY DataVendita) AS ImportoVendutoGiornoPrecedente,
    ImportoVenduto - LAG(ImportoVenduto, 1, 0.00) OVER (ORDER BY DataVendita) AS DifferenzaRispettoAlGiornoPrecedente
FROM
    VenditeGiornaliere
ORDER BY
    DataVendita;

-- Risultato:
-- DataVendita | ImportoVenduto | ImportoVendutoGiornoPrecedente | DifferenzaRispettoAlGiornoPrecedente
-- ----------- | -------------- | ------------------------------ | ------------------------------------
-- 2024-01-01  | 100.00         | 0.00                           | 100.00
-- 2024-01-02  | 120.00         | 100.00                         | 20.00
-- 2024-01-03  | 110.00         | 120.00                         | -10.00
-- 2024-01-04  | 150.00         | 110.00                         | 40.00
-- 2024-01-05  | 90.00          | 150.00                         | -60.00

DROP TABLE VenditeGiornaliere;

Per la prima riga, dato che non esiste un “giorno precedente”, LAG() restituisce il valore di default 0.00 che abbiamo specificato.

Esempio 2: LAG() con PARTITION BY (Top N per Gruppo)

Scenario: Hai vendite per diversi prodotti e vuoi calcolare la differenza di prezzo tra un’occorrenza di un prodotto e la precedente per lo stesso prodotto.

SQL

-- Dati di esempio
CREATE TABLE StoricoPrezziProdotto (
    ProdottoID INT,
    DataModifica DATE,
    Prezzo DECIMAL(10, 2)
);

INSERT INTO StoricoPrezziProdotto (ProdottoID, DataModifica, Prezzo) VALUES
(1, '2024-01-01', 10.00),
(1, '2024-01-05', 12.00),
(1, '2024-01-10', 11.50),
(2, '2024-01-02', 25.00),
(2, '2024-01-07', 26.50),
(3, '2024-01-03', 5.00);

-- Query con LAG() e PARTITION BY
SELECT
    ProdottoID,
    DataModifica,
    Prezzo,
    LAG(Prezzo, 1, Prezzo) OVER (PARTITION BY ProdottoID ORDER BY DataModifica) AS PrezzoPrecedente,
    Prezzo - LAG(Prezzo, 1, Prezzo) OVER (PARTITION BY ProdottoID ORDER BY DataModifica) AS VariazionePrezzo
FROM
    StoricoPrezziProdotto
ORDER BY
    ProdottoID, DataModifica;

-- Risultato (parziale, concentrandosi sul ProdottoID 1):
-- ProdottoID | DataModifica | Prezzo | PrezzoPrecedente | VariazionePrezzo
-- ---------- | ------------ | ------ | ---------------- | ----------------
-- 1          | 2024-01-01   | 10.00  | 10.00            | 0.00
-- 1          | 2024-01-05   | 12.00  | 10.00            | 2.00
-- 1          | 2024-01-10   | 11.50  | 12.00            | -0.50
-- 2          | 2024-01-02   | 25.00  | 25.00            | 0.00
-- ... (e così via per gli altri prodotti)

DROP TABLE StoricoPrezziProdotto;

La clausola PARTITION BY ProdottoID assicura che LAG() “resetta” il suo conteggio per ogni nuovo ProdottoID, confrontando i prezzi solo all’interno dello stesso prodotto. Ho usato Prezzo come valore di default per la prima riga di ogni partizione, in modo che la VariazionePrezzo sia 0 per il primo record di ogni prodotto.

Esempio 3: Usare un Offset Maggiore di 1

Scenario: Vuoi confrontare il valore corrente con quello di due periodi fa.

SQL

SELECT
    DataVendita,
    ImportoVenduto,
    LAG(ImportoVenduto, 2, 0.00) OVER (ORDER BY DataVendita) AS ImportoVendutoDueGiorniFa
FROM
    VenditeGiornaliere
ORDER BY
    DataVendita;

Se il tuo set di dati è più piccolo dell’offset, le righe restituiranno il valore di default specificato (o NULL se non specificato).

Vantaggi di LAG()

  • Leggibilità: Rende il codice molto più leggibile rispetto a complessi self-join o subquery correlate per ottenere lo stesso risultato.
  • Performance: Spesso offre prestazioni migliori rispetto a soluzioni alternative basate su join, poiché l’ottimizzatore di query può gestire l’operazione in modo più efficiente.
  • Semplificazione: Semplifica la logica per problemi di confronto temporale o sequenziale.

(fonte)

Tag: