Come calcolare la differenza tra due righe in SQL

Calcolare la differenza tra due righe in SQL può essere un compito impegnativo. Tuttavia è possibile e c'è più di un modo per farlo. In questo articolo spiegherò come utilizzare le funzioni SQL LEAD() e LAG() per trovare la differenza tra due righe della stessa tabella.

Mi piace pensare a questo articolo come a un "happy hour": avrai la possibilità di imparare due argomenti (il calcolo della differenza tra due righe e le Window Functions di SQL) leggendo una sola pagina. Entriamo subito nel vivo!

Se desideri imparare le cosiddette funzioni finestra o Window Functions, dai un'occhiata al nostro corso interattivo Window Functions. Se invece le conosci già e vuoi esercitarti a scrivere query con funzioni di questo tipo, ti consiglio di provare il nostro set di esercitazioni Window Functions con 100 esercizi pratici.

Trovare la differenza tra due valori nella stessa riga

Per calcolare una qualsiasi differenza, occorrono sempre due elementi; nel caso di SQL, hai bisogno di due record o set di dati. È possibile determinare la differenza tra due colonne dello stesso record, come mostrerò tra poco. È molto semplice. Tuttavia, mi concentrerò principalmente sulla ricerca della differenza tra due valori della stessa colonna in record diversi.

Per prima cosa, parliamo dei nostri dati. Utilizzeremo un database con due tabelle, utilizzate dal governo di una provincia immaginaria per definire alcuni programmi sociali. La prima tabella è housing che contiene dati sul numero di persone che affittano, possiedono o hanno bisogno di una casa. Dai un'occhiata:

Tabella housing

Per progettare un piano di sviluppo residenziale per le persone che ne hanno bisogno, il governo vuole ottenere alcune metriche sui problemi abitativi nelle diverse città. Supponiamo che il governo voglia sapere quante persone in ogni città non possiedono una casa; la query sarà:

SELECT 	
    city,
    total_population, 
    total_population - population_owning_house AS people_not_owning_house
FROM housing

In questa query, è chiaro che si sta calcolando una differenza utilizzando due colonne diverse nello stesso record. In seguito, calcoleremo le differenze utilizzando due record diversi.

Calcolo della differenza tra due valori nella stessa colonna

Di solito, tutte le operazioni eseguite in una query SQL riguardano il record corrente. In questo caso, però, abbiamo bisogno di un secondo record. Questa è la parte difficile. Utilizzeremo una tecnica che impiega le Window Functions LAG() e LEAD() per ottenere dati da un altro record. Se desideri approfondire il tema, ti suggerisco di leggere questo articolo sulle Window Functions che fornisce spiegazioni chiare sulle funzioni finestra con molti esempi.

Supponiamo che, per una città specifica ("Bronzepolis"), si voglia ottenere la variazione delle persone che hanno bisogno di una casa in relazione all'anno precedente. Vediamo la query:

SELECT 	
  city,
  year,
  population_needing_house,
  LAG(population_needing_house) AS previous_year,
  population_needing_house - LAG(population_needing_house)
    OVER (ORDER BY year ) AS difference_previous_year
FROM housing
WHERE city = ‘Bronzepolis’
ORDER BY year

Nel testo blu si può vedere il calcolo del delta SQL tra due righe. Per calcolare una differenza, è necessaria una coppia di record; questi due record sono "il record attuale" e "il record dell'anno precedente". Puoi ottenere quest'ultimo dato con la Window Function LAG(). Questa funzione consente di ottenere i dati dal record precedente (in base a un criterio di ordinamento, che in questo caso è "ORDER BY year").

Di seguito è riportato il risultato di questa query. Le frecce rosse mostrano che la funzione LAG() restituisce lo stesso valore di population_needing_house dell'anno precedente. La colonna a destra mostra il risultato della differenza tra l'anno corrente e quello precedente.

Per la prossima query, estenderemo l'analisi dei problemi abitativi a tutte le città. Per ogni città, vogliamo la differenza tra l'anno in corso e quello precedente per la colonna population_needing_house.

Per prima cosa, rimuoveremo la condizione city = ‘Bronzepolis’. Vogliamo calcolare i valori per ogni città, quindi è necessario un modo per separare i dati in gruppi. Per questo motivo dobbiamo aggiungere la clausola PARTITION BY city alla funzione LAG(). PARTITION BY city consente di elaborare tutti i record della stessa città nella stessa finestra.

Utilizzeremo di nuovo LAG() per calcolare la differenza tra il numero di persone che hanno bisogno di una casa nell'anno in corso e quelle dell'anno precedente. Vediamo la query:

SELECT 	
  city,
  year,
  population_needing_house,
  LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS previous_year,
  population_needing_house - LAG(population_needing_house)
   OVER (PARTITION BY city ORDER BY year ) AS difference_previous_year
FROM housing
ORDER BY city, year

Nel testo blu puoi vedere come viene calcolata la differenza. Ecco una versione in linguaggio semplice che descrive ciò che sta accadendo:

difference_previous_year = 
  population_needing_house nel record attuale 
  - population_needing_house nel record dell'anno precedente

Dove:

population_needing_house nell'attuale record è la colonna population_needing_house

e

population_needing_house nel record dell'anno precedente viene ottenuto con la funzione LAG(population_needing_house) OVER (PARTITION BY city ORDER BY year)

È stata utilizzata la clausola PARTITION per creare un insieme con tutti i record della stessa città. Quindi impiegheremo la clausola ORDER BY per ordinare tutti i record per anno. Infine, abbiamo usato la funzione LAG() per ottenere il valore di population_needing_house dal record precedente.

Un'altra funzione finestra di SQL, LEAD(), è simile a LAG(), ma restituisce il record successivo del set di dati (nel nostro esempio, il dato dell'anno successivo).

Analizzando le due query precedenti, possiamo notare che per calcolare la differenza utilizziamo lo stesso sistema: sottrarre il valore della colonna precedente da quello attuale usando LAG() (o da quello successivo usando LEAD()).

Le Window Functions di SQL sono molto potenti. Per esempi tratti da diversi settori aziendali, come finanza, vendite e trading, consulta questo articolo su quando utilizzare le funzioni finestra in SQL.

Calcolo della differenza tra valori di date in SQL

Negli esempi precedenti, abbiamo calcolato il delta tra due righe utilizzando i valori delle colonne numeriche. Ora ti mostrerò come calcolare la differenza tra due valori di tipo data.

Innanzitutto, consideriamo il tipo di dati del risultato. Quando si calcola la differenza tra due valori di data, il risultato non è una data. È un intervallo che rappresenta il numero di giorni tra le due date.

Supponiamo che esista una tabella chiamata hospital_statistics che memorizza le statistiche dei tre ospedali della provincia. Di seguito è riportato un esempio dei dati contenuti nella tabella:

Tabella hospital_statistics

Supponiamo che esista una malattia rara chiamata MDLR. Il governo vuole studiare la frequenza con cui un paziente affetto da MDLR viene ricoverato in uno degli ospedali e per questo ha chiesto un report con le colonne day, hospital name, number of cases e days_since_the_last_case. Utilizzeremo SQL per creare il report:

SELECT
  day,
  hospital_name,
  number_of_patients,
  day - LAG(day) OVER (ORDER BY day) 
     AS days_since_last_case
FROM hospital_statistics
WHERE illness_name = 'MDLR'
ORDER BY day

Puoi notare lo stesso schema utilizzato in precedenza, che adesso viene impiegato per calcolare i giorni trascorsi dall'ultimo caso. L'unica differenza sta nel calcolo di una differenza tra due date invece di valori numerici.

Come si vede nei risultati, la colonna days_since_last_case è un valore intero che rappresenta un numero di giorni.

SQL consente anche di calcolare le differenze tra i valori timestamp. Esistono inoltre altre interessanti operazioni aritmetiche sulle date che possono essere utilizzate per i tipi di dati relativi alle date. Se desideri approfondire l'argomento, ti suggerisco l'articolo Come analizzare i dati sul COVID-19 delle serie temporali con le Window Functions di SQL.

Trovare la differenza tra record non consecutivi

Finora sono state calcolate le differenze tra record contigui in base a criteri d'ordine specifici. In alcuni casi, è necessario calcolare la differenza tra record non contigui.

Per esempio, torniamo alla prima query. Supponiamo di voler aggiungere un'altra colonna che mostri il numero di casi negli ultimi due anni. Fortunatamente, LAG() e LEAD() hanno un parametro opzionale che specifica quanti record saltare prima/dopo il record corrente. Per impostazione predefinita, questo parametro è 1 (cioè "usa il record successivo/precedente"), ma è possibile impostarlo su un altro numero. Quindi, con questo nuovo parametro, la query sarà:

SELECT 	
  city,
  year,
  population_needing_house,
  LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS one_year_before,
  LAG(population_needing_house,2)
    OVER (PARTITION BY city ORDER BY year ) AS two_years_before,
  population_needing_house - LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS difference_previous_year,
  population_needing_house - LAG(population_needing_house,2)
    OVER (PARTITION BY city ORDER BY year ) AS difference_last_two_years
FROM housing
ORDER BY 1, 2 DESC

In blu, puoi vedere il calcolo della differenza tra due record non consecutivi. Nell'immagine successiva, puoi notare la nuova colonna Difference last two years solo per i valori del 2019; questo perché il 2019 è l'unico anno in cui sono presenti i dati di due anni precedenti. Le linee rosse collegano i risultati della funzione LAG() (ottenendo i dati del 2017 e mostrando gli stessi dati nel record del 2017). Puoi vedere che entrambe le linee hanno lo stesso valore.

Abbiamo mostrato l'importanza delle funzioni LAG() e LEAD(), ma esistono molte altre funzioni finestra in SQL. Leggi questo articolo sulle Window Functions per conoscerne altre.

Ulteriori informazioni su calcoli e Window Functions in SQL

In questo articolo hai imparato a calcolare la differenza tra righe consecutive applicando la sintassi SQL. Hai anche appreso come utilizzare LAG() e LEAD() per trovare la differenza tra righe non consecutive. Anche se esistono altri modi per calcolare questa differenza, come per esempio l'utilizzo di un'operazione di self-join, questi metodi sono molto utili.

Se utilizzi spesso le Window Functions (o SQL in generale) o se desideri semplicemente migliorare le tue conoscenze, questo Cheat Sheet (Foglio riassuntivo) sulle Window Functions è una risorsa eccellente. Per imparare le funzioni finestra attraverso la pratica, prova il nostro corso Window Functions. Per saperne di più sul corso, leggi questo articolo.