腾讯大数据面试SQL-占据好友封面个数
一、题目
有两个表，朋友关系表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.解题思路
- 查询出用户的步数和好友的步数；
- 取出自己步数比好友步数多的记录;
- 按照friend_id进行分组排序，取出步数第一名的用户好友记录
- 关联用户好友表，计算占据封面个数；
2.难度分析
维度 评分
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、SQL
1.计算好友的朋友步数及好友自己的步数
执行SQL
--
select t1.user_id,
t2.steps as user_steps, --自己步数
t1.friend_id,
t3.steps as friend_steps --好友步数
from user_friend t1
left join user_steps t2
on t1.user_id = t2.user_id
left join user_steps t3
on t1.friend_id = t3.user_id执行结果
+-------------+-------------+---------------+---------------+
| t1.user_id | user_steps | t1.friend_id | friend_steps |
+-------------+-------------+---------------+---------------+
| 1 | 100 | 2 | 95 |
| 1 | 100 | 3 | 90 |
| 2 | 95 | 1 | 100 |
| 2 | 95 | 3 | 90 |
| 2 | 95 | 4 | 80 |
| 2 | 95 | 5 | 10 |
| 3 | 90 | 1 | 100 |
| 3 | 90 | 4 | 80 |
| 3 | 90 | 5 | 10 |
| 4 | 80 | 2 | 95 |
| 4 | 80 | 3 | 90 |
| 4 | 80 | 5 | 10 |
| 5 | 10 | 2 | 95 |
| 5 | 10 | 3 | 90 |
| 5 | 10 | 4 | 80 |
+-------------+-------------+---------------+---------------+2.取出自己步数比好友步数多的记录
执行SQL
select user_id,
user_steps,
friend_id,
friend_steps
from (select t1.user_id,
t2.steps as user_steps, --自己步数
t1.friend_id,
t3.steps as friend_steps --好友步数
from user_friend t1
left join user_steps t2
on t1.user_id = t2.user_id
left join user_steps t3
on t1.friend_id = t3.user_id) t
where user_steps > friend_steps执行结果
+----------+-------------+------------+---------------+
| user_id | user_steps | friend_id | friend_steps |
+----------+-------------+------------+---------------+
| 1 | 100 | 2 | 95 |
| 1 | 100 | 3 | 90 |
| 2 | 95 | 3 | 90 |
| 2 | 95 | 4 | 80 |
| 2 | 95 | 5 | 10 |
| 3 | 90 | 4 | 80 |
| 3 | 90 | 5 | 10 |
| 4 | 80 | 5 | 10 |
+----------+-------------+------------+---------------+3.按照friend_id进行分组排序，取出步数第一名的用户好友记录
注意：这里使用了row_number，可以使用rank或者dense_rank 具体根据需要进行选择；
执行SQL
select user_id,
user_steps,
friend_id,
friend_steps
from (select user_id,
user_steps,
friend_id,
friend_steps,
row_number() over (partition by friend_id order by user_steps desc ) rn
from (select t1.user_id,
t2.steps as user_steps, --自己步数
t1.friend_id,
t3.steps as friend_steps --好友步数
from user_friend t1
left join user_steps t2
on t1.user_id = t2.user_id
left join user_steps t3
on t1.friend_id = t3.user_id) t
where user_steps > friend_steps) tt
where rn = 1执行结果
+----------+-------------+------------+---------------+
| user_id | user_steps | friend_id | friend_steps |
+----------+-------------+------------+---------------+
| 1 | 100 | 2 | 95 |
| 1 | 100 | 3 | 90 |
| 2 | 95 | 4 | 80 |
| 2 | 95 | 5 | 10 |
+----------+-------------+------------+---------------+4.关联用户好友表，计算占据封面个数；
执行SQL
select ttt1.user_id,
count(ttt.friend_id) as fm_cnt
from user_friend ttt1
left join(select user_id,
user_steps,
friend_id,
friend_steps
from (select user_id,
user_steps,
friend_id,
friend_steps,
row_number() over (partition by friend_id order by user_steps desc ) rn
from (select t1.user_id,
t2.steps as user_steps, --自己步数
t1.friend_id,
t3.steps as friend_steps --好友步数
from user_friend t1
left join user_steps t2
on t1.user_id = t2.user_id
left join user_steps t3
on t1.friend_id = t3.user_id) t
where user_steps > friend_steps) tt
where rn = 1) ttt
on ttt1.user_id = ttt.user_id
and ttt1.friend_id = ttt.friend_id
group by ttt1.user_id执行结果
+---------------+---------+
| ttt1.user_id | fm_cnt |
+---------------+---------+
| 1 | 2 |
| 2 | 2 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
+---------------+---------+四、建表语句和数据插入
-- 创建好友关系表
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);本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2024-06-04，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
腾讯云开发者
