上个月要做一个月度报表：每个员工的销售额排名、部门内占比、累计值。我用存储过程 + 临时表搞了两百多行 SQL，同事看了一眼说"窗口函数几行就搞定了"。
花了一个周末学了一遍，发现这东西是真的好用。
什么是窗口函数
简单说：在不减少行数的情况下，给每一行加一个"计算列"。
普通 GROUP BY 会把多行聚合成一行，而窗口函数保留所有原始行，只是额外算一个值。
语法骨架
函数名(...) OVER (
PARTITION BY 分组列
ORDER BY 排序列
ROWS BETWEEN 窗口范围
)
PARTITION BY：按什么分组（类似 GROUP BY，但不减少行数）ORDER BY：组内排序ROWS BETWEEN：窗口范围（可选，用于 SUM/AVG 等聚合函数）
排名类
-- 假设有一张销售表 sales(employee_id, department, amount, month)
SELECT
employee_id,
department,
amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
RANK() OVER (ORDER BY amount DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_num
FROM sales
WHERE month = '2026-08'
| employee_id | amount | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| E001 | 50000 | 1 | 1 | 1 |
| E002 | 45000 | 2 | 2 | 2 |
| E003 | 45000 | 3 | 2 | 2 |
| E004 | 40000 | 4 | 4 | 3 |
| E005 | 35000 | 5 | 5 | 4 |
三个排名函数的区别：
ROW_NUMBER：不并列，强制给不同序号（即使值相同）RANK：并列跳号（2, 2, 4）DENSE_RANK：并列不跳号（2, 2, 3）
取每个部门的第一名：
WITH ranked AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY amount DESC) AS rn
FROM sales
WHERE month = '2026-08'
)
SELECT * FROM ranked WHERE rn = 1
这个模式非常常用——先排名，再取 top N。
聚合类窗口函数
SELECT
employee_id,
department,
amount,
SUM(amount) OVER (PARTITION BY department) AS dept_total,
AVG(amount) OVER (PARTITION BY department) AS dept_avg,
amount / SUM(amount) OVER (PARTITION BY department) AS dept_ratio
FROM sales
WHERE month = '2026-08'
dept_total 是该部门的总销售额，dept_ratio 是该员工占部门的比例。每一行都有值，不用 GROUP BY。
累计和滑动窗口
SELECT
month,
amount,
SUM(amount) OVER (ORDER BY month) AS cumulative_sum,
SUM(amount) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3month,
LAG(amount, 1) OVER (ORDER BY month) AS prev_month,
LEAD(amount, 1) OVER (ORDER BY month) AS next_month
FROM monthly_sales
WHERE employee_id = 'E001'
| month | amount | cumulative_sum | rolling_3month | prev_month | next_month |
|---|---|---|---|---|---|
| 1月 | 10000 | 10000 | 10000 | NULL | 12000 |
| 2月 | 12000 | 22000 | 22000 | 10000 | 15000 |
| 3月 | 15000 | 37000 | 37000 | 12000 | 11000 |
| 4月 | 11000 | 48000 | 38000 | 15000 | 13000 |
cumulative_sum：从第一行累加到当前行rolling_3month：前 2 行 + 当前行 = 近 3 个月滑动求和LAG/LEAD：取前一行 / 后一行的值
NTILE 分桶
-- 把员工按销售额分成 4 档
SELECT
employee_id,
amount,
NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales
WHERE month = '2026-08'
quartile = 1 的是 top 25%，= 4 的是 bottom 25%。
兼容性
| 数据库 | 支持情况 |
|---|---|
| MySQL 8.0+ | 全支持 |
| PostgreSQL | 全支持 |
| SQL Server | 全支持 |
| Oracle | 全支持（窗口函数的发源地） |
| MySQL 5.7 及以下 | 不支持 |
MySQL 5.7 想实现类似效果只能用变量模拟或者子查询，很痛苦。如果你的项目还在 5.7，强烈建议升级。
实际场景
每个部门销售额 top 3 的员工：
WITH ranked AS (
SELECT *,
RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS rk
FROM sales WHERE month = '2026-08'
)
SELECT department, employee_id, amount
FROM ranked WHERE rk <= 3
ORDER BY department, rk
月环比增长率：
SELECT
month,
amount,
LAG(amount) OVER (ORDER BY month) AS prev_amount,
ROUND(
(amount - LAG(amount) OVER (ORDER BY month)) * 100.0
/ LAG(amount) OVER (ORDER BY month), 2
) AS growth_rate
FROM monthly_revenue
总结
窗口函数就是"不减少行数的 GROUP BY"。排名用 ROW_NUMBER/RANK/DENSE_RANK，累计用 SUM() OVER(ORDER BY ...)，取前后行用 LAG/LEAD，分组聚合用 SUM/AVG/COUNT OVER(PARTITION BY ...)。会了这几个，报表类需求基本不用写存储过程了。
