窗口函数

Q: Q：窗口函数能不能用在 WHERE 子句里？

A： 不能。WHERE 在窗口函数之前执行，此时窗口函数还没计算。需要先用子查询或 CTE 计算窗口函数，再在外层用 WHERE 过滤。

Q: Q：ROW_NUMBER 和 RANK 什么时候该用哪个？

A： 需要"唯一编号、不并列"用 ROW_NUMBER（如分页、去重）；需要"按值排名、允许并列"用 RANK（如成绩排名、销售排名）。

Q: Q：PARTITION BY 和 GROUP BY 有什么区别？

A： GROUP BY 会将多行合并为一行（行数减少）；PARTITION BY 只是划分窗口范围，不减少行数（每行保留）。两者可以同时使用：先 GROUP BY 聚合，再用窗口函数附加排名。

Q: Q：窗口函数的性能怎么样？

A： 窗口函数通常比自关联子查询性能更好。对于大数据量，建议在 PARTITION BY 和 ORDER BY 涉及的列上创建索引。

🌍 生活类比

想象一个班级的成绩单：

GROUP BY — 把全班按性别分组，算出男生平均分和女生平均分，结果只有两行
窗口函数 — 在每个学生的成绩单上，额外写上"你在男生中排第几名""你比男生平均分高多少"，保留每个人的信息

窗口函数就像一个"透视镜"——不压缩行数，却能在每一行上看到整体的统计信息。

🎯 核心概念

什么是窗口函数

窗口函数在不减少行数的前提下，对"窗口"内的数据进行聚合或排名计算。每行都能看到自己的明细，同时也能看到分组内的汇总信息。

OVER 子句

OVER() 是窗口函数的核心，它定义了"窗口"的范围：

SQL

函数() OVER (
    [PARTITION BY 列]    -- 按哪个字段分区（分组）
    [ORDER BY 列]        -- 分区内如何排序
    [ROWS/RANGE 窗口]    -- 可选：进一步限定行范围
)

ROW_NUMBER / RANK / DENSE_RANK — 排名函数

SQL

-- ROW_NUMBER：严格递增序号，不并列（1, 2, 3, 4）
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- RANK：允许并列，有跳号（1, 2, 2, 4）
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- DENSE_RANK：允许并列，无跳号（1, 2, 2, 3）
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

函数	并列时	跳号	示例结果
ROW_NUMBER	不允许并列	—	1, 2, 3, 4
RANK	允许并列	有	1, 2, 2, 4
DENSE_RANK	允许并列	无	1, 2, 2, 3

NTILE — 分桶

将数据均匀分成 N 组：

SQL

-- 将员工按工资高低分成4个等级（四分位）
NTILE(4) OVER (ORDER BY salary DESC)

LAG / LEAD — 前后行访问

SQL

-- LAG：访问前一行（上一个）
LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date)

-- LEAD：访问后一行（下一个）
LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date)

函数	方向	用途
LAG(col, n)	向前看第 n 行	计算环比、与前一条记录对比
LEAD(col, n)	向后看第 n 行	预测下一条记录

SUM / AVG / COUNT OVER — 聚合窗口函数

SQL

-- 每行显示部门工资总和
SUM(salary) OVER (PARTITION BY department_id)

-- 每行显示部门平均工资
AVG(salary) OVER (PARTITION BY department_id)

-- 每行显示部门人数
COUNT(*) OVER (PARTITION BY department_id)

PARTITION BY 与 ORDER BY in Window

SQL

-- PARTITION BY：划分窗口范围（类似 GROUP BY 的分组，但不压缩行）
-- ORDER BY：窗口内排序，影响排名和累计计算

-- 例：按部门分组，部门内按工资降序排名
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- 例：累计求和（按入职日期排序，逐行累加工资）
SUM(salary) OVER (ORDER BY hire_date)

窗口函数 vs GROUP BY

特性	GROUP BY	窗口函数
行数	减少（合并为一行）	不变（保留所有行）
明细数据	丢失	保留
用途	汇总统计	排名、对比、累计计算
能否同时看明细和汇总	❌ 不能	✅ 可以

💡 经验法则：需要"聚合后保留明细"时，用窗口函数；只需要汇总结果时，用 GROUP BY。

📝 基本语法

SQL

-- 窗口函数通用语法
函数() OVER (
    [PARTITION BY 列1, 列2]
    [ORDER BY 列3 [ASC|DESC]]
    [ROWS BETWEEN ... AND ...]
)

-- 排名
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)
RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC)
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- 前后行
LAG(column, offset, default)  OVER (ORDER BY column)
LEAD(column, offset, default) OVER (ORDER BY column)

-- 聚合
SUM(column) OVER (PARTITION BY col)
AVG(column) OVER (PARTITION BY col)
COUNT(*)    OVER (PARTITION BY col)

-- 分桶
NTILE(n) OVER (ORDER BY column)

💡 提示：

PARTITION BY 可以省略，表示对整个结果集开窗口

ORDER BY 在排名函数中必须，在聚合函数中可选

窗口函数不能在 WHERE 或 HAVING 中使用，需要用子查询或 CTE 包裹

📌 示例

示例：员工工资部门内排名

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS 姓名,
    d.department_name AS 部门,
    e.salary AS 工资,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS 行号,
    RANK()       OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS 排名,
    DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS 密集排名
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, 工资 DESC;

▶ 试一试

执行结果：

TEXT

姓名 | 部门   | 工资      | 行号 | 排名 | 密集排名
----+--------+----------+------+------+---------
张伟 | 技术部 | 12000.00 |    1 |    1 |       1
李娜 | 技术部 |  9500.00 |    2 |    2 |       2
王强 | 销售部 |  8000.00 |    1 |    1 |       1
赵敏 | 销售部 | 11000.00 |    2 |    2 |       2
刘洋 | 市场部 |  7500.00 |    1 |    1 |       1
陈静 | NULL   |  9000.00 |    1 |    1 |       1

说明：三种排名函数的区别在有并列值时最明显。ROW_NUMBER 严格编号，RANK 并列后跳号，DENSE_RANK 并列后不跳号。

示例：计算与部门平均工资的差值

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS 姓名,
    d.department_name AS 部门,
    e.salary AS 工资,
    ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS 部门均值,
    ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS 与均值差
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, 工资 DESC;

▶ 试一试

执行结果：

TEXT

姓名 | 部门   | 工资      | 部门均值  | 与均值差
----+--------+----------+----------+---------
张伟 | 技术部 | 12000.00 | 10750.00 |  1250.00
李娜 | 技术部 |  9500.00 | 10750.00 | -1250.00
赵敏 | 销售部 | 11000.00 |  9500.00 |  1500.00
王强 | 销售部 |  8000.00 |  9500.00 | -1500.00
刘洋 | 市场部 |  7500.00 |  7500.00 |     0.00
陈静 | NULL   |  9000.00 |  9000.00 |     0.00

说明：AVG() OVER() 在每行上计算部门平均值，不压缩行数，每个员工都能直接看到自己与平均值的差距。

示例：LAG 计算订单环比增长

SQL

SELECT 
    o.order_id AS 订单号,
    o.order_date AS 订单日期,
    o.total_amount AS 金额,
    LAG(o.total_amount, 1) OVER (ORDER BY o.order_date) AS 上一单金额,
    ROUND(
        (o.total_amount - LAG(o.total_amount, 1) OVER (ORDER BY o.order_date)) 
        / LAG(o.total_amount, 1) OVER (ORDER BY o.order_date) * 100, 
    2) AS 环比增长百分比
FROM orders o
WHERE o.status != 'cancelled'
ORDER BY o.order_date;

▶ 试一试

执行结果：

TEXT

订单号 | 订单日期   | 金额     | 上一单金额 | 环比增长百分比
------+-----------+---------+-----------+--------------
 1001 | 2026-01-10| 2500.00 |    NULL   |         NULL
 1002 | 2026-02-15| 1800.00 |  2500.00  |       -28.00
 1003 | 2026-02-20| 3200.00 |  1800.00  |        77.78
 1004 | 2026-03-05|  950.00 |  3200.00  |       -70.31
 1005 | 2026-03-10| 4100.00 |   950.00  |       331.58
 ...

说明：LAG() 访问"上一行"的值，非常适合计算环比、同比等趋势分析。第一行没有上一行，返回 NULL。

🎬 场景演练

场景一：部门销售冠军筛选

找出每个部门销售额最高的员工。

SQL

WITH emp_sales AS (
    SELECT 
        CONCAT(e.first_name, e.last_name) AS 姓名,
        d.department_name AS 部门,
        COALESCE(SUM(o.total_amount), 0) AS 总销售额,
        RANK() OVER (PARTITION BY d.department_id ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) AS 部门排名
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY e.employee_id, e.first_name, e.last_name, d.department_id, d.department_name
)
SELECT 姓名, 部门, 总销售额, 部门排名
FROM emp_sales
WHERE 部门排名 = 1
ORDER BY 总销售额 DESC;

要点：先用 CTE + RANK() 排名，再用 WHERE 过滤第一名。窗口函数不能直接写在 WHERE 中，需要包一层。

场景二：员工入职顺序与工资变化追踪

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS 姓名,
    d.department_name AS 部门,
    e.hire_date AS 入职日期,
    e.salary AS 工资,
    LAG(e.salary, 1) OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS 上一位工资,
    ROUND(e.salary - LAG(e.salary, 1) OVER (PARTITION BY e.department_id ORDER BY e.hire_date), 2) AS 工资变化,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS 入职顺序
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, e.hire_date;

要点：LAG() + PARTITION BY 可以追踪分组内的序列变化，适合分析趋势。

❓ 常见问题

Q：窗口函数能不能用在 WHERE 子句里？ A：不能。WHERE 在窗口函数之前执行，此时窗口函数还没计算。需要先用子查询或 CTE 计算窗口函数，再在外层用 WHERE 过滤。

Q：ROW_NUMBER 和 RANK 什么时候该用哪个？ A：需要"唯一编号、不并列"用 ROW_NUMBER（如分页、去重）；需要"按值排名、允许并列"用 RANK（如成绩排名、销售排名）。

Q：PARTITION BY 和 GROUP BY 有什么区别？ A： GROUP BY 会将多行合并为一行（行数减少）；PARTITION BY 只是划分窗口范围，不减少行数（每行保留）。两者可以同时使用：先 GROUP BY 聚合，再用窗口函数附加排名。

Q：窗口函数的性能怎么样？ A：窗口函数通常比自关联子查询性能更好。对于大数据量，建议在 PARTITION BY 和 ORDER BY 涉及的列上创建索引。

📖 小节

函数	用途	示例
ROW_NUMBER	唯一序号	分页、去重
RANK	并列排名（有跳号）	成绩排名
DENSE_RANK	并列排名（无跳号）	等级划分
NTILE	均匀分桶	分位数分析
LAG	访问前一行	环比计算
LEAD	访问后一行	趋势预测
SUM/AVG/COUNT OVER	累计/分组聚合	对比分析

OVER() 定义窗口范围，PARTITION BY 分区，ORDER BY 排序
窗口函数不减少行数，保留明细的同时附加聚合信息
窗口函数不能放在 WHERE 中，需要用子查询或 CTE 包裹

📝 作业

使用 ROW_NUMBER() 为 employees 表按入职日期排序编号，找出最新入职的员工。
使用 RANK() 按部门对员工工资排名，找出每个部门工资最高的前两名。
使用 LAG() 计算每个部门内员工按入职日期排序后的工资变化。
使用 SUM() OVER() 计算每笔订单的累计销售额（按订单日期排序）。

下一课

👉 20-cte-temp-table - CTE与临时表：学习 WITH 子句（CTE）的用法，包括递归 CTE、CTE 与子查询的对比，以及临时表的创建和使用场景。

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

404 Not Found

窗口函数

窗口函数

🌍 生活类比

🎯 核心概念

什么是窗口函数

OVER 子句

ROW_NUMBER / RANK / DENSE_RANK — 排名函数

NTILE — 分桶

LAG / LEAD — 前后行访问

SUM / AVG / COUNT OVER — 聚合窗口函数

PARTITION BY 与 ORDER BY in Window

窗口函数 vs GROUP BY

📝 基本语法

📌 示例

示例：员工工资部门内排名

示例：计算与部门平均工资的差值

示例：LAG 计算订单环比增长

🎬 场景演练

场景一：部门销售冠军筛选

场景二：员工入职顺序与工资变化追踪

❓ 常见问题

📖 小节

📝 作业

下一课

🙏 帮我们做得更好