hive高频面试题1.3用户标签连接查询

数据：
T_1表:
Tags
1,2,3
1,2
2,3

T_2表:
Id  lab
1 A
2 B
3 C

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

1.需要将t_1表中tags炸开，并且显示tags

select
ids,
tags
from t_1
lateral view explode(split(tags,",")) tmp as ids

2.t_1和t_2进行连接

select
tags,
lab
from
(
select
ids,
tags
from t_1
lateral view explode(split(tags,",")) tmp as ids
) t_3
join t_2
on t_2.id=t_3.ids

 

 3.对lab进行列转行＋拼接

select
tags ids,
concat_ws(',',collect_list(lab)) tags
from
(
select
tags,
lab
from
(
select
ids,
tags
from t_1
lateral view explode(split(tags,",")) tmp as ids
) t_3
join t_2
on t_2.id=t_3.ids
) t_4
group by tags