1题目

现有用户关注者列表记录表t_user_follower，有两个字段,用户ID（user_id），关注者列表（follower_ids),关注者列表中是关注用户的用户ID，数据样例如下：

请找出互相关注的用户

2.考点

follower_ids是一个列表，所以需要进行展开，用到UDTF函数（列转行,炸裂函数）lateral view
字符串拼接函数concat_ws()
if()

3.SQL

step1：把follower_ids炸裂，转换成多行

-- 炸裂函数select  user_id,  follower_ids,  follower_idfrom t_user_followerlateral view explode(split(follower_ids,',')) t as follower_id

执行结果

step2:把user和follower_id进行有序拼接，从而把0001关注0002和0002关注0001拼接成相同的字符串，使得两行有相同的内容，产生关联。

select    user_id,    follower_id,    if(user_id<follower_id,concat_ws(',',user_id,follower_id),concat_ws(',',follower_id,user_id)) as friend,    follower_idsfrom t_user_follower lateral view explode(split(follower_ids,',')) t as follower_id

执行结果

step3:根据拼接字符串进行统计，并限定行数为2，从而得到最终结果。

select    friendfrom    (        select            user_id,            follower_ids,            follower_id,            if(user_id<follower_id,concat_ws(',',user_id,follower_id),concat_ws(',',follower_id,user_id)) as friend        from t_user_follower lateral view explode(split(follower_ids,',')) t as follower_id    )ttgroup by friendhaving count(1) =2

执行结果

4.建表语句

-- 建表语句create table t_user_follower(user_id string comment '用户id',follower_ids string comment '关注者列表');

-- 数据插入语句insert into t_user_follower values('0001','0002,0003'),('0002','0001,0003'),('0003','0004'),('0004','0001,0002');

ITPub博客

SQL总结大厂真题-互相关注

最新文章