Funzione SQL LAG()

La funzione LAG() - una delle funzioni finestra di SQL, è uno strumento importante per la pianificazione e l'analisi delle tendenze. In questo articolo vi mostrerò come includere la funzione SQL LAG() nelle vostre query utilizzando alcuni esempi reali.

Lefunzioni finestra diSQL , note anche come funzioni analitiche o funzioni OVER, aggiungono una nuova dimensione all'analisi dei dati. Permettono di includere aggregati o dati di altre righe accanto alla riga corrente.

La funzione LAG() consente di guardare indietro "attraverso la finestra" a una riga precedente e di includere i suoi dati accanto alla riga corrente. Una funzione simile, LEAD(), consente di guardare in avanti alle righe successive. Utilizzando le funzioni LEAD() e LAG(), è possibile includere facilmente nei report caratteristiche utili come i confronti anno per anno.

Se volete fare sul serio con l'analisi dei dati, potreste dare un'occhiata al corso LearnSQL.it'Window Functions (Funzioni Finestra) . Imparerete risolvendo oltre 200 esercizi interattivi guidati utilizzando un vero database a cui accederete tramite il vostro browser. Il corso richiede circa 20 ore; una volta terminato, potrete utilizzare le vostre nuove competenze per potenziare i vostri progetti di analisi dei dati.

Cosa fa la funzione LAG()?

Questa funzione consente di includere il valore di una colonna di una riga precedente accanto ai dati della riga corrente. È particolarmente utile per analizzare le tendenze nel tempo, come i confronti tra mesi e anni.

Può essere utilizzato anche per rispondere a diverse domande. Quanto è grande il divario tra i risultati medi della scuola A e quelli della scuola B? Quanto incide l'utilizzo di una materia prima diversa sulla durata di un componente?

Sintassi della funzione LAG()

Nella sua forma più semplice, la sintassi della funzione SQL LAG() è ...

LAG(column_1) OVER (ORDER BY column_2)

... dove:

• column_1 è il nome della colonna che si vuole includere dalla riga precedente.
• OVER indica che si sta utilizzando una funzione finestra; questa parola chiave è obbligatoria.
• ORDER BY è obbligatoria anche quando si usa LAG(). La sequenza delle righe deve essere prevedibile, altrimenti la funzione non ha senso. Tuttavia, non è necessario che l'ordine scelto sia lo stesso del report finale.
• column_2 è la colonna che si usa per ordinare le righe. È possibile specificare più di una colonna.

SQL mette in sequenza i dati utilizzando i valori della colonna_2. Per ogni riga, guarda indietro alla riga precedente di questa sequenza e recupera il valore della colonna_1. Questo valore viene incluso insieme a qualsiasi altro dato richiesto dalla riga corrente.

Esempio di base della funzione LAG()

Vediamo un semplice confronto mese per mese. Una tabella denominata monthly_sales contiene i seguenti dati:

Se si volesse vedere le vendite del mese scorso insieme a quelle di questo mese, la query avrebbe il seguente aspetto:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month
FROM monthly_sales;

Il risultato è questo:

Si noti che il valore delle vendite del mese precedente è stato riportato nella riga del mese corrente:

Esempi più complessi

Dopo aver appreso come eseguire una semplice query utilizzando la funzione SQL LAG(), vediamo altri modi in cui è possibile utilizzarla nelle analisi.

Uso di LAG() nei calcoli

Spesso si desidera utilizzare i risultati di LAG() nei calcoli. Ad esempio, è possibile visualizzare la differenza e la variazione percentuale tra i mesi. Modifichiamo la query precedente per includere questi calcoli.

È possibile utilizzare i risultati di una funzione nei calcoli come per qualsiasi altra colonna.

La query si presenta così:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month,
  sales_value - LAG(sales_value) OVER (ORDER BY year, month) AS change,
  ((sales_value - LAG(sales_value) OVER (ORDER BY year, month)) * 100)
   / (LAG(sales_value) OVER (ORDER BY year, month)) AS percentage
FROM monthly_sales;

I risultati sono:

Uso di LAG() con un offset

Negli esempi che abbiamo visto, la query ha preso i dati dalla riga precedente dell'insieme. Questo è il comportamento predefinito. Tuttavia, è possibile andare indietro di più righe specificando un offset.

La sintassi per specificare un offset è la seguente:

LAG(column_1, offset) OVER (ORDER BY column_2)

offset è un numero intero che indica quante righe la query deve cercare all'indietro per trovare i dati. Se non si specifica un offset, il database assume un offset pari a 1.

Supponiamo di voler vedere le vendite di questo mese, quelle del mese scorso e quelle del mese precedente affiancate. La query è:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month,
  LAG(sales_value,2) OVER (ORDER BY year, month) AS prev_month
FROM monthly_sales;

I risultati sono:

Incluso un valore predefinito

Nei risultati che abbiamo visto, la funzione LAG() ha restituito NULL nella prima riga perché non c'era una riga precedente. Non sempre si vuole che lo faccia.

Ad esempio, si supponga che un'azienda abbia aperto nel dicembre 2023. Si potrebbe voler mostrare questo fatto inserendo uno zero nelle vendite dell'ultimo mese di dicembre, in modo che sia evidente che le vendite sono aumentate da zero a 7380 durante quel mese.

Lo zero è chiamato " default", ovvero un valore mostrato quando non esistono cifre.

Per includere un valore predefinito nella query, la sintassi è la seguente:

LAG(column_1, offset, default) OVER (ORDER BY column_2)

Si noti che è sempre necessario specificare un offset se si include un valore predefinito. L'offset sarà 1 se si vuole esaminare la riga immediatamente precedente a quella corrente.

La query per l'esempio precedente è:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value, 1, 0) OVER (ORDER BY year, month) AS last_month
FROM monthly_sales;

I risultati sono:

Utilizzo di LAG() con PARTITION BY

Supponiamo che i dati originali siano stati ampliati per includere più di un reparto:

Probabilmente si vorrebbe che ogni reparto fosse tenuto separato nel confronto. Questa operazione è nota come partizionamento. La sintassi per dividere i risultati in partizioni è ...

LAG(column_1) OVER (PARTITION BY column_2 ORDER BY column_3)

... dove column_2 è la colonna che si vuole utilizzare per il partizionamento.

Proviamo a eseguire una query che divida il report per reparto, ma che ordini le righe per anno e mese all'interno di ciascun reparto:

SELECT 
  department, 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month) AS last_month
FROM monthly_sales;

I risultati sono questi:

Si noti che le vendite del mese scorso tornano a essere NULL sulla prima riga del nuovo reparto.

SQL utilizza department come chiave primaria di ordinamento perché è stata specificata nella clausola PARTITION BY. All'interno di department, mette in sequenza i dati per year e poi per month, come da clausola ORDER BY.

Per ogni riga, cerca se esiste una riga precedente appartenente allo stesso department. In caso affermativo, recupera il contenuto della colonna sales_value dalla riga precedente. Questo viene incluso nella riga corrente come last_month. Se non c'era nessuna riga precedente, last_month viene impostato su un valore NULL.

Utilizzo dei risultati di LAG() per ordinare un report

L'ordine finale del report non deve necessariamente corrispondere alla sequenza utilizzata nella clausola OVER. È possibile utilizzare la normale clausola ORDER BY alla fine della query per specificare una sequenza diversa.

Si supponga di voler visualizzare i risultati in base all'aumento del valore delle vendite tra i mesi.

La query potrebbe avere questo aspetto:

SELECT 
  department, 
  year, 
  month, 
  sales_value, 
  sales_value - LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month) AS change
FROM monthly_sales
ORDER BY
  department, 
  sales_value - LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month);

I risultati appaiono così:

Questo può essere utile se si vuole vedere quando l'azienda andava male e quando andava bene. Queste informazioni possono aiutare a risalire all'origine di eventuali problemi.

Dove saperne di più sulla funzione SQL LAG()

Per saperne di più sulla funzione LAG() e sulle funzioni finestra in generale, ecco alcuni articoli che offrono ulteriori informazioni:

• Le funzioni LAG() e LEAD() in SQL
• Funzione SQL LEAD()
• Come calcolare le differenze tra anni in SQL

Se avete bisogno di ripassare le funzioni della finestra, queste risorse possono esservi utili:

• Questo Cheat Sheet diWindow Functions (Funzioni Finestra) è ottimo da tenere con sé mentre si scrivono le query.
• Quando si è alle prime armi, è molto utile guardare questi esempi di funzioni finestra.
• Se siete alla ricerca di un lavoro, ecco alcune domande diWindow Functions (Funzioni Finestra) . Studiatele e vi aiuteranno a superare il colloquio SQL.

Come ho già detto, se volete davvero diventare esperti, vi consiglio il corso LearnSQL.it'Window Functions (Funzioni Finestra) . Imparerete esattamente come utilizzare tutta la potenza delle funzioni finestra di SQL. Inoltre, potrete acquisire sicurezza risolvendo molti esercizi pratici.

Non c'è niente di meglio della pratica per aumentare le proprie conoscenze e competenze. Se desiderate alcuni esempi guidati su cui lavorare da soli, ecco alcune risorse:

• L'articolo Window Functions (Funzioni Finestra) Practice Exercises contiene 11 esercizi con soluzioni.
• Il set di esercitazioni diWindow Functions (Funzioni Finestra) è un percorso di apprendimento contenente oltre 100 esercizi pratici. Lavorerete su tre diversi database per imparare a risolvere diversi tipi di problemi.

La funzione LAG() di SQL è uno strumento straordinario per analizzare i dati e identificare rapidamente le tendenze e mantenere la competitività dell'azienda. Come analista di dati, vale la pena di dedicare del tempo per diventare un esperto della funzione finestra!