hiveSQL-占据多少个好友的封面
一、题目
有两个表，朋友关系表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_steps
from user_friend t1
join user_steps t2
on t1.friend_id = t2.user_id结果如下：注意第三列，是user_id对应的步数，即好友的好友步数
2.好友的好友步数排名，并取出排名第一的人、步数
--好友的好友步数排名
select
user_id,
friend_id,
user_steps
from(
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_num
from user_friend t1
join user_steps t2
on t1.user_id = t2.user_id) t
where row_num = 1结果如下：
3.关联好友步数；
with t_no1 as --好友的好友步数排名
(
select
user_id,
friend_id,
user_steps
from(
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_num
from user_friend t1
join user_steps t2
on t1.user_id = t2.user_id) t
where row_num = 1)
select
t_no1.user_id,
t_no1.friend_id,
t_no1.user_steps,
t_steps.steps as friend_steps
from t_no1
join user_steps t_steps
on t_no1.friend_id = t_steps.user_id查询结果如下
4.增加判断条件，得出是否占据好友封面
with t_no1 as
(--好友的好友步数排名
select
user_id,
friend_id,
user_steps
from(
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_num
from user_friend t1
join user_steps t2
on t1.user_id = t2.user_id) t
where row_num = 1)
select
t_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 flag
from t_no1
join user_steps t_steps
on t_no1.friend_id = t_steps.user_id查询结果如下：
5.查询结果
with t_no1 as
(--好友的好友步数排名
select
user_id,
friend_id,
user_steps
from(
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_num
from user_friend t1
join user_steps t2
on t1.user_id = t2.user_id) t
where row_num = 1),
--查询标签中间结果
t_flag as(
select
t_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 flag
from t_no1
join user_steps t_steps
on t_no1.friend_id = t_steps.user_id)
select
user_steps.user_id,
nvl(sum(flag),0) as num
from user_steps
left join
t_flag
on user_steps.user_id = t_flag.user_id
group 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);
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2023-06-05，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录