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一起使用。

示例

表中的数据如

Login_id	LOGIN_IP	LOGIN_TIME
wangwangA	192.168.0.1,192.168.0.2	20120101010000,20120102010000

则 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

如果表中的数据是

Login_id	LOGIN_IP	LOGIN_TIME
wangwangA	192.168.0.1,192.168.0.2	20120101010000

则对数组中不足的数据补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一起使用。

示例

表中的数据如

Login_id	Login_ip1	Login_ip2
wangwangA	192.168.0.1	192.168.0.2

则 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

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