窗口函数（Window Function）是 SQL 里最被低估的能力之一：它让你在不折叠行的前提下完成排名、累计、前后行取值。在报表、榜单、环比、去重取最新等场景里，它几乎总能替代又长又难维护的自连接与关联子查询。本文用 PARTITION BY、LAG/LEAD 等语法，把常见的报表与排名难题改写成几行清晰 SQL，并给出大表性能与易踩坑的实战建议。
一、窗口函数到底是什么
普通聚合函数（SUM、COUNT）配合 GROUP BY 会把多行压成一行，分组内明细就此丢失。窗口函数则给每一行都开一个”观察窗口”，在窗口内计算，但结果仍按原行返回——既不聚合行数，又能做跨行统计。这正是它适合做排行榜、环比、累计流水的原因。
举个对比：想同时看”每个员工的薪资”和”部门薪资总额”，用 GROUP BY dept 只能得到每个部门一行，看不到员工明细；要么再自连接一次，要么写子查询。窗口函数一行就解决：
-- GROUP BY 写法：只能拿到部门汇总，丢失员工明细
SELECT dept, SUM(salary) FROM employees GROUP BY dept;
-- 窗口函数写法：明细 + 汇总同框返回
SELECT
dept, emp_name, salary,
SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM employees;二、OVER 与 PARTITION BY：分组但不聚合
2.1 最小可用语法
一个窗口函数由三部分组成：函数本身、OVER (PARTITION BY 列 ORDER BY 列)、以及可选的窗口帧。下面统计每个部门的薪资总额，但每行都保留员工明细：
SELECT
dept,
emp_name,
salary,
SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM employees;2.2 和 GROUP BY 的关键差异
GROUP BY dept 只会给你”每个部门一行”；而上面的窗口查询返回”每个员工一行 + 部门总额列”。当你既要明细又要汇总时，窗口函数省掉了子查询和自连接。一个判断标准：只要你想在结果里同时保留”明细行”和”按组分组的聚合值”，窗口函数就是首选。
常见误区要提前说清：窗口函数不能写在 WHERE 或 GROUP BY 里，因为 SQL 执行顺序中窗口函数位于 SELECT 阶段，晚于 WHERE/GROUP BY 求值。想在过滤条件里用窗口计算结果，必须像第六节的 Top-N 那样包一层子查询或 CTE。需要诊断这类查询在大表上的开销时，可以配合 《MySQL EXPLAIN 实战：读懂执行计划优化慢查询》 看执行计划。
三、排名三兄弟：ROW_NUMBER / RANK / DENSE_RANK
三者都用于”按某列排序后编号”，区别在并列时的处理：
SELECT
emp_name,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
RANK() OVER (ORDER BY score DESC) AS rk,
DENSE_RANK() OVER (ORDER BY score DESC) AS drk
FROM exam_result;假设分数为 100/100/90：ROW_NUMBER 永远给 1、2、3（即使并列也强行区分）；RANK 给 1、1、3（并列占名次）；DENSE_RANK 给 1、1、2（并列不占名次）。做”成绩排行榜”通常选 RANK 或 DENSE_RANK。用一张表看更直观：
| score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 100 | 1 | 1 | 1 |
| 100 | 2 | 1 | 1 |
| 90 | 3 | 3 | 2 |
注意：ROW_NUMBER 在并列时没有稳定顺序，若需要”同分按姓名再排”，务必补上第二排序键：ROW_NUMBER() OVER (ORDER BY score DESC, emp_name)。
四、累计与移动：SUM / AVG OVER
加上 ORDER BY 与窗口帧，窗口可以从”整个分区”缩小为”从第一行到当前行”，实现累计值：
SELECT
order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily_orders;ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示”从分区首行到当前行”。把 SUM 换成 AVG，再改成 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW，就能得到”近 3 行移动平均”，常用于时序平滑。
这里有个容易踩的坑：ROWS 按”物理行”数计算，RANGE 按”排序列的值区间”计算。例如 ORDER BY order_date RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW 表示”过去 7 天”，它看的是日期差值而非前后几行；当一天有多条记录时，ROWS 和 RANGE 结果会明显不同，务必按业务语义选对。
五、前后行取值：LAG / LEAD
环比、日活差值、状态变更间隔，都能用 LAG（取前一行）和 LEAD（取后一行）一行搞定，无需自连接：
SELECT
order_date,
amount,
LAG(amount, 1) OVER (ORDER BY order_date) AS prev_day,
amount - LAG(amount, 1) OVER (ORDER BY order_date) AS day_over_day
FROM daily_orders;LAG(amount, 1) 取上一行的 amount，第二参数 N 可往前取第 N 行；第三个参数可指定”首行无前值”时的默认值（如 LAG(amount, 1, 0)）。基于它还能直接算环比增长率：
SELECT
order_date,
amount,
ROUND(
(amount - LAG(amount,1) OVER (ORDER BY order_date))
/ NULLIF(LAG(amount,1) OVER (ORDER BY order_date), 0) * 100, 2
) AS growth_pct
FROM daily_orders;NULLIF(..., 0) 避免首日除零。想知道 PostgreSQL 里这些函数如何与索引配合提速，可参考 《PostgreSQL vs SQL Server：语法差异与迁移避坑指南》。
六、实战：每组取前 N 条（Top-N）
“每个部门薪资最高的前 3 名员工”是经典需求，窗口函数比”关联子查询”可读性高得多：
SELECT *
FROM (
SELECT
dept,
emp_name,
salary,
ROW_NUMBER() OVER (
PARTITION BY dept ORDER BY salary DESC
) AS rn
FROM employees
) t
WHERE rn <= 3;思路是：先在每个 dept 分区内按薪资排序编号，外层再用 WHERE rn <= 3 只保留前三。若并列也要全留，把 ROW_NUMBER 换成 DENSE_RANK 即可。需要按业务规则自动生成这类 SQL 时，《Text2SQL 落地实战：自然语言转 SQL 与安全执行》 提供了一条可行路径。
另一个高频场景是”去重后保留每组最新一条”。比如一张操作日志表有同一 user_id 的多条记录，只要最新的一条：
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY created_at DESC
) AS rn
FROM user_log
)
SELECT * FROM ranked WHERE rn = 1;用 CTE 把”编号”和”过滤”分开，比 GROUP BY + MAX 再回表关联更直观，也不会因为要保留非分组列而不得不写一堆 MAX(col)。
七、和 EXPLAIN 配合：大表性能底线
窗口函数默认会对 PARTITION BY / ORDER BY 的列做排序，大表上可能出现昂贵的 Using filesort 或外部排序。经验法则：
- 给
PARTITION BY的列建索引，让分区边界与索引顺序一致，减少排序； - 窗口内的
ORDER BY尽量复用同一索引顺序； - 上千万行优先先做
WHERE过滤再开窗，缩小输入集。
建议在预发环境用 EXPLAIN ANALYZE 实测一遍，确认没有意外的全表排序。索引的底层取舍可看 《MySQL 索引底层原理：B+树、覆盖索引与最左前缀》。
八、常见坑与最佳实践
| 坑 | 现象 | 对策 |
|---|---|---|
| 忘记 PARTITION BY | 全表当成一个窗口，排名错乱 | 明确分区列，必要时加注释 |
| ORDER BY 缺失 | 累计/排名结果不稳定 | 窗口语义必须显式排序 |
| 窗口帧理解错 | 累计值比预期少/多 | 显式写 ROWS/RANGE BETWEEN |
| 大表无索引 | filesort 拖垮查询 | 按分区/排序列建索引 |
| NULL 参与排序 | NULL 排在最前/最后不一致 | 用 NULLS LAST 或 COALESCE |
九、速查清单
记住四个动词就能覆盖八成场景：排名（ROW_NUMBER/RANK/DENSE_RANK）、累计（SUM/AVG OVER）、前后行（LAG/LEAD）、分组 Top-N（PARTITION BY + 外层过滤）。下次写报表 SQL 前，先问自己”是不是在不该聚合的地方用了 GROUP BY”——如果是，窗口函数大概率更合适。
十、跨数据库兼容性与版本基线
好消息是，主流数据库对窗口函数的支持已经非常成熟：PostgreSQL 从 8.4 起就完整支持；MySQL 直到 8.0 才补齐（5.7 及更早版本完全用不了，迁移老库要特别注意）；SQL Server 自 2008 起支持；Oracle 在分析函数时代就已具备。只要把版本基线钉在 MySQL 8 / PostgreSQL 9+，窗口函数基本可以放心写。
细节差异主要有两个。第一，窗口帧默认值不同：PostgreSQL 和 MySQL 在只写 ORDER BY 而不写帧时，默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW；SQL Server 的范围也到当前行，但按值区间处理。为可移植，建议永远显式写出 ROWS BETWEEN ...。第二，取前 N 行的写法不同：SQL Server 习惯用 TOP + ORDER BY，而我们用的 ROW_NUMBER() + 外层 WHERE 在各大数据库都通用。写一套能在多库运行的报表 SQL，显式窗口帧加 ROW_NUMBER 组合是最稳的选择。
