Transformer des données avec la dénormalisation et les pivots

La réorganisation de la structure des données est un élément clé de la création de solutions d’analytique efficaces. La dénormalisation, la pivotation et l’annulation de la transformation sont des techniques de transformation qui vous aident à réorganiser les données entre les formats normalisés et agrégés, chacun servant des besoins analytiques et de modélisation différents.

Dans cette unité, vous allez apprendre à aplatir les tables normalisées pour des requêtes plus rapides, à faire pivoter des lignes en colonnes pour l’analyse entre tabulaires et à inverser ce processus lorsque vous devez normaliser des jeux de données larges.

Comprendre la dénormalisation

Les bases de données normalisées réduisent la redondance des données en fractionnant les informations entre plusieurs tables associées. Bien que cette conception fonctionne bien pour les systèmes transactionnels, elle crée des défis pour l’analytique. Les requêtes doivent joindre de nombreuses tables, ce qui ralentit les performances et augmente la complexité.

La dénormalisation répond à ces défis en aplatissant des tables associées en un nombre réduit de tables plus larges. Vous combinez des données de tables de dimension directement dans des tables de faits, éliminant ainsi la nécessité de jointures au moment de la requête.

Considérez une base de données des ventes avec des tables distinctes pour les commandes, les clients et les produits. Une requête normalisée nécessite plusieurs jointures :

-- 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;

Une table dénormalisée stocke ces informations ensemble :

-- 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;

Les requêtes sur la table dénormalisée s’exécutent plus rapidement, car aucune jointure n’est requise. Cette approche est courante lors de la création de schémas en étoile et de data marts pour les outils décisionnels. Le compromis est un stockage accru et la nécessité de rafraîchir les tables dénormalisées lorsque les données source changent.

Pivoter des lignes dans des colonnes

Le pivotage transforme des valeurs uniques d’une colonne en colonnes distinctes, convertissant un format de table « long » en un format « large ». Cette transformation est particulièrement utile lors de la préparation des données pour les rapports ou tableaux de bord croisés tabulaires.

La PIVOT clause nécessite une fonction d’agrégation, car plusieurs lignes sources peuvent être mappées à la même cellule dans le résultat pivoté. Vous spécifiez quelles valeurs de colonne deviennent de nouveaux noms de colonnes à l'aide des clauses FOR et IN.

Considérez les données de ventes trimestrielles stockées dans un format long :

-- 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;

Cette requête retourne les revenus sous forme de lignes distinctes pour chaque trimestre. Pour comparer les trimestres côte à côte, pivotez les données :

-- 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)
);

Le résultat montre le chiffre d’affaires trimestriel de chaque région sous forme de colonnes, ce qui rend les comparaisons annuelles simples :

Vous pouvez appliquer plusieurs fonctions d’agrégation lors d'une seule opération de 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)
);

Cela crée des colonnes pour la somme et la moyenne du chiffre d’affaires de chaque trimestre.

Dépivoter des colonnes en lignes

La dépivotisation effectue la transformation inverse : elle convertit les colonnes en lignes. Utilisez unpivoting lorsque vous recevez des données de format large qui nécessitent une normalisation pour l’analyse ou lors de la préparation des données pour les systèmes qui attendent un format long.

La UNPIVOT clause transforme les colonnes spécifiées en paires nom-valeur. Vous définissez une colonne pour contenir les valeurs et une autre pour contenir les noms de colonnes d’origine.

-- 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)
);

Le résultat convertit chaque colonne de mois en lignes :

Par défaut, UNPIVOT exclut les valeurs Null. Pour les inclure, spécifiez INCLUDE NULLS:

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

Vous pouvez également affecter des alias personnalisés aux noms de colonnes dé-pivotées :

-- 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`
  )
);

Choisir la transformation appropriée

Chaque transformation répond à des besoins analytiques spécifiques. Utilisez la dénormalisation lorsque vous avez besoin de performances de requête rapides pour les tableaux de bord et les rapports. Choisissez le pivotement lorsque les analystes ont besoin de comparaisons côte à côte entre catégories. Appliquez unpivot lorsque vous devez normaliser les données étendues pour les pipelines d’agrégation ou de Machine Learning.

Ces transformations fonctionnent souvent ensemble dans des pipelines de données. Vous pouvez dépivoter les données sources pour normaliser leur format, appliquer des transformations, puis pivoter les résultats pour la présentation finale, ou dénormaliser la sortie sous forme de tables optimisées pour les consommateurs en aval.