Trasformare le colonne in righe

L'unpivoting è l'immagine speculare del pivoting: si prende una tabella larga e si trasformano nuovamente le sue colonne in righe. Gli intervistatori lo chiedono quando i dati arrivano in formato da foglio di calcolo, ma devono essere normalizzati per l'analisi.

Esempio: una tabella con le colonne q1, q2, q3, q4 per ogni regione deve diventare un insieme di righe (region, quarter, amount). È questo formato lungo che aggregazioni, join e grafici preferiscono.

La risposta indipendente dal dialetto è UNION ALL: scrivete una SELECT per ogni colonna sorgente, producendo ogni volta un'etichetta letterale e il valore di quella colonna.

Usate UNION ALL, non UNION, così non sostenete il costo dell'eliminazione dei duplicati e conservate ogni riga, anche quando due celle condividono lo stesso valore.

È una classica trappola da colloquio. UNION rimuove le righe duplicate dall'intero risultato. Se East e West avessero entrambe 100 per il Q1, un semplice UNION unirebbe le righe identiche e perdereste dei dati.

UNION ALL concatena senza eliminare i duplicati, proprio ciò che serve per l'unpivoting. È anche più veloce, perché non richiede un ordinamento o un hash per eliminare i duplicati.

Ogni ramo di UNION ALL deve produrre lo stesso numero di colonne, con tipi compatibili e nello stesso ordine. I nomi delle colonne vengono dalla prima SELECT.

Se le colonne della tabella larga differiscono per tipo (ad esempio una è int e un'altra decimal), il motore sceglie un tipo comune. Se sono realmente incompatibili, eseguite un cast esplicito per evitare che l'unione fallisca.

SQL Server dispone dell'operatore dedicato UNPIVOT, più conciso di UNION ALL. Indicate la nuova colonna dei valori, la nuova colonna delle etichette ed elencate le colonne sorgente da riunire.

Un comportamento importante: UNPIVOT elimina le righe in cui il valore è NULL. Gli intervistatori verificano se conoscete questo effetto collaterale.

Se una regione ha NULL in q3, UNPIVOT di SQL Server omette semplicemente quella riga dall'output. Se vi serve una riga per ogni colonna, indipendentemente dai NULL, ricorrete a UNION ALL, che li conserva.

Esplicitate questo compromesso durante un colloquio: l'UNPIVOT nativo è conciso ma perde i NULL; UNION ALL è prolisso ma completo.

PostgreSQL non dispone di UNPIVOT, ma offre un idioma ordinato basato su CROSS JOIN LATERAL e un elenco VALUES. Ogni riga larga viene espansa tramite una piccola tabella inline di coppie (etichetta, valore).

È più pulito di un lungo UNION ALL e legge la tabella sorgente una sola volta.

È utile sollevare un aspetto legato alle prestazioni: il semplice UNION ALL esegue una scansione della tabella larga per ogni ramo (quattro scansioni per quattro trimestri). La forma LATERAL VALUES e UNPIVOT di SQL Server leggono invece la sorgente una sola volta.

Su tabelle di grandi dimensioni questo è importante. Se dovete usare UNION ALL, un ottimizzatore potrebbe comunque eseguire scansioni ripetute; menzionate quindi LATERAL o UNPIVOT come opzioni più efficienti.

Con UNION ALL o LATERAL conservate le righe con valori NULL. Se la domanda richiede solo le celle valorizzate, aggiungete un filtro. In questo modo imitate ciò che SQL Server UNPIVOT fa automaticamente.

Decidere se conservare o eliminare i NULL dipende dal contesto; chiarite quindi il requisito con l'intervistatore prima di scrivere il codice.

Un caso comune successivo: «dalla tabella trimestrale in formato largo, restituisca il ricavo totale per regione su tutti i trimestri». Una volta convertiti i dati in formato lungo con l'unpivot, l'aggregazione è banale: un singolo SUM raggruppato per regione.

Questo dimostra il vero motivo per cui conviene eseguire prima l'unpivot. Sommare quattro colonne separate è fragile, mentre un SUM(amount) GROUP BY region in formato lungo si adatta a qualsiasi numero di trimestri.

Riconosca il segnale che indica la necessità di un unpivot in un problema descritto a parole:

• L'input contiene colonne ripetute che in realtà rappresentano valori (mesi, anni, metriche).
• È necessario aggregare, unire o creare grafici in base a tali valori.
• Si desidera normalizzare, durante l'importazione, i dati de-normalizzati di un foglio di calcolo.

Il formato lungo è quasi sempre la struttura giusta per il lavoro SQL successivo, quindi l'unpivot è spesso il primo passaggio.

Verifichi di conoscere l'insidia più comune dell'unpivot.

L'unpivot trasforma le colonne in righe:

• Portabile: un SELECT per colonna, unito con UNION ALL (mai con UNION semplice).
• SQL Server: UNPIVOT nativo, conciso ma elimina i valori NULL.
• Postgres: CROSS JOIN LATERAL (VALUES ...), con una sola scansione.
• Allinei il numero e i tipi delle colonne tra i vari rami; filtri i NULL se la domanda lo richiede.