Skip to content

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_datern(按日期编号)login_date - rn
11-01110-31
11-02210-31
11-03310-31
11-05411-01
11-06511-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;
dtamountprev_amountdod_ratecum_amount
11-01100NULLNULL100
11-021201000.20220
11-0390120-0.25310
  • LAG(列, n) 取前第 n 行,LEAD(列, n) 取后第 n 行;
  • 第一天没有前一天,结果是 NULL;
  • 累计和写明 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 更稳妥:只写 ORDER BY 时,默认范围按排序值计算,日期重复时会把同一天的行一起算进去。

7. 行转列:每个学生一行,三门课各一列 ​

原表 score(name, subject, score):

namesubjectscore
张三语文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 FunctionWindow 窗口 + Function 函数在一组行(窗口)上计算、不压缩行的函数
PARTITION BYPartition 分区 + By 按照窗口按什么分组
ROW_NUMBERRow 行 + Number 编号行号
DENSE_RANKDense 密集的 + Rank 排名不跳号的排名
LAG / LEAD落后 / 领先取前 n 行 / 后 n 行的值
UNBOUNDED PRECEDINGUnbounded 无边界的 + 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,差值就错了。

和其他知识的连接 ​

自测 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 表