HIVE场景题之合并切割四

1.2.3 切分字符串
编写sql实现如下：
uid tags
1 1,2,3
2 2,3
3 1,2

编写sql实现如下结果：
uid tag
1 1
1 2
1 3
2 2
2 3
3 1
3 2
create table t1( uid int,
tags string
)
row format delimited fields terminated by ‘\t’
;
load data local inpath ‘/hivedata/t1.txt’ into table t1;

select uid, tag from t1
lateral view explode(split(tags,",")) t2 as tag
;
1.2.4 合并字符串concat_ws+collect_list
用户标签连接查询
数据：
T1表:
Tags 1,2,3
1,2
2,3

T2表:
Id lab
1A
2B
3C

根据T1和T2表的数据，编写sql实现如下结果：
ids tags
1,2,3 A,B,C
1,2 A,B
2,3 B,C

select tags,
concat_ws(",",collect_list(lab)) from
(select t1.tags tags, t3.lab lab from
(select tags, id
from t2
lateral view explode(split(tags,",")) tmp as id) t1 join t3
on t1.id=t3.id) tmp group by tags
;
1.2.5 合并
用户标签组合
id tag flag
a b 2
a b 1
a b 3
c d 6
c d 8
c d 8

create table t4( id string,
tag string, flag int
)
row format delimited fields terminated by ‘\t’
;
load data local inpath ‘/hivedata/t4.txt’ overwrite into table t4;

select id, tag,
concat_ws("|",collect_set(flag)) flag from t4
group by id,tag
;

1.2.6 切分

1.2.6用户标签行列互换

uid name tags
1 goudan chihuo,huaci
2 mazi sleep
3 laotie paly

create table t5( uid string,
name string, tags string
)
row format delimited fields terminated by ‘\t’
;
load data local inpath ‘/hivedata/t5.txt’ overwrite into table t5;

select uid, name, tag from t5
lateral view explode(split(tags,",")) t1 as tag
;