Tabelle pivot SQL: Tutorial passo dopo passo

Siete analisti di dati e volete migliorare le vostre capacità? O forse volete scoprire quali strumenti vi servono per avere successo in questo ruolo? Oggi analizzeremo uno degli strumenti più importanti per gli analisti di dati: la tabella pivot SQL.

Una tabella pivot SQL è uno strumento essenziale per organizzare e riassumere rapidamente i dati. Con poche righe di codice, aiuta gli analisti a identificare rapidamente ed efficacemente modelli e tendenze nei dati. Ciò si ottiene ruotando o ruotando le righe e le colonne della tabella e applicando un calcolo aggregato ai dati sottostanti.

Se siete alle prime armi con la creazione di report SQL, date un'occhiata al nostro Come creare semplici report SQL corso. Imparerete a scrivere query complesse in SQL. Tra le molte funzioni insegnate in questo corso c'è la sintassi CASE WHEN, essenziale per la creazione di tabelle pivot.

Prima di passare alle tabelle pivot, ricordatevi di tenere a portata di mano il nostro foglio informativo su SQL per l'analisi dei dati. Potrete utilizzarla come ripasso di alcune delle funzioni di cui parleremo.

Come funzionano le tabelle pivot SQL nell'analisi dei dati

Le tabelle pivot SQL sono uno strumento utile per riorganizzare, riassumere e analizzare rapidamente grandi quantità di informazioni. Forse avete già lavorato con strumenti come Microsoft Excel e Google Sheets; questi offrono funzionalità integrate di tabelle pivot che consentono di eseguire facilmente tali trasformazioni.

Facciamo un esempio. Immaginiamo di avere a disposizione una tabella che mostra i dati di streaming musicale di un artista su diverse piattaforme musicali e paesi.

Tabella:

La disposizione dei dati rende difficile il confronto, soprattutto quando il numero di Paesi e piattaforme aumenta.

Immaginiamo ora di poter strutturare i dati in modo che gli streaming di ciascun Paese siano suddivisi per piattaforma e totalizzati collettivamente. In questo modo saremmo in grado di rispondere a domande come "Quale piattaforma è più popolare in ogni Paese?" e "Su quale piattaforma e quale Paese dovremmo concentrare gli sforzi?" Questa nuova struttura potrebbe avere questo aspetto:

Risultato:

La nuova tabella formattata rende molto più facile confrontare gli stream dell'artista tra piattaforme e Paesi. È ora possibile vedere a colpo d'occhio quale piattaforma funziona meglio in ciascun Paese: Spotify in Francia e Irlanda e SoundCloud in Germania. Il nuovo formato consente analisi e approfondimenti più rapidi, mostrando le prestazioni geografiche e specifiche della piattaforma senza dover scavare manualmente tra le righe di dati.

Gli ingredienti di una tabella pivot SQL

Per prima cosa, analizziamo gli elementi costitutivi della tabella pivot definendoli con un esempio. Per l'illustrazione utilizzeremo la stessa tabella della sezione precedente:

• Identificatore di riga: Questo è il primo ingrediente: si tratta di una colonna selezionata dal dataset per rappresentare le righe della tabella pivot (cioè i singoli record). Nell'esempio precedente, abbiamo usato la colonna platform come identificatore di riga, quindi ogni riga conterrà i dettagli di una piattaforma.
• Identificatore di colonna: Il secondo ingrediente è una colonna che viene selezionata per essere la parte verticale della tabella pivot. Nel nostro esempio, abbiamo usato la colonna Paese come identificatore di colonna; i Paesi saranno elencati orizzontalmente, come intestazioni nella parte superiore della tabella dei risultati.
• Aggregazione: Ora che abbiamo la parte verticale e orizzontale della nostra tabella, l'aggregazione è l'ingrediente che determina come interagiscono i dati sottostanti nell'identificatore di riga e di colonna. Si usano comunemente funzioni di aggregazione come SUM(), AVG() e MAX(). Nel nostro esempio, abbiamo usato SUM() per calcolare i flussi totali per ogni piattaforma in ogni Paese.
• Tabella pivot: La tabella pivot è il piatto finale. Riassume i dati immessi facendo perno sulle righe in colonne con una funzione di aggregazione. Riassumendo efficacemente i dati, la tabella pivot ci aiuta a visualizzare ciò che stiamo analizzando in una forma più strutturata. Nell'esempio precedente, vediamo che il risultato finale dei nostri input mostra gli stream per ogni piattaforma nei Paesi elencati nella nostra tabella originale.

Tabelle pivot native

A seconda del database utilizzato, potrebbe essere disponibile una tabella pivot nativa . In sostanza, ciò significa che esiste una funzione integrata che il database utilizza per creare tabelle pivot SQL. SQL Server è un ottimo esempio di database che offre una funzione PIVOT().

Costruiamo una tabella pivot utilizzando la funzione PIVOT() di SQL Server per riassumere i dati in streaming. I dati che utilizzeremo sono memorizzati in una tabella chiamata streams_table:

Query:

SELECT platform, France, Ireland, Germany
FROM
(
    SELECT platform, country, streams
    FROM streams_table
) AS SourceTable
PIVOT
(
    SUM(streams)
    FOR country IN (France, Ireland, Germany)
) AS PivotTable;

Scomponiamo questa query. Inizieremo con la sottoquery.

1. Definire la tabella sorgente

SELECT platform, country, streams
FROM streams_table

Le righe 4-6 (subito dopo la parentesi aperta) definiscono la tabella SourceTable attraverso una sottoquery. La tabella sorgente ha tre colonne dichiarate nell'istruzione SELECT (platform, country e streams) che provengono dall'iniziale streams_table.

2. Rimodellare i dati utilizzando le operazioni pivot

SUM(streams)
FOR country IN (France, Ireland, Germany)

Successivamente, all'interno della parentesi della funzione PIVOT(), definiamo la nostra PivotTable applicando la funzione aggregata SUM() alla colonna streams. In questo modo si somma il numero di flussi per ogni piattaforma. Quindi specifichiamo su quale colonna effettuare il pivot: country.

Si noti che i valori dei Paesi sono indicati senza virgolette. Si noti anche che in SQL Server i nomi delle colonne che contengono spazi o caratteri speciali o che iniziano con un numero devono essere racchiusi tra parentesi quadre. Se avessimo una colonna contenente gli anni, il nome avrebbe questo aspetto:

SUM(streams)
FOR stream_year IN ([2022], [2023])

3. Selezione delle colonne finali

SELECT platform, France, Ireland, Germany

Infine, torniamo all'inizio della query e selezioniamo le colonne per il nostro output. Questo include la colonna platform e le nuove colonne per ogni paese.

Risultato:

Non tutti i linguaggi SQL hanno funzioni integrate in PIVOT(). Quando questa funzione non è disponibile nel database, c'è un'altra tecnica semplice da applicare per aggirare il problema. Esploriamola.

Creazione di pivot SQL senza tabelle PIVOT() native

Come già accennato, non tutti i database, compresi quelli più noti come MySQL e PostgreSQL, dispongono di funzionalità pivot native. Forse ricorderete che nella nostra introduzione abbiamo parlato della sintassi CASE WHEN. Utilizzando questa sintassi, possiamo ottenere lo stesso risultato!

Poiché MySQL e PostgreSQL sono database molto diffusi, la tecnica CASE WHEN è un metodo molto utilizzato per creare tabelle pivot. Vediamo alcuni esempi:

Esempio 1: Pivoting di dati in streaming

Utilizzeremo lo stesso esempio per dimostrare come l'approccio CASE WHEN possa produrre gli stessi risultati:

Query:

SELECT platform,
    SUM(CASE WHEN country = 'France' THEN streams END) AS france_streams,
    SUM(CASE WHEN country = 'Ireland' THEN streams END) AS ireland_streams,
    SUM(CASE WHEN country = 'Germany' THEN streams END) AS germany_streams
FROM streams_table
GROUP BY platform;

Le tabelle pivot SQL risultanti mostrano i flussi totali per piattaforma per i paesi elencati.

• Identificatore di riga: Poiché abbiamo selezionato la colonna platform, ogni piattaforma appare come una colonna nell'output. Si può notare che abbiamo incluso anche platform nel nostro GROUP BY per separare le piattaforme in gruppi.
• Identificatore di colonna: Per specificare i Paesi come colonne verticali, utilizziamo la struttura CASE WHEN country = 'France'. Se il flusso proviene dalla Francia, l'istruzione CASE WHEN restituisce il flusso che viene poi sommato con la funzione SUM(). Se il flusso non proviene dalla Francia, l'istruzione CASE WHEN restituisce NULL e i valori non vengono conteggiati nella somma e la query passa alla successiva CASE WHEN. Per una spiegazione più dettagliata, consultare l'articolo Come usare CASE WHEN in GROUP BY.
• Aggregazione: Come in precedenza, utilizziamo SUM() come funzione di aggregazione. La logica della nostra dichiarazione di caso stabilisce che i flussi vengono accumulati se il paese è uguale al paese dato; in caso contrario, non viene accumulato nulla.

Utilizziamo la stessa tabella per la dimostrazione, ma invertiamo gli identificatori di riga e colonna e vediamo cosa succede. T

Interrogazione:

SELECT country,
    SUM(CASE WHEN platform = 'Apple Music' THEN streams END) AS apple_music_streams,
    SUM(CASE WHEN platform = 'SoundCloud' THEN streams END) AS soundcloud_streams,
    SUM(CASE WHEN platform = 'Spotify' THEN streams END) AS spotify_streams
FROM streams_table
GROUP BY country;

Risultato:

Come si può vedere, il risultato dell'aggregazione rimane lo stesso. L'unico cambiamento è il modo in cui vengono mostrati i dati:

• Identificatore di riga: Questa volta utilizziamo country come identificatore di riga, in modo che ogni paese appaia come riga orizzontale nel risultato. Includiamo anche country nella clausola GROUP BY per garantire che i risultati siano raggruppati correttamente per paese, in modo da ottenere righe separate per i dati totali dello streaming di ciascun paese.
• Identificatore di colonna: Abbiamo modificato l'identificazione della colonna in piattaforma, in modo che le piattaforme appaiano come colonne verticali nell'output. Questo si ottiene con l'opzione SUM(CASE WHEN platform =...)
• Aggregazione: Come per l'ultima query, i risultati numerici sono gli stessi perché abbiamo usato lo stesso metodo di aggregazione (SUM()).

Per ulteriori esempi di CASE WHEN con l'aggregazione SUM(), consultare il nostro articolo Come usare CASE WHEN con SUM() in SQL.

Esempio 2: pivoting dei dati dei clienti

Vediamo ora come le tabelle pivot possono aiutarci a scoprire i segmenti di mercato target.

La tabella customer_table descrive i dati di vendita dei clienti con quattro colonne: customer, age_category, country e purchases:

Interrogazione:

SELECT age_category,
    SUM(CASE WHEN country = 'France' THEN purchases END) AS France,
    SUM(CASE WHEN country = 'Spain' THEN purchases END) AS Spain,
    SUM(CASE WHEN country = 'Italy' THEN purchases END) AS Italy
FROM customer_table
GROUP BY age_category;

Risultato:

Le tabelle pivot SQL risultanti mostrano gli acquisti totali per categoria di età in Francia, Spagna e Italia dove:

• L'identificatore di riga è la colonna age_category.
• L'identificatore di colonna è country, specificando France, Spain e Italy.
• La funzione aggregata è SUM().

Esempio 3: Operazioni di pivoting sui dati

Vediamo ora un esempio di come l'uso di tabelle pivot SQL possa scoprire potenziali colli di bottiglia o errori nelle operazioni.

La tabella seguente, cake_baking_datadescrive i dati del panificio utilizzando quattro colonne: order_number, stage, order_day e mins_taken:

Tabella:

Supponiamo di voler trovare il tempo medio impiegato da ogni attività in ogni giorno. Ecco la query da utilizzare.

Query:

SELECT 
    order_day,
    AVG(CASE WHEN stage = 'Mixing' THEN mins_taken END) AS avg_mixing_time,
    AVG(CASE WHEN stage = 'Baking' THEN mins_taken END) AS avg_baking_time,
    AVG(CASE WHEN stage = 'Decorating' THEN mins_taken END) AS avg_decorating_time
FROM cake_baking_data
GROUP BY order_day;

Risultato:

La tabella pivot risultante mostra i tempi medi per giorno di ordine per la miscelazione, la cottura e la decorazione, dove:

• L'identificatore di riga è la colonna order_day.
• L'identificatore di colonna è la fase, specificando mixing, baking, o decorating.
• La funzione aggregata è AVG().

Come si può vedere, il workaround di CASE WHEN consente di rimanere all'interno dei database più diffusi, MySQL e PostgreSQL, e di ottenere comunque le stesse tabelle pivot SQL.

Una parola finale sulle tabelle pivot SQL

Per riassumere, è chiaro perché le tabelle pivot SQL sono così importanti. La capacità di ruotare i dati è uno strumento importante nella cassetta degli attrezzi per l'analisi dei dati. La capacità di trasformare e riassumere in modo rapido ed efficiente gli insiemi di dati consente di scoprire le tendenze e di formulare giudizi più intelligenti.

In questo articolo ci siamo concentrati principalmente sul pivoting per gli analisti di dati. Se la carriera di analista di dati vi interessa, vi consiglio di leggere il nostro articolo 25 domande di colloquio SQL per analisti di dati. Vi aiuterà a capire cosa dovete sapere per avere successo in questo campo.

Il modo migliore per migliorare il vostro SQL è fare pratica, pratica, pratica! Vi consigliamo il nostro corso Come creare semplici report SQL per questo, non solo potrete mettere in pratica ciò che abbiamo imparato oggi, ma imparerete anche a costruire report significativi. Quindi dateci un'occhiata e continuate a migliorare le vostre abilità con le tabelle pivot SQL!