腾讯大数据面试SQL-微信运动步数在好友中的排名
一、题目
有两个表，朋友关系表t6_user_friend，用户步数表t6_user_steps。朋友关系表包含两个字段，用户id，用户好友的id；用户步数表包含两个字段，用户id，用户的步数.用户在好友中的排名
-- t6_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 |
+----------+------------+
--t6_user_friend数据
+---------------------+-------------------+
| t6_user_steps.user_id | t6_user_steps.steps |
+---------------------+-------------------+
| 1 | 100 |
| 2 | 95 |
| 3 | 90 |
| 4 | 80 |
| 5 | 10 |
+---------------------+-------------------+二、题目分析
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
解法分析
- 要求解的是自己在好友中的排名，那么需要有自己和好友的步数，可是好友关系表中只有“好友”，需要加入自己的数据；
- 求排名，需要分组开窗；
- 需要筛选出自己名次的那一行数据；
三、SQL
1.列出好友步数，并将自己步数添加到结果中
执行SQL
--好友步数
select t1.user_id, t1.friend_id, t2.steps
from t6_user_friend t1
join t6_user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自己步数
select user_id, user_id as friend_id, steps
from t6_user_steps查询结果
+--------------+----------------+------------+
| _u1.user_id | _u1.friend_id | _u1.steps |
+--------------+----------------+------------+
| 1 | 2 | 95 |
| 1 | 3 | 90 |
| 2 | 1 | 100 |
| 2 | 3 | 90 |
| 2 | 4 | 80 |
| 2 | 5 | 10 |
| 3 | 1 | 100 |
| 3 | 4 | 80 |
| 3 | 5 | 10 |
| 4 | 2 | 95 |
| 4 | 3 | 90 |
| 4 | 5 | 10 |
| 5 | 2 | 95 |
| 5 | 3 | 90 |
| 5 | 4 | 80 |
| 1 | 1 | 100 |
| 2 | 2 | 95 |
| 3 | 3 | 90 |
| 4 | 4 | 80 |
| 5 | 5 | 10 |
+--------------+----------------+------------+2.按照用户分组，给每个用户的“好友”进行排名
查询SQL
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 t6_user_friend t1
join t6_user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自己步数
select user_id,
user_id as friend_id,
steps
from t6_user_steps) tt1执行结果
+--------------+----------------+------------+----------+
| tt1.user_id | tt1.friend_id | tt1.steps | row_num |
+--------------+----------------+------------+----------+
| 1 | 1 | 100 | 1 |
| 1 | 2 | 95 | 2 |
| 1 | 3 | 90 | 3 |
| 2 | 1 | 100 | 1 |
| 2 | 2 | 95 | 2 |
| 2 | 3 | 90 | 3 |
| 2 | 4 | 80 | 4 |
| 2 | 5 | 10 | 5 |
| 3 | 1 | 100 | 1 |
| 3 | 3 | 90 | 2 |
| 3 | 4 | 80 | 3 |
| 3 | 5 | 10 | 4 |
| 4 | 2 | 95 | 1 |
| 4 | 3 | 90 | 2 |
| 4 | 4 | 80 | 3 |
| 4 | 5 | 10 | 4 |
| 5 | 2 | 95 | 1 |
| 5 | 3 | 90 | 2 |
| 5 | 4 | 80 | 3 |
| 5 | 5 | 10 | 4 |
+--------------+----------------+------------+----------+3求取最终结果
查询SQL
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 t6_user_friend t1
join t6_user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自己步数
select user_id,
user_id as friend_id,
steps
from t6_user_steps) tt1) tt2
where user_id = friend_id执行结果
+----------+----------+
| user_id | row_num |
+----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
+----------+----------+四、建表语句和数据插入
CREATE TABLE t6_user_friend
(
user_id INT,
friend_id INT
);
-- 插入数据
INSERT INTO t6_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 t6_user_steps
(
user_id INT,
steps INT
);
INSERT INTO t6_user_steps
VALUES (1, 100),
(2, 95),
(3, 90),
(4, 80),
(5, 10);本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；