一、题目

有两个表，朋友关系表user_friend，用户步数表user_steps。朋友关系表包含两个字段，用户id，用户好友的id；用户步数表包含两个字段，用户id，用户的步数

查询：占据多少个好友的封面（在好友的列表中排行第一，且必须超过好友的步数）

user_friend 数据

+----------+------------+| user_id  | friend_id  |+----------+------------+| 1        | 2          || 1        | 3          || 2        | 1          || 2        | 3          || 2        | 4          || 2        | 5          || 3        | 1          || 3        | 4          || 3        | 5          || 4        | 2          || 4        | 3          || 4        | 5          || 5        | 2          || 5        | 3          || 5        | 4          |+----------+------------+

user_friend数据

+---------------------+-------------------+| user_steps.user_id  | user_steps.steps  |+---------------------+-------------------+| 1                   | 100               || 2                   | 95                || 3                   | 90                || 4                   | 80                || 5                   | 10                |+---------------------+-------------------+

二、题目分析

1.计算步骤

1.要求在好友中排名第一，由于好友关系是相互的，所以查出user_id的步数，然后按照friend_id分组，得出好友的朋友步数列表。

2.取排名第一的数据,然后就知道在好友的好友列表中排名的第一的人了；

3.将结果关联出好友的步数；

4.然后把第一的数据和朋友的步数进行比较得出是否是否占据了朋友封面；

5.需要计算所有用户，使用步数表拿到全量用户进行关联计算；

2.难度分析

维度	评分
题目难度	⭐️⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

三、SQL

1.给好友的好友步数

--好友的好友步数select t1.user_id,t1.friend_id,t2.steps as user_stepsfrom user_friend t1join user_steps t2on t1.friend_id = t2.user_id

结果如下：注意第三列，是user_id对应的步数，即好友的好友步数

2.好友的好友步数排名，并取出排名第一的人、步数

--好友的好友步数排名selectuser_id,friend_id,user_stepsfrom(select t1.user_id,t1.friend_id,t2.steps as user_steps,row_number()over(partition by t1.friend_id order by t2.steps desc) as row_numfrom user_friend t1join user_steps t2on t1.user_id = t2.user_id) twhere row_num = 1

结果如下：

3.关联好友步数；

with t_no1 as --好友的好友步数排名(selectuser_id,friend_id,user_stepsfrom(select t1.user_id,t1.friend_id,t2.steps as user_steps,row_number()over(partition by t1.friend_id order by t2.steps desc) as row_numfrom user_friend t1join user_steps t2on t1.user_id = t2.user_id) twhere row_num = 1)selectt_no1.user_id,t_no1.friend_id,t_no1.user_steps,t_steps.steps as friend_stepsfrom t_no1join user_steps t_stepson t_no1.friend_id = t_steps.user_id

查询结果如下

4.增加判断条件，得出是否占据好友封面

with t_no1 as(--好友的好友步数排名selectuser_id,friend_id,user_stepsfrom(select t1.user_id,t1.friend_id,t2.steps as user_steps,row_number()over(partition by t1.friend_id order by t2.steps desc) as row_numfrom user_friend t1join user_steps t2on t1.user_id = t2.user_id) twhere row_num = 1)selectt_no1.user_id as user_id,t_no1.friend_id as friend_id,t_no1.user_steps as user_steps,t_steps.steps as friend_steps,if(t_no1.user_steps>t_steps.steps,1,0) as flagfrom t_no1join user_steps t_stepson t_no1.friend_id = t_steps.user_id

查询结果如下：

5.查询结果

with t_no1 as(--好友的好友步数排名selectuser_id,friend_id,user_stepsfrom(select t1.user_id,t1.friend_id,t2.steps as user_steps,row_number()over(partition by t1.friend_id order by t2.steps desc) as row_numfrom user_friend t1join user_steps t2on t1.user_id = t2.user_id) twhere row_num = 1),--查询标签中间结果t_flag as(selectt_no1.user_id as user_id,t_no1.friend_id as friend_id,t_no1.user_steps as user_steps,t_steps.steps as friend_steps,if(t_no1.user_steps>t_steps.steps,1,0) as flagfrom t_no1join user_steps t_stepson t_no1.friend_id = t_steps.user_id)selectuser_steps.user_id,nvl(sum(flag),0) as numfrom user_stepsleft joint_flagon user_steps.user_id = t_flag.user_idgroup by user_steps.user_id

查询结果

四、建表语句和数据插入

-- 创建好友关系表CREATE TABLE user_friend (  user_id INT,  friend_id INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 插入数据INSERT INTO user_friend VALUES (1, 2), (1, 3), (2, 1), (2, 3), (2, 4), (2, 5),(3, 1), (3, 4), (3, 5),(4, 2), (4, 3), (4, 5),(5, 2), (5, 3), (5, 4);
CREATE TABLE user_steps(  user_id INT,  steps INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
INSERT INTO user_steps VALUES (1,100),(2,95),(3,90),(4,80),(5,10);

点点关注不迷路，天天学习有进步

分享↓收藏↓点赞↓再看↓

ITPub博客

hiveSQL-占据多少个好友的封面