1. 前言
MaxCompute（原ODPS）是阿里云自主研发的具有业界领先水平的分布式大数据处理平台, 尤其在集团内部得到广泛应用，支撑了多个BU的核心业务。 MaxCompute除了持续优化性能外，也致力于提升SQL语言的用户体验和表达能力，提高广大ODPS开发者的生产力。MaxCompute基于ODPS2.0新一代的SQL引擎，显著提升了SQL语言编译过程的易用性与语言的表达能力。我们在此推出MaxCompute(ODPS2.0)重装上阵系列文章
第一弹 - 善用MaxCompute编译器的错误和警告
第二弹 - 新的基本数据类型与内建函数
第三弹 - 复杂类型
第四弹 - CTE，VALUES，SEMIJOIN
第五弹 - SELECT TRANSFORM
第六弹 - User Defined Type
第七弹 - Grouping Set, Cube and Rollup
第八弹 - 动态类型函数
第九弹 - 脚本模式与参数视图
第十弹 - IF ELSE分支语句
第十一弹 - QUALIFY
本文将向您介绍MaxCompute支持的新语法 - PIVOT/UNPIVOT，即通过PIVOT关键字基于聚合将一个或者多个指定值的行转换为列；通过UNPIVOT关键字可将一个或者多个列转换为行。常见的场景入下：
1.1 场景1
某个业务表，需要把表中的值当做新的列，并且根据每个值聚合现有的结果，从而实现行转列的效果。在没有支持PIVOT前，要实现这个需求，需要结合GROUP BY语法+聚合函数+Filter语法过滤来实现。
1.2 场景2
某个业务表，需要构造一个新的列，把原有的几个列名合并在这个列里面，并且用另一个新列来放置原来几个列的值，从而实现列转行的效果。在没有支持UNPIVOT前，要实现这个需求，需要结合CROSS JOIN语法+CASE WHEN表达式来构造实现。
2. PIVOT/UNPIVOT功能
2.1 PIVOT
2.1.1 PIVOT概述
PIVOT语法将指定的行旋转为多列，并且对其余列值聚合得到结果并旋转表。PIVOT语法是FROM子句的一部分。
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>] ... ) ) [...]
- 表示行转列时需要计算的聚合函数，且聚合函数的外层不能嵌套任何函数，可以是Scalar函数和列组成的表达式。同时聚合函数的参数内部不能有其他聚合函数、Window函数，以及聚合函数的列只能是上游表中的列。
- 表示行转列时需要计算的聚合函数的对应列的别名。
- 表示行转列的对应行的列名，不能是任何的表达式。
- 表示行转列的对应行的值，也可以是表达式，但是不允许有任何的聚合函数和窗口函数，并且每一个元组内的元素数量要与数量一致。
- 表示行转列后新的列的别名，不指定别名时，会试图推测别名，推测失败会由系统自动生成一个别名。
更详细的语法使用说明可参考文档。
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出现的列的集合。
2.1.2 PVIOT示例
- 数据准备。以下表代表几家连锁店对应物品在对应年份的销售情况。
create table shops_table as select * from (select * from values ('pen', 10, 500, 'shop1', 2020), ('pen', 11, 500, 'shop2', 2020), ('pen', 9, 300, 'shop3', 2020), ('pen', 12, 400,'shop4', 2020), ('pen', 15, 200, 'shop1', 2021), ('pen', 16, 300, 'shop2', 2021), ('pen', 16, 400, 'shop3', 2021), ('pen', 15, 300, 'shop4', 2021), ('ruler', 20, 700, 'shop1', 2020), ('ruler', 19, 900, 'shop2', 2020), ('ruler', 22, 800, 'shop3', 2020), ('ruler', 19, 700, 'shop4', 2020), ('ruler', 25, 300, 'shop1', 2021), ('ruler', 20, 500, 'shop2', 2021), ('ruler', 23, 500, 'shop3', 2021), ('ruler', 26, 600, 'shop4', 2021) shops(item_name, count, sales, shop_name, year)); select * from shops_table; -- 结果如下： +-----------+------------+------------+-----------+------------+ | item_name | count | sales | shop_name | year | +-----------+------------+------------+-----------+------------+ | pen | 10 | 500 | shop1 | 2020 | | pen | 11 | 500 | shop2 | 2020 | | pen | 9 | 300 | shop3 | 2020 | | pen | 12 | 400 | shop4 | 2020 | | pen | 15 | 200 | shop1 | 2021 | | pen | 16 | 300 | shop2 | 2021 | | pen | 16 | 400 | shop3 | 2021 | | pen | 15 | 300 | shop4 | 2021 | | ruler | 20 | 700 | shop1 | 2020 | | ruler | 19 | 900 | shop2 | 2020 | | ruler | 22 | 800 | shop3 | 2020 | | ruler | 19 | 700 | shop4 | 2020 | | ruler | 25 | 300 | shop1 | 2021 | | ruler | 20 | 500 | shop2 | 2021 | | ruler | 23 | 500 | shop3 | 2021 | | ruler | 26 | 600 | shop4 | 2021 | +-----------+------------+------------+-----------+------------+
- 统计各个年份各个店对物品的卖出数量情况。
- 没有支持PVIOT语法前，实现如下：
SELECT item_name ,year ,SUM(CASE shop_name WHEN 'shop1' THEN count END) AS shop1 ,SUM(CASE shop_name WHEN 'shop2' THEN count END) AS shop2 ,SUM(CASE shop_name WHEN 'shop3' THEN count END) AS shop3 ,SUM(CASE shop_name WHEN 'shop4' THEN count END) AS shop4 FROM shops_table GROUP BY item_name ,year ; --结果如下： +-----------+------------+------------+------------+------------+------------+ | item_name | year | 'shop1' | 'shop2' | 'shop3' | 'shop4' | +-----------+------------+------------+------------+------------+------------+ | pen | 2020 | 10 | 11 | 9 | 12 | | pen | 2021 | 15 | 16 | 16 | 15 | | ruler | 2020 | 20 | 19 | 22 | 19 | | ruler | 2021 | 25 | 20 | 23 | 26 | +-----------+------------+------------+------------+------------+------------+
- 通过PVIOT语法实现如下：
select * from (select item_name, year,count,shop_name from shops_table) pivot (sum(count) for shop_name in ('shop1', 'shop2', 'shop3', 'shop4')); --结果如下： +------------+------------+------------+------------+------------+------------+ | item_name | year | 'shop1' | 'shop2' | 'shop3' | 'shop4' | +------------+------------+------------+------------+------------+------------+ | pen | 2020 | 10 | 11 | 9 | 12 | | pen | 2021 | 15 | 16 | 16 | 15 | | ruler | 2020 | 20 | 19 | 22 | 19 | | ruler | 2021 | 25 | 20 | 23 | 26 | +------------+------------+------------+------------+------------+------------+
可以在此时为聚合函数和新的列起别名，列名根据下划线合并：
select * from (select item_name, count, shop_name, year from shops_table) pivot (sum(count) as sum_count for shop_name in ('shop1' as shop_name_1, 'shop2' as shop_name_2, 'shop3' as shop_name_3, 'shop4' as shop_name_4)); --结果如下： +------------+------------+-----------------------+-----------------------+-----------------------+-----------------------+ | item_name | year | shop_name_1_sum_count | shop_name_2_sum_count | shop_name_3_sum_count | shop_name_4_sum_count | +------------+------------+-----------------------+-----------------------+-----------------------+-----------------------+ | pen | 2020 | 10 | 11 | 9 | 12 | | pen | 2021 | 15 | 16 | 16 | 15 | | ruler | 2020 | 20 | 19 | 22 | 19 | | ruler | 2021 | 25 | 20 | 23 | 26 | +------------+------------+-----------------------+-----------------------+-----------------------+-----------------------+
- 计算每个物品每家商店每年的总卖出数量和最高销售额，通过PIVOT实现如下：
select * from shops_table pivot (sum(count) as sum_count, max(sales) as max_sales for shop_name in ('shop1' as shop_name_1, 'shop2' as shop_name_2, 'shop3' as shop_name_3, 'shop4' as shop_name_4)); --结果如下： +-----------+------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+ | item_name | year | shop_name_1_sum_count | shop_name_2_sum_count | shop_name_3_sum_count | shop_name_4_sum_count | shop_name_1_max_sales | shop_name_2_max_sales | shop_name_3_max_sales | shop_name_4_max_sales | +-----------+------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+ | pen | 2020 | 10 | 11 | 9 | 12 | 500 | 500 | 300 | 400 | | pen | 2021 | 15 | 16 | 16 | 15 | 200 | 300 | 400 | 300 | | ruler | 2020 | 20 | 19 | 22 | 19 | 700 | 900 | 800 | 700 | | ruler | 2021 | 25 | 20 | 23 | 26 | 300 | 500 | 500 | 600 | +-----------+------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
- 只计算shop1在2020年和2021对于每件物品的总卖出数量和最高销售额，通过PIVOT实现如下：
select * from shops_table pivot (sum(count) as sum_count, max(sales) as max_sales for (shop_name, year) in (('shop1', 2021) as shop1_2021, ('shop1', 2020) as shop1_2020)); --结果如下： +-----------+----------------------+----------------------+----------------------+----------------------+ | item_name | shop1_2021_sum_count | shop1_2020_sum_count | shop1_2021_max_sales | shop1_2020_max_sales | +-----------+----------------------+----------------------+----------------------+----------------------+ | pen | 15 | 10 | 200 | 500 | | ruler | 25 | 20 | 300 | 700 | +-----------+----------------------+----------------------+----------------------+----------------------+
2.2 UNPIVOT
2.2.1 UNPIVOT概述
UNPIVOT语法通过将列转换为行来旋转表格，UNPIVOT语法是FROM子句的一部分。
SELECT ... FROM ... UNPIVOT [EXCLUDE NULLS] ( <new_column_of_name> [, <new_column_of_name>] ... FOR (<new_column_of_value> [, <new_column_of_value>] ...) IN ( (<column> [, <column>] ...) AS (<column_value> [, <column_value>] ...) [, (<column> [, <column>] ...) AS (<column_value> [, <column_value>] ...)] ... ) ) [...]
- [EXCLUDE NULLS]若指定该语法，则会过滤掉所有都是null的行。
- 列转行以后用于存储原有的列名的列，必须为列名不能是表达式也不能重名。数量需要和每一个元祖内部元素的数量相同，其中不指定时，MaxCompute会自动生成一组string类型的元祖。
- 列转行以后用于存储原有的列对应值的列，必须为列名不能是表达式也不能重名，数量需要和每一个元祖内部元素的数量相同。
- 用于列转行的原有的列。
- 用于列转行的原有的列的别名，可以用于替换原有的列名，内部不允许有列名。
更详细的语法使用说明可参考文档。
UNPIVOT语法可以等效为CROSS JOIN + CASE WHEN表达式的结合。以下面这个例子为例：
SELECT ... FROM ... UNPIVOT [exclude nulls] ( (measure1, ..., measureM) FOR (axis1, ..., axisN) IN ((c11, ..., c1M) AS (value11, ..., value1N), (c21, ..., c2M) AS (value21, ..., value2N), ...)) [...]
上面的语法等效于
SELECT * FROM ( 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)) ) [WHERE measure1 is not null OR ... OR measureM is not null]
2.2.2 UNPIVOT示例
- 数据准备。以下表代表几家连锁店对应物品在对应年份的销售情况：
create table shops as select * from （select * from values ('pen', 2020, 100, 200, 300, 400), ('pen', 2021, 100, 200, 200, 100), ('ruler', 2020, 300, 400, 300, 200), ('ruler', 2021, 400, 300, 100, 100) shops(item_name, year, shop1, shop2, shop3, shop4)）; SELECT * from shops; --执行结果： +-----------+------------+------------+------------+------------+------------+ | item_name | year | shop1 | shop2 | shop3 | shop4 | +-----------+------------+------------+------------+------------+------------+ | pen | 2020 | 100 | 200 | 300 | 400 | | pen | 2021 | 100 | 200 | 200 | 100 | | ruler | 2020 | 300 | 400 | 300 | 200 | | ruler | 2021 | 400 | 300 | 100 | 100 | +-----------+------------+------------+------------+------------+------------+
- 旋转表，得到各个商店的销售数量，并且用新的列名count来替代。
- 没有UNPIVOT前的实现方式：
select * from( select item_name,year, 'shop1' as shop_name, shop1 as count from shops union ALL select item_name,year, 'shop2' as shop_name, shop2 as count from shops UNION ALL select item_name,year, 'shop3' as shop_name, shop3 as count from shops UNION ALL select item_name,year, 'shop4' as shop_name, shop4 as count from shops ); --执行结果 +------------+------------+------------+------------+ | item_name | year | shop_name | count | +------------+------------+------------+------------+ | pen | 2020 | shop1 | 100 | | pen | 2021 | shop1 | 100 | | ruler | 2020 | shop1 | 300 | | ruler | 2021 | shop1 | 400 | | pen | 2020 | shop2 | 200 | | pen | 2021 | shop2 | 200 | | ruler | 2020 | shop2 | 400 | | ruler | 2021 | shop2 | 300 | | pen | 2020 | shop3 | 300 | | pen | 2021 | shop3 | 200 | | ruler | 2020 | shop3 | 300 | | ruler | 2021 | shop3 | 100 | | pen | 2020 | shop4 | 400 | | pen | 2021 | shop4 | 100 | | ruler | 2020 | shop4 | 200 | | ruler | 2021 | shop4 | 100 | +------------+------------+------------+------------+
- 通过UNPIVOT实现：
select * from shops unpivot (count for shop_name in (shop1, shop2, shop3, shop4)); --执行结果 +------------+------------+------------+------------+ | item_name | year | count | shop_name | +------------+------------+------------+------------+ | pen | 2020 | 100 | shop1 | | pen | 2020 | 200 | shop2 | | pen | 2020 | 300 | shop3 | | pen | 2020 | 400 | shop4 | | pen | 2021 | 100 | shop1 | | pen | 2021 | 200 | shop2 | | pen | 2021 | 200 | shop3 | | pen | 2021 | 100 | shop4 | | ruler | 2020 | 300 | shop1 | | ruler | 2020 | 400 | shop2 | | ruler | 2020 | 300 | shop3 | | ruler | 2020 | 200 | shop4 | | ruler | 2021 | 400 | shop1 | | ruler | 2021 | 300 | shop2 | | ruler | 2021 | 100 | shop3 | | ruler | 2021 | 100 | shop4 | +------------+------------+------------+------------+
- 如果shop1和shop2是东区商店，shop3和shop4是西区商店，接下来需要一个新的列表示东区商店和西区商店。其中count1和count2两列分别存储了两店的销售数量。
select * from shops unpivot ((count1, count2) for shop_name in ((shop1, shop2) as 'east_shop', (shop3, shop4) as 'west_shop')); --执行结果 +------------+------------+------------+------------+------------+ | item_name | year | count1 | count2 | shop_name | +------------+------------+------------+------------+------------+ | pen | 2020 | 100 | 200 | east_shop | | pen | 2020 | 300 | 400 | west_shop | | pen | 2021 | 100 | 200 | east_shop | | pen | 2021 | 200 | 100 | west_shop | | ruler | 2020 | 300 | 400 | east_shop | | ruler | 2020 | 300 | 200 | west_shop | | ruler | 2021 | 400 | 300 | east_shop | | ruler | 2021 | 100 | 100 | west_shop | +------------+------------+------------+------------+------------+
别名可以是多列，但是对应的需要生成的新的列名要相应增加：
select * from shops unpivot ((count1, count2) for (shop_name, location) in ((shop1, shop2) as ('east_shop', 'east'), (shop3, shop4) as ('west_shop', 'west'))); --执行结果 +------------+------------+------------+------------+------------+------------+ | item_name | year | count1 | count2 | shop_name | location | +------------+------------+------------+------------+------------+------------+ | pen | 2020 | 100 | 200 | east_shop | east | | pen | 2020 | 300 | 400 | west_shop | west | | pen | 2021 | 100 | 200 | east_shop | east | | pen | 2021 | 200 | 100 | west_shop | west | | ruler | 2020 | 300 | 400 | east_shop | east | | ruler | 2020 | 300 | 200 | west_shop | west | | ruler | 2021 | 400 | 300 | east_shop | east | | ruler | 2021 | 100 | 100 | west_shop | west | +------------+------------+------------+------------+------------+------------+
3.小结
PIVOT/UNPIVOT语法，以更简洁易用的方式满足行转列和列转行的需求，简化了查询语句，提高了广大大数据开发者的生产力。