UNPIVOT : transformation de colonnes en valeurs avec avantages et pièges de performance

Une seule instruction UNPIVOT peut transformer trois colonnes en trois lignes. Semble simple, mais si votre tableau contient 10 colonnes à pivoter, la base de données effectuera 10 balayages complets du tableau — êtes-vous certain de vouloir procéder ainsi ?

Introduction : qu'est-ce que l'opération UNPIVOT ?

Dans le développement de rapports et le nettoyage de données en base de données, nous devons fréquemment convertir un tableau "aplati" (largeur importante) en un tableau "long" (nombre de lignes élevé).

Données initiales (tableau large) :

Exigence métier (tableau long) :

Cette opération qui transforme les "noms de colonnes" en "valeurs de colonne" tout en augmentant le nombre de lignes s'appelle l'oppération UNPIVOT. La base de données KingbaseES fournit l'opérateur UNPIVOT spécifiquement pour répondre à ce besoin.

I. Mécanisme fondamental de UNPIVOT

1.1 Processus d'exécution

Lorsque KingbaseES traite une commande UNPIVOT, il suit les étapes logiques suivantes :

1. Définir la liste des colonnes à pivoter - spécifier les colonnes à transformer (comme chimie, bio), ces colonnes doivent avoir des types de données compatibles
2. Conserver les colonnes non pivotées - les autres colonnes (comme etudiant) verront leurs valeurs dupliquées dans chaque ligne générée
3. Créer de nouvelles colonnes - une colonne pour les noms et une pour les valeurs correspondantes

1.2 Exemple de syntaxe

SELECT * FROM notes_tableau
UNPIVOT (
    note FOR matiere IN (chimie, bio)
) AS alias_unpivot;

Points clés :

• note : nouvelle colonne de valeurs, contenant les valeurs initiales
• matiere : nouvelle colonne de noms, contenant les noms de colonnes originaux ('chimie', 'bio')
• Un alias explicite est obligatoire pour la table UNPIVOT (AS alias_unpivot)
• Les colonnes participant à l'opération UNPIVOT doivent avoir des types de données compatibles

II. Équivalente de UNPIVOT : UNION ALL

Une manière intuitive de comprendre UNPIVOT est de le considérer comme un sucre syntaxique pour plusieurs instructions SELECT connectées par UNION ALL :

-- Équivalent de UNPIVOT
SELECT etudiant, 'chimie' AS matiere, chimie AS note FROM notes_tableau
UNION ALL
SELECT etudiant, 'bio' AS matiere, bio AS note FROM notes_tableau;

Cela signifie : KingbaseES exécute l'opération UNPIVOT en suivant la logique de réécriture UNION ALL. Chaque colonne à transformer correspond à un balayage indépendant.

III. Pièges de performance : pourquoi UNPIVOT est risqué avec de grands volumes de données ?

3.1 Coût des multiples balayages

Si la source contient 10 colonnes à pivoter, cela signifie 10 balayages complets du tableau.

Pour un tableau de 100 millions de lignes, même avec des balayages efficaces, l'accumulation d'E/S pour 10 balayages est considérable. C'est le goulot d'étranglement le plus critique de UNPIVOT lors du traitement de grands ensembles de données.

3.2 Conditions de filtrage répétées

Si la requête source contient des conditions de filtrage complexes, l'utilisation directe de UNPIVOT peut entraîner l'exécution répétée de ces conditions, augmentant la pression d'E/S.

-- Écriture dangereuse : les conditions complexes seront exécutées N fois
SELECT * FROM (
    SELECT etudiant, chimie, bio, physique, mathematiques
    FROM notes_tableau
    WHERE annee = 2024
      AND ecole_id IN (SELECT id FROM ecoles WHERE region = 'Europe')
) t
UNPIVOT (note FOR matiere IN (chimie, bio, physique, mathematiques)) AS ua;

Ici, il y a 5 colonnes pivotées, et les conditions WHERE complexes seront exécutées 5 fois.

IV. Stratégies d'optimisation : utilisation des CTE pour consolider les résultats intermédiaires

4.1 Préfiltrage avec CTE

Encapsuler la source et ses conditions de filtrage dans une CTE (expression de table commune) :

WITH notes_filtrees AS (
    SELECT etudiant, chimie, bio, physique, mathematiques
    FROM notes_tableau
    WHERE annee = 2024
      AND ecole_id IN (SELECT id FROM ecoles WHERE region = 'Europe')
)
SELECT * FROM notes_filtrees
UNPIVOT (note FOR matiere IN (chimie, bio, physique, mathematiques)) AS ua;

Principe d'optimisation : La CTE consolide d'abord l'ensemble de résultats filtré, les multiples balayages ultérieurs ne porteront que sur cet ensemble intermédiaire plus petit, plutôt que de répéter des balayages complets de la source.

4.2 Recommandations d'experts

1. Contrôler le nombre de colonnes pivotées - plus il y a de colonnes, plus le nombre de balayages est élevé. Ne pas dépasser 10 colonnes est une bonne pratique.
2. Assurer une correspondance stricte des types de données - éviter les conversions implicites qui augmentent la charge CPU.
3. Pour les très grands volumes, utiliser un ETL - pour les ensembles de données de plus de 100 millions de lignes, il est préférable d'effectuer l'opération UNPIVOT lors de l'étape ETL plutôt qu'à l'exécution de la requête.

V. Relation entre UNPIVOT et PIVOT : avertissement sur l'irréversibilité

Un piège souvent négligé : les données transformées par PIVOT ne peuvent pas être restaurées complètement par UNPIVOT.

La raison réside dans la nature réductrice des opérations d'agrégation. Si Martin a deux notes en chimie (85 et 90), SUM(note) les combinera en 175. UNPIVOT ne peut pas décomposer cette valeur de 175 en deux enregistrements originaux.

Si votre métier nécessite des conversions bidirectionnelles (ligne-colonne + colonne-ligne), évaluez attentivement le choix des fonctions d'agrégation ou conservez suffisamment d'informations d'identification avant l'opération PIVOT.

VI. Résumé des meilleures pratiques

1. UNPIVOT est un outil puissant pour transformer des colonnes en valeurs - il simplifie considérablement l'écriture des rapports SQL, mais nécessite que les colonnes participant à la conversion aient des types compatibles.
2. Soyez vigilant face aux multiples balayages - chaque colonne pivotée correspond à un balayage. 10 colonnes = 10 balayages.
3. Utilisez toujours des CTE pour le préfiltrage - encapsulez les conditions de filtrage dans une CTE pour éviter les balayages complets répétés.
4. Pour les très grands volumes, utilisez un ETL - effectuer UNPIVOT à l'exécution de la requête n'est pas rentable.
5. PIVOT et UNPIVOT ne sont pas complètement inverses - l'agrégation réduit la dimension et perd les informations d'identification des lignes.

Conclusion

UNPIVOT est un outil efficace pour transformer des colonnes en lignes, mais ce n'est pas un repas gratuit. Comprendre sa logique de balayage basée sur UNION ALL vous permettra de prendre des décisions de performance appropriées : pour les petits volumes, utilisez-le directement ; pour les volumes moyens, consolidez avec des CTE ; pour les très grands volumes, confiez le traitement à un ETL.

Cet article est basé sur la base de données KingbaseES V9.