SQL 公会成员活跃度排名：RANK窗口函数分组排序（网易面试题）

一、题目背景

这道题来自网易游戏事业部的数据分析岗面试。大型多人在线游戏的公会系统是玩家社交的核心载体，公会排名直接关系到资源分配和荣誉展示。公会内部的成员活跃度排名需要综合多个维度——仅看在线时长容易刷，仅看贡献值又忽略了陪伴价值——所以用加权公式计算活跃度分数，再用 DENSE_RANK 实现连续的并列排名。

业务场景：每周公会战结算时，系统根据成员活跃度排名发放贡献奖励。DENSE_RANK 确保活跃度相同的成员获得相同奖励档次，且不会因为跳号导致"中间档位空缺"。

二、题目

现有一张公会成员活跃度表 t3_guild_activity，记录了各公会成员的在线时长和贡献值。请在每个公会内部按活跃度（在线时长 + 贡献值 * 10）对成员进行排名，要求并列的活跃度使用相同排名且排名连续（即 DENSE_RANK）。

公会活跃度表 t3_guild_activity：

+-----------+------------+-----------+----------+
| guild_id  | member_id  | duration  | contrib  |
+-----------+------------+-----------+----------+
| G01       | M001       | 120       | 500      |
| G01       | M002       | 200       | 300      |
| G01       | M003       | 150       | 450      |
| G01       | M004       | 150       | 500      |
| G02       | M005       | 100       | 600      |
| G02       | M006       | 180       | 400      |
| G02       | M007       | 180       | 400      |
| G02       | M008       | 90        | 200      |
+-----------+------------+-----------+----------+

三、思路分析

本题考察窗口函数 DENSE_RANK() 在分组内按复合计算值排序的应用。核心公式：活跃度 = duration + contrib * 10。

解题步骤：

1. 计算每个成员的活跃度分数 activity_score = duration + contrib * 10；
2. 使用 DENSE_RANK() OVER (PARTITION BY guild_id ORDER BY activity_score DESC) 进行排名；
3. 最终结果按公会和排名排序展示。

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

四、逐步推导

1. 计算活跃度分数

执行SQL

select guild_id,
       member_id,
       duration,
       contrib,
       duration + contrib * 10 as activity_score
from t3_guild_activity

执行结果

+-----------+------------+-----------+----------+-----------------+
| guild_id  | member_id  | duration  | contrib  | activity_score  |
+-----------+------------+-----------+----------+-----------------+
| G01       | M001       | 120       | 500      | 5120            |
| G01       | M002       | 200       | 300      | 3200            |
| G01       | M003       | 150       | 450      | 4650            |
| G01       | M004       | 150       | 500      | 5150            |
| G02       | M005       | 100       | 600      | 6100            |
| G02       | M006       | 180       | 400      | 4180            |
| G02       | M007       | 180       | 400      | 4180            |
| G02       | M008       | 90        | 200      | 2090            |
+-----------+------------+-----------+----------+-----------------+
8 rows selected (0.212 seconds)(https://www.dwsql.com)

2. 使用 DENSE_RANK 排名

执行SQL

select guild_id,
       member_id,
       duration,
       contrib,
       activity_score,
       dense_rank() over (partition by guild_id order by activity_score desc) as rank
from (
    select guild_id,
           member_id,
           duration,
           contrib,
           duration + contrib * 10 as activity_score
    from t3_guild_activity
) t
order by guild_id, rank

执行结果

+-----------+------------+-----------+----------+-----------------+-------+
| guild_id  | member_id  | duration  | contrib  | activity_score  | rank  |
+-----------+------------+-----------+----------+-----------------+-------+
| G01       | M004       | 150       | 500      | 5150            | 1     |
| G01       | M001       | 120       | 500      | 5120            | 2     |
| G01       | M003       | 150       | 450      | 4650            | 3     |
| G01       | M002       | 200       | 300      | 3200            | 4     |
| G02       | M005       | 100       | 600      | 6100            | 1     |
| G02       | M006       | 180       | 400      | 4180            | 2     |
| G02       | M007       | 180       | 400      | 4180            | 2     |
| G02       | M008       | 90        | 200      | 2090            | 3     |
+-----------+------------+-----------+----------+-----------------+-------+
8 rows selected (0.612 seconds)(https://www.dwsql.com)

五、常见坑点

坑1：DENSE_RANK vs ROW_NUMBER 选错

题目明确要求"并列用相同排名且排名连续"，这是 DENSE_RANK 的特征——DENSE_RANK 并列不跳号（1,2,2,3），RANK 并列跳号（1,2,2,4），ROW_NUMBER 并列不分（1,2,3,4）。如果选错函数，要么排名不连续，要么并列被强行区分，都不符合题意。

坑2：复合公式计算位置

活跃度 = duration + contrib * 10，在子查询中先算好 activity_score，外层直接 ORDER BY activity_score DESC 排名，比在 ROW_NUMBER 内部写 ORDER BY duration + contrib * 10 更清晰，也避免窗口函数里重复计算。

坑3：PARTITION BY 写错导致跨公会排名

PARTITION BY guild_id 确保每个公会内部独立排名。如果漏写 PARTITION BY，所有公会的成员混在一起排（比如 G01 的成员排到第1，G02 的成员从第6开始），结果完全错误。

六、举一反三

1. 多重排序规则：如果活跃度相同，再按 duration DESC 打破平局，改 ORDER BY activity_score DESC, duration DESC。这是面试中常见追问——"如果有两个成员活跃度完全相同怎么排？"

2. Top N 活跃成员榜单：在排名结果上 WHERE rank <= 10，取每个公会活跃度前 10 名成员，用于公会内表彰和额外奖励发放

3. 活跃度分层分析：用 NTILE(4) OVER (PARTITION BY guild_id ORDER BY activity_score DESC) 将每个公会成员分为 4 个活跃度层级（核心/活跃/普通/潜水），统计各层人数占比，判断公会是否健康

4. 跨周期活跃度对比：记录本周和上周的排名，计算排名变化 rank_this_week - rank_last_week，正值表示排名上升（更活跃），用 LAG 或自连接 JOIN 上周表实现

七、知识点总结

考点	说明
DENSE_RANK	连续排名不跳号，ORDER BY activity_score DESC 活跃度越高排名越靠前
PARTITION BY	按公会分组，每个公会内部独立排名，互不干扰
子查询预计算	活跃度分数在子查询中算好，外层直接使用，逻辑清晰易维护
复合排序字段	活跃度 = 在线时长 + 贡献值 × 10，权重公式体现了不同维度的业务价值

八、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
CREATE TABLE t3_guild_activity (
    guild_id  string COMMENT '公会ID',
    member_id string COMMENT '成员ID',
    duration  int    COMMENT '在线时长(小时)',
    contrib   int    COMMENT '贡献值'
) COMMENT '公会成员活跃度表';

-- 数据插入
INSERT INTO t3_guild_activity VALUES
('G01', 'M001', 120, 500),
('G01', 'M002', 200, 300),
('G01', 'M003', 150, 450),
('G01', 'M004', 150, 500),
('G02', 'M005', 100, 600),
('G02', 'M006', 180, 400),
('G02', 'M007', 180, 400),
('G02', 'M008', 90, 200);

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题