ODPS的TRANS_ARRAY应用

–qlr拆分
drop table if exists tmp_fcxx_qlr;
create table tmp_fcxx_qlr as
select zl
,qlr_old
,qlr_new
from (
select trans_array(2, ‘;’, zl, qlr, qlr) as (zl, qlr_old, qlr_new)
from dc01_rkk_ml_zcxx_bdcqrxx
where dt = ‘${bdp.system.bizdate}^{\prime }andregex{p}_{e}xtract(qlr{,}^{\prime }([[:punct:]]{)}^{\prime }){=}^{\prime }{;}^{\prime }unionselecttran{s}_{a}rray(2{,}^{\prime }{,}^{\prime },zl,qlr,qlr)as(zl,ql{r}_{o}ld,ql{r}_{n}ew)fromdc01_{r}k{k}_m{l}_{z}cx{x}_{b}dcqrxxwheredt{=}^{\prime }${bdp.system.bizdate}’
and regexp_extract(qlr, ‘([[:punct:]])’) = ‘,’
union
select trans_array(2, ‘/’, zl, qlr, qlr) as (zl, qlr_old, qlr_new)
from dc01_rkk_ml_zcxx_bdcqrxx
where dt = ‘${bdp.system.bizdate}^{\prime }andregex{p}_{e}xtract(qlr{,}^{\prime }([[:punct:]]{)}^{\prime }){=}^{\prime }{/}^{\prime }unionselecttran{s}_{a}rray(2{,}^{\prime }{、}^{\prime },zl,qlr,qlr)as(zl,ql{r}_{o}ld,ql{r}_{n}ew)fromdc01_{r}k{k}_m{l}_{z}cx{x}_{b}dcqrxxwheredt{=}^{\prime }${bdp.system.bizdate}’
and regexp_extract(qlr, ‘([[:punct:]])’) = ‘、’
) t
where qlr_old is not null
;
trans_array (num_keys, separator, key1,key2,…,col1, col2,col3) as (key1,key2,…,col1, col2)
trans_array(2, ‘;’, zl, qlr, qlr) as (zl, qlr_old, qlr_new) – > 2指两列，zl / qlr两列，拆分第3列qlr，最后生成3列表：zl / 老权利人 / 新权利人

• 命令说明
  将一行数据转为多行的UDTF，将列中存储的以固定分隔符格式分隔的数组转为多行。

• 参数说明
  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。