Inizi qui? Questa lezione fa parte di un tutorial completo sull’uso di SQL per l’analisi dei dati. Controlla l’inizio.

In questa lezione tratteremo:

• Pivot da righe a colonne
• Pivot da colonne a righe
• Cosa c’è dopo?

Pivot di righe in colonne

Questa lezione ti insegnerà come prendere dati formattati per l’analisi e ruotarli per la presentazione o la creazione di grafici . Prenderemo un set di dati simile a questo:

e lo faremo sembrare così:

Per questo esempio, utilizzeremo lo stesso set di dati dei giocatori di College Football utilizzato nella lezione CASE. Puoi visualizzare i dati direttamente qui.

Iniziamo aggregando i dati per mostrare il numero di giocatori di ogni anno in ciascuna conferenza, in modo simile al primo esempio nella lezione di Inner Join:

Visualizza in modalità.

Per trasformare i dati, avremo bisogno di inserire la query precedente in una sottoquery. Può essere utile creare la sottoquery e selezionare tutte le colonne da essa prima di iniziare a effettuare le trasformazioni. Rieseguire la query in passaggi incrementali come questo semplifica il debug se la query non viene eseguita. Tieni presente che puoi eliminare la clausola ORDER BY dalla sottoquery poiché la riordineremo i risultati nella query esterna.

Supponendo che funzioni come pianificato (i risultati dovrebbero essere esattamente uguali alla prima query), è il momento di suddividere i risultati in colonne diverse per diversi anni. Ogni elemento nell’istruzione SELECT crea una colonna, quindi dovrai creare una colonna separata per ogni anno:

Tecnicamente, ora hai raggiunto l’obiettivo di questo tutorial. Ma questo potrebbe ancora essere migliorato. Noterai che la query precedente produce un elenco ordinato alfabeticamente per Conferenza. Potrebbe avere più senso aggiungere una colonna “giocatori totali” e ordinarla in base a quella (dal più grande al più piccolo):

E il gioco è fatto! Visualizzalo in modalità.

Colonne pivot a righe

Molti dati che troverai su Internet sono formattati per il consumo, non per l’analisi. Prendi, ad esempio, questa tabella che mostra il numero di terremoti nel mondo dal 2000 al 2012:

In questo formato è difficile rispondere a domande come “cosa “è la magnitudo media di un terremoto?” Sarebbe molto più semplice se i dati fossero visualizzati in 3 colonne: “magnitudo”, “anno” e “numero di terremoti”. Ecco come trasformare i dati in quella forma:

Per prima cosa, controlla questi dati in Modalità:

Nota: colonna i nomi iniziano con “anno_” perché la modalità richiede che i nomi delle colonne inizino con lettere.

La prima cosa da fare qui è creare una tabella che elenchi tutte le colonne della tabella originale come righe in una nuova tabella A meno che tu non abbia un sacco di colonne da trasformare, il modo più semplice è spesso elencarle in una sottoquery:

Una volta che hai questo , puoi eseguire un cross join con la tabella worldwide_earthquakes per creare una vista espansa:

Nota che ogni riga nella worldwide_earthquakes viene replicato 13 volte. L’ultima cosa da fare è risolvere questo problema utilizzando un’istruzione CASE che estrae i dati dalla colonna corretta nella tabella worldwide_earthquakes dato il valore in la colonna year:

Visualizza il prodotto finale in modalità.

Congratulazioni per aver completato il tutorial SQL avanzato! Ora che hai un controllo su SQL, il passaggio successivo è affinare il tuo processo analitico.

Abbiamo creato la sezione di formazione su SQL Analytics proprio per questo scopo. Con set di dati falsi per imitare situazioni del mondo reale, puoi avvicinarti a questa sezione come formazione sul posto di lavoro. Dai un’occhiata!