SQL 直播间送礼价值Top 3观众：ROW_NUMBER窗口函数（快手面试题）

一、题目

已知表 t4_gift_log 记录了直播间观众的送礼明细，包含字段：user_id（送礼用户）、live_room_id（直播间）、gift_id（礼物ID）、gift_price（礼物单价）、gift_count（礼物数量）、gift_time（送礼时间）。

请统计每个直播间送礼价值最高的前3名观众。送礼价值 = 礼物单价 × 礼物数量（gift_price × gift_count）。注意：同一观众可能在一个直播间内多次送礼，需要先按观众汇总总送礼价值，再取每个直播间的前3名。

样例数据

+----------+---------------+----------+-------------+-------------+----------------------+
| user_id  | live_room_id  | gift_id  | gift_price  | gift_count  |      gift_time       |
+----------+---------------+----------+-------------+-------------+----------------------+
| u001     | 1001          | G01      | 10          | 3           | 2024-06-01 20:01:00  |
| u001     | 1001          | G02      | 5           | 2           | 2024-06-01 20:05:00  |
| u002     | 1001          | G03      | 50          | 1           | 2024-06-01 20:02:00  |
| u003     | 1001          | G01      | 10          | 1           | 2024-06-01 20:03:00  |
| u004     | 1001          | G04      | 20          | 2           | 2024-06-01 20:04:00  |
| u005     | 1001          | G05      | 8           | 1           | 2024-06-01 20:06:00  |
| u006     | 1002          | G06      | 30          | 2           | 2024-06-01 21:01:00  |
| u007     | 1002          | G03      | 50          | 1           | 2024-06-01 21:02:00  |
| u008     | 1002          | G02      | 5           | 10          | 2024-06-01 21:03:00  |
| u009     | 1002          | G04      | 20          | 1           | 2024-06-01 21:04:00  |
| u010     | 1002          | G01      | 10          | 1           | 2024-06-01 21:05:00  |
| u011     | 1002          | G05      | 8           | 1           | 2024-06-01 21:06:00  |
+----------+---------------+----------+-------------+-------------+----------------------+

二、思路分析

这道题是典型的"分组内取 Top N"问题，核心是窗口函数 row_number 的使用。思路分三步：

• 先聚合：同一个观众在同一个直播间可能送过多次礼物，必须先按 (live_room_id, user_id) 分组，用 sum(gift_price * gift_count) 汇总出每个观众的总送礼价值。
• 再排名：用 row_number() over (partition by live_room_id order by 送礼价值 desc) 对每个直播间内的观众按送礼价值降序编号。
• 取前3名：外层过滤 rn <= 3，即得到每个直播间送礼价值最高的前3名观众。

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

三、逐步推导

步骤1：按观众汇总每个直播间的送礼价值

先按（直播间，观众）分组，把同一观众的多次送礼累加成总送礼价值。

执行SQL

select
    live_room_id,
    user_id,
    sum(gift_price * gift_count) as gift_value
from t4_gift_log
group by live_room_id, user_id

执行结果

+---------------+----------+-------------+
| live_room_id  | user_id  | gift_value  |
+---------------+----------+-------------+
| 1001          | u002     | 50          |
| 1001          | u003     | 10          |
| 1001          | u001     | 40          |
| 1001          | u004     | 40          |
| 1001          | u005     | 8           |
| 1002          | u008     | 50          |
| 1002          | u007     | 50          |
| 1002          | u010     | 10          |
| 1002          | u009     | 20          |
| 1002          | u011     | 8           |
| 1002          | u006     | 60          |
+---------------+----------+-------------+
11 rows selected (0.444 seconds)(https://www.dwsql.com)

注意：u001 在直播间 1001 送过两次礼（10×3 + 5×2 = 40），汇总后合并成一条记录。

步骤2：用 ROW_NUMBER 按直播间分组排名

在汇总结果上，用 row_number() 按直播间分区、按送礼价值降序编号。

执行SQL

select
    live_room_id,
    user_id,
    gift_value,
    row_number() over (partition by live_room_id order by gift_value desc) as rn
from (
    select
        live_room_id,
        user_id,
        sum(gift_price * gift_count) as gift_value
    from t4_gift_log
    group by live_room_id, user_id
) t

执行结果

+---------------+----------+-------------+-----+
| live_room_id  | user_id  | gift_value  | rn  |
+---------------+----------+-------------+-----+
| 1001          | u002     | 50          | 1   |
| 1001          | u001     | 40          | 2   |
| 1001          | u004     | 40          | 3   |
| 1001          | u003     | 10          | 4   |
| 1001          | u005     | 8           | 5   |
| 1002          | u006     | 60          | 1   |
| 1002          | u008     | 50          | 2   |
| 1002          | u007     | 50          | 3   |
| 1002          | u009     | 20          | 4   |
| 1002          | u010     | 10          | 5   |
| 1002          | u011     | 8           | 6   |
+---------------+----------+-------------+-----+
11 rows selected (0.415 seconds)(https://www.dwsql.com)

步骤3：过滤出每个直播间的前3名

最后在窗口函数结果外层加 where rn <= 3 即可。

执行SQL

select
    live_room_id,
    user_id,
    gift_value,
    rn
from (
    select
        live_room_id,
        user_id,
        gift_value,
        row_number() over (partition by live_room_id order by gift_value desc) as rn
    from (
        select
            live_room_id,
            user_id,
            sum(gift_price * gift_count) as gift_value
        from t4_gift_log
        group by live_room_id, user_id
    ) t1
) t2
where rn <= 3
order by live_room_id, rn

执行结果

+---------------+----------+-------------+-----+
| live_room_id  | user_id  | gift_value  | rn  |
+---------------+----------+-------------+-----+
| 1001          | u002     | 50          | 1   |
| 1001          | u001     | 40          | 2   |
| 1001          | u004     | 40          | 3   |
| 1002          | u006     | 60          | 1   |
| 1002          | u008     | 50          | 2   |
| 1002          | u007     | 50          | 3   |
+---------------+----------+-------------+-----+
6 rows selected (0.651 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：同一观众多次送礼未先聚合，导致重复上榜

一个观众可能在同一直播间送过多次礼物（如 u001 送了两次）。如果直接对明细记录开窗排名，同一个 user_id 会占多个名次，结果错误。正确做法是先用 group by live_room_id, user_id + sum(gift_price * gift_count) 汇总，再排名。

坑2：并列金额时的排序不确定

当两个观众的送礼价值相同时（如直播间 1001 的 u001 和 u004 都是 40），row_number 会随机分配名次。如果业务要求并列金额都能入选，或需要稳定的排名结果，可以考虑用 rank() / dense_rank()，或在 order by 中追加 user_id 作为第二排序键保证确定性。

坑3：ROW_NUMBER 与 RANK、DENSE_RANK 的区别

• row_number()：即使金额相同，也会给出连续的 1、2、3 名次，不重复不跳跃。
• rank()：并列金额名次相同，下一个名次跳跃（如 1、1、3）。
• dense_rank()：并列金额名次相同，下一个名次连续（如 1、1、2）。

本题要求"取前3名观众"，用 row_number 即可；若用 rank，遇到并列时可能取到超过3条记录。

五、知识点总结

考点	说明
GROUP BY 先聚合	按 (live_room_id, user_id) 分组，sum(gift_price * gift_count) 汇总送礼价值
ROW_NUMBER() 窗口函数	partition by 分组，order by 排序，为每个直播间内的观众编号
Top N 取数	where rn <= 3 过滤每个分组的前3名
ROW_NUMBER / RANK / DENSE_RANK	并列值时三种排名函数的差异，按业务口径选择

六、建表语句和数据插入

点击展开 DDL & DML

--建表语句
create table t4_gift_log (
    user_id string comment '送礼用户',
    live_room_id bigint comment '直播间',
    gift_id string comment '礼物ID',
    gift_price bigint comment '礼物单价',
    gift_count bigint comment '礼物数量',
    gift_time string comment '送礼时间'
);

--数据插入
insert into t4_gift_log(user_id, live_room_id, gift_id, gift_price, gift_count, gift_time) values
('u001', 1001, 'G01', 10, 3, '2024-06-01 20:01:00'),
('u001', 1001, 'G02', 5, 2, '2024-06-01 20:05:00'),
('u002', 1001, 'G03', 50, 1, '2024-06-01 20:02:00'),
('u003', 1001, 'G01', 10, 1, '2024-06-01 20:03:00'),
('u004', 1001, 'G04', 20, 2, '2024-06-01 20:04:00'),
('u005', 1001, 'G05', 8, 1, '2024-06-01 20:06:00'),
('u006', 1002, 'G06', 30, 2, '2024-06-01 21:01:00'),
('u007', 1002, 'G03', 50, 1, '2024-06-01 21:02:00'),
('u008', 1002, 'G02', 5, 10, '2024-06-01 21:03:00'),
('u009', 1002, 'G04', 20, 1, '2024-06-01 21:04:00'),
('u010', 1002, 'G01', 10, 1, '2024-06-01 21:05:00'),
('u011', 1002, 'G05', 8, 1, '2024-06-01 21:06:00');

📱关注公众号

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

💬加群交流

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