Pulizia dei dati in SQL

La pulizia dei dati è una parte importante di qualsiasi analisi dei dati. Qui discuteremo le tecniche di pulizia dei dati in SQL.

Trovo quasi impossibile concentrarmi sul lavoro quando la mia scrivania è in disordine. Se è ingombra di carta, tazze di caffè o giocattoli a caso che mia figlia ha portato in ufficio, non c'è alcuna possibilità che riesca a fare qualcosa finché la mia scrivania non torna in ordine. Per qualche motivo, è come se il disordine sulla mia scrivania si fosse fatto strada nella mia mente.

Questa stessa idea è pertinente alla pulizia dei dati. Molte volte ho dedicato ore a un'analisi e ho tratto le mie conclusioni per poi scoprire un'incongruenza nei dati che vanifica l'intero rapporto. Come analisti, di solito ci buttiamo subito nell'analisi dei dati senza prima dedicare del tempo ad assicurarci che i nostri dati siano puliti. Questo può portare a molte ore di perdita di tempo o, peggio ancora, a report imprecisi.

Che cos'è la pulizia dei dati?

Il processo di pulizia dei dati (detto anche data cleansing) consiste nell'identificare le imprecisioni presenti in un set di dati e nel correggerle. È il primo passo di qualsiasi analisi e comprende l'eliminazione di dati, l'aggiornamento di dati e la ricerca di incongruenze o di cose che non hanno senso.

È possibile apprendere tutte le funzioni SQL necessarie per pulire i dati in SQL nella nostra traccia SQL dalla A alla Z traccia. La traccia contiene 7 corsi SQL interattivi che vi insegneranno l'SQL completo, dalle basi agli argomenti intermedi, fino ai concetti SQL avanzati come le funzioni finestra e le query ricorsive. Si tratta della serie di corsi SQL più completa disponibile su Internet.

Tecniche di pulizia dei dati

Ora che le idee sono chiare, diamo un'occhiata alle tecniche SQL che si possono utilizzare per ripulire i dati. Per ogni esempio, utilizzeremo la tabella delle aziende mostrata di seguito. Essa contiene informazioni su varie aziende:

Come e quando cancellare i dati

A volte capita di dover eliminare dei dati dal set di dati. Ciò può essere dovuto al fatto che i dati non sono pertinenti all'analisi, oppure sono duplicati o imprecisi. Nei prossimi esempi esploreremo questi diversi scenari e come affrontarli.

Esempio 1: Eliminazione di dati duplicati

La prima cosa da fare è cercare i dati che devono essere eliminati. Ciò può essere dovuto a duplicati o al fatto che i dati non sono rilevanti. In questa tabella si nota subito che la riga relativa all'azienda Toughtam è duplicata. Non sarà facile identificare questo problema in un set di dati di grandi dimensioni. Prima di cancellare la riga, vediamo come individuarla.

In questo set di dati, ogni azienda dovrebbe avere una sola riga, quindi usiamo le clausole GROUP BY e HAVING per identificare i nomi duplicati. Questa query conterà il numero di volte in cui ogni nome esiste nel database usando GROUP BY. Poi utilizza la clausola HAVING per filtrare i risultati solo per i nomi che esistono più di una volta.

SELECT name, 
	 COUNT(name) as count
FROM companies
GROUP BY name
HAVING(count > 1)

La query restituirà il seguente risultato:

Ottimo! Ora sappiamo che la società Toughtam è duplicata, ma come possiamo eliminare una delle righe? Utilizzeremo una combinazione di ROW_NUMBER() e DELETE. Innanzitutto, aggiungiamo un numero di riga per ogni riga in base alla colonna nome:

SELECT name, 
	 ROW_NUMBER() OVER(PARTITION BY name) AS rn
FROM companies

Abbiamo aggiunto una nuova colonna che mostra il numero di riga per ogni nome. Come si può vedere, ora ci sono 1 e 2 per le righe di Toughtam. Ora eseguiamo un'istruzione DELETE per rimuovere tutte le righe in cui la colonna rn è superiore a 1.

DELETE 
FROM (
SELECT name, 
	 	ROW_NUMBER() OVER(PARTITION BY name) AS rn
FROM companies
) 
WHERE rn > 1

Ora il nostro set di dati ha questo aspetto:

Esempio 2: Ordinare i dati prima dell'eliminazione

In questo esempio, le righe di Toughtam sono le stesse (con l'eccezione di id), quindi non stiamo ordinando le righe in base a qualcosa di diverso da come appaiono nel database. Spesso si scopre che le righe sono duplicate, ma forse i campi non sono identici. In questo caso, si può aggiungere una clausola ORDER BY dopo PARTITION BY.

Ad esempio, supponiamo che i dati abbiano questo aspetto:

Sembra che questo record sia stato aggiornato per questa azienda il 1/20/2023 e che il numero di dipendenti sia aumentato. Se si volesse mantenere il record più recente, si dovrebbe eseguire:

DELETE 
FROM (
SELECT name, 
	 	ROW_NUMBER() OVER(PARTITION BY name ORDER BY created DESC) AS rn
FROM companies
) 
WHERE rn > 1

Come si può vedere, ora ordiniamo l'istruzione ROW_NUMBER() in base al campo created in ordine decrescente, estraendo per primo il record creato più di recente. Vengono quindi eliminati i record successivi al primo.

Per ulteriori informazioni sulla ricerca di valori duplicati in SQL, consultate il nostro articolo Come trovare valori duplicati in SQL.

Esempio 3: Rimozione dei valori NULL

Ora diamo un'occhiata a NULLs. NULL indica un valore mancante; per saperne di più su di esso, consultate qui. A seconda delle implicazioni dei valori NULL nei dati, è possibile rimuovere le righe o aggiornarle. Nel nostro esempio, vediamo due valori NULL. Una riga ha il valore NULL per l'industria e l'altra il valore year_founded. Ciascuna riga verrà gestita in modo diverso.

Un'azienda deve avere un anno di fondazione. Utilizzeremo DELETE per rimuovere la riga mancante, poiché sembra essere un dato errato.

SELECT
FROM companies 
WHERE year_founded IS NULL 

Nella query di cui sopra, si utilizza la clausola IS NULL. Questa clausola esamina la colonna year_founded e restituisce tutte le righe in cui è presente IS NULL. Una volta verificato che si tratta della riga che vogliamo rimuovere, possiamo eliminarla con l'esecuzione:

DELETE
FROM companies 
WHERE year_founded IS NULL 

A questo punto, abbiamo finito di cancellare i dati errati e siamo pronti a passare all'istruzione UPDATE. Utilizziamola per correggere l'altro valore NULL.

Come aggiornare i dati

L'istruzione UPDATE viene utilizzata per modificare i dati esistenti. Questa tecnica di pulizia dei dati viene utilizzata per correggere dati imprecisi o per formattare i dati (rendendoli più leggibili). Nei prossimi esempi, esamineremo questi tipi di scenari per capire come manipolare i dati con UPDATE.

Esempio 1: Etichetta significativa per i valori NULL

Come abbiamo già visto, c'è un'azienda che ha un valore NULL per l'industria. Non c'è problema perché, nella nostra situazione ipotetica, sappiamo che non tutti i settori sono disponibili nel nostro database. In questo caso, dobbiamo aggiornare il valore NULL con "Altro". Un'altra opzione sarebbe quella di sostituire NULLs con "NA" o "Non applicabile".

Per prima cosa, utilizziamo SELECT per estrarre la riga con il settore NULL:

SELECT *
FROM companies 
WHERE industry IS NULL 

Ora che sappiamo di aver estratto la riga corretta, possiamo UPDATE la colonna industria. Lo faremo eseguendo:

UPDATE companies 
SET industry = ‘Other’
WHERE industry IS NULL 

Quando si usa UPDATE, la prima cosa da fare è identificare la tabella che si vuole modificare. Nel nostro esempio, questa tabella è companies. Successivamente, occorre indicare la colonna che si sta aggiornando e il suo valore. Identifichiamo la colonna usando SET [column name]. Quindi definiamo il cambiamento della colonna usando = [ value ]. La clausola WHERE è la stessa che si userebbe se si scrivesse un'istruzione SELECT. Vogliamo cambiare il settore in "Altro" solo se il settore IS NULL.

Esempio 2: Correggere la maiuscola dei valori

A questo punto i dati sono migliorati, ma la colonna state avrebbe bisogno di un po' di pulizia. Alcuni valori sono in maiuscolo e altri in minuscolo. In genere, l'abbreviazione dello stato è maiuscola, quindi aggiorniamo tutti i valori minuscoli in maiuscolo.

Poiché vogliamo assicurarci che tutti i valori dello stato siano in maiuscolo, possiamo eseguire:

UPDATE companies 
SET state = UPPER(state)

Quando si inserisce l'istruzione UPPER() attorno a un nome di colonna, si cambiano tutte le lettere in maiuscolo. (Con l'istruzione LOWER() si fa il contrario, cambiando tutte le lettere in minuscolo). Ora la nostra tabella ha questo aspetto:

Esempio 3: Correggere gli errori logici

Un'ultima cosa da verificare nel set di dati è la presenza di errori logici. Nei nostri dati, vediamo che un'azienda ha un valore year_founded di 2032. Questo non è possibile, perché un'azienda non può essere fondata nel futuro. Possiamo identificare i record con data futura eseguendo:

SELECT *
FROM companies 
WHERE year_founded > CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP() restituisce la data e l'ora corrente. Nell'istruzione precedente, vengono estratti tutti i record in cui year_founded è successivo al momento in cui viene eseguita la query. Si può anche codificare una data, ad esempio la data di oggi.

Dopo un po' di ricerche, si scopre che si tratta di un errore di battitura e che l'azienda è stata fondata nel 2012, quindi si procede a modificare il record:

UPDATE companies
SET year_founded = 2012
FROM companies 
WHERE id = 3

In questo scenario, nella clausola WHERE abbiamo specificato un id specifico. Questo perché molto probabilmente non si vuole aggiornare tutti i valori di year_founded in modo che siano dello stesso anno per ogni azienda che ha un anno di fondazione nel futuro. Conosciamo l'anno di fondazione di questa specifica azienda, quindi aggiorneremo solo quel record.

Ricordarsi sempre di pulire i dati

I nostri dati appaiono ora molto più chiari rispetto al set di dati originale. La pulizia dei dati, per quanto noiosa, è una parte essenziale del processo di analisi dei dati. Non date mai per scontato che i dati con cui state lavorando siano puliti. Esplorate i dati alla ricerca di duplicati, NULLs, e di eventuali fallacie logiche.

Ora avete compreso diverse tecniche SQL che potete utilizzare per modificare i vostri dati, tra cui DELETE e UPDATE. Un ottimo passo successivo è quello di prendere il SQL dalla A alla Z Il percorso. Contiene 7 corsi interattivi di SQL, tra cui un intero corso sulle clausole DELETE, UPDATE e INSERT. È la serie di corsi SQL più completa disponibile su Internet. Questa traccia vi aiuterà a rafforzare tutto ciò che avete imparato in questo articolo e vi darà l'opportunità di fare ulteriore pratica. Buona pulizia dei dati!