Conoscevi queste funzioni SQL?

Ecco quattro funzioni SQL che è necessario saper padroneggiare per creare delle query super potenti e performanti.

La funzione COALESCE restituisce la prima espressione non nulla in un elenco. Questo comando cambia particolarmente la vita quando si lavora con i nostri dati da visualizzare in una dashboard. Se tutti i valori presenti nell’elenco sono NULL, la funzione COALESCE() restituirà NULL.

SELECT COALESCE(NULL, 'Pippo', 'Pluto');

restituirà: “Pippo” in quanto questo è il primo valore non NULL della lista.

Immaginiamo di gestire in una tabella la nostra contabilità casalinga e in una colonna di questa tabella indicare quanti soldi ho speso ogni giorno per ogni mese.

data	importo	descrizione
01/01/2023	10	carburante
01/01/2023	15	cibo
01/01/2023	5	sigarette
02/01/2023	7	colazione
02/01/2023	3	caffè
03/01/2023	11	pranzo
03/01/2023	20	cena
07/01/2023	12	carburante
07/01/2023	3	caffè
09/01/2023	25	carburante

Quanti soldi avrò speso in un mese? Risulta abbastanza facile con una query del tipo

SELECT DISTINCT data, SUM(importo) AS somma FROM contabilita GROUP BY data

L’output risultante sarà la tabella:

Data	somma
01/01/2023	30
02/01/2023	10
03/01/2023	31
07/01/2023	15
09/01/2023	25

Il chè è corretto, ma il problema si ha per quei giorni senza alcuna transazione, poichè non ci verrà mostrato alcun record. Possiamo facilmente osservarlo nella tabella precedente in quanto non c’è traccia dei giorni 4,5,6,8, poichè non ho avuto  transazioni.

COALESCE viene in nostro aiuto per evitare ciò. Possiamo facilmente sostituire qualsiasi cella con valore nullo della colonna delle spese con uno zero usando questo comando. Questa query utilizza il supporto di un’altra tabella chiamata calendario dove sono presenti tutti i giorni dell’anno. In questo caso a noi serve solo il mese di gennaio 2023, motivo per cui viene inserita una condizione WHERE filtrando solo i giorni che ci interessano.

WITH 

gennaio AS
(SELECT 
    data
FROM calendario
WHERE anno = "2023" AND mese=1
),

SUM_EXPENSES AS 
(        
SELECT DISTINCT 
       data, 
       SUM(importo) AS expenses
FROM contabilita
GROUP BY 1
)

SELECT 
    gennaio.data,
    COALESCE(SUM_EXPENSES.expenses,0) as expenses
FROM gennaio
LEFT JOIN SUM_EXPENSES
  ON gennaio.data = SUM_EXPENSES.data

A questo punto, la tabella risultante conterrà un valore non nullo, per ciascun giorno del mese, ed ovviamente il campo somma conterrà il valore raggruppato nel caso in cui per uno specifico giorno ci siano più importi spesi.

Le Window Function

Piccola introduzione: le Window Function,  funzioni finestra, funzioni OVER o funzioni analitiche introdotte su MySQL a partire dalla versione 8, permettono di eseguire funzioni di aggregazione su gruppi di righe. Sono estremamente ugili poichè rendono molto più semplice la creazione di aggregazioni complesse. Tali funzioni sono di seguito riepilogate:

• LAG: recupera il valore di un campo nella riga precedente;
• LEAD: recupera il valore corrispondente nella riga successiva;
• FIRST_VALUE: indica il primo valore della finestra;
• LAST_VALUE: indica l’ultimo valore della finestra;
• NTH_VALUE: recupera l’ennesimo valore della finestra;
• ROW_NUMBER: recupera il numero di riga corrente nella partizione;
• RANK: fornisce l’ordine della riga nella partizione corrente;
• PERCENT_RANK: è lo stesso concetto di RANK del punto precedente, ma espresso in percentuale;
• DENSE_RANK: identica alla funzione RANK ma considera nella stessa posizione gli elementi duplicati;
• CUME_DIST: è finalizzato al calcolo della distribuzione cumulativa;
• NTILE: suddivide i valori di un campo in un certo numero di gruppi (indicato come argomento della funzione) ed assegna ad ognuno di questi gruppi un valore progressivo. Tali segmenti prendono il nome di bucket. La funzione NTILE fornisce il numero di bucket della riga corrente.

Ciascuna di queste funzioni, segue una sintassi ben precisa:

[nome_funzione] 
OVER( 
   PARTITION BY [parametri di partizionamento] 
   ORDER BY [parametri di ordinamento] 
   [limiti della finestra] 
)

dove:

• [nome_funzione] indica la funzione da applicare sulla finestra di righe. Può trattarsi di una delle Window Function o di una classica funzione di aggregazione
• OVER è la parola chiave centrale delle Window Function che separa la funzione da applicare dalla definizione della finestra
• PARTITION BY [parametri di partizionamento] indica il criterio in base al quale i record vanno partizionati. Non è obbligatorio, se omesso tutti record coinvolti nella query saranno ritenuti membri di un’unica partizione
• ORDER BY [parametri di ordinamento] specifica in che ordine saranno messe le righe al fine di una corretta distribuzione tra le finestre;
• [limiti della finestra] specifica dove inizia e finisce ogni finestra.

LAG

Come già anticipato nel paragrafo precedente, la funzione LAG è una funzione finestra. Nell’analisi dei dati, una delle attività più comuni è probabilmente il calcolo del valore delta, dove per delta si intende la stima teorica di quanto il valore di un’opzione può cambiare. Che si tratti di entrate, costi, variazioni di prezzo, variazioni di volume o qualsiasi altra cosa immaginabile, calcolarne il delta significa calcolare la differenza di tempo tra i numeri. Per fare ciò, possiamo usare appunto la funzione LAG, la quale non fa altro che recuperare i dati da una riga precedente. Il nostro obiettivo principale sarà calcolare l’importo giornaliero della differenza di denaro speso. Per fare ciò, dobbiamo calcolare il valore precedente di ciascun indicatore. Quindi, la funzione LAG deve essere calcolata in sequenza, non per giorni casuali, ed è per questo che c’è ORDER BY data.

Facciamo riferimento alla tabella iniziale per sviluppare di seguito la nostra query:

SELECT 
   data, 
   SUM(importo), 
   LAG(SUM(importo)) OVER(ORDER BY data) AS lag_result 
FROM contabilita 
GROUP BY data 
ORDER BY data

I valori contenuti nella tabella iniziale, vengono raggruppati per data, in modo tale da avere un unico importo totale per ogni giorno di riferimento. Questa query genera un output contenente in ogni riga l’importo di denaro speso durante quel giorno specifico e l’importo di denaro speso durante il giorno precedente (colonna lag_result):

data	SUM(importo)	lag_result
2023-01-01	30
2023-01-02	10	30
2023-01-03	31	10
2023-01-07	15	31
2023-01-09	25	15

Per calcolare la variazione delta, dobbiamo solo sottrarre il giorno precedente dal giorno corrente, con una piccola modifica al codice precedente:

SELECT 
  data, 
  SUM(importo), 
  SUM(importo) - LAG(SUM(importo)) OVER(ORDER BY data) AS lag_result
FROM contabilita
GROUP BY data
ORDER BY data

E l’output corrispondente apparirà come segue:

data	SUM(importo)	lag_result
2023-01-01	30
2023-01-02	10	-20
2023-01-03	31	21
2023-01-07	15	-16
2023-01-09	25	10

Da notare che il primo giorno contiene un valore nullo nell’ultima colonna. Questo perchè la funzione LAG ritorna il valore precendete, ma nel caso della prima riga, il valore precedente semplicemente non esiste! Possiamo sistemare il “problema” utilizzando la funzione COALESCE, in modo tale da ritornare il valore zero nel caso di null.

SELECT 
  data, 
  SUM(importo), 
  SUM(importo) - COALESCE(LAG(SUM(importo)) OVER(ORDER BY DATA), 0) AS lag_result
FROM contabilita
GROUP BY data
ORDER BY data

Come conseguenza, i risultati saranno i seguenti:

data	SUM(importo)	lag_result
2023-01-01	30	30
2023-01-02	10	-20
2023-01-03	31	21
2023-01-07	15	-16
2023-01-09	25	10

RANK, DENSE_RANK e ROW_NUMBER

Ordinare elementi è il secondo grande problema più frequente di qualsiasi analista di dati. In SQL, ci sono diversi modi con cui puoi assegnare un rango a una riga, che approfondiremo con un esempio. Consideriamo ancora una volta la stessa tabella iniziale, ma vogliamo sapere qual è il tipo di spesa più elevato. Per fare ciò, sommiamo tutte le spese raggruppandole per tipo di spesa.

Vogliamo sapere dove spendiamoo di più. Ci sono diversi modi per farlo. Possiamo usare ROW_NUMBER(), RANK() e DENSE_RANK(). Ordineremo la tabella precedente utilizzando tutte e tre le funzioni e vedremo quali sono le principali differenze tra loro utilizzando la seguente query:

WITH temp_tab AS
(
SELECT 
    SUM(importo) AS importo_totale,
    descrizione
FROM contabilita
GROUP BY descrizione
)

SELECT *, 
    ROW_NUMBER() OVER (ORDER BY importo_totale DESC) AS ROW_NUMBER, 
    RANK() OVER (ORDER BY importo_totale DESC) AS RANK, 
    DENSE_RANK() OVER (ORDER BY importo_totale DESC) AS DENSE_RANK 
FROM temp_tab

Otteniamo il seguente output:

importo_totale	descrizione	ROW_NUMBER	RANK	DENSE_RANK
47	carburante	1	1	1
21	pranzo	2	2	2
21	cena	3	2	2
7	colazione	4	4	3
6	caffè	5	5	4
5	sigarette	6	6	5

Quali sono le differenze delle tre funzioni?

• ROW_NUMBER() restituisce un numero univoco per ogni riga a partire da 1. In caso di parità, assegna arbitrariamente un numero se non è definito un secondo criterio.
• RANK() restituisce un numero univoco per ogni riga a partire da 1, tranne quando ci sono pareggi, che assegnerà lo stesso numero. Inoltre, seguirà un divario dopo un rango duplicato (si veda la riga di pranzo e cena, dove il rank 2 viene assegnato il rank 4).
• DENSE_RANK() non lascia “spazi vuoti” dopo un rango duplicato (si veda la riga di pranzo e cena, dove il rank 2 viene assegnato il rank 3).

SUM..OVER

Il totale parziale è probabilmente una delle window function più utili soprattutto quando si desidera visualizzare la crescita. Utilizzando una funzione finestra con SUM(), possiamo calcolare un’aggregazione cumulativa. Per fare ciò, dobbiamo solo sommare una variabile usando l’aggregatore SUM() ma ordinare questa funzione usando una seconda colonna. Useremo quindi la colonna data che fungerà appunto da riferimento temporale. È possibile osservare la query corrispondente come segue:

SELECT
   DATA, 
   SUM(SUM(importo)) OVER(ORDER BY data) AS importo_parziale
FROM contabilita 
GROUP BY data

Osserviamo l’uso di un doppio SUM(SUM()). Per quale motivo? Se avessimo utilizzato solo un SUM, l’operazione di somma avrebbe coinvolto solo la prima riga di ogni giorno, nel caso in cui per ciascuna data avessimo inserito più importi di tipo diverso (colazione, caffè, pranzo, etc…). In tal caso gli importi successivi al primo sarebbero stati ignorati. Con l’uso del SUM annidato, anche se può apparire poco elegante, viene calcolato l’importo totale esatto per ogni giorno (visto che raggruppiamo per la colonna data) e poi viene calcolato l’importo parziale.

In alternativa avremmo potuto usare, a mio parere in maniera più elegante, la combinazione WITH … AS in questo modo:

WITH temp_tab AS ( 
    SELECT SUM(importo) AS importo_totale, 
    data 
    FROM contabilita 
    GROUP BY data 
)


SELECT
    data, 
    SUM(importo_totale) OVER(ORDER BY data) AS importo_parziale
FROM temp_tab