全部标签
写点什么
OpenHarmony算法元宇宙MySQL移动开发学习方法Web3.0高效工作数据库Python音视频前端AI大数据团队管理程序员运维深度思考低代码redisgolang微服务架构flutter 查看更多

深入 MaxCompute - 第十二弹 -PIVOT/UNPIVOT

作者：阿里云大数据AI技术
  • 2023-08-31
    浙江

  • 本文字数：9705 字

    阅读完需：约 32 分钟

简介：  MaxCompute 推出新语法 - PIVOT/UNPIVOT：通过 PIVOT 关键字基于聚合将一个或者多个指定值的行转换为列；通过 UNPIVOT 关键字可将一个或者多个列转换为行。以更简洁易用的方式满足行转列和列转行的需求，简化了查询语句，提高了广大大数据开发者的生产力。


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


MaxCompute 基于 MaxCompute2.0 新一代的 SQL 引擎，显著提升了 SQL 语言编译过程的易用性与语言的表达能力。我们在此推出深入 MaxCompute 系列文章


第一弹 - 善用MaxCompute编译器的错误和警告


第二弹 - 新的基本数据类型与内建函数


第三弹 - 复杂类型


第四弹 - CTE，VALUES，SEMIJOIN


第五弹 - SELECT TRANSFORM


第六弹 - User Defined Type


第七弹 - Grouping Set, Cube and Rollup


第八弹 - 动态类型函数


第九弹 - 脚本模式与参数视图


第十弹 - IF ELSE分支语句


第十一弹 - QUALIFY


本文将向您介绍 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 shop4FROM    shops_tableGROUP 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_tablepivot (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_tablepivot (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 shopsunion ALL select item_name,year, 'shop2' as shop_name, shop2 as count from shopsUNION ALL select item_name,year, 'shop3' as shop_name, shop3 as count from shopsUNION 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 shopsunpivot (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 shopsunpivot ((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 shopsunpivot ((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 语法，以更简洁易用的方式满足行转列和列转行的需求，简化了查询语句，提高了广大大数据开发者的生产力。

划线
评论
复制
发布于: 刚刚阅读数: 3

版权声明: 本文为 InfoQ 作者【阿里云大数据AI技术】的原创文章。

原文链接:【http://xie.infoq.cn/article/d8d514493972ef9418d343e62】。文章转载请联系作者。

大数据
用户头像

阿里云大数据AI技术

关注

还未添加个人签名 2020-10-15 加入

分享阿里云计算平台的大数据和AI方向的技术创新和趋势、实战案例、经验总结。

评论

发布
暂无评论
Copyright © 2023, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号 | 产品资质
深入MaxCompute -第十二弹 -PIVOT/UNPIVOT_大数据_阿里云大数据AI技术_InfoQ写作社区