1、建表
create table if not exists temp.lateral_test
(id string,
value string
)
ROW format delimited FIELDS TERMINATED BY ',' ;
2、插入数据
load data local inpath 'latearl.txt' overwrite into table temp.lateral_test;
select * from lateral_test;
数据如下:
3.1、collect_set:去重、变数组(列传行)
select id,collect_set(value) as a from temp.lateral_test group by id
3.2、数组前加序号访问对应元素,从0开始
select id,
a[0] a0,
a[1] a1
from
(select id,collect_set(value) as a from temp.lateral_test group by id) b
4、利用lateral view explode 对3.1的数据实现行转列(k、hh别名不可少)
select id,
hh
from
(select id,collect_set(value)as a from temp.lateral_test group by id)t
lateral view explode(a)k as hh
PS:explode 可以把单行数组类型数据转为列形式:
select explode(split(concat_ws(',','1','2','3','4'),','))