MaxCompute - ODPS重装上阵 第十二弹 - PIVOT/UNPIVOT

前言

MaxCompute（原ODPS）是阿里云自主研发的具有业界领先水平的分布式大数据处理平台, 尤其在集团内部得到广泛应用，支撑了多个BU的核心业务。 MaxCompute除了持续优化性能外，也致力于提升SQL语言的用户体验和表达能力，提高广大ODPS开发者的生产力。

MaxCompute基于ODPS2.0新一代的SQL引擎，显著提升了SQL语言编译过程的易用性与语言的表达能力。

本文将向您介绍MaxCompute支持的新语法 - PIVOT/UNPIVOT，即通过PIVOT关键字基于聚合将一个或者多个指定值的行转换为列；通过UNPIVOT关键字可将一个或者多个列转换为行。常见的场景入下：

• 场景1

某个业务表，需要把表中的值当做新的列，并且根据每个值聚合现有的结果，从而实现行转列的效果。在没有支持PIVOT前，要实现这个需求，需要结合GROUP BY语法+聚合函数+Filter语法过滤来实现。

• 场景2

某个业务表，需要构造一个新的列，把原有的几个列名合并在这个列里面，并且用另一个新列来放置原来几个列的值，从而实现列转行的效果。在没有支持UNPIVOT前，要实现这个需求，需要结合CROSS JOIN语法+CASE WHEN表达式来构造实现。

PIVOT/UNPIVOT功能

PIVOT

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

• <aggregate_function>

表示行转列时需要计算的聚合函数，且聚合函数的外层不能嵌套任何函数，可以是Scalar函数和列组成的表达式。同时聚合函数的参数内部不能有其他聚合函数、Window函数，以及聚合函数的列只能是上游表中的列。

• <alias>

表示行转列时需要计算的聚合函数的对应列的别名。

• <column>

表示行转列的对应行的列名，不能是任何的表达式。

• <value>

表示行转列的对应行的值，也可以是表达式，但是不允许有任何的聚合函数和窗口函数，并且每一个元组内的元素数量要与<column>数量一致。

• <new_column>

表示行转列后新的列的别名，不指定别名时，会试图推测别名，推测失败会由系统自动生成一个别名。

更详细的语法使用说明可参考文档。

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出现的列的集合。

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', 2