SQL (Structured Query Language) è uno strumento fondamentale per chi lavora con i dati. Introdotto negli anni ’70, SQL è ancora oggi uno dei linguaggi più utilizzati per la gestione, l’analisi e l’elaborazione di dati in database relazionali. Che tu sia un data engineer, un data analyst o un data scientist, conoscere SQL è essenziale per manipolare e interrogare i dati in modo efficace.
In questo articolo, esploreremo alcune tecniche avanzate di SQL che possono aiutarti a risolvere problemi complessi, ottimizzare le query e migliorare la gestione dei dati. Dalle Common Table Expressions (CTE) alle funzioni di finestra come ROW_NUMBER(), LAG e LEAD, scopriremo come sfruttare al meglio queste funzionalità per lavorare in modo più efficiente.
Contenuto Principale
1. Common Table Expression (CTE)
Le Common Table Expressions (CTE) sono uno strumento potente per semplificare query complesse. Una CTE è un risultato temporaneo che può essere referenziato all’interno di una query SELECT, INSERT, UPDATE o DELETE. Questo permette di suddividere query complicate in parti più piccole e leggibili, migliorando la manutenibilità del codice.
Esempio di CTE Ricorsiva
Una delle caratteristiche più utili delle CTE è la possibilità di creare CTE ricorsive, che sono particolarmente utili per gestire dati gerarchici, come le strutture organizzative o alberi genealogici.
WITH RECURSIVE cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM Employees WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1 FROM Employees e INNER JOIN cteReports r ON e.ManagerID = r.EmpID ) SELECT FirstName || ' ' || LastName AS FullName, EmpLevel, (SELECT FirstName || ' ' || LastName FROM Employees WHERE EmployeeID = cteReports.MgrID) AS Manager FROM cteReports ORDER BY EmpLevel, MgrID;
In questo esempio, la CTE ricorsiva cteReports genera una gerarchia di dipendenti e i loro manager, con un livello di profondità indicato da EmpLevel. Questo approccio è particolarmente utile per analizzare strutture organizzative complesse.
2. Rimozione di Righe Duplicate
Quando si lavora con dati grezzi, è comune incontrare duplicati. Un modo efficiente per gestire questo problema è utilizzare la funzione di finestra ROW_NUMBER(), che assegna un numero univoco a ciascuna riga all’interno di una partizione di dati.
Esempio di Rimozione Duplicati
WITH dane (id, name, age, date) AS ( SELECT 1, 'John Smit', 19, '2020-01-01' UNION ALL SELECT 2, 'Eva Nowak', 21, '2021-01-01' UNION ALL SELECT 3, 'Danny Clark', 24, '2021-01-01' UNION ALL SELECT 4, 'Alicia Kaiser', 25, '2021-01-01' UNION ALL SELECT 5, 'John Smit', 19, '2021-01-01' UNION ALL SELECT 6, 'Eva Nowak', 21, '2022-01-01' ) SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY date DESC) AS rn, * FROM dane ) WHERE rn = 1;
In questo esempio, la funzione ROW_NUMBER() assegna un numero a ciascuna riga in base alla data, permettendo di filtrare solo le righe più recenti. Questo è particolarmente utile quando si lavora con dati storici e si vuole mantenere solo l’ultima versione di ciascun record.
3. Trovare Nuovi Record o Record Mancanti
Un compito comune è identificare record nuovi o mancanti tra due tabelle. Questo può essere fatto utilizzando il comando EXISTS o un LEFT JOIN con una clausola WHERE.
Esempio con EXISTS
SELECT * FROM raw_Employees a WHERE NOT EXISTS ( SELECT 1 FROM Employees b WHERE a.EmployeeID = b.EmployeeID );
Questo query restituisce solo i record presenti in raw_Employees che non esistono in Employees. Questo approccio è utile per sincronizzare tabelle o per identificare nuovi dati da inserire in un database.
4. Funzioni di Finestra: LAG e LEAD
Le funzioni di finestra come LAG e LEAD sono utili per confrontare i valori di una riga con quelli delle righe precedenti o successive. Questo è particolarmente utile per analisi temporali, come il confronto anno su anno o mese su mese.
Esempio con LAG
WITH currency (date, price, currency) AS ( SELECT CAST('2006-01-02' AS DATE), 3.2582, 'USD' UNION ALL SELECT CAST('2006-01-03' AS DATE), 3.2488, 'USD' UNION ALL SELECT CAST('2006-01-04' AS DATE), 3.1858, 'USD' UNION ALL SELECT CAST('2006-01-05' AS DATE), 3.1416, 'USD' UNION ALL SELECT CAST('2006-01-06' AS DATE), 3.1507, 'USD' ) SELECT date, currency, price, LAG(price) OVER (ORDER BY date) AS previous_day_price, (price - LAG(price) OVER (ORDER BY date)) / LAG(price) OVER (ORDER BY date) AS change FROM currency;
Questo esempio mostra come confrontare il prezzo di una valuta con quello del giorno precedente, calcolando anche la variazione percentuale. La funzione LAG è particolarmente utile per analisi temporali e trend.
5. UNPIVOT e PIVOT
Le operazioni UNPIVOT e PIVOT sono utili per trasformare i dati da un formato colonnare a uno righe (UNPIVOT) o viceversa (PIVOT). Queste operazioni sono particolarmente utili per la modellazione dei dati e la creazione di report.
Esempio di UNPIVOT
WITH data (productID, I2024, II2024, III2024, IV2024) AS ( SELECT 1, 100, 123, 234, 4323 UNION ALL SELECT 2, 123, 445, 33, 2212 UNION ALL SELECT 3, 1222, 1223, 1232, 43232 UNION ALL SELECT 4, 111, 223, 234, 213 UNION ALL SELECT 5, 22332, 2323, 2334, 4342 ) SELECT * FROM data UNPIVOT INCLUDE NULLS ( sales FOR quarter IN (I2024, II2024, III2024, IV2024) );
Questo esempio trasforma i dati da un formato colonnare a uno di tipo righe, rendendoli più facili da analizzare. L’operazione UNPIVOT è particolarmente utile quando si lavora con dati in formato wide (molte colonne) e si vuole trasformarli in formato long (molte righe).
Domande Frequenti (FAQ)
1. Quando dovrei usare una CTE?
Le CTE sono utili quando hai bisogno di semplificare query complesse o quando devi fare riferimento più volte allo stesso set di dati all’interno di una query. Sono particolarmente utili per query ricorsive o per suddividere query complesse in parti più gestibili.
2. Qual è la differenza tra LAG e LEAD?
La funzione LAG confronta la riga corrente con una riga precedente, mentre LEAD confronta la riga corrente con una riga successiva. Entrambe sono utili per analisi temporali e trend.
3. Come posso rimuovere duplicati in SQL?
Puoi usare la funzione ROW_NUMBER() per assegnare un numero univoco a ciascuna riga e poi filtrare solo le righe con il numero 1. Questo approccio è particolarmente utile quando si lavora con dati storici.
Conclusione
In questo articolo, abbiamo esplorato alcune delle tecniche avanzate di SQL che possono aiutarti a gestire e analizzare i dati in modo più efficace. Dalle CTE ricorsive alle funzioni di finestra come LAG e LEAD, queste funzionalità ti permettono di risolvere problemi complessi e ottimizzare le tue query.
Ora che hai acquisito queste competenze, ti incoraggio a sperimentare con queste tecniche nei tuoi progetti. SQL è uno strumento potente, e più lo pratichi, più diventerai abile nel manipolare e analizzare i dati. Buon coding!
