深色模式
02 · 窗口函数经典题:TopN、去重、连续登录
学完你能说出:
- 窗口函数和 GROUP BY 的区别:不压缩行。
- ROW_NUMBER、RANK、DENSE_RANK 的区别(100、80、80、50)。
- 6 道高频题的写法:分组 TopN、去重保留最新、连续登录、环比、累计和、行转列。
先看地图
大数据岗的 SQL 笔试题,大部分都是窗口函数。本篇先讲窗口函数是什么,再把 6 道高频题各写一遍:
① 窗口函数的结构 → ② 三个排名函数 → ③ 分组 TopN → ④ 去重保留最新 → ⑤ 连续登录 → ⑥ LAG 环比与累计和 → ⑦ 行转列
一句话本质
窗口函数 = 在不合并行的前提下,给每一行算一个「和同组其他行比较」的结果:排名、上一行的值、累计到当前行的和。
生活类比
考试成绩单:
- GROUP BY:每个班压成一行,只剩「班级 + 平均分」,看不到个人。
- 窗口函数:每个学生的行都还在,只是旁边多了一列「班内排名」或「班级平均分」。
一步步看
1. 结构
sql
函数() OVER (PARTITION BY 分组列 ORDER BY 排序列 [窗口范围])| 部分 | 意思 | 例子 |
|---|---|---|
PARTITION BY | 按什么分组(各组分别计算) | 按班级 |
ORDER BY | 组内按什么排序 | 按分数降序 |
| 窗口范围 | 算到哪些行(可选) | 从组的第一行到当前行 |
窗口函数在 SELECT 阶段计算,所以不能直接写在 WHERE 里,要先在子查询里算出来,外层再过滤。
2. 三个排名函数
分数 100、80、80、50:
| 函数 | 结果 | 规则 |
|---|---|---|
ROW_NUMBER() | 1、2、3、4 | 一直连续,并列也分先后 |
RANK() | 1、2、2、4 | 并列同名次,后面跳号 |
DENSE_RANK() | 1、2、2、3 | 并列同名次,后面不跳号 |
选哪个:要「严格取 N 行」用 ROW_NUMBER;「并列都要、名次按人数算」用 RANK;「前 3 个分数档的人都要」用 DENSE_RANK。
3. 分组 TopN:每个城市销售额前 3 的商品
sql
SELECT city, item, sales
FROM (
SELECT city, item, sales,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY sales DESC) AS rn
FROM item_sales
) t
WHERE rn <= 3;套路:子查询里编号,外层按编号筛。
4. 去重保留最新:每个用户只留最新一条资料
用户 42 的资料被改过 3 次,表里有 3 行:
sql
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY update_time DESC) AS rn
FROM user_profile
) t
WHERE rn = 1;这是数仓里最常用的写法之一:增量同步过来的数据同一个主键可能有多个版本,用它保留最新的。
5. 连续登录:找出连续登录 3 天及以上的用户
思路:日期减去行号,连续的日期会得到同一个值。
用户 A 的登录日期:
| login_date | rn(按日期编号) | login_date - rn |
|---|---|---|
| 11-01 | 1 | 10-31 |
| 11-02 | 2 | 10-31 |
| 11-03 | 3 | 10-31 |
| 11-05 | 4 | 11-01 |
| 11-06 | 5 | 11-01 |
差值相同的就是同一段连续登录:10-31 这组有 3 天,11-01 这组有 2 天。
sql
SELECT user_id, grp, COUNT(*) AS days
FROM (
SELECT user_id, login_date,
DATE_SUB(login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp
FROM (SELECT DISTINCT user_id, login_date FROM login_log) d -- 先去重:一天登录多次只算一次
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;(DATE_SUB 是 Hive / Spark SQL 的写法;MySQL 写 DATE_SUB(login_date, INTERVAL rn DAY)。)
6. LAG 算环比,SUM() OVER 算累计和
每天的销售额表 daily_sales(dt, amount):
sql
SELECT dt, amount,
LAG(amount, 1) OVER (ORDER BY dt) AS prev_amount, -- 前一天的值
amount / LAG(amount, 1) OVER (ORDER BY dt) - 1 AS dod_rate, -- 日环比
SUM(amount) OVER (ORDER BY dt
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount -- 累计和
FROM daily_sales;| dt | amount | prev_amount | dod_rate | cum_amount |
|---|---|---|---|---|
| 11-01 | 100 | NULL | NULL | 100 |
| 11-02 | 120 | 100 | 0.20 | 220 |
| 11-03 | 90 | 120 | -0.25 | 310 |
LAG(列, n)取前第 n 行,LEAD(列, n)取后第 n 行;- 第一天没有前一天,结果是 NULL;
- 累计和写明
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW更稳妥:只写ORDER BY时,默认范围按排序值计算,日期重复时会把同一天的行一起算进去。
7. 行转列:每个学生一行,三门课各一列
原表 score(name, subject, score):
| name | subject | score |
|---|---|---|
| 张三 | 语文 | 90 |
| 张三 | 数学 | 85 |
| 李四 | 语文 | 70 |
sql
SELECT name,
SUM(CASE WHEN subject = '语文' THEN score ELSE 0 END) AS chinese,
SUM(CASE WHEN subject = '数学' THEN score ELSE 0 END) AS math
FROM score
GROUP BY name;结果:张三 90、85;李四 70、0。如果缺考要显示空而不是 0,把 ELSE 0 去掉、用 MAX 代替 SUM。
为什么这么设计
为什么需要窗口函数?GROUP BY 不够吗?
- 问题:GROUP BY 一组只留一行,「每个城市前 3 名」「每个人和上一次比」这类需求需要保留明细行,同时看到组内信息。用自连接或相关子查询也能做,但写法复杂、性能差。
- 方案:窗口函数在每一行旁边附加一个组内计算结果,不压缩行。
- 好处:排名、TopN、去重、环比、累计都能一条 SQL 写清楚,而且只需扫一遍数据。
一句话答法
GROUP BY 会把一组压成一行;窗口函数保留每一行,同时给它附加组内的排名、前一行的值或累计值,所以 TopN、去重、环比这类题都用它。
为什么「日期减行号」能找出连续登录?
- 问题:连续的日期之间相差 1 天,但不能直接比较相邻两行很多次。
- 方案:按日期编号,连续的日期和行号同步加 1,相减后得到同一个值;一旦断开,日期多加了,差值就变了。
- 好处:把「连续」变成「差值相同」,就能直接 GROUP BY 计数。
一句话答法
连续日期每天加 1,行号也每行加 1,两者相减是个常数;一断开差值就变,所以按差值分组就能数出每段连续的天数。
英文术语拆解
| 术语 | 拆开看 | 一句话意思 |
|---|---|---|
| Window Function | Window 窗口 + Function 函数 | 在一组行(窗口)上计算、不压缩行的函数 |
| PARTITION BY | Partition 分区 + By 按照 | 窗口按什么分组 |
| ROW_NUMBER | Row 行 + Number 编号 | 行号 |
| DENSE_RANK | Dense 密集的 + Rank 排名 | 不跳号的排名 |
| LAG / LEAD | 落后 / 领先 | 取前 n 行 / 后 n 行的值 |
| UNBOUNDED PRECEDING | Unbounded 无边界的 + Preceding 前面的 | 从组的第一行开始 |
面试怎么问
Q:ROW_NUMBER、RANK、DENSE_RANK 有什么区别?
以 100、80、80、50 为例:ROW_NUMBER 是 1、2、3、4,一直连续;RANK 是 1、2、2、4,并列后跳号;DENSE_RANK 是 1、2、2、3,并列后不跳号。
Q:怎么求每个部门工资最高的前 3 个人?
在子查询里用
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)编号,外层筛 rn <= 3。如果并列的人都要,换成 RANK 或 DENSE_RANK。
Q:怎么找出连续登录 3 天以上的用户?
先按用户和日期去重,再按用户分组、按日期排序编号,用日期减去行号,连续的日期会得到同一个差值;最后按用户和差值分组计数,筛出 3 天及以上的。
Q:同一个主键有多条记录,怎么保留最新的一条?
ROW_NUMBER() OVER (PARTITION BY 主键 ORDER BY 更新时间 DESC),取 rn = 1。
容易混的地方
- 窗口函数不能直接写在 WHERE 里:要套一层子查询。
COUNT(*) OVER (PARTITION BY city)不会减少行数:每行都显示所在城市的总行数。- 只写
ORDER BY的 SUM() OVER:默认窗口是从第一行到当前值(RANGE),排序值相同的行会被一起算,日期重复时结果和预期不同。 - 连续登录要先去重:一天登录两次,行号会多加 1,差值就错了。
和其他知识的连接
- ← 来自 SQL 执行顺序与常见坑:窗口函数在 SELECT 阶段计算。
- → 影响 数仓分层与建模:DWD 层的去重、拉链表的生成都用窗口函数。
- → 影响 Spark 调优:PARTITION BY 会触发 Shuffle,倾斜的分组键同样会让窗口函数变慢。
自测 3 问
Q1:分数 95、90、90、85、80,ROW_NUMBER、RANK、DENSE_RANK 分别是多少?
点开看答案
ROW_NUMBER:1、2、3、4、5;RANK:1、2、2、4、5;DENSE_RANK:1、2、2、3、4。
Q2:用户登录日期是 11-01、11-02、11-04、11-05、11-06,用「日期减行号」分成几段?各几天?
点开看答案
行号 1–5,差值依次是 10-31、10-31、11-01、11-01、11-01。分成两段:11-01 到 11-02 共 2 天,11-04 到 11-06 共 3 天。
Q3:为什么不能写 WHERE ROW_NUMBER() OVER (...) <= 3?
点开看答案
窗口函数在 SELECT 阶段计算,而 WHERE 在 SELECT 之前执行,那时还没有这个值。要在子查询里先算出 rn,外层再 WHERE rn <= 3。
✅ 验收清单
- 能写出窗口函数的完整结构,并说出它和 GROUP BY 的区别
- 能对 100、80、80、50 写出三种排名结果
- 能不看资料写出分组 TopN 和去重保留最新
- 能讲清「日期减行号」的原理并写出连续登录 SQL
- 能用 LAG 写环比、用 SUM() OVER 写累计和
- 能用 CASE WHEN 写行转列
下一步该懂什么
- 这些 SQL 跑在 Hive 表上:内部表、外部表、分区、分桶 → Hive 表