从 GROUP BY 到窗口函数

零基础渐进教程 — 每一步都有完整数据结果，不跳过任何中间过程

🎯 为什么需要窗口函数？

想象你在看班级成绩单——你想知道每个人的分数（原表数据），同时想知道：每个人的分数在全班排第几名？每个人的分数和全班平均分差多少？

GROUP BY 做不到这件事——GROUP BY 会把全班 30 人"压缩"成 1 行（只有平均分），你再也看不到每个人的分数了。
窗口函数能做到——它能在每个人的行旁边，"贴上"全班排名、全班平均分等信息。原来的 30 行还是 30 行，但每一行多了一列"排名"或"平均分"。

这就是窗口函数的核心价值：既保留明细数据，又能看到聚合信息。

示例数据（全篇通用）

employees 员工表

CREATE TABLE employees (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(50) NOT NULL,
  dept       VARCHAR(30),
  salary     DECIMAL(10,2),
  hire_date  DATE
);

全部数据 (7 rows)

id	name	dept	salary	hire_date
1	张三	技术部	15000	2022-03-15
2	李四	技术部	18000	2021-07-01
3	王五	市场部	12000	2023-01-10
4	赵六	市场部	12000	2023-06-20
5	张三	财务部	11000	2020-11-05
6	孙七	技术部	18000	2022-09-18
7	周八	NULL	9000	2024-02-01

记住这7条数据，后面所有查询都基于它。注意：技术部3人、市场部2人、财务部1人、周八没有部门。

第 0 步：最普通的查询（没有分组）

0. 普通查询

→

1. 聚合函数

→

2. GROUP BY

→

3. HAVING

→

4. 窗口函数

问题全公司薪资最高的3个人是谁？

SELECT name, dept, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

执行结果 (3 rows)

name	dept	salary
李四	技术部	18000
孙七	技术部	18000
张三	技术部	15000

问题薪资大于12000的有哪些人？

SELECT name, dept, salary
FROM employees
WHERE salary > 12000;

执行结果 (3 rows)

name	dept	salary
张三	技术部	15000
李四	技术部	18000
孙七	技术部	18000

👆 到目前为止，每行就是一条记录，没有任何"合并"或"统计"。

第 1 步：聚合函数（对整张表做统计）

0. 普通查询

→

1. 聚合函数

→

2. GROUP BY

→

3. HAVING

→

4. 窗口函数

问题全公司有多少人？平均薪资多少？

SELECT
  COUNT(*)          AS 总人数,
  ROUND(AVG(salary),2) AS 平均薪资,
  SUM(salary)       AS 薪资总和,
  MAX(salary)       AS 最高薪资,
  MIN(salary)       AS 最低薪资
FROM employees;

执行结果 (1 row — 7条数据被压缩成了1条)

总人数	平均薪资	薪资总和	最高薪资	最低薪资
7	13571.43	95000.00	18000.00	9000.00

🧠 理解要点

聚合函数把一整列的数据压缩成一个数字。7个人的薪资 → 1个平均值、1个总和、1个最大值。
代价是：细节没了——你不知道是谁的薪资最高，只知道最高是18000。

问题 COUNT(*) vs COUNT(dept) 有什么区别？

SELECT
  COUNT(*)     AS count_star,
  COUNT(dept)  AS count_dept
FROM employees;

执行结果 (1 row)

count_star	count_dept
7	6

COUNT(*) 数所有行 = 7。COUNT(dept) 只数 dept 不为 NULL 的行 = 6（周八的 dept 是 NULL，被跳过）。

第 2 步：GROUP BY（按组分别统计）

0. 普通查询

→

1. 聚合函数

→

2. GROUP BY

→

3. HAVING

→

4. 窗口函数

🧠 GROUP BY 到底干了什么？

想象你面前有一堆员工卡片，GROUP BY dept 的意思就是：
按 dept 把卡片分成几堆，然后对每一堆分别做统计。

关键理解：分组之后，每一组变成了一行。原来组里的每个人被"压扁"了，只留下统计结果。
你不能在 SELECT 里直接写 name，因为一个组里有好几个人，数据库不知道你想显示谁的 name。

问题每个部门各有多少人？

SELECT dept, COUNT(*) AS cnt
FROM employees
GROUP BY dept;

执行结果 (4 rows — 注意 NULL 也算一组)

dept	cnt
技术部	3
市场部	2
财务部	1
NULL	1

👆 7条数据被分成了4堆，每堆压缩成1行。原来组里每个人的 name、salary、hire_date 都消失了，只剩统计数字。

思考如果我这样写，会发生什么？

-- ❌ 错误写法！
SELECT dept, name, COUNT(*) AS cnt
FROM employees
GROUP BY dept;

报错！ 技术部有3个人（张三、李四、孙七），数据库不知道 name 该显示谁的。规则：SELECT 里出现的非聚合列，必须出现在 GROUP BY 里。

问题每个部门的平均薪资和总薪资？

SELECT dept,
       COUNT(*) AS 人数,
       ROUND(AVG(salary),2) AS 平均薪资,
       SUM(salary) AS 总薪资
FROM employees
GROUP BY dept;

执行结果 (4 rows)

dept	人数	平均薪资	总薪资
技术部	3	17000.00	51000.00
市场部	2	12000.00	24000.00
财务部	1	11000.00	11000.00
NULL	1	9000.00	9000.00

问题按部门+薪资分组（多字段 GROUP BY）

GROUP BY 后面可以加多个字段，意思是"这两个字段的组合相同才算同一组"。

SELECT dept, salary, COUNT(*) AS cnt
FROM employees
GROUP BY dept, salary;

执行结果 (5 rows — 相同部门+相同薪资的合并了)

dept	salary	cnt
技术部	18000	2
技术部	15000	1
市场部	12000	2
财务部	11000	1
NULL	9000	1

👆 技术部的李四和孙七都是18000，合并成了1行 cnt=2。市场部王五和赵六都是12000，也合并了。

关键理解 GROUP BY 的本质

分组前 — 7行原始数据

name	dept	salary
张三	技术部	15000
李四	技术部	18000
孙七	技术部	18000
王五	市场部	12000
赵六	市场部	12000
张三	财务部	11000
周八	NULL	9000

GROUP BY dept 后 — 4行汇总

dept	COUNT	AVG(salary)
技术部	3	17000
市场部	2	12000
财务部	1	11000
NULL	1	9000

⚠️ name 列消失了！因为一个组有多个 name，不知道该显示哪个。这就是 GROUP BY 的代价。

第 3 步：HAVING（过滤分组后的结果）

0. 普通查询

→

1. 聚合函数

→

2. GROUP BY

→

3. HAVING

→

4. 窗口函数

🧠 WHERE vs HAVING

WHERE：分组前过滤，一行一行检查（"薪资>10000的人"）。
HAVING：分组后过滤，一组一组检查（"人数>=2的部门"）。

执行顺序：WHERE → GROUP BY → HAVING
先过滤行，再分组，再过滤组。

问题哪些部门有2人及以上？

SELECT dept, COUNT(*) AS cnt
FROM employees
GROUP BY dept
HAVING COUNT(*) >= 2;

执行结果 (2 rows — 财务部和 NULL 组被过滤掉了)

dept	cnt
技术部	3
市场部	2

问题 2人及以上且平均薪资>13000的部门？

SELECT dept,
       COUNT(*) AS cnt,
       ROUND(AVG(salary),2) AS avg_sal
FROM employees
GROUP BY dept
HAVING COUNT(*) >= 2
   AND AVG(salary) > 13000;

执行结果 (1 row — 市场部平均12000，也被过滤了)

dept	cnt	avg_sal
技术部	3	17000.00

👆 市场部虽然2人，但平均薪资12000不满足>13000，也被过滤了。只剩技术部。

第 4 步：窗口函数（分组统计但不压扁）

0. 普通查询

→

1. 聚合函数

→

2. GROUP BY

→

3. HAVING

→

4. 窗口函数

🧠 窗口函数 = GROUP BY 的统计 + 保留每一行

GROUP BY 的问题是：统计完，人没了。你知道技术部平均17000，但不知道每个人具体多少。

窗口函数的做法是：分组的逻辑还在，但不压扁。每个人旁边贴一列统计数字。

类比：GROUP BY 像是"把每堆卡片用皮筋捆起来，只看捆上的标签"。
窗口函数像是"每张卡片上都贴一张便签纸，写着这堆的统计信息，但卡片本身还在"。

核心对比同一个问题，两种写法

问题：每个部门的平均薪资是多少？

❶ GROUP BY — 结果只有4行

SELECT dept,
       ROUND(AVG(salary),2) AS avg_sal
FROM employees
GROUP BY dept;

dept	avg_sal
技术部	17000
市场部	12000
财务部	11000
NULL	9000

name 列没了！7行→4行

❷ 窗口函数 — 结果还是7行，多了统计列

SELECT name, dept, salary,
       ROUND(AVG(salary) OVER (PARTITION BY dept),2) AS avg_sal
FROM employees;

name	dept	salary	avg_sal
张三	技术部	15000	17000
李四	技术部	18000	17000
孙七	技术部	18000	17000
王五	市场部	12000	12000
赵六	市场部	12000	12000
张三	财务部	11000	11000
周八	NULL	9000	9000

✅ 人还在！每人旁边多了部门平均

关键区别：GROUP BY 把7行压成4行，窗口函数还是7行，只是每行多了个统计值。avg_sal 那一列的值，跟左边 GROUP BY 的结果完全一样，只是被"贴"到了每个人旁边。

语法拆解 OVER 和 PARTITION BY

AVG(salary) OVER (PARTITION BY dept)

部分	作用	相当于
AVG(salary)	对 salary 求平均	跟 GROUP BY 里的 AVG 一样
OVER	告诉数据库：这是窗口函数，不是普通聚合	窗口函数的标志
PARTITION BY dept	按 dept 分组	相当于 GROUP BY dept

你可以把 OVER (PARTITION BY dept) 理解为 "按 dept 分组，但不压扁"。

问题全公司薪资排名（三种排名函数对比）

ORDER BY 是窗口函数里另一个关键部分——它决定窗口内怎么排。

SELECT name, dept, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS r,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees;

执行结果 (7 rows — 重点看并列18000的三种排名)

name	dept	salary	rn (ROW_NUMBER)	r (RANK)	dr (DENSE_RANK)
李四	技术部	18000	1	1	1
孙七	技术部	18000	2	1	1
张三	技术部	15000	3	3	2
王五	市场部	12000	4	4	3
赵六	市场部	12000	5	4	3
张三	财务部	11000	6	6	4
周八	NULL	9000	7	7	5

ROW_NUMBER = 1,2,3,4...

绝不并列。18000的两人，一个排1一个排2（谁1谁2不确定）

RANK = 1,1,3,4...

并列但跳号。18000两人都是第1，下一个直接跳到第3

DENSE_RANK = 1,1,2,3...

并列不跳号。18000两人都是第1，下一个是第2（最常用）

实际用得最多的是 ROW_NUMBER（去重）和 DENSE_RANK（排名）

组合使用 PARTITION BY + ORDER BY

PARTITION BY 决定"按什么分组"，ORDER BY 决定"组内怎么排"。两个一起用就能做"每组内排名"。

SELECT name, dept, salary,
       ROW_NUMBER() OVER (
         PARTITION BY dept     -- 按部门分组
         ORDER BY salary DESC  -- 组内按薪资降序
       ) AS dept_rank
FROM employees
WHERE dept IS NOT NULL;

执行结果 (6 rows — 每个部门内部分别排名)

name	dept	salary	dept_rank
李四	技术部	18000	1
孙七	技术部	18000	2
张三	技术部	15000	3
王五	市场部	12000	1
赵六	市场部	12000	2
张三	财务部	11000	1

👆 技术部内部排 1,2,3；市场部内部排 1,2；财务部只有1人所以就是1。每个部门的排名是独立计算的。

问题每个部门只留薪资最高的那个人（去重）

-- 第1步：给每个部门内的人编号
SELECT *,
       ROW_NUMBER() OVER (
         PARTITION BY dept
         ORDER BY salary DESC, id ASC  -- 薪资相同则id小的优先
       ) AS rn
FROM employees
WHERE dept IS NOT NULL;

中间结果 — 先看 rn 列 (6 rows)

id	name	dept	salary	hire_date	rn
2	李四	技术部	18000	2021-07-01	1
6	孙七	技术部	18000	2022-09-18	2
1	张三	技术部	15000	2022-03-15	3
3	王五	市场部	12000	2023-01-10	1
4	赵六	市场部	12000	2023-06-20	2
5	张三	财务部	11000	2020-11-05	1

-- 第2步：只取 rn = 1 的行
SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY dept
           ORDER BY salary DESC, id ASC
         ) AS rn
  FROM employees
  WHERE dept IS NOT NULL
) t
WHERE rn = 1;

最终结果 (3 rows — 每个部门只留1人)

id	name	dept	salary	hire_date	rn
2	李四	技术部	18000	2021-07-01	1
3	王五	市场部	12000	2023-01-10	1
5	张三	财务部	11000	2020-11-05	1

去重套路：① PARTITION BY 按去重字段分组 → ② ORDER BY 决定保留哪条 → ③ 外层 WHERE rn=1 只取每组第一条。这个模式记住就行，非常常用。

问题每个人的薪资，和所在部门平均相比差多少？

这个问题 GROUP BY 做不到！因为你需要同时看到"个人薪资"和"部门平均"。

SELECT name, dept, salary,
       ROUND(AVG(salary) OVER (PARTITION BY dept),2) AS dept_avg,
       ROUND(salary - AVG(salary) OVER (PARTITION BY dept),2) AS diff
FROM employees
WHERE dept IS NOT NULL;

执行结果 (6 rows — diff 为正是高于平均，为负是低于平均)

name	dept	salary	dept_avg	diff
张三	技术部	15000	17000	-2000
李四	技术部	18000	17000	+1000
孙七	技术部	18000	17000	+1000
王五	市场部	12000	12000	0
赵六	市场部	12000	12000	0
张三	财务部	11000	11000	0

👆 这就是窗口函数的威力：个人数据和分组统计同时出现在一行里。张三（技术部15000）比部门平均低2000，一眼就看出来了。

问题按入职日期排序，累计发了多少薪资？

SUM + ORDER BY = 累计求和（每一行的结果是"从第一行到当前行的总和"）

SELECT name, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees
WHERE dept IS NOT NULL;

执行结果 (6 rows — running_total 是从上到下逐行累加)

name	hire_date	salary	running_total	计算过程
张三	2020-11-05	11000	11000	11000
李四	2021-07-01	18000	29000	11000+18000
张三	2022-03-15	15000	44000	29000+15000
孙七	2022-09-18	18000	62000	44000+18000
王五	2023-01-10	12000	74000	62000+12000
赵六	2023-06-20	12000	86000	74000+12000

问题每个人和上一个入职的人比，薪资变化了多少？

SELECT name, hire_date, salary,
       LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal,
       salary - LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS diff
FROM employees
WHERE dept IS NOT NULL;

执行结果 (6 rows)

name	hire_date	salary	prev_sal	diff
张三	2020-11-05	11000	0	+11000
李四	2021-07-01	18000	11000	+7000
张三	2022-03-15	15000	18000	-3000
孙七	2022-09-18	18000	15000	+3000
王五	2023-01-10	12000	18000	-6000
赵六	2023-06-20	12000	12000	0

👆 LAG(salary, 1, 0) = 取上一行的 salary，如果没上一行就返回0。LEAD 是取下一行，用法一样。

问题按 name + dept 两个字段联合去重，每组只保留1条

PARTITION BY 后面加多个字段，就是"这几个字段的组合相同才算同一组"。

-- 先加一条重复数据
INSERT INTO employees (name, dept, salary, hire_date)
VALUES ('李四', '技术部', 18000, '2021-07-02');

-- 现在有8条数据，李四(技术部)有两条
SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY name, dept   -- 两个字段联合分区
           ORDER BY id ASC            -- id小的优先保留
         ) AS rn
  FROM employees
) t
WHERE rn = 1;

执行结果 (7 rows — 重复的李四只保留了id=2的那条)

id	name	dept	salary	hire_date	rn
1	张三	技术部	15000	2022-03-15	1
2	李四	技术部	18000	2021-07-01	1
3	王五	市场部	12000	2023-01-10	1
4	赵六	市场部	12000	2023-06-20	1
5	张三	财务部	11000	2020-11-05	1
6	孙七	技术部	18000	2022-09-18	1
7	周八	NULL	9000	2024-02-01	1

👆 张三出现了两次（技术部+财务部），但因为 PARTITION BY name, dept，（张三，技术部）和（张三，财务部）是两个不同的分区，所以都保留了。只有（李四，技术部）这个分区有两条，去重后只留了1条。

总结：什么时候用什么

你想干什么	用	为什么
看每个部门的统计数据	GROUP BY	你不需要每个人的细节，只要汇总
过滤掉某些组（如人数<2的部门）	GROUP BY + HAVING	HAVING 专门过滤分组后的结果
每个人的薪资旁边显示部门平均	窗口函数	需要个人数据和统计同时出现
排名 / 取每组前N名	窗口函数	GROUP BY 做不了排名
去重但保留完整行	窗口函数	DISTINCT 只能看去重列，ROW_NUMBER 能保留全部字段
累计求和 / 移动平均	窗口函数	SUM + ORDER BY = 累计，ROWS BETWEEN = 移动窗口
取前后行的值做对比	窗口函数	LAG / LEAD 专门干这个

一句话记忆

GROUP BY = 压扁（每组→1行） vs 窗口函数 = 贴便签（每行旁边加统计值）

从 GROUP BY 到窗口函数

示例数据（全篇通用）

employees 员工表

第 0 步：最普通的查询（没有分组）

问题 全公司薪资最高的3个人是谁？

问题 薪资大于12000的有哪些人？

第 1 步：聚合函数（对整张表做统计）

问题 全公司有多少人？平均薪资多少？

🧠 理解要点

问题 COUNT(*) vs COUNT(dept) 有什么区别？

第 2 步：GROUP BY（按组分别统计）

🧠 GROUP BY 到底干了什么？

问题 每个部门各有多少人？

思考 如果我这样写，会发生什么？

问题 每个部门的平均薪资和总薪资？

问题 按部门+薪资分组（多字段 GROUP BY）

关键理解 GROUP BY 的本质

分组前 — 7行原始数据

GROUP BY dept 后 — 4行汇总

第 3 步：HAVING（过滤分组后的结果）

🧠 WHERE vs HAVING

问题 哪些部门有2人及以上？

问题 2人及以上且平均薪资>13000的部门？

第 4 步：窗口函数（分组统计但不压扁）

🧠 窗口函数 = GROUP BY 的统计 + 保留每一行

核心对比 同一个问题，两种写法

❶ GROUP BY — 结果只有4行

❷ 窗口函数 — 结果还是7行，多了统计列

语法拆解 OVER 和 PARTITION BY

问题 全公司薪资排名（三种排名函数对比）

ROW_NUMBER = 1,2,3,4...

RANK = 1,1,3,4...

DENSE_RANK = 1,1,2,3...

组合使用 PARTITION BY + ORDER BY

问题 每个部门只留薪资最高的那个人（去重）

问题 每个人的薪资，和所在部门平均相比差多少？

问题 按入职日期排序，累计发了多少薪资？

问题 每个人和上一个入职的人比，薪资变化了多少？

问题 按 name + dept 两个字段联合去重，每组只保留1条

总结：什么时候用什么

一句话记忆

问题全公司薪资最高的3个人是谁？

问题薪资大于12000的有哪些人？

问题全公司有多少人？平均薪资多少？

问题每个部门各有多少人？

思考如果我这样写，会发生什么？

问题每个部门的平均薪资和总薪资？

问题按部门+薪资分组（多字段 GROUP BY）

问题哪些部门有2人及以上？

核心对比同一个问题，两种写法

问题全公司薪资排名（三种排名函数对比）

问题每个部门只留薪资最高的那个人（去重）

问题每个人的薪资，和所在部门平均相比差多少？

问题按入职日期排序，累计发了多少薪资？

问题每个人和上一个入职的人比，薪资变化了多少？

问题按 name + dept 两个字段联合去重，每组只保留1条