Corso
Manipolazione dei dati in SQL
4 ore
337.4K
Creare report a partire da un dataset è una competenza essenziale se lavori con i dati. In fondo, vuoi essere in grado di rispondere a domande di business critiche usando i dati a tua disposizione. Molto spesso, queste risposte vengono presentate sotto forma di grafici di report. Ma a volte servono anche report in forma di tabelle. In entrambi i casi, potresti dover riassumere i dati con semplici calcoli. In SQL, puoi riassumere/aggregare i dati usando le funzioni di aggregazione. Con queste funzioni, potrai rispondere a domande come:
- Qual è il valore massimo per
some_column_from_the_table? oppure - Quali sono i valori minimi di
some_column_from_the_tablerispetto aanother_column_from_the_table?
e molte altre.
Iniziamo a eseguire un po' di aggregazione dei dati.
Nota: per seguire questo tutorial, devi sapere come scrivere query di base in PostgreSQL (che userai come RDBMS). Questo tutorial può fare da buon ripasso.
Configurare il database
Per prima cosa impostiamo un database PostgreSQL e ripristiniamo questo backup, che contiene la tabella che userai in questo tutorial. Se vuoi imparare come ripristinare un backup di un database in PostgreSQL, puoi seguire la prima sezione di questo tutorial.
Se sei riuscito a ripristinare il backup, dovresti vedere una tabella chiamata international_debt all'interno del database (dovrai prima creare un database, se non ne hai già uno). Diamo rapidamente un'occhiata alle prime righe della tabella (una semplice query di select ti aiuterà a farlo):
La tabella contiene informazioni sulle statistiche del debito di diversi paesi in tutto il mondo per l'anno corrente, in varie categorie (vedi le colonne indicator_name e indicator_code). La colonna debt mostra l'ammontare del debito (in USD) che un determinato paese ha in una specifica categoria. I dati appartengono al dominio dell'economia e vengono spesso usati per analizzare le condizioni economiche dei diversi paesi. I dati sono stati raccolti dalla Banca Mondiale.
Ora che hai configurato correttamente il database, eseguiamo alcune semplici query per conoscere meglio i dati. Apri lo strumento pgAdmin e comincia.
Le informazioni semplici contano
Dalla figura sopra, puoi vedere che ci sono molte voci duplicate per un singolo paese ma per categorie diverse. Una domanda che nasce subito è:
Quali sono i diversi paesi di cui la tabella contiene dei record?
Se esegui semplicemente una query di select con la colonna country_name, non otterrai la risposta giusta a questa domanda, perché il risultato della query conterrà duplicati. Usiamo la parola chiave DISTINCT per risolvere questo problema.
select distinct country_name from international_debt;
E questo dovrebbe restituire qualcosa del genere:
Ora hai una buona risposta alla domanda di cui sopra. Un'ultima domanda prima di passare alle funzioni di aggregazione:
Quanti tipi diversi di indicatori di debito ci sono nella tabella?
La query per rispondere dovrebbe essere simile a quella sopra. Devi solo cambiare il nome della colonna. La prendi come esercizio? Il risultato dovrebbe essere simile al seguente:
Funzioni di aggregazione
Iniziamo eseguendo una query con una funzione di aggregazione e procediamo di conseguenza. Lungo il percorso, imparerai di più sulle sintassi e sulle strutture da seguire quando applichi funzioni di aggregazione in SQL.
select sum(debt) from international_debt;
E il risultato:
Con la funzione di aggregazione SUM(), puoi calcolare la somma aritmetica su una colonna (che contiene valori numerici). Con la query sopra, hai scoperto il debito totale in sospeso dei paesi elencati nella tabella.
Nota che SUM() non prende in considerazione i valori NULL quando calcola la somma. Ora, troviamo la risposta alla domanda:
Qual è l'ammontare massimo del debito?
Ecco in tuo aiuto la funzione di aggregazione MAX():
select max(debt) from international_debt;
E la risposta è:
Come la funzione SUM(), anche MAX() non considera le voci NULL nei suoi calcoli. Esiste anche una funzione analoga, MIN(). Fammi sapere il valore minimo della colonna debt nella sezione Comments? Ora è una buona idea verificare se c'è un valore non valido nella colonna debt per assicurarti che i risultati finora siano corretti.
Nota che le funzioni qui vengono usate in minuscolo, come mostrato sopra.
Quando esegui questa query: select * from international_debt where debt is null;, dovresti ottenere un risultato vuoto. Ora scopriamo il numero totale di paesi distinti presenti nella tabella.
select count(distinct(country_name)) from international_debt;
E vedi che nella tabella sono presenti in totale 124 paesi distinti. Fai molta attenzione alla successione di funzioni che hai applicato nella query sopra. Sì, qui è consentito, cioè collegare più di una funzione di aggregazione in modo logico.
Ora, supponi di voler vedere il valore medio della colonna debt. La funzione è AVG():
select avg(debt) from international_debt;
Vedi che il valore è 1306633214.966397971 (USD). È una buona idea presentare questi risultati con nomi di colonna adeguati. Dai risultati sopra, puoi vedere che PostgreSQL cambia il nome della colonna nel nome della funzione di aggregazione (inclusa nella query) quando restituisce il risultato. Quindi è buona pratica assegnare un alias appropriato a queste colonne. Puoi farlo così:
select avg(debt) as Average_Debt_By_A_Country from international_debt;
Il risultato è molto più interpretabile:
Portiamo ora il tutto a un livello un po' più complesso. Per poter rispondere a domande come Quali sono i valori minimi di some_column_from_the_table rispetto a another_column_from_the_table?, devi abbinare una funzione di aggregazione alla clausola GROUP BY. Vediamo come.
Funzioni di aggregazione + GROUP BY + altro
Supponiamo che tu voglia produrre un report in cui mostri il country_name e la somma dei relativi debiti. Ecco un esempio:
Report come questo sono piuttosto comuni nel mondo reale. Quindi, quale potrebbe essere la query per ottenere un report del genere? Dovrai usare la funzione SUM() su debt. E dovrai anche mostrare il country_name insieme alla somma dei debiti. Eseguiamo la seguente query:
select country_name, sum(debt) from international_debt;
Non produce forse il seguente errore?
ERROR: column "international_debt.country_name" must appear in the GROUP BY clause or be used in an aggregate function
Capiremo adesso cosa significa. Quando usi una funzione di aggregazione (come SUM()) con una colonna non aggregata come country_name, devi passare la colonna non aggregata a una clausola GROUP BY. Quindi, la query corretta sarà:
select country_name, sum(debt) as total_debt from international_debt group by country_name;
E il risultato è proprio quello giusto:
Nota l'uso degli alias nella query.
Ora, supponi di dover ordinare questo report rispetto a total_debt in modo decrescente. Ricordi la clausola ORDER BY? Sì, puoi abbinare le funzioni di aggregazione anche alla clausola ORDER BY:
select country_name, sum(debt) as total_debt from international_debt
group by country_name order by total_debt desc;
Il risultato ora dovrebbe essere ordinato in modo inverso:
Nota la colonna che hai usato nella clausola ORDER BY.
Ora un'altra domanda importante:
Qual è l'importo di debito più alto nelle diverse categorie (in ordine inverso)?
Qui dovrai usare la funzione MAX(). A questo punto la query per rispondere non dovrebbe essere difficile da scrivere.
select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc;
E ottieni un report pulito:
Puoi anche limitare il numero di righe in report di questo tipo. Diciamo che vuoi includere solo le prime cinque voci nel report sopra. Puoi farlo usando la clausola LIMIT.
select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc
limit 5;
È il momento del report finale per questo tutorial. Devi includere i nomi dei paesi nel report sopra. Come puoi farlo? La query seguente ti permette di farlo:
select country_name, indicator_code, max(debt) as maximum_debt from international_debt
group by country_name, indicator_code order by maximum_debt desc;
Un altro bel report:
Nella query sopra, hai aggiunto la colonna country_name dopo la clausola SELECT e l'hai aggiunta anche dopo GROUP BY. Puoi estendere questo schema a qualsiasi numero di colonne.
L'ordine di GROUP BY, ORDER BY e LIMIT è molto importante quando generi report come questo. Se per errore cambi l'ordine, dovrai affrontare errori. Verificalo tu stesso:
select country_name, sum(debt) as total_debt from international_debt
order by total_debt desc group by country_name;
E ottieni:
ERROR: syntax error at or near "group"
LINE 1: ... from international_debt order by total_debt desc group by c...Nella query sopra, hai messo la clausola ORDER BY prima di GROUP BY, cosa non consentita. In realtà, non è applicabile nemmeno quando non usi funzioni di aggregazione. L'ordine corretto è - GROUP BY -> ORDER BY -> LIMIT. Ricordalo sempre.
Andare oltre
Complimenti! Sei arrivato alla fine di questo tutorial. In questo tutorial, hai conosciuto le diverse funzioni di aggregazione in PostgreSQL e come usarle per generare report utili. Sono competenze fondamentali per un data scientist. Per potenziare in modo sistematico le tue competenze SQL, puoi seguire i seguenti corsi DataCamp:
Argomenti
