腾讯大数据面试SQL-占据好友封面个数
一、题目
有两个表，朋友关系表t2_user_friend，用户步数表t2_user_steps。朋友关系表包含两个字段，用户id，用户好友的id；用户步数表包含两个字段，用户id，用户的步数
查询： 占据多少个好友的封面（在好友的列表中排行第一，且必须超过好友的步数）
-- t2_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 |
+----------+------------+
--t2_user_friend数据
+---------------------+-------------------+
| t2_user_steps.user_id | t2_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 t2_user_friend t1
left join t2_user_steps t2
on t1.user_id = t2.user_id
left join t2_user_steps t3
on t1.friend_id = t3.user_id执行结果
+-------------+-------------+---------------+---------------+
| t1.user_id | t2_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 t2_user_friend t1
left join t2_user_steps t2
on t1.user_id = t2.user_id
left join t2_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 t2_user_friend t1
left join t2_user_steps t2
on t1.user_id = t2.user_id
left join t2_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 t2_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 t2_user_friend t1
left join t2_user_steps t2
on t1.user_id = t2.user_id
left join t2_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 t2_user_friend
(
user_id INT,
friend_id INT
);
-- 插入数据
INSERT INTO t2_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 t2_user_steps
(
user_id INT,
steps INT
);
INSERT INTO t2_user_steps
VALUES (1, 100),
(2, 95),
(3, 90),
(4, 80),
(5, 10);本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；