[大数据]odps列转行之trans_array/trans_cols

trans_array

用于将一行数据转为多行的UDTF，将列中存储的以固定分隔符格式分隔的数组转为多行。?

命令格式

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

参数说明

● ?num_keys: bigint类型常量，必须>=0。在转为多行时作为转置key的列的个数。

Key是指在将一行转为多行时，在多行中重复的列。

● ?separator:string类型常量，用于将字符串拆分成多个元素的分隔符。为空时报异常。

● ?keys:转置时作为key的列， 个数由num_keys指定。如果num_keys指定所有的列都作为key（即num_keys等于所有列的

数），则只返回一行。

● ?cols: 要转为行的数组，keys之后的所有列视为要转置的数组，必须为string类型，存储的内容是字符串格式的数组，

“Hangzhou;Beijing;shanghai”，是以”;”分隔的数组。

返回：

? ? 转置后的行，新的列名由as指定。作为key的列类型保持不变，其余所有的列是string类型。拆分成的行数以个数多的数

为准，不足的补NULL。

使用限制

? ? UDTF使用上有一些限制

● ?所有作为key的列必须处在前面，而要转置的列必须放在后面。

● ?在一个select中只能有一个udtf，不可以再出现其它的列

● ?不可以与group by/cluster by/distribute by/sort by一起使用。

示例

表中的数据如

则 trans_array(1, “,”, login_id, login_ip, login_time) as (login_id,login_ip,login_time)

产生的数据是

Login_id ? ? ?Login_ip ? ? ? ?Login_time

wangwangA ? ? 192.168.0.1 ? ? 20120101010000

wangwangA ? ? 192.168.0.2 ? ? 20120102010000

如果表中的数据是

则对数组中不足的数据补NULL

Login_id ? ? Login_ip ? ? ? ?Login_time

wangwangA ? ?192.168.0.1 ? ? 20120101010000

wangwangA ? ?192.168.0.2 ? ? NULL

trans_cols

用于将一行数据转为多行的UDTF，将不同的列转为行。?

命令格式

trans_cols (num_keys, key1,key2,…,col1, col2,col3) as (idx, key1,key2,…,col1, col2)

参数说明

? ? num_keys: bigint类型常量，必须>=0。在转为多行时作为转置key的列的个数。

Key是指在将一行转为多行时，在多行中重复的列,如要将A,B,C,D转为

A,B,C

A,B,D

则A,B列为key

keys: 转置时作为key的列，由num_keys决定哪些列作为key。

cols: 要转为行的列，类型必须相同。

返回：

? ? 转置后新的列名由as指定。输出的第一列是转置的下标，下标从1开始。

作为key的列类型保持不变，其余所有的列与原来类型一致。如果num_keys指定所有的列都作为key（即num_keys等于所

列的个数），则只返回一行。

使用限制

? ? UDTF使用上有一些限制

● ?所有作为key的列必须处在前面，而要转置的列必须放在后面。

● ?在一个select中只能有一个udtf，不可以再出现其它的列，如不可以写成

Select login_id, trans_cols(1, login_id, login_ip1, login_ip2) as(idx, login_id, login_ip)

● ?不可以与roup by/cluster by/distribute by/sort by一起使用。

示例

表中的数据如

则 trans_cols(1, login_id, login_ip1, login_ip2) as (idx, login_id, login_ip)

的输出为:

idx ? ?Login_id ? ? Login_ip

1 ? ? ?wangwangA ? ?192.168.0.1

2 ? ? ?wangwangA ? ?192.168.0.2

如果对你有帮助，就请点个赞吧～