Utilisation et exemples des mots-clés PIVOT et UNPIVOT - MaxCompute

MaxCompute prend en charge les mots-clés PIVOT et UNPIVOT. Utilisez le mot-clé PIVOT pour transformer une ou plusieurs lignes en colonnes sur la base d'une agrégation. Utilisez le mot-clé UNPIVOT pour transformer une ou plusieurs colonnes en lignes. Cette rubrique décrit l'utilisation des mots-clés PIVOT et UNPIVOT et fournit des exemples.

Mot-clé PIVOT

Le mot-clé PIVOT génère une colonne pour chaque groupe spécifié de valeurs de ligne. PIVOT fait partie de la clause FROM et peut être utilisé avec d'autres mots-clés, tels que JOIN.

Remarque

Le mot-clé PIVOT est en déploiement progressif. Certains utilisateurs peuvent ne pas avoir accès à cette fonctionnalité.

Format de la commande

SELECT ... 
FROM ... 
PIVOT ( 
    <aggregate function> [AS <alias>] [, <aggregate function> [AS <alias>]] ... 
    FOR (<column> [, <column>] ...) 
    IN ( 
        (<value> [, <value>] ...) AS <new column> 
        [, (<value> [, <value>] ...) AS <new column>] 
        ... 
       ) 
    ) 
[...]

Paramètres :

Paramètre

Obligatoire

Description

aggregate function

Oui

Fonction d'agrégation. Pour plus d'informations, consultez la section Présentation des fonctions d'agrégation.

alias

Non

Alias de la fonction d'agrégation. L'alias est lié aux noms de colonnes générés après l'opération PIVOT. Pour plus d'informations, consultez la section Limites.

column

Oui

Nom de la colonne de la table source dont vous souhaitez transformer les valeurs de ligne en colonnes.

value

Oui

Valeurs de ligne à transformer en colonnes.

new column

Non

Nom de la nouvelle colonne après la transformation.

Limites

Fonctions d'agrégation :
- Les fonctions d'agrégation ne peuvent pas être imbriquées dans d'autres fonctions.
- Les paramètres d'une fonction d'agrégation peuvent être une expression composée de fonctions scalaires et de colonnes.
- Les paramètres d'une fonction d'agrégation ne peuvent pas contenir d'autres fonctions d'agrégation ni de fonctions de fenêtrage.
- Les colonnes d'une fonction d'agrégation doivent provenir de la table en amont.
L'élément alias doit être un nom de colonne et ne peut pas être une expression.
La value peut être une expression. Les colonnes de l'expression doivent provenir de la table en amont. L'expression peut contenir des fonctions scalaires, mais ne peut contenir aucune fonction d'agrégation ni fonction de fenêtrage.
Les alias utilisés dans une opération PIVOT sont importants car ils déterminent les noms des colonnes générées. Les conventions de dénomination sont les suivantes :
- PIVOT (agg1 for axis1 in ('1', '2', '3', ...)) :
  
  Si la value est une constante plutôt qu'une expression et que la fonction d'agrégation n'a pas d'alias, les noms de colonnes générés sont les value elles-mêmes, telles que '1', '2' et '3'.
- PIVOT (agg1 as a for axis1 in ('1', '2', '3', ...)) :
  
  Si la value est une constante et que la fonction d'agrégation possède un alias, les noms de colonnes générés suivent le format value_AggregateFunctionAlias, tel que '1'_a et '2'_a.
  
  Si vous exécutez la commande SET odps.sql.bigquery.compatible=true; pour activer le mode de compatibilité BigQuery, les noms de colonnes générés suivent le format AggregateFunctionAlias_value, tel que a_'1' et a_'2'.
- PIVOT (agg1 as a, agg2 as b for axis1 in ('1', '2', '3', ...)) :
  
  Si la value est une constante et que plusieurs fonctions d'agrégation possèdent des alias, les noms de colonnes générés suivent le format value_AggregateFunctionAlias, tel que '1'_a, '2'_a, ..., '1'_b, '2'_b, ....
  
  Si vous exécutez la commande SET odps.sql.bigquery.compatible=true; pour activer le mode de compatibilité BigQuery, les noms de colonnes générés suivent le format AggregateFunctionAlias_value, tel que a_'1', a_'2', ..., b_'1', b_'2', ....
- PIVOT (agg1 as a, agg2 for axis1 in (expr1, expr2, '3', ...)) :
  
  Si une value est une expression, MaxCompute génère d'abord des alias pour les expressions, tels que expr1 et expr2, ainsi que pour toute fonction d'agrégation sans alias, telle que agg2. L'instruction est interprétée comme PIVOT (agg1 as a, agg2 as generated_alias1 for axis1 in (expr1 as generated_alias2, expr2 as generated_alias3, '3', ...)). Les noms de colonnes générés suivent le format ValueAlias_AggregateFunctionAlias, tel que generated_alias2_a, generated_alias3_a, '3'_a, ..., generated_alias2_generated_alias1, generated_alias3_generated_alias1, '3'_generated_alias1, ....
  
  Si vous exécutez la commande SET odps.sql.bigquery.compatible=true; pour activer le mode de compatibilité BigQuery, les noms de colonnes générés suivent le format AggregateFunctionAlias_ValueAlias, tel que a_generated_alias2, a_generated_alias3, a_'3', ..., generated_alias1_generated_alias2, generated_alias1_generated_alias3, generated_alias1_'3', ....

Notes d'utilisation

La syntaxe PIVOT équivaut à une combinaison de GROUP BY, d'une fonction d'agrégation et de FILTER. Par exemple :

SELECT ...
FROM ...
PIVOT (
  agg1 AS a, agg2 AS b, ...
  FOR (axis1, ..., axisN)
  IN (
      (v11, ..., v1N) AS label1,
      (v21, ..., v2N) AS label2, 
      ...)
)

Cela équivaut à l'instruction suivante :

select 
k1, ... kN, 
agg1 AS label1_a filter (where axis1 = v11 and ... and axisN = v1N), 
agg2 AS label1_b filter (where axis1 = v11 and ... and axisN = v1N), 
..., 
agg1 AS label2_a filter (where axis1 = v21 and ... and axisN = v2N),
agg2 AS label2_b filter (where axis1 = v21 and ... and axisN = v2N), 
..., 
from xxxxxx
group by k1, ... kN

Dans cette instruction, la table de la clause FROM est le résultat de l'opération PIVOT en amont. k1, ... kN représente l'ensemble de toutes les colonnes qui n'apparaissent pas dans agg1, agg2, ... ni dans axis1, ..., axisN.

Exemples

Les données d'exemple montrent les ventes de fruits d'une entreprise par saison. L'instruction DDL (Data Definition Language) pour créer la table est la suivante.

-- Create a table
create table mf_cop_sales (tran_id bigint,
                           productID string,
                           tran_amt decimal,
                           season string);
insert into table mf_cop_sales values(1,'apple',100,'Q1'),
                                     (2,'orange',200,'Q1'),
                                     (3,'banana',300,'Q1'),
                                     (4,'apple',400,'Q2'),
                                     (5,'orange',500,'Q2'),
                                     (6,'banana',600,'Q2'),
                                     (7,'apple',700,'Q3'),
                                     (8,'orange',800,'Q3'),
                                     (9,'banana',700,'Q3'),
                                     (10,'apple',500,'Q4'),
                                     (11,'orange',400,'Q4'),
                                     (12,'banana',200,'Q4');
-- The details of the sales table are as follows
select * from mf_cop_sales;
+------------+------------+------------+------------+
| tran_id    | productid  | tran_amt   | season     | 
+------------+------------+------------+------------+
| 1          | apple      | 100        | Q1         | 
| 2          | orange     | 200        | Q1         | 
| 3          | banana     | 300        | Q1         | 
| 4          | apple      | 400        | Q2         | 
| 5          | orange     | 500        | Q2         | 
| 6          | banana     | 600        | Q2         | 
| 7          | apple      | 700        | Q3         | 
| 8          | orange     | 800        | Q3         | 
| 9          | banana     | 700        | Q3         | 
| 10         | apple      | 500        | Q4         | 
| 11         | orange     | 400        | Q4         | 
| 12         | banana     | 200        | Q4         | 
+------------+------------+------------+------------+

Consultez les ventes pour chaque saison de l'année.

SELECT  *
FROM    (
            SELECT  season
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (SUM(tran_amt) FOR season IN ('Q1' AS spring,'Q2' AS summer,'Q3' AS autumn,'Q4' AS winter))
;
-- The following result is returned:
+--------+--------+--------+--------+
| spring | summer | autumn | winter |
+--------+--------+--------+--------+
| 600    | 1500   | 2200   | 1100   |
+--------+--------+--------+--------+

Consultez les ventes pour chaque produit de l'année.

SELECT  *
FROM    (
            SELECT  productid
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (SUM(tran_amt)
AS sumbypro FOR productid IN ('apple','orange','banana'))
;
-- The following result is returned:
+------------------+-------------------+-------------------+
| 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

-- Enable BigQuery compatibility mode
SET odps.sql.bigquery.compatible=true;
SELECT  *
FROM    (
            SELECT  productid
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (SUM(tran_amt)
AS sumbypro FOR productid IN ('apple','orange','banana'))
;
-- The following result is returned:
+------------------+-------------------+-------------------+
| sumbypro_'apple' | sumbypro_'orange' | sumbypro_'banana' |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

Consultez le produit ayant enregistré les ventes les plus élevées au quatrième trimestre (Q4).

SELECT  *
FROM    (
            SELECT  season
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (MAX(tran_amt) FOR season IN ('Q4'))
;
-- The following result is returned:
+------+
| 'q4' |
+------+
| 500  |
+------+

Mot-clé UNPIVOT

Le mot-clé UNPIVOT transforme les colonnes en lignes. UNPIVOT fait partie de la clause FROM et peut être utilisé avec d'autres mots-clés, tels que JOIN.

Format de la commande

SELECT ...
FROM ...
UNPIVOT (
  <new column of value> [, <new column of value>] ...
  FOR (<new column of name> [, <new column of name>] ...)
  IN (
      (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]
      [, (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]]
      ...
    )
)
[...]

Les paramètres sont décrits ci-dessous :

Paramètre

Obligatoire

Description

new column of value

Oui

Nom de la nouvelle colonne générée après la transformation. Les valeurs de cette colonne sont renseignées à partir des valeurs des colonnes transformées en lignes.

new column of name

Oui

Nom de la nouvelle colonne générée après la transformation. Les valeurs de cette colonne sont renseignées à partir des noms des colonnes transformées en lignes.

column

Oui

Noms des colonnes à transformer en lignes. Les noms de colonnes servent à renseigner new column of name. Les valeurs de colonnes servent à renseigner new column of value.

column value

Non

Alias des colonnes transformées en lignes.

Limites

Chaque nouvelle colonne de valeur (new column of value) correspond à un groupe de colonnes spécifié pour la transformation ((<column1> [, <column2>] ...)). Par conséquent, le nombre de new column of value doit correspondre au nombre de groupes de colonnes. Par exemple, new column of value1, ..., new column of valueM correspond à :
```
(column11, ..., column1N) AS (column value11, ..., column value1N),
(column21, ..., column2N) AS (column value21, ..., column value2N),
...
(columnM1, ..., columnMN) AS (column valueM1, ..., column valueMN)
```
Chaque nouvelle colonne de nom (new column of name) correspond à un groupe d'alias de colonne ((<column value1> [, <column value2>] ...)). Par conséquent, le nombre de new column of name doit correspondre au nombre de groupes d'alias. Par exemple, new column of name1, ..., new column of nameM correspond à :
```
(column value11, ..., column value1N), 
(column value21, ..., column value2N), 
...
(column valueM1, ..., column valueMN)
```
Remarque
Vous pouvez omettre (<column value> [, <column value>] ...). MaxCompute génère automatiquement des alias pour les colonnes spécifiées. Si vous souhaitez personnaliser les alias, assurez-vous que le nombre d'alias correspond au nombre de colonnes.
Les collections new column of value et new column of name ne doivent contenir que des noms de colonnes et aucune expression. De plus, les collections new column of value et new column of name ne peuvent pas contenir de noms en double, car les éléments des collections new column of value et new column of name sont affichés sous forme de colonnes.
column doit être un nom de colonne de la table en amont.
column value peut être une constante ou une expression. S'il s'agit d'une expression, elle ne peut contenir aucune colonne. Cela garantit qu'elle peut être réduite à une constante par pliage de constantes.
Le nombre de groupes de colonnes (<column1> [, <column2>] ...) ne peut pas dépasser 100. Sinon, une expansion excessive des données peut se produire.
Si vous omettez les alias de colonne ((<column value> [, <column value>] ...)), MaxCompute génère automatiquement un ensemble de valeurs de chaîne pour les remplacer. Les règles sont les suivantes :
- Pour UNPIVOT (measure1 for axis in (c1, c2, c3, ...)), les valeurs générées sont (c1, c2, c3, ...). La syntaxe d'origine est réécrite comme suit : UNPIVOT (measure1 for axis in (c1 as c1, c2 as c2, c3 as c3, ...)).
- Dans tous les autres cas où les alias ne sont pas spécifiés, MaxCompute génère automatiquement des alias pour les colonnes spécifiées.
- Si vous omettez les alias pour certaines colonnes mais en spécifiez pour d'autres, assurez-vous que les alias spécifiés sont de type STRING. Cela garantit la compatibilité avec les alias STRING générés automatiquement. Si vous utilisez un type autre que STRING, vous devez spécifier tous les alias et ne pouvez en omettre aucun.

Notes d'utilisation

La syntaxe UNPIVOT équivaut à une combinaison de CROSS JOIN et de FILTER (une expression CASE WHEN). Par exemple :

SELECT ...
FROM ...
UNPIVOT (
    (measure1, ..., measureM)
    FOR (axis1, ..., axisN)
    IN ((c11, ..., c1M) AS (value11, ..., value1N),
        (c21, ..., c2M) AS (value21, ..., value2N), ...))
[...]

Cela équivaut à l'instruction suivante :

select 
    k1, ... kN,
    case 
    when axis1 = value11 and ... and axisN = value1N then c11
    when axis1 = value21 and ... and axisN = value2N then c21
    ...
    else null 
    end as measure1,
    ..., 
    case 
    when axis1 = value11 and ... and axisN = value1N then c1M
    when axis1 = value21 and ... and axisN = value2N then c2M
    else null 
    end as measureM, 
    axis1, ..., axisN
    from xxxx 
    join (values (value11, ..., value1N),(value21, ..., value2N), ...) as generated_table_name(axis1, ..., axisN))

Exemples

Les données d'exemple montrent les ventes d'articles dans différents magasins pour des années spécifiques. L'instruction DDL pour créer la table est la suivante.

-- Create a table 
create table mf_shops(item_id bigint, 
                      year string, 
                      shop1 decimal, 
                      shop2 decimal, 
                      shop3 decimal, 
                      shop4 decimal); 
-- Insert data
with shops_table as  
		 (select * from values(1, 2020, 100, 200, 300, 400), 
                          (1, 2021, 100, 200, 200, 100), 
                          (2, 2020, 300, 400, 300, 200), 
                          (2, 2021, 400, 300, 100, 100)
                shops(item_id, year, shop1, shop2, shop3, shop4) 
     ) 
insert overwrite table mf_shops 
select * from shops_table; 
-- Query data
select * from mf_shops;
-- The following result is returned:
+------------+------+-------+-------+-------+-------+
| item_id    | year | shop1 | shop2 | shop3 | shop4 |
+------------+------+-------+-------+-------+-------+
| 1          | 2020 | 100   | 200   | 300   | 400   |
| 1          | 2021 | 100   | 200   | 200   | 100   |
| 2          | 2020 | 300   | 400   | 300   | 200   |
| 2          | 2021 | 400   | 300   | 100   | 100   |
+------------+------+-------+-------+-------+-------+

Fusionnez les chiffres de ventes de tous les magasins et affichez-les dans une nouvelle colonne nommée sales.

-- Merge the sales figures from all stores.
select * from mf_shops
unpivot (sales for shop in (shop1, shop2, shop3, shop4));

-- The following result is returned:
+------------+------------+------------+------+
| item_id    | year       | sales      | shop |
+------------+------------+------------+------+
| 1          | 2020       | 100        | shop1 |
| 1          | 2020       | 200        | shop2 |
| 1          | 2020       | 300        | shop3 |
| 1          | 2020       | 400        | shop4 |
| 1          | 2021       | 100        | shop1 |
| 1          | 2021       | 200        | shop2 |
| 1          | 2021       | 200        | shop3 |
| 1          | 2021       | 100        | shop4 |
| 2          | 2020       | 300        | shop1 |
| 2          | 2020       | 400        | shop2 |
| 2          | 2020       | 300        | shop3 |
| 2          | 2020       | 200        | shop4 |
| 2          | 2021       | 400        | shop1 |
| 2          | 2021       | 300        | shop2 |
| 2          | 2021       | 100        | shop3 |
| 2          | 2021       | 100        | shop4 |
+------------+------------+------------+------+

Vous pouvez attribuer un alias à chaque nom de magasin. L'alias peut être une valeur de la table ou une chaîne.

select * from mf_shops
unpivot (sales for shop in (shop1 as 'shop_name_1', shop2 as 'shop_name_2', shop3 as 'shop_name_3', shop4 as 'shop_name_4'));

-- The following result is returned:
+------------+------------+------------+------+
| item_id    | year       | sales      | shop |
+------------+------------+------------+------+
| 1          | 2020       | 100        | shop_name_1 |
| 1          | 2020       | 200        | shop_name_2 |
| 1          | 2020       | 300        | shop_name_3 |
| 1          | 2020       | 400        | shop_name_4 |
| 1          | 2021       | 100        | shop_name_1 |
| 1          | 2021       | 200        | shop_name_2 |
| 1          | 2021       | 200        | shop_name_3 |
| 1          | 2021       | 100        | shop_name_4 |
| 2          | 2020       | 300        | shop_name_1 |
| 2          | 2020       | 400        | shop_name_2 |
| 2          | 2020       | 300        | shop_name_3 |
| 2          | 2020       | 200        | shop_name_4 |
| 2          | 2021       | 400        | shop_name_1 |
| 2          | 2021       | 300        | shop_name_2 |
| 2          | 2021       | 100        | shop_name_3 |
| 2          | 2021       | 100        | shop_name_4 |
+------------+------------+------------+------+

Supposons que shop1 et shop2 soient des magasins de la région Est, et shop3 et shop4 des magasins de la région Ouest. La requête suivante affiche les ventes pour les régions Est et Ouest. Les colonnes sales1 et sales2 stockent respectivement les chiffres de ventes des deux magasins de chaque région.

select * from mf_shops
unpivot ((sales1, sales2) for shop in ((shop1, shop2) as 'east_shop', (shop3, shop4) as 'west_shop'));

-- The following result is returned:
+------------+------------+------------+------------+------+
| item_id    | year       | sales1     | sales2     | shop |
+------------+------------+------------+------------+------+
| 1          | 2020       | 100        | 200        | east_shop |
| 1          | 2020       | 300        | 400        | west_shop |
| 1          | 2021       | 100        | 200        | east_shop |
| 1          | 2021       | 200        | 100        | west_shop |
| 2          | 2020       | 300        | 400        | east_shop |
| 2          | 2020       | 300        | 200        | west_shop |
| 2          | 2021       | 400        | 300        | east_shop |
| 2          | 2021       | 100        | 100        | west_shop |
+------------+------------+------------+------------+------+

Vous pouvez utiliser plusieurs colonnes pour les alias. Le nombre de colonnes de valeur doit également être augmenté en conséquence.

select * from mf_shops
unpivot ((sales1, sales2) for (shop_name, location) in ((shop1, shop2) as ('east_shop', 'east'), (shop3, shop4) as ('west_shop', 'west')));

+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+

Utilisez EXCLUDE NULLS pour filtrer les lignes où sales1 et sales2 sont nulles.

with 
shops as (select * from values
          (1, 2020, 100, 200, 300, 400),
          (1, 2021, 100, 200, 200, 100),
          (2, 2020, 300, 400, 300, 200),
          (2, 2021, 400, 300, 100, 100),
          (3, 2020, null, null, null, null) 
          shops(item_id, year, shop1, shop2, shop3, shop4)) 
select * from shops 
unpivot exclude nulls ((sales1, sales2) for (shop_name, location) in ((shop1, shop2) as ('east_shop', 'east'), (shop3, shop4) as ('west_shop', 'west')));

-- The following result is returned:
+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+