MaxCompute：TRANS_ARRAY

使用限制

• 所有作為key的列必須位於在前面，而要轉置的列必須放在後面。

• 在一個select中只能有一個UDTF，不可以再出現其他的列。

• 不可以與group by、cluster by、distribute by、sort by一起使用。

命令格式

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函數屬於其他函數，更多其他業務情境的函數請參見其他函數。