SQL Pivot: Conversione di righe in colonne

Prova questo notebook in Databricks

Dai un'occhiata all'ebook Perché il data lakehouse è il tuo prossimo data warehouse per scoprire i meccanismi interni della Databricks Lakehouse Platform.

AGGIORNATO AL 10/11/2018

Pivot è stato introdotto per la prima volta in Apache Spark 1.6 come nuova funzionalità DataFrame che consente agli utenti di ruotare un'espressione con valori di tabella trasformando i valori univoci di una colonna in singole colonne.

La versione Apache Spark 2.4 estende questa potente funzionalità di pivoting dei dati anche ai nostri utenti SQL. In questo blog, utilizzando le registrazioni delle temperature a Seattle, mostreremo come possiamo utilizzare questa comune funzionalità SQL Pivot per ottenere trasformazioni complesse dei dati.

Esaminare le temperature estive con Pivot

Quest'estate a Seattle le temperature sono salite a livelli insopportabili, raggiungendo picchi elevati tra gli 80 e i 90 gradi, per nove giorni a luglio.

Supponiamo di voler esplorare o esaminare se ci fosse una tendenza storica all'aumento dei livelli di mercurio. Un modo intuitivo per esaminare e presentare questi numeri è avere i mesi come colonne e quindi le medie mensili massime di ogni anno in una singola riga. In questo modo sarà facile confrontare le temperature sia orizzontalmente, tra mesi adiacenti, sia verticalmente, tra anni diversi.

Ora che abbiamo il supporto per la sintassi PIVOT in Spark SQL, possiamo ottenerlo con la seguente query SQL.

La query precedente produrrà un risultato simile a:

Beh, sembra che ci siano anni buoni e anni cattivi. L'anno 2016 sembra un anno piuttosto favorevole all'energia.

Pivoting in SQL

Diamo un'occhiata più da vicino a questa query per capire come funziona. Innanzitutto, dobbiamo specificare la clausola FROM, che è l'input del pivot, in altre parole, la tabella o la sottoquery in base alla quale verrà eseguito il pivoting. Nel nostro caso, siamo interessati agli anni, ai mesi e alle temperature elevate, quindi questi sono i campi che compaiono nella sottoquery.

In secondo luogo, consideriamo un'altra parte importante della query, la clausola PIVOT. Il primo argomento della clausola PIVOT è una funzione di aggregazione e la colonna da aggregare. Quindi specifichiamo la colonna pivot nella sotto-clausola FOR come secondo argomento, seguito dall'operatore IN contenente i valori della colonna pivot come ultimo argomento.

La colonna pivot è il punto attorno al quale verrà ruotata la tabella e i valori della colonna pivot verranno trasposti in colonne nella tabella di output. La clausola IN consente inoltre di specificare un alias per ogni valore pivot, semplificando la generazione di nomi di colonna più significativi.

Un'idea importante sul pivot è che esegue un'aggregazione raggruppata in base a un elenco di colonne group-by implicite insieme alla colonna pivot. Le colonne group-by implicite sono colonne della clausola FROM che non compaiono in alcuna funzione di aggregazione o come colonna pivot.

Nella query precedente, con la colonna pivot che è la colonna month e la colonna group-by implicita che è la colonna year, l'espressione avg(temp) verrà aggregata su ogni coppia di valori distinti di (year, month), dove month è uguale a uno dei valori della colonna pivot specificati. Di conseguenza, ciascuno di questi valori aggregati verrà mappato nella cella corrispondente della riga year e del mese column.

Vale la pena notare che a causa di questo group-by implicito, dobbiamo assicurarci che qualsiasi colonna che non desideriamo faccia parte dell'output pivot venga esclusa dalla clausola FROM, altrimenti la query produrrebbe risultati indesiderati.

Specifica di più espressioni di aggregazione

L'esempio precedente mostra solo un'espressione di aggregazione utilizzata nella clausola PIVOT, mentre in realtà, gli utenti possono specificare più espressioni di aggregazione, se necessario. Ancora una volta, con i dati meteorologici di cui sopra, possiamo elencare le temperature massime elevate insieme alle temperature medie elevate tra giugno e settembre.

In caso di più espressioni di aggregazione, le colonne saranno il prodotto cartesiano dei valori della colonna pivot e delle espressioni di aggregazione, con i nomi come _.

Colonne di raggruppamento rispetto a colonne pivot

Ora supponiamo di voler includere le basse temperature nella nostra esplorazione delle tendenze della temperatura da questa tabella delle temperature giornaliere basse:

Per combinare questa tabella con la precedente tabella delle temperature giornaliere elevate, potremmo unire queste due tabelle nella colonna "Data". Tuttavia, poiché useremo pivot, che esegue il raggruppamento per date, possiamo semplicemente concatenare le due tabelle usando UNION ALL. E vedrai più avanti, questo approccio ci offre anche maggiore flessibilità:

Ora proviamo la nostra query pivot con la nuova tabella combinata:

Di conseguenza, otteniamo la media alta e la media bassa per ogni mese degli ultimi 4 anni in una tabella. Tieni presente che dobbiamo includere la colonna flag nella query pivot, altrimenti l'espressione avg(temp) si baserebbe su un mix di temperature alte e basse.

Potresti aver notato che ora abbiamo due righe per ogni anno, una per le temperature elevate e l'altra per le temperature basse. Questo perché abbiamo incluso un'altra colonna, flag, nell'input pivot, che a sua volta diventa un'altra colonna di raggruppamento implicita oltre alla colonna originale year.

In alternativa, invece di essere una colonna di raggruppamento, il flag può anche fungere da colonna pivot. Quindi ora abbiamo due colonne pivot, month e flag:

Questa query ci presenta un layout diverso degli stessi dati, con una riga per ogni anno, ma due colonne per ogni mese.

Quali sono i passaggi successivi

Per eseguire gli esempi di query utilizzati in questo blog, consulta gli esempi SQL pivot in questo notebook di accompagnamento.

Grazie ai collaboratori della community di Apache Spark per i loro contributi!

(Questo post sul blog è stato tradotto utilizzando strumenti basati sull'intelligenza artificiale) Post originale