Historique de la question.
Cette exigence tire son origine des pipelines d'ingestion de données migratoires de systèmes de tableur hérités ou d'exportations de fichiers plats, où des métriques en série temporelle ou des attributs catégoriels sont dénormalisés en en-têtes de colonnes au format large (par exemple, Jan_Ventes, Fév_Ventes) plutôt qu'en lignes normalisées. De tels schémas sont prévalents dans les processus métier pilotés par Excel avant ETL dans des entrepôts relationnels, nécessitant une transformation en tables de faits étroites pour permettre l'analyse temporelle et les JOIN dimensionnels. Le défi réside dans la transposition de ces projections de colonnes statiques en flux de tuples dynamiques sans recourir à un traitement impératif ligne par ligne.
Le problème.
Les déclarations SELECT standard fixent l'identité des colonnes projetées au moment de l'analyse, empêchant une seule projection d'émettre différentes colonnes source sur différentes lignes de sortie sans corrélation LATERAL ou itération procédurale. L'objectif est de fabriquer un produit cartésien entre chaque ligne source et une table de dimension virtuelle énumérant les noms d'attributs, puis de multiplexer la valeur source correcte dans une colonne de résultat générique via une logique conditionnelle. Cela doit être accompli en utilisant uniquement la syntaxe de jointure standard et les expressions scalaires disponibles dans ANSI SQL:1999 et ultérieures.
La solution.
Utiliser un CROSS JOIN contre une table dérivée exprimée par le constructeur de ligne VALUES, qui énumère les clés catégorielles (par exemple, noms de mois) sous forme de lignes. Dans la liste SELECT, employez une expression CASE recherchée qui associe chaque clé à sa colonne source correspondante, projetant effectivement la valeur dénormalisée dans une structure de ligne normalisée. Filtrer le résultat pour exclure les valeurs NULL générées lorsque un attribut source est manquant pour une clé particulière, garantissant que la sortie finale contient uniquement des mesures valides.
SELECT s.cost_center_id, m.fiscal_month, CASE m.fiscal_month WHEN 'M01' THEN s.m01_amt WHEN 'M02' THEN s.m02_amt WHEN 'M03' THEN s.m03_amt -- ... mois supplémentaires WHEN 'M12' THEN s.m12_amt END AS amount FROM budget_wide s CROSS JOIN ( VALUES ('M01'), ('M02'), ('M03'), ('M04'), ('M05'), ('M06'), ('M07'), ('M08'), ('M09'), ('M10'), ('M11'), ('M12') ) AS m(fiscal_month) WHERE CASE m.fiscal_month WHEN 'M01' THEN s.m01_amt -- ... répéter pour tous pour éviter les NULL END IS NOT NULL;
Un service financier a exporté les allocations budgétaires de l'année fiscale depuis un modèle Excel d'entreprise vers une table de staging, où douze colonnes mensuelles (M01_Amt à M12_Amt) représentaient des périodes temporelles dénormalisées pour chaque centre de coût. L'entrepôt de données SAP cible nécessitait un schéma de table de faits étroite (CostCenter_ID, Fiscal_Month, Amount), nécessitant une transformation de dé-pivotement dans le script de chargement ANSI SQL pour éviter un traitement intermédiaire avec Python. Le volume de cinquante millions d'enregistrements a exclu toute transformation manuelle ou stratégies de chargement par multiples passes.
Solution 1 : Union All par colonne.
L'approche initiale utilisait douze requêtes SELECT séparées, chacune projetant une colonne de mois différente codée en dur dans les colonnes génériques Amount et Month_Name, combinées via UNION ALL. Pros : Cette méthode bénéficie d'une compatibilité universelle, fonctionnant sur les bases de données mainframe héritées et les anciens moteurs SQL qui manquent de syntaxe de jointure moderne. Cons : Elle effectue douze analyses complètes de la table sur les données source, entraînant une dégradation de l'E/S linéaire ; le plan de requête devient volumineux et difficile à mettre en cache, et toute modification de schéma (ajout d'une treizième période) nécessite de modifier douze listes de projection séparées.
Solution 2 : Génération SQL dynamique.
Une alternative consistait à construire le texte de la requête de manière dynamique dans une couche d'application en itérant sur les tables de métadonnées pour générer les branches CASE nécessaires ou les bras UNION à l'exécution. Pros : Cela offre une flexibilité face aux schémas évolutifs et réduit le travail manuel d'auteur SQL lors de la gestion de centaines de colonnes. Cons : Cela viole l'interdiction de la logique procédurale ; cela introduit des vecteurs d'attaque par injection SQL et un surcoût de compilation, et l'instruction résultante ne peut pas être encapsulée dans une vue de base de données statique ou une définition de procédure stockée.
Solution 3 : Cross Join avec Values.
L'implémentation acceptée a utilisé un CROSS JOIN avec un constructeur VALUES définissant les douze périodes fiscales, multiplexant le bon montant via une expression CASE identifiée virtuellement sur l'identifiant de période. Pros : Elle s'exécute en un seul passage sur la table source, exploite des algorithmes de jointure efficaces, et est entièrement déclarative et portable à travers Oracle, SQL Server, PostgreSQL et Db2 sans indices spécifiques au fournisseur. Cons : Elle nécessite le support de SQL:1999 pour les constructeurs de ligne, indisponible sur les systèmes anciens, et la verbosité de l'expression CASE augmente le surcoût de maintenance à moins d'être générée via des modèles.
Résultat.
La latence de transformation est réduite de vingt-cinq minutes à moins de quatre-vingt-dix secondes en éliminant les analyses de table redondantes inhérentes au modèle UNION ALL. Le processus de chargement est devenu résilient aux extensions de schéma, nécessitant seulement l'ajout d'une ligne au constructeur VALUES lors de l'introduction de nouvelles périodes fiscales. De plus, la logique a été encapsulée dans une vue standard, permettant un interrogation directe ad-hoc par les utilisateurs de Tableau sans étapes ETL intermédiaires.
Comment évitez-vous que les valeurs NULL dans les colonnes source n'apparaissent comme des lignes dans le résultat dé-pivoté sans provoquer l'évaluation de l'expression CASE deux fois dans le plan d'exécution ?
Les candidats intègrent souvent l'expression CASE à l'intérieur d'un prédicat de clause WHERE tel que WHERE CASE ... END IS NOT NULL, ce qui oblige l'optimiseur à calculer la projection deux fois - une fois pour le filtrage et une fois pour la sortie. Le modèle efficace ANSI SQL matérialise le résultat à l'intérieur d'une table dérivée ou d'une Common Table Expression (CTE) : SELECT * FROM (SELECT ..., CASE ... END AS val FROM ... CROSS JOIN ...) sub WHERE val IS NOT NULL. Cela calcule le CASE une fois, filtre les lignes, et maintient une séparation claire des préoccupations pour l'optimiseur de requêtes.
Lors du dé-pivotement de colonnes avec des types de données hétérogènes (par exemple, une colonne de commentaire VARCHAR à côté d'une colonne de montant DECIMAL), quelle stratégie de casting ANSI SQL spécifique garantit la cohérence de type dans la colonne de valeur de résultat unique sans perte de données ?
De nombreux candidats comptent incorrectement sur la conversion de type implicite, qui peut tronquer les chaînes ou perdre la précision décimale, ou ils tentent UNION ALL sans réaliser que les règles de coercition de type varient selon la plate-forme. La solution robuste consiste à caster explicitement chaque colonne source vers un supertype commun - généralement VARCHAR - dans chaque branche WHEN de l'expression CASE : CASE WHEN key='Comment' THEN CAST(text_col AS VARCHAR(500)) ELSE CAST(num_col AS VARCHAR(500)) END. Cela garantit que toutes les valeurs retournées partagent un seul type de données compatible avec la définition de la colonne des résultats, préservant la représentation textuelle des données numériques si nécessaire.
Pourquoi l'approche CROSS JOIN avec VALUES semble-t-elle superficiellement créer une explosion de produit cartésien, et comment l'optimiseur atténue-t-il cela par rapport au comportement d'élimination NULL d'un opérateur UNPIVOT natif ?
Le CROSS JOIN génère logiquement M×N lignes (lignes source multipliées par le nombre d'attributs) avant le filtrage, ce que les candidats craignent de dégrader les performances sur de grands ensembles de données. Cependant, les optimisateurs modernes basés sur les coûts reconnaissent la dépendance des données de l'expression CASE sur la petite table constante et transforment souvent le plan en une simple projection ou un opérateur physique UNPIVOT en interne, évitant ainsi la multiplication réelle des lignes. Contrairement à UNPIVOT natif, qui élimine généralement automatiquement les résultats NULL, cette méthode nécessite une clause WHERE explicite pour rejeter les lignes où l'attribut source était NULL, sinon l'ensemble de résultats contient des faits vides spuriés qui corrompent les calculs d'agrégation en aval.