MaxCompute - ODPS重装上阵第十二弹 - PIVOT/UNPIVOT-低调大师

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

2023-09-12 787

前言

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

UNPIVOT

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的行。

<new_column_of_name>

列转行以后用于存储原有的列名的列，必须为列名不能是表达式也不能重名。数量需要和每一个<column value>元祖内部元素的数量相同，其中<column value>不指定时，MaxCompute会自动生成一组string类型的元祖。

<new_column_of_value>

列转行以后用于存储原有的列对应值的列，必须为列名不能是表达式也不能重名，数量需要和每一个<column>元祖内部元素的数量相同。

<column>

用于列转行的原有的列。

<column_value>

用于列转行的原有的列的别名，可以用于替换原有的列名，内部不允许有列名。

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

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]

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

小结

PIVOT/UNPIVOT语法，以更简洁易用的方式满足行转列和列转行的需求，简化了查询语句，提高了广大大数据开发者的生产力。

点击立即免费试用云产品开启云上实践之旅！

原文链接

本文为阿里云原创内容，未经允许不得转载

微信关注我们

原文链接：https://my.oschina.net/yunqi/blog/10109883

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

快速成长的秘诀｜学会自我培养和培养他人

快速成长总共三篇，分别是《完成自我认知升级》、《自我成长好方法》和《自我培养和培养他人》。本篇是第三篇，篇幅较长。针对长文的阅读方式，依旧建议在《完成自我认知升级》中提到的阅读方式：“在一个不被打扰的时间做好只字不差阅读，用批判性思维思考和理解其中的逻辑，内化成自己的逻辑。” 成长快与慢，每个人都会关心，每个人都在等待，与其等着被人培养不如学会自我培养，与其放养不如掌握培养人的方法。组织需要面对人才培养、主管需要面对人才培养，员工同样需要面对自我培养，对人才培养的诉求从未变过，但是很少被满足过。接触过一些关于人才培养的课程和文章，都非常优秀，但培养结束后效果不明显，本质的原因是东西是好东西，但是在执行过程中缺乏跟进，辅导和反馈，也就是说在执行层面出现了断层，理论和实践无法结合，或者理论未围绕实战进行。究根问底，最核心的原因是人才培养是一项意志力 * 连续性的活动，没有意志力培养不出人来，没有连续性看不到培养结果，即便有意志力，有连续性，有时候还跟方法，跟受众性格、受众需求等因素相关。总之人才培养非常复杂，难以见效，正因为难以见效，人才培养可能变成了培训，变成了分享，变成了谈话等方式，...

2023-09-12

342

Apache DolphinScheduler 是一款开源的分布式任务调度系统，旨在帮助用户实现复杂任务的自动化调度和管理。DolphinScheduler 支持多种任务类型，可以在单机或集群环境下运行。下面将介绍如何实现 DolphinScheduler 的自动化打包和单机/集群部署。自动化打包所需环境：maven、jdk 执行以下shell完成代码拉取及打包，打包路径：/opt/action/dolphinscheduler/dolphinscheduler-dist/target/apache-dolphinscheduler-dev-SNAPSHOT-bin.tar.gz sudo su - root <<EOF cd /opt/action git clone git@github.com:apache/dolphinscheduler.git cd Dolphinscheduler git fetch origin dev git checkout -b dev origin/dev #git log --oneline EOF } # 打包 build()...

2023-09-12

346

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。