T-SQL 窗口函数实战：排名与聚合

dbaai

T-SQL 窗口函数实战：排名与聚合

一、具体的问题

很多写 T-SQL 的人，一遇到"按部门给员工工资排名""算每个月的累计销售额""取每组前三条记录"这类需求，第一反应是 GROUP BY 配合子查询，甚至把整张表 self-join 好几遍。结果 SQL 又长又慢，还经常因为 GROUP BY 把明细行压没了而算错数。

窗口函数（Window Function）就是为这种"既要保留明细行、又要在行间做计算"的场景而生的。本文用一个员工薪酬 + 销售业绩的真实模型，把 ROW_NUMBER / RANK / DENSE_RANK 三种排名函数，以及 SUM() OVER 累计、移动平均讲清楚，并给出可照做的 T-SQL。

二、核心原理

1. 窗口函数不折叠行

和聚合函数最大的区别：窗口函数不会把多行合并成一行。它用 OVER(...) 定义一个"窗口"（一组相关行），对每一行计算一个值，但原始明细行全部保留。这正是它能边排名边保留原始数据的根本原因。

2. 三种排名函数的差异

• ROW_NUMBER()：纯顺序编号，1、2、3、4……即使值相同也给出不同序号，无并列。
  
• RANK()：相同值并列，并列后跳过名次。如两个第 2 名，下一个是第 4 名。
  
• DENSE_RANK()：相同值并列，但不跳名次。两个第 2 名，下一个是第 3 名。
  

选错函数，排名结果会差很大。

3. PARTITION BY 与 ORDER BY

OVER 里 PARTITION BY 决定"在哪个分组内排"（如按部门分组），ORDER BY 决定"按什么顺序排"。这两个是窗口函数的灵魂。注意：窗口里的 ORDER BY 是"计算排序"而非"结果去重"，和查询顶层的 ORDER BY 是两回事。

4. 聚合 + 窗口 = 累计与移动

SUM(sal) OVER(PARTITION BY dept ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 就能算出"到当前月为止的累计销售额"，不需要自连接。再用 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 可以算移动平均。

三、实例参考（动手步骤）

建一张员工薪酬表和销售事实表，跑通三种排名与累计。

2. -- 1) 建表并灌入测试数据
3. CREATE TABLE dbo.EmpSalary (
4.   emp_id   INT,
5.   dept     VARCHAR(20),
6.   salary   INT
7. );
8. INSERT INTO dbo.EmpSalary VALUES
9.   (1,'研发',18000),(2,'研发',15000),(3,'研发',15000),(4,'研发',12000),
10.   (5,'销售',16000),(6,'销售',16000),(7,'销售',9000);
12. -- 2) 三种排名对比（按部门内 salary 降序）
13. SELECT dept, emp_id, salary,
14.        ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn,
15.        RANK()       OVER(PARTITION BY dept ORDER BY salary DESC) AS rk,
16.        DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS drk
17. FROM dbo.EmpSalary
18. ORDER BY dept, salary DESC;
19. -- 观察：研发部 salary=15000 的两人，ROW_NUMBER 给出 2/3（无并列），
20. --       RANK 给出 2/2（并列第2、下一人第4），DENSE_RANK 给出 2/2（并列第2、下一人第3）。

复制代码

2. -- 3) 每月累计销售额（移动累计，不丢明细行）
3. CREATE TABLE dbo.MonthSales (sales_month DATE, amount INT);
4. INSERT INTO dbo.MonthSales VALUES
5.   ('2026-01-01',1000),('2026-02-01',1500),('2026-03-01',800),('2026-04-01',2000);
7. SELECT sales_month, amount,
8.        SUM(amount) OVER(ORDER BY sales_month
9.                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
10.        AVG(amount) OVER(ORDER BY sales_month
11.                         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg_3
12. FROM dbo.MonthSales
13. ORDER BY sales_month;
14. -- running_total 依次累加到 5300；moving_avg_3 为当前月与前后各一月的均值。

复制代码

2. -- 4) 取每个部门工资最高的前两名（TOP N per group）
3. WITH ranked AS (
4.   SELECT dept, emp_id, salary,
5.          ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn
6.   FROM dbo.EmpSalary
7. )
8. SELECT dept, emp_id, salary
9. FROM ranked
10. WHERE rn <= 2
11. ORDER BY dept, rn;
12. -- 研发返回 18000/15000；销售返回 16000/16000（ROW_NUMBER 在两值相同时任意取两个）。

复制代码

前后对比：用 GROUP BY + 子查询实现"取每个部门前两名"至少要两次扫描或 self-join，窗口函数一次扫描搞定，执行计划里少了 Nested Loop，逻辑读明显下降。

四、实操检查清单

• 排名需求是否真的需要"并列"？要并列用 RANK/DENSE_RANK，要唯一序号用 ROW_NUMBER。
  
• OVER 里是否漏了 PARTITION BY？漏了会导致全表当成一个分组排名。
  
• 累计/移动窗口的 ROWS BETWEEN 边界是否正确（UNBOUNDED PRECEDING 还是 N PRECEDING）？
  
• 窗口里的 ORDER BY 和查询顶层 ORDER BY 是否混淆？前者决定窗口计算顺序，后者决定结果展示顺序。
  
• 取 TOP N per group 是否用 CTE + ROW_NUMBER 过滤，而非 TOP 配合错误的 GROUP BY？
  
• 大表上窗口函数是否配合了正确的索引（PARTITION BY/ORDER BY 列最好有复合索引）避免排序溢出到 tempdb？
  

五、一个常被忽略的坑：窗口函数里的排序溢出

曾有一条按月累计的报表 SQL 在 500 万行的表上跑了 40 秒，执行计划显示"Sort"操作符把整个数据集溢写到 tempdb。根因是窗口函数的 ORDER BY 列没有对应索引，SQL Server 只能先全表排序再开窗。补上 (sales_month) 上的索引后，排序被索引顺序替代，耗时降到 2 秒。教训：窗口函数的排序列务必有索引支撑，否则性能会随数据量线性恶化。