一、题目
有两个表，朋友关系表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);本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术“发表。原文: 同时有“数据仓库技术”社群以及有几十位小伙伴一起讨论数据仓库相关技术，欢迎你的加入，社群免费。
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
