Trasformare i dati con denormalizzazione e pivot

La ridenozione della struttura dei dati è una parte fondamentale della creazione di soluzioni di analisi efficaci. La denormalizzazione, la pivoting e l'unpivot sono tecniche di trasformazione che consentono di riorganizzare i dati tra formati normalizzati e aggregati, ognuno dei quali serve diverse esigenze di analisi e modellazione.

In questa lezione si apprenderà come appiattire le tabelle normalizzate per query più veloci, ruotare le righe in colonne per l'analisi incrociata e invertire tale processo quando è necessario normalizzare set di dati ampi.

Capire la denormalizzazione

I database normalizzati riducono la ridondanza dei dati suddividendo le informazioni tra più tabelle correlate. Sebbene questa progettazione funzioni bene per i sistemi transazionali, crea sfide per l'analisi. Le query devono unire molte tabelle, rallentando le prestazioni e aumentando la complessità.

La denormalizzazione risolve questi problemi appiattindo le tabelle correlate in tabelle meno ampie. Nelle tabelle dei fatti, i dati vengono combinati direttamente dalle tabelle delle dimensioni, eliminando la necessità di utilizzare i join durante la fase di query.

Si consideri un database di vendita con tabelle separate per ordini, clienti e prodotti. Una query normalizzata richiede più join:

-- Normalized query with multiple joins
SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.region,
       p.product_name,
       p.category,
       o.quantity,
       o.total_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

Una tabella denormalizzata archivia queste informazioni insieme:

-- Create a denormalized orders table
CREATE OR REPLACE TABLE sales.orders_denormalized AS
SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.region,
       p.product_name,
       p.category,
       o.quantity,
       o.total_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

Le query sulla tabella denormalizzata vengono eseguite più velocemente perché non sono necessari join. Questo approccio è comune quando si creano schemi star e data mart per gli strumenti di business intelligence. Il compromesso è l'aumento dello spazio di archiviazione e la necessità di aggiornare le tabelle denormalizzate quando cambiano i dati di origine.

Trasformare le righe in colonne

Il pivot trasforma valori univoci da una colonna in colonne separate, convertendo un formato di tabella "long" in uno "wide". Questa trasformazione è particolarmente utile quando si preparano i dati per report o dashboard incrociati.

La clausola PIVOT richiede una funzione di aggregazione perché più righe sorgente potrebbero essere mappate sulla stessa cella nel risultato pivotato. Specifica i valori di colonna che diventano nuovi nomi di colonna usando le clausole FOR e IN.

Prendere in considerazione i dati relativi alle vendite trimestrali archiviati in un formato lungo:

-- Create sample sales data
CREATE OR REPLACE TEMP VIEW quarterly_sales(year, quarter, region, revenue) AS
VALUES (2024, 1, 'East', 150000),
       (2024, 2, 'East', 175000),
       (2024, 3, 'East', 160000),
       (2024, 4, 'East', 200000),
       (2024, 1, 'West', 180000),
       (2024, 2, 'West', 165000),
       (2024, 3, 'West', 190000),
       (2024, 4, 'West', 210000),
       (2025, 1, 'East', 165000),
       (2025, 2, 'East', 185000);

-- View data in long format
SELECT * FROM quarterly_sales;

Questa query restituisce i ricavi come righe separate per ogni trimestre. Per confrontare i trimestri affiancati, ruotare i dati:

-- Pivot quarters into columns
SELECT year, region, Q1, Q2, Q3, Q4
FROM quarterly_sales
PIVOT (
  SUM(revenue)
  FOR quarter IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4)
);

Il risultato mostra i ricavi trimestrali di ogni area come colonne, rendendo semplici i confronti annuali nell'anno:

È possibile applicare più funzioni di aggregazione in una singola operazione pivot:

-- Pivot with multiple aggregations
SELECT year, Q1_total, Q1_avg, Q2_total, Q2_avg
FROM (SELECT year, quarter, revenue FROM quarterly_sales)
PIVOT (
  SUM(revenue) AS total,
  AVG(revenue) AS avg
  FOR quarter IN (1 AS Q1, 2 AS Q2)
);

In questo modo vengono create colonne sia per la somma che per la media dei ricavi di ogni trimestre.

Annullare ilpivot delle colonne in righe

L'unpivoting esegue la trasformazione inversa: converte nuovamente le colonne in righe. Usare l'unpivoting quando si ricevono dati in formato ampio che richiedono la normalizzazione per l'analisi o quando si preparano i dati per i sistemi che richiedono un formato lungo.

La UNPIVOT clausola trasforma le colonne specificate in coppie nome-valore. Si definisce una colonna per contenere i valori e un'altra per contenere i nomi delle colonne originali.

-- Create wide-format data
CREATE OR REPLACE TEMP VIEW regional_targets(region, jan, feb, mar, apr) AS
VALUES ('North', 50000, 55000, 52000, 58000),
       ('South', 45000, 48000, 51000, 53000),
       ('East',  60000, 62000, 65000, 68000);

-- Unpivot monthly columns into rows
SELECT *
FROM regional_targets
UNPIVOT (
  target_amount FOR month IN (jan, feb, mar, apr)
);

Il risultato converte ogni colonna del mese in righe:

Per impostazione predefinita, UNPIVOT esclude i valori Null. Per includerli, specificare INCLUDE NULLS:

-- Unpivot while keeping null values
SELECT *
FROM regional_targets
UNPIVOT INCLUDE NULLS (
  target_amount FOR month IN (jan, feb, mar, apr)
);

È anche possibile assegnare alias personalizzati ai nomi di colonna non pivotate:

-- Unpivot with custom month labels
SELECT *
FROM regional_targets
UNPIVOT (
  target_amount FOR month IN (
    jan AS `January`,
    feb AS `February`,
    mar AS `March`,
    apr AS `April`
  )
);

Scegliere la trasformazione corretta

Ogni trasformazione serve specifiche esigenze analitiche. Usare la denormalizzazione quando sono necessarie prestazioni di query veloci per dashboard e report. Scegliere la funzione "pivot" quando gli analisti necessitano di confronti paralleli tra le categorie. Applicare il processo di "unpivot" quando è necessario normalizzare i dati ampi per le pipeline di aggregazione o apprendimento automatico.

Queste trasformazioni spesso interagiscono tra loro nelle pipeline di dati. È possibile trasformare i dati di origine tramite un'operazione di unpivot per ristrutturare il formato, applicare trasformazioni e quindi eseguire un pivot per la presentazione finale, o denormalizzare l'output in tabelle ottimizzate per gli utenti finali.