PIVOT和UNPIVOT关键字的使用和示例_云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心

MaxCompute支持PIVOT和UNPIVOT关键字，通过PIVOT关键字可以基于聚合将一个或者多个指定值的行转换为列；通过UNPIVOT关键字可以将一个或者多个列转换为行。本文为您介绍PIVOT和UNPIVOT关键字的使用和示例。

PIVOT关键字

PIVOT关键字对于指定的每一组行值，都会生成对应的列。PIVOT关键字是FROM子句的一部分，可以和JOIN等其他关键字一同使用。

说明

当前PIVOT关键字处于灰度发布中，部分用户可能无法使用。

命令格式

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>] 
        ... 
       ) 
    ) 
[...]

参数说明：

参数	是否必选	说明
aggregate function	是	聚合函数，可使用的聚合函数请参见聚合函数。
alias	否	聚合函数的别名，别名和最终PIVOT处理过后生成的列名相关，详情请参见使用限制。
column	是	指定转换为列的行值在源表中的列名称。
value	是	指定转换为列的行值。
new column	否	转换后新的列名称。

使用限制

聚合函数相关：
- 聚合函数的外层不能嵌套任何函数。
- 聚合函数参数可以是Scalar函数和列组成的表达式。
- 聚合函数的参数内部不能有其他聚合函数、Window函数。
- 聚合函数的列只能是上游表中的列。
alias只能是列名称，不能是任何表达式。
value内部可以是表达式，表达式中的列只能是上游表中的列，允许包含Scalar函数，但是不允许有任何聚合函数和Window函数。
PIVOT内部的所有的别名不可忽略，别名和最终PIVOT处理过后的列名息息相关，存在如下几种情况：
- PIVOT (agg1 for axis1 in ('1', '2', '3', ...))：
  如果value是常量而不是表达式且聚合函数未指定别名，那么PIVOT处理后列名为value，即'1','2','3'...。
- PIVOT (agg1 as a for axis1 in ('1', '2', '3', ...))：
  如果value是常量而不是表达式且聚合函数指定了别名，那么PIVOT处理后列名为value_聚合函数别名，即'1'_a, '2'_a, ...。
- PIVOT (agg1 as a, agg2 as b for axis1 in ('1', '2', '3', ...))：
  如果value是常量而不是表达式且聚合函数指定了别名，那么PIVOT处理后列名为value_聚合函数别名，即'1'_a, '2'_a, ..., '1'_b, '2'_b, ...。
- PIVOT (agg1 as a, agg2 for axis1 in (expr1, expr2, '3', ...))：
  如果有value是表达式，首先MaxCompute会为表达式（expr1和expr2）和未指定别名的聚合函数agg2生成别名，即PIVOT (agg1 as a, agg2 as generated_alias1 for axis1 in (expr1 as generated_alias2, expr2 as generated_alias3, '3', ...))。那么PIVOT处理后列名为value/表达式别名_聚合函数别名，即generated_alias2_a, generated_alias3_a,'3'_a ..., generated_alias2_generated_alias1, generated_alias3_generated_alias1,'3'_ generated_alias1...。

使用说明

PIVOT语法可以等效为Group by、Aggregate function和Filter的结合，示例如下。

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

等效于如下语句：

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

其中from语句内的表是PIVOT上游的结果，k1, ... kN是所有未在agg1, agg2, ...和axis1, ..., axisN出现的列的集合。

使用示例

示例数据为某公司今年各个季节的水果销售流水，建表DDL如下。

--创建表
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');
--销售表详细信息如下
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         | 
+------------+------------+------------+------------+

查询本年度每个季节的销售情况。

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) 
                ); 
--返回结果
+--------+--------+--------+--------+
| spring | summer | autumn | winter |
+--------+--------+--------+--------+
| 600    | 1500   | 2200   | 1100   |
+--------+--------+--------+--------+

查询本年度各个产品的销售情况。

select * from (select productid, 
                      tran_amt 
                 from mf_cop_sales) 
              pivot (sum(tran_amt) as sumbypro 
                 for productid in ('apple', 'orange', 'banana')); 
--返回结果
+------------------+-------------------+-------------------+
| 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

查询本年度第四季度销售额最高的产品。

select * from （select season, 
                      tran_amt 
                 from mf_cop_sales) 
         pivot (max(tran_amt) for season in ('Q4')）; 

--返回结果
+------+
| 'Q4' |
+------+
| 500  |
+------+

UNPIVOT关键字

UNPIVOT关键字对于指定的每一组列，都会生成对应的行。其中UNPIVOT关键字是FROM子句的一部分，可以和JOIN关键字等其他关键字一同使用。

命令格式

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>] ...)]]
      ...
    )
)
[...]

参数说明如下：

参数	是否必选	说明
new column of value	是	转换后新生成的列名称，该列的值由指定转换为行的列的值填充。
new column of name	是	转换后新生成的列名称，该列的值由指定转换为行的列名称填充。
column	是	指定转换为行的列名称，列的名称用来填充new column of name；列的值用来填充new column of value。
column value	否	指定转换为行的列的别名

使用限制

一个新生成的列（new column of value）需要对应一组指定转换为行的列，即一个new column of value对应一组 (<column1> [, <column2>] ...)，所以new column of value的个数需要和指定列的组数相同，即new column of value1, ..., new column of valueM对应：
```
(column11, ..., column1N) AS (column value11, ..., column value1N),
(column21, ..., column2N) AS (column value21, ..., column value2N)，
...
(columnM1, ..., columnMN) AS (column valueM1, ..., column valueMN)
```
一个新生成的列（new column of name）需要对应一组指定列的别名，即一个new column of name对应一组 (<column value1> [, <column value2>] ...)，所以new column of name的个数需要和指定列别名的组数相同，即new column of name1, ..., new column of nameM对应：
```
(column value11, ..., column value1N), 
(column value21, ..., column value2N)， 
...
(column valueM1, ..., column valueMN）
```
说明
实际使用中可以省略 (<column value> [, <column value>] ...)，MaxCompute会为指定列自动生成别名。如果您需要自定义指定别名，一定要保证数量的一一对应。
new column of value和new column of name的集合必须是列名称，不能包含任何的表达式，并且new column of value和new column of name的集合不能重名，因为new column of value和new column of name集合内部的元素都是需要输出的列。
column只能是上游表的列名。
column value可以为常数或表达式。如果是表达式，就不允许有任何列，保证可以常量折叠成常数。
(<column1> [, <column2>] ...)的组数不能超过100，否则会造成数据过度膨胀。
如果省略指定列的别名（(<column value> [, <column value>] ...)，MaxCompute自动生成一组String的值来代替，规则如下：
- UNPIVOT (measure1 for axis in (c1, c2, c3, ...))：此情况下生成的值是(c1, c2, c3, ...)，即原语法改写为：UNPIVOT (measure1 for axis in (c1 as c1, c2 as c2, c3 as c3, ...))。
- 除上述情况以外的所有未指定别名的情况，全部都由MaxCompute自动生成指定列的别名。
- 若是有些列省略，有些列设置了别名，需要保证设置的别名是String类型、可以兼容自动生成的String类型的别名。若是非String类型，需要设置所有的别名，不能省略。

使用说明

UNPIVOT语法可以等效为Cross Join和Filter（case when expression）的结合，示例如下。

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

等效于如下语句：

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

使用示例

示例数据为各个商店对应物品在对应年份的销售情况，建表DDL如下。

--创建表 
create table mf_shops(item_id bigint, 
                      year string, 
                      shop1 decimal, 
                      shop2 decimal, 
                      shop3 decimal, 
                      shop4 decimal); 
--插入数据
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; 
--查询数据
select * from mf_shops;
--返回结果
+------------+------+-------+-------+-------+-------+
| 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   |
+------------+------+-------+-------+-------+-------+

合并各个商店的销售额，并且用新的列名sales来展示销售额。

--合并各个商店的销售额
select * from mf_shops
unpivot (sales for shop in (shop1, shop2, shop3, shop4));

--返回结果
+------------+------------+------------+------+
| 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 |
+------------+------------+------------+------+

可以为每一个商店名字起一个别名，别名可以是表内的值，也可以是字符串：

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

--返回结果
+------------+------------+------------+------+
| 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 |
+------------+------------+------------+------+

如果shop1和shop2是东区商店，shop3和shop4是西区商店，一个新的表展示东区商店和西区商店。其中sales1和sales2两列分别存储了两店的销售额。

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

--返回结果
+------------+------------+------------+------------+------+
| 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 |
+------------+------------+------------+------------+------+

别名可以是多列，对应的列数也要相应增加。

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

对于sales1和sales2为null的无意义行，可以通过exclude nulls过滤。

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

--返回结果
+------------+------------+------------+------------+-----------+----------+
| 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     |
+------------+------------+------------+------------+-----------+----------+