罗罗编程学习记录
本页内容 展开查看

05.7.2 窗口函数

所属:全栈开发面试题库 / 05 数据库与存储 / 05.7 SQL 语言与查询技巧

覆盖范围

  • 窗口函数与聚合函数的区别、OVER 子句结构
  • PARTITION BY、ORDER BY、窗口帧(frame)的语义
  • ROWS 与 RANGE 的区别、有无 ORDER BY 的默认帧差异
  • 排名函数 ROW_NUMBER/RANK/DENSE_RANK/NTILE
  • 偏移函数 LAG/LEAD/FIRST_VALUE/LAST_VALUE
  • Top-N 每组、累计求和、移动平均、同比环比

高频题速览

编号 题目 难度 频率
05.7.2-001 窗口函数和 GROUP BY 聚合有什么区别 基础 ★★★★★
05.7.2-002 ROW_NUMBER、RANK、DENSE_RANK 的区别 基础 ★★★★★
05.7.2-003 窗口帧的默认范围是什么 高级 ★★★★★
05.7.2-004 ROWS 和 RANGE 帧有什么区别 高级 ★★★★
05.7.2-005 LAG/LEAD 与累计求和的写法 进阶 ★★★★
05.7.2-006 每组 Top-N 怎么写 进阶 ★★★★★
05.7.2-007 FIRST_VALUE/LAST_VALUE 的常见坑 高级 ★★★

窗口结构主线

函数名() OVER (
  PARTITION BY 分组列        -- 类似 GROUP BY,但不折叠行
  ORDER BY 排序列            -- 帧内排序
  ROWS/RANGE BETWEEN ...     -- 窗口帧范围
)

05.7.2-001 窗口函数和 GROUP BY 聚合有什么区别?

频率 ★★★★★ | 难度 基础

一句话结论

GROUP BY 聚合把多行折叠成一行,丢失明细;窗口函数用 OVER 子句在"不折叠行"的前提下对每一行计算一个聚合/排名值,明细行与聚合值同时保留。

概念解释

  • 聚合:多行 → 一行,每组只有一条结果。
  • 窗口:多行 → 多行,每行都能看到它所在窗口的聚合值。
  • PARTITION BY:窗口内的分组,不折叠行。

原理流程

  1. 数据先按 PARTITION BY 分组。
  2. 组内按 ORDER BY 排序。
  3. 每一行在自己的窗口帧上计算函数值。
  4. 所有明细行保留。

代码 / 示例

-- GROUP BY:每个部门只有一行
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

-- 窗口函数:每个员工一行,同时显示其部门平均工资
SELECT name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

常见误区

  • 误区:窗口函数会减少行数。正解:窗口函数不改变输出行数,只增加列。
  • 误区:窗口函数必须和 GROUP BY 一起用。正解:窗口函数独立于 GROUP BY,可在 WHERE/GROUP BY 之后使用。

追问

  • 问:窗口函数在逻辑执行顺序的哪个阶段?答:在 FROM/WHERE/GROUP BY/HAVING 之后、ORDER BY 之前,因此 WHERE 里不能用窗口函数别名。

05.7.2-002 ROW_NUMBER、RANK、DENSE_RANK 有什么区别?

频率 ★★★★★ | 难度 基础

一句话结论

ROW_NUMBER 无论是否并列都给唯一连续编号(1,2,3,4);RANK 并列同名次并跳过后续名次(1,1,3,4);DENSE_RANK 并列同名次但不跳号(1,1,2,3)。

概念解释

  • ROW_NUMBER:行号唯一,并列时按排序不确定性或附加规则打破。
  • RANK:并列占名次,后面跳号。
  • DENSE_RANK:并列不跳号,名次连续。

代码 / 示例

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

-- salary 相同时:
-- Alice  10000  rn=1  rnk=1  drnk=1
-- Bob    10000  rn=2  rnk=1  drnk=1
-- Carol   9000  rn=3  rnk=3  drnk=2

常见误区

  • 误区:并列时 ROW_NUMBER 的顺序是确定的。正解:ORDER BY 列值相同行顺序不确定,需要再加唯一列作为次序。
  • 误区:RANK 和 DENSE_RANK 一样。正解:RANK 跳号(1,1,3),DENSE_RANK 不跳(1,1,2)。

追问

  • 问:去重每组重复行取一条用哪个?答:ROW_NUMBER() OVER (PARTITION BY 重复键 ORDER BY 更新时间 DESC) 取 rn=1。

05.7.2-003 窗口帧的默认范围是什么?有无 ORDER BY 有何差异?

频率 ★★★★★ | 难度 高级

一句话结论

当 OVER 子句带 ORDER BY 时,默认窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(分组第一行到当前行,同值行并入当前行);当 OVER 子句没有 ORDER BY 时,默认帧是整个分区(RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),即所有行。

概念解释

  • 窗口帧(frame):在分区内进一步圈定"当前行参与计算的行范围"。
  • 带 ORDER BY:默认从分区首行累计到当前行(累计求和语义)。
  • 不带 ORDER BY:默认整个分区,每行结果相同(全分区聚合语义)。
  • RANGE vs ROWS:见下一题。

原理流程

  1. 先 PARTITION BY 分区。
  2. 若有 ORDER BY,按序排列。
  3. 按默认帧圈定当前行的窗口。
  4. 在帧上计算聚合/取值函数。

代码 / 示例

-- 带 ORDER BY:默认帧 = 分区首行到当前行 → 累计求和
SELECT dept_id, month, amount,
  SUM(amount) OVER (
    PARTITION BY dept_id
    ORDER BY month
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 默认,可省略
  ) AS cum_sum
FROM sales;

-- 不带 ORDER BY:默认帧 = 整个分区 → 每行都是分区总和
SELECT dept_id, amount,
  SUM(amount) OVER (PARTITION BY dept_id) AS dept_total
FROM sales;

常见误区

  • 误区:无论有无 ORDER BY,默认帧都是"到当前行"。正解:不带 ORDER BY 时默认帧是整个分区,每行结果相同。
  • 误区:SUM(...) OVER (PARTITION BY ...) 是累计。正解:没有 ORDER BY 时它是全分区总和,不是累计;要累计必须加 ORDER BY。
  • 误区:LAST_VALUE 取的是分区最后一行。正解:默认帧到当前行,LAST_VALUE 实际取到当前行,需显式帧到 UNBOUNDED FOLLOWING。

追问

  • 问:为什么默认帧带 ORDER BY 时是 RANGE 而不是 ROWS?答:SQL 标准规定默认 RANGE,同值行会被视作同一帧点,这是累计求和语义需要的。

05.7.2-004 ROWS 帧和 RANGE 帧有什么区别?

频率 ★★★★ | 难度 高级

一句话结论

ROWS 按物理行数圈定窗口(前 N 行到后 N 行),不受同值影响;RANGE 按排序值的逻辑范围圈定,把排序值相同的行归入同一帧点,因此 RANGE 帧的行数是不确定的。

概念解释

  • ROWS:物理行偏移,精确行数。
  • RANGE:值范围,同值同行。
  • 默认:带 ORDER BY 时默认 RANGE,不带 ORDER BY 时 ROWS/RANGE 都是整个分区。

代码 / 示例

-- ROWS:前 1 行到当前行(物理 3 行),移动平均
AVG(amount) OVER (
  ORDER BY month
  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
)

-- RANGE:month 值相同的行都并入当前帧(逻辑范围)
SUM(amount) OVER (
  ORDER BY month
  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

常见误区

  • 误区:ROWS 2 PRECEDING 等于 RANGE 2 PRECEDING。正解:当排序列有重复值时,RANGE 会把同值行都算进来,行数多于 ROWS。
  • 误区:移动平均用默认帧就行。正解:默认是 RANGE 到当前行,不是"前 N 行",必须显式写 ROWS BETWEEN。

追问

  • 问:哪些数据库支持 RANGE 带数值偏移?答:标准 SQL 规定 RANGE 的偏移需绑定值差值,各数据库实现差异大(如 PostgreSQL 不支持 RANGE 带数值偏移),生产中常用 ROWS 表达移动窗口,跨库更稳。

05.7.2-005 LAG/LEAD 怎么用?累计求和怎么写?

频率 ★★★★ | 难度 进阶

一句话结论

LAG(列, n) 取当前行向前第 n 行的值,LEAD 取向后第 n 行,常用于环比;累计求和用 SUM(...) OVER (PARTITION BY ... ORDER BY ...) 配合默认帧(首行到当前行)。

代码 / 示例

-- 环比:本月 vs 上月
SELECT dept_id, month, amount,
  LAG(amount, 1) OVER (PARTITION BY dept_id ORDER BY month) AS prev_month,
  amount - LAG(amount, 1) OVER (PARTITION BY dept_id ORDER BY month) AS diff,
  SUM(amount) OVER (PARTITION BY dept_id ORDER BY month) AS cum_amount
FROM sales;

-- 同比:取去年同月,用 LAG(month, 12)
LAG(amount, 12) OVER (PARTITION BY dept_id ORDER BY month) AS yoy

常见误区

  • 误区:LAG 默认偏移是 0。正解:LAG(列) 默认取前 1 行,超出分区边界返回 NULL。
  • 误区:累计求和要手动写 frame。正解:带 ORDER BY 时默认帧就是首行到当前行,可省略。

05.7.2-006 每组 Top-N 怎么写?

频率 ★★★★★ | 难度 进阶

一句话结论

用 ROW_NUMBER() OVER (PARTITION BY 分组列 ORDER BY 排序列 DESC) 给每组编号,再在外层 WHERE 取 rn <= N;比子查询嵌套 GROUP BY MAX 更直观,且能取到完整行。

代码 / 示例

-- 每个部门工资最高的 3 名员工
SELECT * FROM (
  SELECT name, dept_id, salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn <= 3;

-- 取每组最新一条记录(去重)
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM user_log
) t
WHERE rn = 1;

常见误区

  • 误区:用 GROUP BY + MAX 就能拿到 Top-N 整行。正解:GROUP BY 只能拿到聚合值,要拿整行需 JOIN 回原表或用窗口函数。
  • 误区:并列时 ROW_NUMBER 公平。正解:并列会被随机打破;要并列都保留用 RANK 或 DENSE_RANK。

追问

  • 问:MySQL 8 以前不支持窗口函数怎么办?答:用变量模拟行号,或用关联子查询;MySQL 8.0+/PG/SQL Server 2012+/Oracle 均原生支持。

05.7.2-007 FIRST_VALUE/LAST_VALUE 有什么常见坑?

频率 ★★★ | 难度 高级

一句话结论

FIRST_VALUE/LAST_VALUE 受窗口帧约束,默认帧是"分区首行到当前行",因此 LAST_VALUE 默认取到的是当前行而非分区最后一行,必须显式把帧扩到 UNBOUNDED FOLLOWING 才能取到分区末行。

代码 / 示例

-- 错误:LAST_VALUE 默认只到当前行,结果等于当前行
SELECT dept_id, month, amount,
  LAST_VALUE(amount) OVER (PARTITION BY dept_id ORDER BY month) AS last_amt
FROM sales;  -- 每个月都返回当月金额

-- 正确:显式帧到分区末行
SELECT dept_id, month, amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY dept_id ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_amt
FROM sales;

-- 取每组首行/末行值更稳妥的写法:
FIRST_VALUE(amount) OVER (PARTITION BY dept_id ORDER BY month)  -- 首行可用

常见误区

  • 误区:LAST_VALUE 取分区最后一行。正解:受默认帧限制,必须手动扩帧。
  • 误区:FIRST_VALUE 也有同样问题。正解:默认帧从首行开始,FIRST_VALUE 天然取到首行,不受影响。

追问

  • 问:取每组最大值对应哪一行怎么写?答:用 FIRST_VALUE(...) OVER (PARTITION BY ... ORDER BY 排序列 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 或直接 ROW_NUMBER 取 rn=1。

05.7.2-008 NTILE、CUME_DIST、PERCENT_RANK 怎么用?

频率 ★★ | 难度 进阶

一句话结论

NTILE(n) 把分区行分成 n 组,用于分位数;CUME_DIST 是累计分布;PERCENT_RANK 是相对排名;常用于分箱和百分位统计。

代码 / 示例

-- 按工资分 4 档
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

05.7.2-009 窗口函数和 GROUP BY 能一起用吗?

频率 ★★ | 难度 进阶

一句话结论

可以。窗口函数在 GROUP BY 之后执行,可对聚合结果再做窗口;如 SELECT dept, COUNT(*), AVG(COUNT(*)) OVER (PARTITION BY dept)。

待整理清单

  • [ ] NTILE、CUME_DIST、PERCENT_RANK 的使用场景
  • [ ] 窗口函数在 MySQL/PG/SQL Server/Oracle 的支持版本差异
  • [ ] 窗口函数的性能代价:是否需要额外排序、如何利用索引