HiveSql-微信运动在好友中的排名
一、题目
有两个表，朋友关系表user_friend，用户步数表user_steps。朋友关系表包含两个字段，用户id，用户好友的id；用户步数表包含两个字段，用户id，用户的步数
1.用户在好友中的排名
user_friend 数据
+----------+------------+
| user_id | friend_id |
+----------+------------+
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 3 | 4 |
| 4 | 2 |
| 4 | 3 |
| 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.要求解的是自己在好友中的排名，那么需要有自己和好友的步数，可是好友关系表中只有“好友”，需要加入自己的数据；
2.求排名，需要分组开窗；
3.需要筛选出自己名次的那一行数据；
三、SQL
1.列出好友步数，并将自己步数添加到结果中
--好友步数
select t1.user_id,t1.friend_id,t2.steps
from user_friend t1
join user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自己步数
select user_id,user_id as friend_id,steps
from user_steps查询结果如下：
2.按照用户分组，给每个用户的“好友”进行排名
select
tt1.user_id,
tt1.friend_id,
tt1.steps,
row_number()over(partition by tt1.user_id order by tt1.steps desc) as row_num
from
(
--好友步数
select
t1.user_id,
t1.friend_id,
t2.steps
from user_friend t1 join user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自己步数
select
user_id,
user_id as friend_id,
steps
from user_steps
) tt1结果如下，我们最终需要的是红色框出来的行，所以再筛选出来,去掉非必要字段。
3求取最终结果
select
user_id,
row_num
from
(
select
tt1.user_id,
tt1.friend_id,
tt1.steps,
row_number()over(partition by tt1.user_id order by tt1.steps desc) as row_num
from
(
--好友步数
select
t1.user_id,
t1.friend_id,
t2.steps
from user_friend t1 join user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自己步数
select
user_id,
user_id as friend_id,
steps
from user_steps
) tt1
)tt2
where user_id = friend_id查询结果
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2023-06-02，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录