文档

TRANS_ARRAY

更新时间:

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

使用限制

  • 所有作为key的列必须位于在前面,而要转置的列必须放在后面。

  • 在一个select中只能有一个UDTF,不可以再出现其他的列。

命令格式

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

参数说明

  • num_keys:必填。 BIGINT类型常量,值必须>=0。在转为多行时作为转置key的列的个数。

  • separator:必填。STRING类型常量,用于将字符串拆分成多个元素的分隔符。为空时返回报错。

  • keys:必填。转置时作为key的列, 个数由num_keys指定。如果num_keys指定所有的列都作为key(即num_keys等于所有列的个数),则只返回一行。

  • cols: 必填。要转为行的数组,keys之后的所有列视为要转置的数组,必须为STRING类型,存储的内容是字符串格式的数组,例如Hangzhou;Beijing;shanghai,是以分号(;)分隔的数组。

返回值说明

返回转置后的行,新的列名由as指定。作为key的列类型保持不变,其余所有的列是STRING类型。拆分成的行数以个数多的数组为准,不足的补NULL。

使用示例

  • 示例1:例如表t_table中的数据如下。

    +----------+----------+------------+
    | login_id | login_ip | login_time |
    +----------+----------+------------+
    | wangwangA | 192.168.0.1,192.168.0.2 | 20120101010000,20120102010000 |
    | wangwangB | 192.168.45.10,192.168.67.22,192.168.6.3 | 20120111010000,20120112010000,20120223080000 |
    +----------+----------+------------+
    --执行SQL。
    select trans_array(1, ",", login_id, login_ip, login_time) as (login_id,login_ip,login_time) from t_table;
    --返回结果如下。
    +----------+----------+------------+
    | login_id | login_ip | login_time |
    +----------+----------+------------+
    | wangwangB | 192.168.45.10 | 20120111010000 |
    | wangwangB | 192.168.67.22 | 20120112010000 |
    | wangwangB | 192.168.6.3 | 20120223080000 |
    | 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
  • 示例2:例如表mf_fun_array_test_t中的数据如下。

    +------------+------------+------------+------------+
    | id         | name       | login_ip   | login_time |
    +------------+------------+------------+------------+
    | 1          | Tom        | 192.168.100.1,192.168.100.2 | 20211101010101,20211101010102 |
    | 2          | Jerry      | 192.168.100.3,192.168.100.4 | 20211101010103,20211101010104 |
    +------------+------------+------------+------------+
    
    --用两个key,id和name进行转数组,执行SQL。
    select trans_array(2, ",", Id,Name, login_ip, login_time) as (Id,Name,login_ip,login_time) from mf_fun_array_test_t;
    --返回结果如下,已经对key,id和name进行分组拆解。
    +------------+------------+------------+------------+
    | id         | name       | login_ip   | login_time |
    +------------+------------+------------+------------+
    | 1          | Tom        | 192.168.100.1 | 20211101010101 |
    | 1          | Tom        | 192.168.100.2 | 20211101010102 |
    | 2          | Jerry      | 192.168.100.3 | 20211101010103 |
    | 2          | Jerry      | 192.168.100.4 | 20211101010104 |
    +------------+------------+------------+------------+

相关函数

TRANS_ARRAY函数属于其他函数,更多其他业务场景的函数请参见其他函数