split:按特定符号切分为数组
explode:将数组数据炸开,单个select限制一个explode
LATERAL VIEW:侧写函数,可以使explode同其他字段一起查询
select
distinct c1
,case
when c2 = 'A级' then c2
when LOCATE('线',c2)=0 then concat(c2,'线')
else c2 end as c2
,c3
,c4
from table as a
LATERAL VIEW explode(split(a.c1,',')) a AS c1
LATERAL VIEW explode(split(a.c2,',')) a AS c2
where a.ds = '${yyyyMMdd,-1d}'
单独使用情况下:
select
explode(split(c1,',') ) as c1
from table
where ds = '${yyyyMMdd,-1d}'
spilt以后的数据,
结果
select
distinct explode(split(region_code,',') ) as region_code
select distinct c1
from table_name as a
lateral view explode(split(region_code,',')) a as c1
where ds = '${yyyyMMdd,-1d}'