Padroneggiare le funzioni SQL è una skill molto importante per tutti gli sviluppatori SQL e i data analyst. Lo scopo di questo articolo è di riportare e classificare in un’unica pagina l’elenco delle funzioni SQL più utili e frequentemente utilizzate. All’interno dell’articolo faremo riferimento a tre tra i principali RDBMS (Relational Database Management System) presenti oggi sul mercato: Microsoft Sql Server, Oracle e MySql.
Funzioni SQL per gestire le stringhe
L’SQL mette a disposizione un lungo elenco di funzioni per manipolare e gestire le colonne che contengono del testo. Una delle esigenze più frequenti è quella di estrarre soltanto una porzione di una stringa. Per far questo possiamo utilizzare la funzione SUBSTRING su Sql Server e Mysql o l’equivalente SUBSTR su Oracle. Qui trovi un articolo di approfondimento.
Molto spesso dobbiamo concatenare più valori in una sola colonna di output. Occorre fare molta attenzione al comportamento delle relative funzioni in presenza di null. Ad esempio su tutti e tre i DBMS possiamo usare la funzione CONCAT, ma mentre su SQL Server e Oracle i null in input vengono semplicemente ignorati, su MySql la presenza di un null genererà sempre un null anche in output, a prescindere dagli altri argomenti.
Molto utile è anche la funzione REPLACE per sostituire pezzi di testo. Questa funzione può avere vari utilizzi, anche molto utili nei processi di data cleaning, gli abbiamo dedicato un articolo specifico.
Un’esigenza molto comune tra gli sviluppatori SQL è quella di gestire correttamente i null. Esiste una funzione comune ai tre DBMS: COALESCE, la cui sintassi è molto semplice. La funzione accetta almeno due argomenti, se il primo non è null allora il suo valore coinciderà con l’output della COALESCE, mentre se è null si passerà a valutare il secondo argomento e così via. Ad esempio COALESCE(email, fax) restituirà sempre la mail, ad eccezione di quando tale colonna contiene null, in quel caso infatti la funzione restituirà il valore presente nella colonna fax. Occorre infine tener presente che le particolarità di Oracle nello gestire il null si ripercuotono anche sull’output della funzione COALESCE. Ad esempio questa query
SELECT COALESCE ('', 'a') FROM DUAL;
restituirà su Oracle il carattere ‘a’, mentre su SQL Server e MySql darà in output la stringa vuota.
Gestire le date con l’SQL
Prima di parlare delle funzioni per gestire le date, dobbiamo ricordare che in ambiente Oracle le colonne di tipo date incorporano anche le informazioni relative a ore, minute e secondi, come approfondito in questo link. Per troncare la data alle ore 00:00 possiamo usare su Oracle la funzione TRUNC, mentre su MySQL e Sql Server possiamo convertire un tipo datetime in date tramite la funzione CAST(<data> AS DATE).
Diverso tra i tre DBMS è anche il modo di estrarre il giorno, il mese e l’anno a partire da una data. Su Sql Server e MySql possiamo usare rispettivamente le funzione DAY, MONTH e YEAR. Su Oracle invece abbiamo la funzione EXTRACT che può essere declinata in EXTRACT(YEAR FROM <Data>), EXTRACT(MONTH FROM <Data>), EXTRACT(DAY FROM <Data>).
Concludiamo questa sezione riportando le funzioni da utilizzare per calcolare la differenza in giorni tra due date. Su Sql Server possiamo utilizzare la funzione DATEDIFF in questo modo: DATEDIFF(Day, <Data1>, <Data2>). Se Data2 è maggiore di Data1 allora la funzione restituirà un numero positivo, altrimenti uno negativo. Occorre fare attenzione se ci spostiamo su MySql perché la sintassi è molto simile (l’unica differenza è che accetta solo due argomenti contenenti le date), ma il metodo di calcolo è diverso: l’output di DATEDIFF sarà positivo quando è la prima data a essere maggiore della seconda. Su Oracle la situazione è ancora diversa: dovremmo scrivere infatti TRUNC(<Date1> – <Date2>)
Funzioni SQL di aggregazione
Su tutti e tre i DBMS sono disponibili le funzioni di aggregazione Sum, Avg, Max, Min, Count. Attenzione invece al calcolo della deviazione standard. Per calcolare la deviazione standard di una colonna vista come l’intera popolazione statistica occorrerà utilizzare le funzioni:
- STDEVP su SQL Server
- STDDEV_POP su Oracle
- STDDEV su Mysql
Se utilizziamo rispettivamente su SQL Server e Oracle le funzioni STDEV e STDDEV quello che andremo a calcolare è la stima corretta della varianza campionaria (la cui formula contiene al denominatore il coefficiente N-1 invece di N). Occorre infine ricordare che su SQL Server le funzioni per il calcolo della media e della deviazione standard non restituiscono i risultati attesi se applicate direttamente a una colonna di tipo int, senza che venga applicata una conversione al tipo decimal. Puoi approfondire il calcolo degli indici statistici a questo link.
Continua a imparare
- le nuove funzioni di SQL Server 2017 TRIM, CONCAT_WS e STRING_AGG
- come convertire righe in colonne con l’SQL
- creiamo una tabella pivot con l’SQL