Lezioni e consulenze di Excel
Corso in aula a Torino
Lezioni e consulenze di SQL e Database
Corso in aula a Torino
SQL Server 2017: le nuovi funzioni
In questo articolo parleremo di tre nuove e utilissime funzioni disponibili dalla versione 2017 di Microsoft Sql Server rendendo ancora più semplice e immediata l’esperienza di sviluppo dei programmatori T-SQL.
Funzione TRIM
Fino alla versione 2016 di Sql Server, per eliminare gli spazi bianchi a sinistra e a destra di una stringa, occorreva combinare le due funzione LTRIM e RTRIM come nell’esempio in basso:
SELECT LTRIM(RTRIM(‘ string ‘ ))
Questa sintassi risulta spesso “pesante”, soprattutto in quei contesti dove tale funzione è piuttosto frequente, pensiamo ad esempio alle fasi di data quality e integration.
Finalmente, possiamo raggiungere il medesimo risultato con la sola funzione TRIM, analogamente a quanto già possibile su altri DBMS come ad esempio su Oracle.
SELECT TRIM(‘ string ‘ )
Oltre agli spazi bianchi, la funzione TRIM può essere utilizzata per eliminare anche dei caratteri particolari. Questo permette di risolvere agevolmente problematiche molto comuni come “eliminare gli zeri a sinistra della stringa” che, ad esempio, emergono spesso da colonne che contengono indistintamente codici fiscali e partite iva. Vediamo uno script d’esempio
CREATE TABLE #test (codice varchar(10))
INSERT INTO #test VALUES (’01’),(’02’),(‘2’),(‘0’),(‘001’)
SELECT Codice,
trim(‘0’ from codice) as AfterTrim
FROM #test Codice
AfterTrim 01
1 02
2 2
2 0 001
1
Funzione CONCAT_WS
Molto spesso si ha l’esigenza di concatenare più colonne separandole con una virgola oppure un altro separatore.
Creiamo un semplice caso d’esempio
CREATE TABLE #test2 (nome varchar(50),
cognome varchar(50),
citta varchar(50),
indirizzo varchar(50))
INSERT INTO #test2 values (‘nicola’,’iantomasi’,’torino’,null), (‘giovanni’,’rossi’,’milano’,’via montenapoleone’)
Per concatenare le quattro colonne dell’esempio, occorre ripetere il carattere separatore tre volte.
SELECT CONCAT(nome,’,’,cognome,’,’,citta,’,’,indirizzo) as AfterConcat
FROM #test2
Se dovessimo concatenare dieci colonne, occorrerebbe ripeterlo nove volte…
Tramite la funzione CONCAT_WS possiamo invece indicare il carattere separatore una sola volta, in questo modo
SELECT CONCAT_WS(‘,’,nome,cognome,citta,indirizzo) as AfterConcatWs
FROM #test2
Molto importante è notare il diverso comportamento delle funzioni CONCAT e CONCAT_WS in presenza di null.
La funzione ConcatWS elimina il separatore nel caso in cui la colonna seguente contenga un null. Occorre essere consapevoli di questo comportamento: probabilmente questo non è il comportamento che vogliamo se ad esempio il nostro obiettivo è convertire in formato csv i dati della tabella.
Tuttavia possiamo “porre rimedio” inglobando le colonne all’interno della funzione isnull
SELECT CONCAT_WS(‘,’, isnull(nome,”),isnull(cognome,”),isnull(citta,”),isnull(indirizzo”)) as AfterConcatWs
from #test2
Al contrario, se volessimo creare una stringa indirizzo con esattamente questa caratteristica di “saltare” le colonne, l’implementazione con la funzione CONCAT risulterebbe molto più lunga e frustante.
Funzione STRING_AGG
In presenza di una clausola group by, spesso si ha la necessità di concatenare (eventualmente con un separatore) i valori di una colonna di tipo varchar relativi alla chiave di aggregazione.
Prima del 2017, per risolvere questo problema gli sviluppatori T-SQL hanno dato sfoggio della loro abilità e creatività utilizzando le più svariate tecniche: funzioni xml path, conversioni in json, variabili che si concatenano con esse stesse, eccetera…
Da SQL Server 2017 abbiamo a disposizione una funzione di aggrezione utilissima per questo scopo: STRING_AGG.
Niente può essere più esplicativo di uno script d’esempio
CREATE TABLE #test3 (IdConto int, IdCliente int)
INSERT INTO #test3 VALUES (1,1),(1,2),(2,3),(3,4),(3,null)
SELECT IdConto,
string_agg(IdCliente,’;’) as ClientiCointestatari
FROM #test3
GROUP BY IdConto IdCconto
ClientiCointestatari 1
1;2 2
3 3
4
Notiamo inoltre la conversione implicita di colonne int in varchar e il comportamento in presenza di null.
Esercizio!
Combinando le due funzioni string_agg e CONCAT_WS possiamo convertire facilmente una tabella relazionale nel contenuto di un file csv.
Bonus: Funzione APPROX_COUNT_DISTINCT
Nel caso di elevate quantità di dati, l’utilizzo di una COUNT(DISTINCT ) potrebbe essere deleterio in termini di performance.
APPROX_COUNT_DISTINCT fornisce un valore approssimato rispetto al COUNT(DISTINCT) con delle performance migliori. Per la precisione
al 97% l”errore sarà inferiore del 2%
SELECT IdFatture,
APPROX_COUNT_DISTINCT(IdCliente) AS NumeroApprossimatoDiClienti
FROM Fatture
GROUP BY IdFatture
Tuttavia, occorre comunque fare massima attenzione alle performance come sottolineato da Pinal Dave in questo interessante articolo.
Come imparare a scrivere una query
Le librerie e il web sono piene di manuali sul linguaggio SQL, molti dei quali estremamente precisi e approfonditi. Noi vogliamo provare ad approcciarci alla didattica in maniera diversa, non soffermandoci sulla sintassi, ma sul ragionamento necessario per tradurre del linguaggio naturale in una query. Riteniamo che la natura dichiarativa dell’SQL richieda un approccio didattico differente rispetto agli altri linguaggi di programmazione.
Negli anni ’70, prima di una disputa legale per ragioni di brevetto, il nome del nostro linguaggio preferito era SEQUEL. Purtroppo la caduta della “E” ha tolto evidenza ad un aspetto molto significativo del nome: la lettera “E” stava per “English” per sottolineare la stretta vicinanza tra l’SQL e la lingua inglese parlata nel quotidiano.
Come parliamo e come programmiamo?
Nel libro Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 (MCSA) troviamo un esempio chiarificatore: ad un essere umano ci rivolgiamo in modo naturale con una frase del tipo
“Prendi una mela dal frigorifero”.
Se parlassimo ad un robot (o programmassimo in un altro linguaggio) riformuleremmo la frase in questo modo più algoritmico
“Vai al frigorifero e prendi una mela”
in quanto sentiremmo l’esigenza di impartire le istruzioni con un ordine più rigido. Ebbene, la programmazione in Sql ricalca il primo modo di esprimersi, quello tra esseri umani. Quando scriviamo una query dobbiamo specificare prima i nomi delle colonne (all’interno della clausola select) e solo in seguito il nome delle tabelle (nella clausola from). Con un po’ di fantasia, la query esatta sarà
SELECT mela
FROM frigorifero
invece di una più algoritmica
FROM frigorifero
SELECT mela
Come scrivere con l’SQL
Partendo da queste premesse, abbiamo creato delle presentazioni su SlideShare che accompagnano il lettore nella scrittura delle prime query in SQL. Vi riportiamo in basso la prima presentazione sui costrutti di base Select, From e Where (consigliamo la visualizzazione a schermo intero). Abbiamo cercato di utilizzare i colori per sottolineare come le richieste in linguaggio naturale siano traducibili, senza troppa fatica, in clausole Sql.
Linguaggio SQL: utilizzo di select, from e where from Nicola Iantomasi
Perché non usare MySql a fini didattici
Molto spesso, quando ci vengono affidati corsi di base di SQL per lavoratori neoassunti, ci viene chiesto di utilizzare MySql.
In questo articolo vi spiego cinque motivi per cui MySql non è a mio parere il DBMS migliore per illustrare l’SQL a scopo didattico,
in particolar modo a persone che si approcciano per la prima volta seriamente a tale linguaggio.
Alcune di queste caratteristiche potrebbero essere viste anche come un “benefit” se osservate dal punto di vista di un programmatore esperto, ma didatticamente rappresentano per me un ostacolo nella strada per insegnare e imparare a scrivere query che
restituiscano informazioni corrette, oltre a semplicemente terminare senza errori di esecuzione.
Gestione colonne non raggruppate
Molto spesso, il primo concetto un po’ ostico da digerire per gli studenti all’interno di un corso introduttivo di Sql è il raggruppamento. La tentazione di aggiungere nella select, senza l’utilizzo di funzioni di aggregazioni, delle colonne che non sono presenti nel group by è molto forte all’inizio, nonostante le ripetute raccomandazioni. In questi casi, un bel messaggio di errore sarebbe gradito ed “educativo”. Invece su Mysql, una query come la seguente compila senza errori associando ad ogni regione di residenza un nome casuale tra quelli del gruppo.
SELECT RegioneResidenza,
Nome,
count(*) as NumeroClienti
FROM Clienti
GROUP BY RegioneResidenza;
FULL JOIN non utilizzabile
Un altro argomento fondamentale in un corso introduttivo sull’SQL sono le varie tipologie di JOIN.
Per quanto un programmatore esperto possa raggiungere facilmente lo stesso obiettivo con del codice leggermente più complicato,
dal punto di vista didattico la mancanza della FULL JOIN risulta frustante per gli insegnanti e per gli studenti, in quanto nel flusso
della lezione è naturale chiedersi quale sintassi utilizzare per avere un comportamento pari “all’unione” dei costrutti LEFT JOIN e RIGHT JOIN.
Gestione JOIN senza ON
Purtroppo l’esperienza in azienda mi ha mostrato come tutt’oggi molti sviluppatori utilizzino la sintassi deprecata “virgola nella From più condizioni nella where” per eseguire una JOIN tra più tabelle, scrivendo query come questa
SELECT *
FROM Fatture, Clienti
WHERE Fatture.IdCliente = Clienti.IdCliente;
Tra i molteplici motivi per cui questa sintassi è sconsigliata (probabilmente ci dedicheremo un altro articolo), potrebbe esserci quello per cui l’inavvertita cancellazione delle condizioni nella where porterebbe a dei risultati abbondantemente errati, invece di un errore in fase di compilazione. Tuttavia ciò non si applica a MySql. Questa query infatti compila ed è equivalente ad una cross join. Provare per credere…
SELECT *
FROM Fatture
INNER JOIN Clienti;
Gestione nuove colonne in tabelle già popolate
Dopo le lezioni sull’interrogazione, si passa alle istruzioni DML. Reputo molto istruttivo dal punto di vista didattico far riflettere gli studenti sulla necessità di definire una nuova colonna con l’attributo NULL se la tabella è già popolata. Dopo la creazione, aggiorneremo tale colonna con un update e infine, se necessario, la modificheremo per darle l’attributo NOT NULL.
Su MySQL invece possiamo tranquillamente aggiungere fin da subito una colonna con l’attributo NOT NULL. Verranno inseriti in automatico degli zeri se la colonna è di tipo numerico, o alternativamente delle stringhe vuote. Tale comportamento può essere anche comodo in fase di sviluppo per un programmatore esperto e consapevole, ma dal punto di vista didattico non è l’ideale per chi sta imparando.
Operazioni tra tipi differenti
Una fetta importante degli errori runtime su codice Sql è legato ad errate conversioni in operazioni tra dati di tipo differente.
Dal punto di vista didattico reputo fondamentale investire molto tempo sul concetto di tipo associato alle colonne, sottolineando come esso sia un elemento cardine nella definizione di tabella e di quante problematiche (anche legati alle performance) possa portare la scelta di tipi sbagliati. Usare MySql per fare le dimostrazioni non è proprio l’ideale visto che questa query compila e da 1.
SELECT ‘a’+1;
Articoli correlati
Scopri le differenze tra Sql Server e Oracle per lavorare correttamente su entrambi gli ambienti.
Carica altro
Caricamento...