ODPS SQL ——列转行、行转列这回让我玩明白了！_AI阅读总结

包阅导读总结

1. 关键词：MaxCompute、列转行、行转列、TRANS_ARRAY、LATERAL VIEW EXPLODE

2. 总结：本文详细介绍在 MaxCompute 中使用 TRANS_ARRAY 和 LATERAL VIEW EXPLODE 函数实现列转行及行转列，通过实例讲解操作方式、注意事项，并提及用 WM_CONCAT 函数完成行转列，最后提供了参考文档。

3. 主要内容：

– 列转行

– 场景：将 A 表中 self_code_list 转化为 a_tag_list，通过 join 处理映射关系。

– 方法：使用 TRANS_ARRAY 和 LATERAL VIEW EXPLODE 函数，前者需注意参数设置，后者入参须为 ARRAY。

– 差异：处理空列时结果不同，LATERAL VIEW EXPLODE 不保留空行，TRANS_ARRAY 保留。

– 行转列

– 过程：以特定 key 把多行数据某列拼接成一列或多列。

– 函数：使用 WM_CONCAT 函数，通过逗号拼接。

– 参考：更多相关内容可参考 MaxCompute 官方文档。

思维导图：

文章地址：https://mp.weixin.qq.com/s/93EiX5nUeTSxyDOt3SE1CA

文章来源：mp.weixin.qq.com

作者：高迅

发布时间：2024/7/31 10:10

语言：中文

总字数：1696字

预计阅读时间：7分钟

评分：86分

标签：SQL,数据处理,数据转换,列转行,行转列

以下为原文内容

本内容来源于用户推荐转载，旨在分享知识与观点，如有侵权请联系删除联系邮箱 media@ilingban.com

本文详细介绍了在MaxCompute中如何使用TRANS_ARRAY和LATERAL VIEW EXPLODE函数来实现列转行的功能。

有这样一种场景，需要将下面A表中的self_code_list转化为a_tag_list，self_code到a_tag有一一映射关系的，这个映射关系在B表中。对于映射关系的转化一般是用join的方式去解决（目前没有想到更好的方式，如果有哪位大神有更好的方式欢迎在评论区留言）。

图1-A表

图2-B表

对目前这种数据结构肯定是不好处理的，但是如果转化成图3中所示：

就可以用直接用self_code关联B表从而得到a_tag的值，如图4中所示，思路清晰、操作简单粗暴。

图4

所以核心的问题来了，应该怎么操作把A表转成图3中的样子，这种操作其实就是列转行，解释一下，就是把一行数据的某列（一般是数组）或者几列展开，并选某列或者某几列作为展开的key，把一行数据转成多行数据。在前面把表A从图1转成图3的案例中，我们是以id, name做为key把self_code_list这一列展开成多行。

在odps的内建函数中有两个函数可以帮我们轻而易举地完成列转行：

https://www.alibabacloud.com/help/zh/maxcompute/user-guide/trans-array

LATERAL VIEW EXPLODE(column)

https://www.alibabacloud.com/help/zh/maxcompute/user-guide/lateral-view

SELECT TRANS_ARRAY(2,',',id,name,self_code_list) AS (id,name,self_code) FROM (      SELECT id,name          ,ARRAY_JOIN(FROM_JSON(JSON_FORMAT(self_code_list),"array<string>"),',')          AS self_code_list       FROM  TABLE_A       ORDER BY id ASC )

表A里self_code_list字段类型是JSON，而TRANS_ARRAY 则要求转为行的的列类型必须是String，所以先把self_code_list转化为String 类型。

这里结合这个列子解释一下这个函数的参数：

trans_array (<num_keys>, <separator>, <key1>,<key2>,…,<col1>,<col2>,<col3>) as (<key1>,<key2>,...,<col1>, <col2>)

第一个参数是列转行时做为key的列数，在本例中我们用id和name作为key，所以是2。

第二个参数是把一个String展开为多个String，也就是一行变多行的分割符，根据具体数据的分割符号而定，一般是逗号，分号等。

剩下的参数是String类型的列名，函数会根据第一个参数来判断最后M个列是要展开的列，前面N个列是作为key的列。在本例子中我们列名参数依次是id, name, slef_code_list，而num_key = 2，所以结果集中id, name 两列会作为key 列，而self_code_list则是被展开的列。

SELECT id    ,name    ,self_codeFROM  TABLE_A    LATERAL VIEW EXPLODE(FROM_JSON(JSON_FORMAT(self_code_list),"array<string>")) tmp AS self_code;

需要注意的是EXPLODE 函数的入参必须是ARRAY的。

两种方式都是可以实现列转行，但是两者在处理为空的列会有细微的差别。

看下这几条原始的数据：

SELECT id, name, self_code_list from TABLE_A
whereidIN(291,112,116,252)；

图5

针对这四条数据分别用两种方式做转化。

使用TRANS_ARRAY

图6

使用LATERAW VIEW EXPLODE

图7

可以看到使用LATERAW VIEW EXPLODE的方式结果集不会保留为空的行，而TRANS_ARRAY的方式则会保留为空的行。

好了，列转行聊完了，该说说行转列。还记得我们初衷吗？我们是要把TableA的self_code_list映射成a_tag_list，如图8所示。经过前面的列转行操作就可以很轻易的和TABLE表关联，得到图4所示的临时表。

图8

从图4到图8的操作就是行转列，也就是把多行数据转化成一列或多个列。当然了这也不是瞎转的，跟列转行一样在转化时需要根据key来转化。列转行行转列是一个互逆的过程，在列转行时我们把每行的某列值拆分为多个值，然后按照key变成多行。那么行转列就是根据key把多行数据的某列拼接成一份数据，再依据key变成一行。在本例图4-图8的过程中，我们以id, name做为key，对atag列用逗号做拼接，id, name, a_tag_list组成唯一的一行。当然也可以转成多列，只需要在拼接的时候指定列的区分方式，然后再对列值做SPLIT 操作即可得到多列。这种拼接的方式可以通过函数WM_CONCAT。

https://www.alibabacloud.com/help/zh/maxcompute/user-guide/wm-concat

在上面的例子中我们是这样使用WM_CONCAT函数的：

SELECT id    ,name    ,WM_CONCAT(',',a_tag) a_tagfrom T_tmp_4;

这样我们就得到了图8所示的结果集。

至此我们完成了表的列转行、行转列，并最终达成了我们的目标。关于更多表的行转列、列转行还可以参考MaxCompute官方文档：

https://www.alibabacloud.com/help/zh/maxcompute/use-cases/transpose-rows-to-columns-or-columns-to-rows

分类

ODPS SQL ——列转行、行转列这回让我玩明白了！_AI阅读总结 — 包阅AI

以下为原文内容