题目内容

有用户标签表如下 user_tag

有一张标签维表 tag表

请取出如下内容

查询语句

select
tt.user_id,
concat_ws(',',collect_list(tag.tag_desc)) as tag_desc
from
(
select user_id,new_tag_id
from user_tag
lateral view outer explode(split(tag_id,',')) t as new_tag_id) tt
left join
tag
on tt.new_tag_id = tag.tag_id
group by tt.user_id

查询结果

查询逻辑

step1 列转行，将user_tag表中的tag_id 转换为多行，每行一个tag_id内容，该处需要注意第三行，tag_id为空，lateral view 属于内联接，所以需要使用lateral view outer,属于考察细心程度的点

select user_id,new_tag_id
from user_tag
lateral view outer explode(split(tag_id,',')) t as new_tag_id

step2 关联维表，由tag_id关联出tag_desc

select
    *
from
(
select user_id,new_tag_id
from user_tag
lateral view outer explode(split(tag_id,',')) t as new_tag_id) tt
left join
tag
on tt.new_tag_id = tag.tag_id

Step3 行转列，使用聚合函数将多行tag_desc 拼接为一行,得到结果

select
tt.user_id,
concat_ws(',',collect_list(tag.tag_desc)) as tag_desc
from
(
select user_id,new_tag_id
from user_tag
lateral view outer explode(split(tag_id,',')) t as new_tag_id) tt
left join
tag
on tt.new_tag_id = tag.tag_id
group by tt.user_id

基础数据准备

创建表user_tag

create table user_tag
(
  user_id bigint,
  tag_id string
)

创建表tag

create table tag
(
  tag_id bigint,
  tag_desc string
)

向user_tag中插入数据

insert into user_tag values
(111,'1,2,3'),
(222,'1,3,4'),
(333,null),
(444,'2');

向tag表中插入数据

insert into tag values
(1,'提前点送达'),
(2,'恶意刷单'),
(3,'非本人跑单'),
(4,'私自取消单');