SQL avanzato per Data Scientists

Tips and tricks per andare oltre le classiche istruzioni da "introduzione a SQL"

Intro

Povero SQL.

Lo Structured Query Language appartiene a quella ristrettissima elite di linguaggi che hanno saputo resistere alla clamorosa evoluzione tecnologica degli ultimi 50 anni, senza battere ciglio e senza essere messo da parte.

Ed è diventato l’equivalente, per il mondo dei dati, di quello che è la lingua inglese nella vita di tutti i giorni: un must che chiunque, bene o male, deve saper parlare.

Purtroppo, proprio come con l’inglese, moltissimi si limitano ad un vocabolario molto ristretto: diciamo che “select * from x where y” è il nuovo “the pen is on the table”.

SQL ha tanto in comune con l’inglese:

• È tutto sommato semplice - almeno nelle sue funzionalità base

• Permette di andare molto lontano

• Ha una ricchezza lessicale in realtà molto superiore di quanto sembra

Quest’ultimo è il punto fondamentale: è vero che basta poco per un uso basilare di SQL, ma è un peccato ignorarne le sue funzionalità più avanzate!

Ne passerò in rassegna alcune di particolare interesse per chi si occupa di data science. Ma prima faccio un rapido passo indietro nel tempo.

Giusto un riferimento temporale

Qualcuno ricorderà i floppy disk, questi insoliti oggetti che i millennials identificano come la stampa 3D del simbolo “salva” presente in molte applicazioni.

SQL fu introdotto nel 1974, vale a dire:

• 13 anni prima dell’introduzione dei floppy rigidi da 3,5” ad alta densità (gli unici che ho usato direttamente)

• 2 anni prima del primo floppy flessibile da 5,25” (che ho visto, ma mai usato)

• Proprio nel periodo in cui erano stati introdotti i floppy da 8” (il disco nero nella foto, che non ho mai visto dal vivo)

1 GB di disco fisso e 1 MB di disco portatile (come i floppy) erano ancora dei sogni nel 1974, anno in cui tra l’altro Intel introduceva il rivoluzionario 8080, una cpu a 2MHz di grande successo.

Tanto è successo da quegli anni, anche per SQL.

Ad un nocciolo duro, che viene insegnato in tutti i corsi di base, si sono aggiunte tante funzioni avanzate: alcune sono diventate standard, altre sono andate a costituire una serie di dialetti (legati a specifici database) che sono comunque di facile comprensione.

Cosa non vedremo

SQL permette di manipolare e interrogare dati strutturati, ossia tabelle.

Non parlerò di tanti argomenti, già coperti da migliaia di corsi di base (come questo). In particolare:

• Ignorerò tutto ciò che non riguarda un uso analitico di SQL (es. creazione tabelle, viste, indici, trigger; gestione permessi; inserimenti, cancellazioni, update)

• Nel mondo analitico, ignorerò le basi (es. select from where, group by, join), ossia tutto quello che è riassunto qui

Setup e dati

Ho parlato prima di dialetti SQL. Io userò, per familiarità, quello di PrestoDB (lo stesso di AWS Athena): nulla di particolarmente esotico, potrebbe cambiare qualcosa a livello di sintassi rispetto al vostro database preferito, ma i concetti saranno gli stessi.

Userò una sandbox liberamente disponibile via docker hub che contiene già alcune tabelle e illustrerò i concetti principali direttamente tramite esempi. Come interfaccia, mi trovo bene con DBeaver: gli screenshot sono presi da lì.

Mi baserò su una sola tabella: un semplice elenco di ordini su un sito internet, con identificativo di un cliente, data, importo, priorità e stato dell’ordine.

select custkey, orderdate, totalprice, orderpriority, orderstatus
from tpch.sf1.orders
order by 1
limit 10

Ecco di cosa parliamo:

Cube (& friends)

Partiamo da una sorta di group by on steroids: cube (che ha anche due parenti stretti, grouping sets e rollup). Di fatto permette di unire tutte le combinazioni di raggruppamento su più variabili.

Ammettiamo, sul nostro dataset, di volere contare:

• Tutti gli ordini

• Tutti gli ordini per priorità dell’ordine

• Tutti gli ordini per stato

• Tutti gli ordini per priorità dell’ordine e per stato

Anziché una union di tante group by differenti (con la seccatura di dover gestire il numero di colonne), basta usare la parola magica cube, così:

select orderstatus, orderpriority, count(*)
from tpch.sf1.orders
group by cube(orderstatus, orderpriority)
order by orderstatus, orderpriority

Questo è il risultato, dove null indica che non è stato fatto il raggruppamento:

Va gestito eventualmente a monte il fatto che una colonna possa assumere a priori il valore null, ma capite che è una funzione potente e compatta.

Array (e trasformazioni)

Anche gli array sono utili.

Ammettiamo di volere vedere gli anni in cui un cliente ha fatto acquisti (ordinati e senza duplicati), e poi limitarci solo agli anni pari:

select custkey, 
	array_sort(array_distinct(array_agg(extract(year from orderdate)))) array_anni,
	filter(array_sort(array_distinct(array_agg(extract(year from orderdate)))), x -> x % 2 = 0) array_anni_pari
from tpch.sf1.orders
group by 1
order by 1
limit 10

Questo è il risultato:

Funzioni matematiche, approssimazioni, tips & tricks

Esistono un’infinità di funzioni matematiche o “di convenienza”. Evito di addentrarmi in funzioni statistiche avanzate (che pure ci sono) e mi limito giusto ad un paio che uso spesso.

max_by

Quant’è la spesa massima di un cliente (easy)… e in quale giorno l’ha fatta?

select custkey, max(totalprice) spesa_max, max_by(orderdate,totalprice) data_spesa_max
from tpch.sf1.orders
where custkey < 100
group by 1
order by 1
limit 10

Possiamo dire addio alle query annidate per estrarre questa informazione :)

approx_percentile

Per ogni cliente, quanto vale il 10mo, 50esimo e 90esimo percentile di spesa (arrotondato)?

select custkey, transform(approx_percentile(totalprice,array [.1,.5,.9]), x -> round(x)) percentili
from tpch.sf1.orders
where custkey < 100
group by 1
order by 1
limit 10

Con gli array è molto compatto.

Window functions

Ma il vero superpotere sono le window functions (o funzioni finestra), che permettono di fare operazioni complesse sulle righe del risultato di una query. Si può fare di tutto!

Per ogni cliente e per ogni anno, potrei voler calcolare:

• Numero e importo totale degli ordini (easy)

• Quanto ha speso l’anno prima e l’anno dopo (e già qui non è scontato)

• Qual è la somma cumulata, anno dopo anno, del numero e degli importi - quindi il secondo anno sarà la somma di primo e secondo, il terzo anno sarà la somma di primo, secondo e terzo, etc.

Lo possiamo fare così (per comodità e leggibilità del risultato, mi limito ad un paio di clienti molto “attivi”):

select
	custkey,
	extract(year from orderdate) anno,
	count(*) conteggio,
	sum(totalprice) totalprice,
	lag(count(*)) over(partition by custkey order by extract(year from orderdate)) anno_prec,
	lead(count(*)) over(partition by custkey order by extract(year from orderdate)) anno_succ,
	sum(count(*)) over(partition by custkey order by extract(year from orderdate) rows unbounded preceding) conteggio_cumul,
	sum(sum(totalprice)) over(partition by custkey order by extract(year from orderdate) rows unbounded preceding) totalprice_cumul
from tpch.sf1.orders
where custkey in (102004,102022)
group by 1,2
order by 1,2

E il risultato è questo:

Le prime volte che ho visto queste funzioni, ho pensato “wow” e non le ho comprese al 100%… quel sum(count(*)) era un po’ sconvolgente.

La spiegazione è articolata: all’interno c’è la funzione di aggregazione (count(*)), all’esterno la funzione finestra (sum(…)), che opera sul risultato della query esterna che può essere ripartizionato (over(partition by …)). Specificando rows si può operare su un numero fisso di righe diverse, oppure dall’inizio della finestra (unbounded preceding).

Abbastanza compatto per operazioni piuttosto complesse!

Conclusioni

Potrei andare avanti a oltranza: SQL è un mondo… in gran parte sommerso! E penso che valga la classica logica paretiana: una grande maggioranza dei suoi utilizzatori si ferma ad una piccola porzione delle sue funzionalità.

In parte è anche giusto così… in parte!

La realtà è che ci sono motivi profondi se una tecnologia:

1. Riesce a imporsi

2. Riesce ad evolvere e a diventare un punto di riferimento

E come nello sport, è più facile vincere una volta che confermarsi nel tempo, come dimostrano le tante tecnologie rimaste sulla cresta dell’onda per un paio d’anni e poi dimenticate (o peggio, diventate un debito tecnologico).

SQL invece è ancora in forma, grazie in primis alla sua semplicità… che è però un’arma a doppio taglio: spero che la disponibilità di piattaforme moderne e open-source (come quella che ho usato), unita a corsi o articoli avanzati, possa dare qualche spunto per sfruttarlo ben oltre le sue funzionalità più basilari!