微信运动步数好友排名：UNION ALL + ROW_NUMBER 分组排序（腾讯面试题）

数据仓库晨曦

发布于 2026-07-16 21:23:52

一、题目背景

这道题来自腾讯微信事业群的数据开发岗面试。微信运动是微信内置的运动记录功能，用户每天可以看到自己的步数以及好友的步数排行榜。排行榜的核心逻辑是：给定用户和好友关系，计算每个用户在各自好友圈中的步数排名。这道题考察的就是如何在关系表中补齐自身数据，再用窗口函数进行分组排名。

业务场景：微信运动每晚8点会推送"今日步数好友排名"消息，需要根据用户的步数数据 + 好友关系数据，实时计算出每个用户在好友中的步数排名。这道题的 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_steps：

+----------+--------+
| user_id  | steps  |
+----------+--------+
| 1        | 100    |
| 2        | 95     |
| 3        | 90     |
| 4        | 80     |
| 5        | 10     |
+----------+--------+

三、思路分析

这是关系表补全 + 窗口函数排名的基础题型，核心是 UNION ALL 补全自身 + ROW_NUMBER 分组排序 + 自筛选：

补全自身数据：好友关系表中只有"用户→好友"的关系，但排名需要包含用户自己。用 UNION ALL 将 SELECT user_id, user_id AS friend_id, steps FROM t6_user_steps 追加到好友步数结果中
分组排名：用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY steps DESC) 按用户分组、按步数降序排名
筛选自身排名：WHERE user_id = friend_id 过滤出每个用户自己的排名记录

维度	评分
题目难度	⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

四、逐步推导

步骤1：UNION ALL 拼接好友步数和自身步数

先将好友关系表 JOIN 步数表获取好友的步数，再 UNION ALL 追加用户自身的步数（user_id 同时作为 friend_id）。

执行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

执行结果

+----------+------------+--------+
| user_id  | friend_id  | 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     |
+----------+------------+--------+
20 rows selected (0.571 seconds)(https://www.dwsql.com)

步骤2：按用户分组，用 ROW_NUMBER 对好友步数排名

对步骤1的结果，按 user_id 分组、按 steps 降序排名，得到每个用户好友圈内所有成员的步数排名。

执行SQL

select user_id,
       friend_id,
       steps,
       row_number() over (partitionby user_id orderby 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
         unionall
         -- 自身步数
         select user_id, user_id as friend_id, steps
         from t6_user_steps
     ) tt1

执行结果

+----------+------------+--------+----------+
| user_id  | friend_id  | 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        |
+----------+------------+--------+----------+
20 rows selected (1.128 seconds)(https://www.dwsql.com)

步骤3：筛选出自身排名，得到最终结果

在步骤2的基础上，通过 WHERE user_id = friend_id 过滤出每个用户自己对应的那一条排名记录。

执行SQL

select user_id, row_num
from (
    select user_id,
           friend_id,
           steps,
           row_number() over (partitionby user_id orderby 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
             unionall
             -- 自身步数
             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        |
+----------+----------+
5 rows selected (0.777 seconds)(https://www.dwsql.com)

五、常见坑点

坑1：忘记 UNION ALL 自身数据

好友关系表只存了"用户→好友"的映射，如果不 UNION ALL 用户自己的步数记录，排名中就没有自己。排行榜中需要看到自己排在什么位置，所以必须把 user_id, user_id AS friend_id, steps 追加进来。

坑2：ROW_NUMBER vs RANK vs DENSE_RANK

如果两个好友步数相同，ROW_NUMBER() 会随机给其中一个排1、另一个排2（并列时不重复）；RANK() 会给相同步数的人排相同名次，但下一个跳过（1,1,3）；DENSE_RANK() 不跳过（1,1,2）。本题没有出现并列步数，所以 ROW_NUMBER 和 RANK 结果一致，但实际业务中应根据需求选择。

坑3：WHERE 条件放在外层而不是内层

不能在子查询中直接 WHERE user_id = friend_id 后再开窗排名——这样会把好友数据全过滤掉，只剩下自己一条记录，ROW_NUMBER 的结果永远是1。必须先在完整数据集上排名，再到最外层筛选。

六、举一反三

使用 RANK() 处理并列名次：如果步数可能相同，将 ROW_NUMBER() 换成 RANK() 可以得到更合理的并列排名（如两人并列第2，下一个人是第4名）
按日期维度统计每日排名：增加 date 字段，PARTITION BY user_id, date ORDER BY steps DESC，可产出每个用户每日在好友中的步数排名趋势
DENSE_RANK 连续排名：用 DENSE_RANK() 实现连续排名（1,2,2,3...），适合前端展示"第N名"的榜单场景
好友步数 TOP-N：在步骤2的结果上 WHERE row_num <= 3 即可得到每个用户好友圈内步数最高的TOP3好友

七、知识点总结

将好友步数和自身步数合并为同一个数据集

八、建表语句和数据插入

CREATE TABLE t6_user_friend (
    user_id   intCOMMENT'用户ID',
    friend_id intCOMMENT'好友ID'
) COMMENT'好友关系表';

CREATETABLE t6_user_steps (
    user_id intCOMMENT'用户ID',
    steps   intCOMMENT'步数'
) COMMENT'用户步数表';

INSERTINTO 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);

INSERTINTO t6_user_steps VALUES
(1,100),(2,95),(3,90),(4,80),(5,10);

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-15，如有侵权请联系 cloudcommunity@tencent.com 删除

微信