SQL 窗口函数 学习笔记
易错点
查找每个大陆(continent)中最大的国家(按区域area)，显示该大洲 (continent)，国家名(name)和面积(area) 代码能不能这样写
SELECT continent, name, area, rank() over(partition by continent order by area desc) as rank FROM world WHERE rank = 1不可以，窗口函数别名无法直接在 WHERE 中使用。MySQL 中，
WHERE子句的执行顺序早于窗口函数，因此无法直接引用窗口函数的别名（如rank = 1），会报 “未知列 rank” 错误
用子查询
SELECT continent, name, area FROM ( SELECT continent, name, area, RANK() OVER(PARTITION BY continent ORDER BY area DESC) AS rnk FROM world WHERE area > 0 ) AS biao WHERE rnk = 1一、窗口函数核心概念
1. 定义
窗口函数（Window Function）是 SQL 中用于在不合并行的前提下，对指定范围（窗口）内的数据进行聚合、排序、分析的函数。
- 普通聚合函数（GROUP BY + SUM/COUNT）：将多行合并为一行返回结果；
- 窗口函数：为每行数据返回一个计算结果，保留原表所有行。
2. 通俗比喻
普通聚合函数：把全班按性别分组，只返回每组的总分（每组 1 行）；窗口函数：给每个同学的成绩单上，额外标注其性别组的总分（每行都有）。
3. 核心语法
函数名(参数) OVER ( PARTITION BY 分区列 ORDER BY 排序列 ASC/DESC ROWS/RANGE BETWEEN 起始边界 AND 结束边界 )说明：
PARTITION BY 分区列、ORDER BY 排序列、ROWS/RANGE均为可选子句，根据业务需求选择是否添加；- 实际使用时直接替换 “分区列”“排序列” 等为具体字段名即可，比如
PARTITION BY 班级、ORDER BY 分数 DESC。
二、窗口函数分类及详解
1. 聚合类窗口函数
（1）核心函数
SUM ()、COUNT ()、AVG ()、MAX ()、MIN ()（基于普通聚合函数改造）。
（2）关键特点
- 无 ORDER BY：计算整个分区的聚合值；
- 有 ORDER BY：计算 “分区首行到当前行” 的累计值。
（3）实战案例
基础表（score）：
| 学号 | 姓名 | 科目 | 分数 | 班级 |
|---|---|---|---|---|
| 1 | 张三 | 数学 | 90 | 一班 |
| 2 | 李四 | 数学 | 85 | 一班 |
| 3 | 王五 | 数学 | 95 | 二班 |
| 4 | 赵六 | 语文 | 88 | 一班 |
案例 1：计算班级数学平均分（无 ORDER BY）
SELECT 学号, 姓名, 分数, 班级, -- 按班级分区，计算整个分区的平均分 AVG(分数) OVER (PARTITION BY 班级) AS 班级数学平均分 FROM score WHERE 科目='数学';| 学号 | 姓名 | 分数 | 班级 | 班级数学平均分 |
|---|---|---|---|---|
| 1 | 张三 | 90 | 一班 | 87.5 |
| 2 | 李四 | 85 | 一班 | 87.5 |
| 3 | 王五 | 95 | 二班 | 95 |
案例 2：计算班级分数累计值（有 ORDER BY）
SELECT 学号, 姓名, 分数, 班级, SUM(分数) OVER (PARTITION BY 班级 ORDER BY 分数) AS 班级累计分数 FROM score;结果（一班）：
| 学号 | 姓名 | 分数 | 班级 | 班级累计分数 |
|---|---|---|---|---|
| 2 | 李四 | 85 | 一班 | 85 |
| 1 | 张三 | 90 | 一班 | 175 |
| 4 | 赵六 | 88 | 一班 | 263 |
2. 排序类窗口函数
（1）核心函数及区别
| 函数名 | 核心特点 | 示例（同分数 90） |
|---|---|---|
| ROW_NUMBER() | 无重复排名，即使分数相同也编唯一号 | 1,2,3,4 |
| RANK() | 有重复排名，重复值占同一位置，后续排名跳空 | 1,2,2,4 |
| DENSE_RANK() | 有重复排名，重复值占同一位置，后续排名不跳空 | 1,2,2,3 |
（2）高频疑问点
- ❓ 窗口内的 ORDER BY 是否改变输出行顺序？❌ 不改变！窗口内的 ORDER BY 仅用于计算排名的依据，若需输出结果排序，需加全局 ORDER BY。
- ❓ RANK () 必须搭配 ORDER BY 吗？语法上可选，但业务上必须！省略 ORDER BY 时，分区内所有行排名都是 1，无业务意义。
SELECT 姓名, 班级, 分数, ROW_NUMBER() OVER (PARTITION BY 班级 ORDER BY 分数 DESC) AS 行号排名, RANK() OVER (PARTITION BY 班级 ORDER BY 分数 DESC) AS 普通排名, DENSE_RANK() OVER (PARTITION BY 班级 ORDER BY 分数 DESC) AS 密集排名 FROM score;结果（一班，新增王五分数 90）：
| 姓名 | 班级 | 分数 | 行号排名 | 普通排名 | 密集排名 |
|---|---|---|---|---|---|
| 张三 | 一班 | 90 | 1 | 1 | 1 |
| 王五 | 一班 | 90 | 2 | 1 | 1 |
| 李四 | 一班 | 85 | 3 | 3 | 2 |
3. 分析类窗口函数
（1）核心函数及作用
| 函数 | 作用 | 默认偏移量 |
|---|---|---|
| LAG (列名，n, 默认值) | 获取当前行的前 n 行数据 | 1 |
| LEAD (列名，n, 默认值) | 获取当前行的后 n 行数据 | 1 |
| FIRST_VALUE (列名) | 获取分区内排序后的第一个值 | - |
| LAST_VALUE (列名) | 获取分区内排序后的最后一个值 | - |
（2）高频疑问点
- ❓ LAST_VALUE () 为什么返回值不符合预期？默认窗口范围是 “分区首行到当前行”，需手动扩展到整个分区：
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。 - ❓ 分析类函数需要加 ORDER BY 吗？必须加！“前 / 后行”“首 / 末值” 依赖排序规则，省略则逻辑混乱。
（3）实战案例（销售表 sales）
| 日期 | 区域 | 销售额 |
|---|---|---|
| 2024-01-01 | 华东 | 1000 |
| 2024-01-02 | 华东 | 1200 |
| 2024-01-03 | 华东 | 1100 |
案例 1：LAG () 计算环比增长率
SELECT 日期, 区域, 销售额, LAG(销售额, 1, 0) OVER (PARTITION BY 区域 ORDER BY 日期) AS 前一天销售额, ROUND((销售额 - LAG(销售额,1,0) OVER (PARTITION BY 区域 ORDER BY 日期)) / NULLIF(LAG(销售额,1,0) OVER (PARTITION BY 区域 ORDER BY 日期),0)*100,2) AS 环比增长率(%) FROM sales;结果：
| 日期 | 区域 | 销售额 | 前一天销售额 | 环比增长率 (%) |
|---|---|---|---|---|
| 2024-01-01 | 华东 | 1000 | 0 | NULL |
| 2024-01-02 | 华东 | 1200 | 1000 | 20.00 |
| 2024-01-03 | 华东 | 1100 | 1200 | -8.33 |
案例 2：LAST_VALUE () 正确获取分区末值
SELECT 日期, 区域, 销售额, LAST_VALUE(销售额) OVER ( PARTITION BY 区域 ORDER BY 日期 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS 区域最后一天销售额 FROM sales;结果：
| 日期 | 区域 | 销售额 | 区域最后一天销售额 |
|---|---|---|---|
| 2024-01-01 | 华东 | 1000 | 1100 |
| 2024-01-02 | 华东 | 1200 | 1100 |
| 2024-01-03 | 华东 | 1100 | 1100 |
4. 窗口范围（ROWS/RANGE）
（1）核心概念
窗口范围定义 “分区 + 排序后，哪些行参与当前行的计算”，是窗口函数的 “精细控制层”。
（2）核心语法（可直接复用）
ROWS BETWEEN 起始边界 AND 结束边界 -- 或 RANGE BETWEEN 起始边界 AND 结束边界常用边界值（直接写即可）：
| 边界值 | 含义 |
|---|---|
| UNBOUNDED PRECEDING | 分区第一行 |
| CURRENT ROW | 当前行 |
| UNBOUNDED FOLLOWING | 分区最后一行 |
| n PRECEDING | 当前行的前 n 行（n 写具体数字，如 1 PRECEDING） |
| n FOLLOWING | 当前行的后 n 行（n 写具体数字，如 1 FOLLOWING） |
（3）核心区别（ROWS vs RANGE）
| 类型 | 定义方式 | 特点 | 示例（分数 90 重复） |
|---|---|---|---|
| ROWS | 按物理行数限定 | 直接数行，逻辑简单 | 累计到第 3 行 = 80+85+90 |
| RANGE | 按列值大小限定 | 相同值的行全部包含 | 累计到 90=80+85+90+90 |
（4）默认规则（必记）
| 场景 | 默认窗口范围 |
|---|---|
| 有 ORDER BY，无显式范围 | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| 无 ORDER BY，无显式范围 | ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
（5）实战案例：3 日移动平均
SELECT 日期, 销售额, AVG(销售额) OVER ( ORDER BY 日期 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS 3日移动平均 FROM sales;结果：
| 日期 | 销售额 | 3 日移动平均 |
|---|---|---|
| 2024-01-01 | 1000 | 1000.00 |
| 2024-01-02 | 1200 | 1100.00 |
| 2024-01-03 | 1100 | 1100.00 |
三、高频易错点汇总
- 窗口内的 ORDER BY ≠ 全局 ORDER BY：前者仅用于计算规则，后者控制输出行顺序；
- RANK () 省略 ORDER BY 无业务意义，所有行排名为 1；
- LAST_VALUE () 需手动扩展窗口范围到整个分区；
- 分析类函数（LAG/LEAD/FIRST_VALUE/LAST_VALUE）必须加 ORDER BY；
- OVER () 内的 WHERE 是非标准写法，数据筛选优先用全局 WHERE 子句；
- 除数为 0 问题：用 NULLIF (值，0) 避免环比增长率报错。
四、常见业务场景
| 函数类型 | 典型场景 | 示例需求 |
|---|---|---|
| 聚合类 | 累计计算、分组平均值 | 累计销售额、班级平均分 |
| 排序类 | 排名、取 Top N | 班级成绩排名、各区域销售额 Top3 |
| 分析类 | 环比 / 同比、首尾值对比 | 日销售额环比、本月首日销售额对比 |
| 窗口范围 | 移动平均、相邻行分析 | 7 日销量平均、前后 1 天数据对比 |
五、核心总结
- 窗口函数核心：不合并行，为每行返回基于 “分区 + 排序 + 范围” 的计算结果；
- 三大分类：聚合类（累计 / 分组计算）、排序类（排名）、分析类（跨行对比）；
- 窗口范围：ROWS 按物理行（常用），RANGE 按值范围（处理重复值）；
- 关键技巧：LAST_VALUE () 扩展窗口范围、排序类函数必加 ORDER BY、全局 ORDER BY 控制输出顺序。