MaxCompute:Pivotement et dépivotement manuels en SQL

Fonctionnement du pivotement et du dépivotement

Le schéma suivant illustre les concepts de pivotement et de dépivotement.

• Pivotement (lignes vers colonnes) : Transforme un tableau au format long en un format large. Il fait pivoter les valeurs d'une seule colonne (par exemple, subject) vers plusieurs nouvelles colonnes.

• Dépivotement (colonnes vers lignes) : Transforme un tableau au format large en un format long. Il regroupe plusieurs colonnes (par exemple, chinese, mathematics) en une seule colonne de valeurs, en utilisant une nouvelle colonne pour identifier leur source d'origine.

Exemple de données

Les exemples de cette rubrique utilisent les deux tables suivantes.

• Créez la table source rowtocolumn et insérez des données pour les exemples de pivotement.

  CREATE TABLE rowtocolumn (name string, subject string, result bigint);
  INSERT INTO TABLE rowtocolumn VALUES
  ('Bob' , 'chinese' , 74),
  ('Bob' , 'mathematics' , 83),
  ('Bob' , 'physics' , 93),
  ('Alice' , 'chinese' , 74),
  ('Alice' , 'mathematics' , 84),
  ('Alice' , 'physics' , 94);

  Interrogez la table rowtocolumn :

  SELECT * FROM rowtocolumn;
  
  -- Result:
  +------------+-------------+------------+
  | name       | subject     | result     |
  +------------+-------------+------------+
  | Alice      | chinese     | 74         |
  | Alice      | mathematics | 83         |
  | Alice      | physics     | 93         |
  | Bob        | chinese     | 74         |
  | Bob        | mathematics | 84         |
  | Bob        | physics     | 94         |
  +------------+-------------+------------+

• Créez la table source columntorow et insérez des données pour les exemples de dépivotement.

  CREATE TABLE columntorow (name string, chinese bigint, mathematics bigint, physics bigint);
  INSERT INTO TABLE columntorow VALUES
  ('Bob' , 74, 83, 93),
  ('Alice', 74, 84, 94);

  Interrogez la table columntorow :

  SELECT * FROM columntorow;
  
  -- Result:
  +------------+------------+-------------+------------+
  | name       | chinese    | mathematics | physics    |
  +------------+------------+-------------+------------+
  | Bob        | 74         | 83          | 93         |
  | Alice      | 74         | 84          | 94         |
  +------------+------------+-------------+------------+

Pivotement à l'aide de CASE WHEN ou de fonctions intégrées

Les méthodes suivantes permettent de pivoter les données des lignes vers les colonnes.

• Méthode 1 : CASE WHEN (standard SQL) Cette méthode utilise l'agrégation conditionnelle. Une expression CASE WHEN extrait la note pour chaque matière, et une fonction d'agrégation telle que MAX() consolide les résultats en une seule ligne par étudiant.

  Cas d'utilisation :

  • La portabilité est prioritaire : la logique SQL standard fonctionne sur presque tous les systèmes de base de données.

  • La lisibilité est essentielle : la logique est explicite et facile à suivre pour tout développeur SQL.

  • Le nombre de colonnes de pivotement est fixe et gérable.

  Inconvénients :

  • Devient verbeux et difficile à maintenir lors du pivotement vers un grand nombre de colonnes.

  SELECT
      name,
      MAX(CASE subject WHEN 'chinese' THEN result END) AS chinese,
      MAX(CASE subject WHEN 'mathematics' THEN result END) AS mathematics,
      MAX(CASE subject WHEN 'physics' THEN result END) AS physics
  FROM rowtocolumn
  GROUP BY name;

  Résultat :

  +--------+------------+-------------+------------+
  | name   | chinese    | mathematics | physics    |
  +--------+------------+-------------+------------+
  | Bob    | 74         | 83          | 93         |
  | Alice  | 74         | 84          | 94         |
  +--------+------------+-------------+------------+

• Méthode 2 : Fonctions intégrées (spécifiques à MaxCompute) Cette méthode utilise d'abord CONCAT et WM_CONCAT pour agréger les matières et les notes en une seule chaîne clé-valeur par étudiant. La fonction KEYVALUE analyse ensuite cette chaîne pour extraire la note de chaque matière dans une colonne distincte.

  Cas d'utilisation :

  • La concision du code est prioritaire, en particulier avec de nombreuses valeurs de pivotement.

  • Vous travaillez dans l'écosystème MaxCompute et n'avez pas besoin de compatibilité multiplateforme.

  Inconvénients :

  • Verrouillage de la plateforme : le code n'est pas portable vers d'autres bases de données SQL.

  • Goulot d'étranglement potentiel au niveau des performances : la manipulation de chaînes sur des ensembles de données très volumineux peut être moins efficace que l'agrégation directe.

  SELECT
      name,
      KEYVALUE(key_value_string, 'chinese') AS chinese,
      KEYVALUE(key_value_string, 'mathematics') AS mathematics,
      KEYVALUE(key_value_string, 'physics') AS physics
  FROM (
      SELECT
          name,
          WM_CONCAT(';', CONCAT(subject, ':', result)) AS key_value_string
      FROM rowtocolumn
      GROUP BY name
  ) AS source_with_concat;

  Résultat :

  +--------+------------+-------------+------------+
  | name   | chinese    | mathematics | physics    |
  +--------+------------+-------------+------------+
  | Bob    | 74         | 83          | 93         |
  | Alice  | 74         | 84          | 94         |
  +--------+------------+-------------+------------+

Pour les transformations de données qui étendent une seule ligne en plusieurs lignes, utilisez Lateral View avec des fonctions telles que EXPLODE , INLINE ou TRANS_ARRAY .

Dépivotement à l'aide de UNION ALL ou de fonctions intégrées

Les méthodes suivantes permettent de dépivoter les données des colonnes vers les lignes.

• Méthode 1 : UNION ALL (standard SQL) Cette méthode combine plusieurs instructions SELECT avec UNION ALL, où chaque instruction récupère une colonne de matière différente. Cela transforme efficacement les colonnes de matière (chinese, mathematics, physics) en une seule colonne subject.

  Cas d'utilisation :

  • L'exactitude et la portabilité sont primordiales : il s'agit de la méthode la plus fiable et universellement compatible, qui gère les valeurs NULL correctement sans logique supplémentaire.

  • La clarté prime sur la performance : l'intention du code est immédiatement claire, même si ce n'est pas l'option la plus performante.

  Inconvénients :

  • Pénalité de performance sur les grandes tables : l'analyse multiple d'une grande table peut être très inefficace dans un environnement distribué.

  • Verbosité : la longueur du code augmente linéairement avec le nombre de colonnes à dépivoter.

  SELECT name, subject, result
  FROM (
      SELECT name, 'chinese' AS subject, chinese AS result FROM columntorow
      UNION ALL
      SELECT name, 'mathematics' AS subject, mathematics AS result FROM columntorow
      UNION ALL
      SELECT name, 'physics' AS subject, physics AS result FROM columntorow
  ) unpivoted_data
  ORDER BY name;

  Résultat :

  +--------+-------------+------------+
  | name   | subject     | result     |
  +--------+-------------+------------+
  | Bob    | chinese     | 74         |
  | Bob    | mathematics | 83         |
  | Bob    | physics     | 93         |
  | Alice  | chinese     | 74         |
  | Alice  | mathematics | 84         |
  | Alice  | physics     | 94         |
  +--------+-------------+------------+

• Méthode 2 : Fonctions intégrées (spécifiques à MaxCompute) Cette méthode utilise CONCAT pour créer une chaîne délimitée à partir des colonnes de matière. TRANS_ARRAY et SPLIT_PART éclatent ensuite cette chaîne en plusieurs lignes et analysent la matière et la note correspondantes.

  Cas d'utilisation :

  • La performance est critique sur les grandes tables : cette approche évite les analyses multiples de la table.

  • Vous êtes à l'aise avec la syntaxe et les fonctions spécifiques à MaxCompute.

  Inconvénients :

  • Verrouillage de la plateforme : le code est hautement spécifique à MaxCompute et n'est pas portable.

  • Syntaxe complexe : la combinaison de TRANS_ARRAY et de sous-requêtes imbriquées peut être plus difficile à lire et à déboguer que UNION ALL.

  Remarque

  Cette méthode repose sur CONCAT. Si une colonne source (chinese, mathematics ou physics) contient une valeur NULL, la chaîne concaténée entière devient NULL, ce qui entraîne une perte silencieuse de données pour cette ligne. Enveloppez les colonnes dans NVL() avant la concaténation — par exemple, NVL(chinese, 0) — ou utilisez plutôt la méthode UNION ALL. Pour plus d'informations, consultez NVL.

  SELECT name,
         SPLIT_PART(subject,':',1) AS subject,
         SPLIT_PART(subject,':',2) AS result
  FROM (
         SELECT TRANS_ARRAY(1,';',name,subject) AS (name,subject)
         FROM (
              SELECT name,
                     CONCAT('chinese',':',chinese,';','mathematics',':',mathematics,';','physics',':',physics) AS subject
              FROM columntorow )
  );

  Résultat :

  +--------+-------------+------------+
  | name   | subject     | result     |
  +--------+-------------+------------+
  | Bob    | chinese     | 74         |
  | Bob    | mathematics | 83         |
  | Bob    | physics     | 93         |
  | Alice  | chinese     | 74         |
  | Alice  | mathematics | 84         |
  | Alice  | physics     | 94         |
  +--------+-------------+------------+

Étapes suivantes

MaxCompute fournit également des opérateurs natifs PIVOT et UNPIVOT qui offrent une syntaxe plus concise et souvent plus efficace pour ces transformations. Pour plus d'informations, consultez PIVOT and UNPIVOT.