说个常遇到的场景业务方甩过来一句“把每个部门销售额前3名的员工列出来还要带排名”。放在五六年前我大概率会写子查询套子查询或者干脆把数据捞回应用层让C#那边循环算。这两种方式我都干过一个比一个难受SQL越写越长性能还说不清。窗口函数Window Function正是SQL Server专门用来解决这类“既要看明细、又要看整体”问题的工具。它能在保留每一行原始数据的同时在一行里同时看到本行值、分区聚合值、还有相邻行的值不需要把结果集压扁成GROUP BY之后的聚合行。这篇文章我会从OVER()子句的三种构成要素讲起把排名、偏移、聚合三类常用窗口函数逐个拆解再给四个可以直接复制的实战SQL最后把我实际踩过的坑和性能调优建议一并分享。不管是写报表、做数据分析还是给业务系统写复杂查询这篇都能当个查询手册用。1. 为什么窗口函数值得专门学传统聚合的痛点与新解法1.1 没有窗口函数时这些需求有多难写先回忆没有窗口函数的年代。我想求“每个部门按销售额从高到低排名”GROUP BY dept只能给我们每个部门的总额拿不到每个员工的明细想拿明细就得用相关子查询SELECT s1.dept, s1.region, s1.amount, (SELECT COUNT(*) 1 FROM dbo.sales s2 WHERE s2.dept s1.dept AND s2.amount s1.amount) AS rn FROM dbo.sales s1;这写法在10万行的小表上还能忍一旦表到百万级相关子查询每条外层数据都要回表扫描一次性能会迅速恶化。更别提“计算某员工占部门总销售额的百分比”“对比本月和上月的销售额”“移动平均线”这类需求用自连接写出来简直是灾难稍有不慎还会把数据join重复。1.2 窗口函数到底改变了什么窗口函数的本质是在SQL的逻辑执行顺序中“跑在GROUP BY之后、SELECT最终投影之前”的一层计算。它不做行折叠而是对每一行开一扇“窗户”让这一行能“看到”它所在分区里的其他行然后执行聚合、排名或偏移计算。一句话区分三种常见场景普通聚合GROUP BY把多行压成一行明细丢失。窗口聚合SUM(amount) OVER (...)不丢明细每一行都带着自己分区的总和。窗口排名每行获得一个序号排名结果可以直接作为列返回。这个“不丢明细”的特性正是报表类需求最需要的。1.3 SQL Server版本演进你手上的版本能用到什么程度窗口函数不是一版全给的SQL Server这些年分批加入了不同能力。我在项目里见到过还在跑2012的客户也见过2022的新库能力边界完全不同SQL Server版本窗口函数支持情况2005/2008只有ROW_NUMBER、RANK、DENSE_RANK、NTILE以及加OVER()的聚合函数2012新增LAG、LEAD、FIRST_VALUE、LAST_VALUE、PERCENTILE_CONT、PERCENTILE_DISC支持ROWS/RANGE框架2016性能优化支持内存优化表的窗口计算2022LAG/LEAD支持IGNORE NULLS聚合窗口支持更多边界控制所以如果你的生产库还在SQL Server 2012之前下面的LAG/LEAD例子会直接报错需要用自连接替代。我建议你打开SSMS后顺手敲一句SELECT VERSION;确认版本再决定用什么写法。2. OVER()子句拆解分区、排序与框架的三重逻辑2.1 OVER()是窗口函数的“遥控器”所有窗口函数都长这样函数() OVER ( [PARTITION BY 分组列] [ORDER BY 排序列 [ASC|DESC]] [ROWS/RANGE 边界定义] )三个部分都可以省略但省略之后含义完全不同全空的OVER()整个结果集就是自己的一个分区看到的是全表的总计。只有PARTITION BY在每个分组内看到组内总计组内行顺序不确定。加入ORDER BY顺带定义窗口内的排序同时会改变聚合窗口的默认计算范围。很多初学者搞不懂“为什么我的SUM(amount) OVER (ORDER BY sale_date)算出来是累计值不是全表总额”问题就出在第三个部分。这个坑我放到第四章详细讲。2.2 PARTITION BY把数据切成互不干扰的小组PARTITION BY的逻辑和GROUP BY有点像都是按列分组但效果是“给每行标记它属于哪个组”而不把组内多行压成一行。比如SELECT dept, region, amount, SUM(amount) OVER (PARTITION BY dept) AS dept_total FROM dbo.sales;输出结果里“电子”部门的每一行都会带着同一个dept_total同时这一行自己的region、amount也都保留着。这个“明细总计同行显示”能力写报表时太常用了。2.3 ORDER BY窗口内排序和默认框架的触发开关ORDER BY在窗口函数里有双重身份对排名函数决定排名顺序比如ORDER BY amount DESC就是销售额高的排前面。对聚合函数一旦写了ORDER BY默认框架从“整个分区”悄悄变成“从分区第一行到当前行”也就是触发累计计算。这个双重身份是窗口函数最容易出问题的地方。我见过有人用SUM(amount) OVER (PARTITION BY dept ORDER BY sale_date)想拿部门总销售额结果拿到的却是“截至当前日期的部门累计销售额”报表数怎么都对不上。2.4 先跑通第一个窗口查询我用下面这张销售表做全篇的演示数据你可以在自己的SSMS里直接建表跑一遍CREATE TABLE dbo.sales ( id INT IDENTITY(1,1) PRIMARY KEY, dept VARCHAR(20) NOT NULL, region VARCHAR(20) NOT NULL, sale_date DATE NOT NULL, amount DECIMAL(10,2) NOT NULL ); INSERT INTO dbo.sales (dept, region, sale_date, amount) VALUES (电子, 华东, 2024-01-05, 1200.00), (电子, 华东, 2024-01-12, 2350.50), (电子, 华北, 2024-01-18, 800.00), (电子, 华北, 2024-02-03, 1999.00), (服装, 华东, 2024-01-08, 650.00), (服装, 华东, 2024-01-25, 1500.00), (服装, 华南, 2024-02-10, 2200.00), (服装, 华南, 2024-02-15, 1000.00), (食品, 华南, 2024-01-02, 300.00), (食品, 华南, 2024-02-01, 450.50), (食品, 西南, 2024-02-20, 780.00), (食品, 西南, 2024-03-01, 900.00);先跑一个最基础的窗口查询感受一下“明细带总体”的效果SELECT dept, region, sale_date, amount, SUM(amount) OVER (PARTITION BY dept) AS dept_total, SUM(amount) OVER () AS grand_total FROM dbo.sales ORDER BY dept, sale_date;结果里每一行都会有两个额外的总计列一个是部门小计一个是全表单据总额。这个查询跑通之后窗口函数的基本手感就建立了。3. 三大类窗口函数逐个拆解排名、偏移与聚合的实战区别3.1 排名族ROW_NUMBER、RANK、DENSE_RANK、NTILE排名类是最常用的一族。四个函数名字像行为差很多面试和实际应用里都容易混淆函数行为相同排名是否有断号典型用途ROW_NUMBER()连续编号不关注重复值无重复编号取Top N、去重、分页RANK()并列名次按人数跳号有跳号标准竞赛排名DENSE_RANK()并列名次名次连续无跳号需要密集排名的榜单NTILE(n)把分区平均分成n组返回组号—数据分桶、百分位抽样看个具体例子SELECT dept, region, amount, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amount DESC) AS rn, RANK() OVER (PARTITION BY dept ORDER BY amount DESC) AS rk, DENSE_RANK() OVER (PARTITION BY dept ORDER BY amount DESC) AS dr, NTILE(2) OVER (PARTITION BY dept ORDER BY amount DESC) AS grp FROM dbo.sales;假设“电子”部门有两条amount相同的记录ROW_NUMBER()会给它们编号1和2RANK()则都在第1名但下一个名次从第3名开始DENSE_RANK()会让下一个名次是第2名。这在实际报表里决定了“并列第二名到底算不算第二名”完全看业务口径。3.2 偏移族LAG、LEAD让你看见相邻行LAG(列, 偏移量, 默认值)取“当前行前面第N行”的值LEAD(列, 偏移量, 默认值)取“当前行后面第N行”的值。这是计算同比、环比、差值的最优解没有之一。SELECT dept, sale_date, amount, LAG(amount, 1, 0) OVER (PARTITION BY dept ORDER BY sale_date) AS prev_amount, amount - LAG(amount, 1, 0) OVER (PARTITION BY dept ORDER BY sale_date) AS diff_amount FROM dbo.sales WHERE dept N服装;注意两点PARTITION BY dept让偏移只在部门内部进行否则上一行会跨部门取数逻辑直接错。第三个参数0是偏移不到时的默认值。分组内第一行没有“上一行”不写默认值返回NULL如果后续计算直接拿它做减法结果会变成NULL报表里就是一堆空值。3.3 聚合族在窗口模式下的威力SUM、AVG、COUNT、MIN、MAX加上OVER()之后用途完全不一样。最常用的是分区总计、累计求和、移动平均SELECT dept, sale_date, amount, SUM(amount) OVER (PARTITION BY dept) AS dept_total, AVG(amount) OVER (PARTITION BY dept) AS dept_avg FROM dbo.sales;这就是存量的“明细汇总”。注意这里OVER()里只有PARTITION BY没有ORDER BY所以SUM看到的是整个部门分区。一旦加上ORDER BY行为就切换为累计见第四章。3.4 边缘函数FIRST_VALUE、LAST_VALUE、PERCENTILE_CONTFIRST_VALUE和LAST_VALUE用来取窗口内第一行和最后一行的值。比如看每个部门“第一笔销售单的金额”可以直接SELECT dept, sale_date, amount, FIRST_VALUE(amount) OVER (PARTITION BY dept ORDER BY sale_date) AS first_sale_amount FROM dbo.sales;这里有个细节LAST_VALUE实际取的是“当前框架内最后一行”如果不手动把窗口框架扩展到分区末尾默认框架只到当前行LAST_VALUE会退化成当前行的值。必须写成LAST_VALUE(amount) OVER ( PARTITION BY dept ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_sale_amount才能取到分区最后一行。这个问题的根因就在下一章的框架规则里。4. 窗口框架FRAME进阶默认框的陷阱与自定义框的应用4.1 默认框架为什么你的SUM突然变成累计值窗口函数里最难理解、也是最冤枉踩坑的部分是窗口框架的默认规则OVER()里只有PARTITION BY窗口框架默认是整个分区。OVER()里出现了ORDER BY默认框架变为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW也就是“从分区开头到当前行按排序值相同扩展”的累计窗口。所以下面两条SQL看起来接近结果完全不同-- 结果1每个部门的销售总额每行都一样 SELECT SUM(amount) OVER (PARTITION BY dept) FROM dbo.sales; -- 结果2每个部门截至当前日期的累计销售额最后一行才等于部门总额 SELECT SUM(amount) OVER (PARTITION BY dept ORDER BY sale_date) FROM dbo.sales;我在一次给客户做销售看板时就栽在这上面。交付前我检查SQL看到SUM(amount) OVER (PARTITION BY dept ORDER BY sale_date)以为是部门总额直到业务方说“为什么电子部门的总额每行都不一样”才意识到这是累计值而不是合计值。4.2 ROWS BETWEEN把窗口精确框出来要精确控制窗口就用ROWS/RANGE BETWEEN ... AND ...语法有固定的几个边界UNBOUNDED PRECEDING分区第一行N PRECEDING往前N行CURRENT ROW当前行N FOLLOWING往后N行UNBOUNDED FOLLOWING分区最后一行求三日移动平均就是最经典的用法SELECT sale_date, amount, AVG(amount) OVER ( ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3day FROM dbo.sales;与默认累计框的区别是移动平均框会随着行号移动每次只取当前行和往前两行做平均。这种写法做趋势分析、平滑曲线非常常见。4.3 ROWS和RANGE的区别ROWS按“物理行数”定位边界写ROWS BETWEEN 2 PRECEDING AND CURRENT ROW就是实打实的三行。RANGE按“排序键的值的范围”定位边界同一排序值的所有行都会被包进来。默认框架用的就是RANGE。RANGE在排序键有大量重复值时会把并列行一起算进窗口。比如排序键是日期同一天有100条销售RANGE BETWEEN 1 PRECEDING AND CURRENT ROW会把前一天和当天所有记录都包含进来行数可能远不止“两天的行数”。而ROWS则严格只取最多两天的窗口行数上限不管当天有多少行都只按行数截断。如果排序键是唯一且连续的ROWS和RANGE结果一样一旦有重复结果可能明显不同。报表口径要求“按时间窗口”选RANGE要求“按N条记录”选ROWS。5. 四个拿来即用的窗口函数实战案例附完整SQL5.1 案例一每个部门销售额Top3需求每个部门按销售额排前三的销售记录。这个需求在窗口函数普及以前SQL写起来版本差异极大窗口函数一行解决WITH ranked AS ( SELECT dept, region, sale_date, amount, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amount DESC) AS rn FROM dbo.sales ) SELECT dept, region, sale_date, amount, rn FROM ranked WHERE rn 3 ORDER BY dept, rn;为什么用ROW_NUMBER()而不是RANK()因为业务要的是“固定取三条”即使有并列也该只取三条RANK()在并列时会多出超过三条反而不满足需求。这个选择直接决定结果行数。5.2 案例二月度销售额环比需求算出每个月销售额再和上个月比增长率。这里先GROUP BY得到月度聚合再在聚合结果上做LAGWITH monthly AS ( SELECT YEAR(sale_date) AS y, MONTH(sale_date) AS m, SUM(amount) AS month_sales FROM dbo.sales GROUP BY YEAR(sale_date), MONTH(sale_date) ) SELECT y, m, month_sales, LAG(month_sales, 1) OVER (ORDER BY y, m) AS prev_month_sales, CASE WHEN LAG(month_sales, 1) OVER (ORDER BY y, m) 0 THEN NULL ELSE (month_sales - LAG(month_sales, 1) OVER (ORDER BY y, m)) * 1.0 / LAG(month_sales, 1) OVER (ORDER BY y, m) END AS growth_rate FROM monthly;注意几个容易翻车的点LAG的ORDER BY y, m必须和月份的升序一致否则“上月”不是真正的上月。增长率要乘1.0转成小数否则SQL Server的整数除法会把0.5直接截成0。分组内第一个月没有“上月”会返回NULL用CASE包一层防止烂值进入业务报表。5.3 案例三按条件去重保留最新一条记录数据导入时经常会出现同一业务主键多条记录要保留最新时间那一条。用ROW_NUMBER()加CTE是最干净的做法WITH dedup AS ( SELECT dept, region, sale_date, amount, ROW_NUMBER() OVER (PARTITION BY dept, region ORDER BY sale_date DESC) AS seq FROM dbo.sales ) SELECT dept, region, sale_date, amount FROM dedup WHERE seq 1;比用子查询、EXISTS、GROUP BY MAX的组合要清晰得多。不过要提醒一句这个去重是“后来者居上”如果业务上需要保留最早一条把ORDER BY改成ASC即可。5.4 案例四累计销售额占比与帕累托分析需求看每个部门“前几名的销售额累计占比达到多少”。用聚合窗口加默认累计框就能得到一条帕累托曲线WITH running AS ( SELECT dept, region, amount, SUM(amount) OVER ( PARTITION BY dept ORDER BY amount DESC ) AS running_total, SUM(amount) OVER (PARTITION BY dept) AS dept_total FROM dbo.sales ) SELECT dept, region, amount, running_total, dept_total, CAST(running_total * 1.0 / dept_total AS DECIMAL(5,2)) AS cum_ratio FROM running;这里SUM(amount) OVER (PARTITION BY dept ORDER BY amount DESC)刻意用默认累计框因为我们要的就是“从第一名累加到当前行”。如果你想要的是部门总额反而需要手动把窗口框成整个分区。同一个函数不同的帧意义完全不同。6. 我在实际项目中踩过的窗口函数坑与性能建议6.1 排序没有索引支撑Sort算子拖垮整个查询窗口函数里的ORDER BY是真正的排序操作没有索引时SQL Server会生成Sort算子把中间结果放到tempdb。数据量大时tempdb迅速膨胀等待类型常常就是SORT_RUNS和WRITELOG的连锁反应。我通常按这个顺序做优化把PARTITION BY的列放在索引前面ORDER BY列放在后面建立组合索引。索引尽量做成覆盖索引避免窗口排序后还要回表取列。只取需要的列不要在窗口子句里拖一堆大字段。比如前面案例一频繁按dept分区、按amount排序就可以加索引CREATE INDEX ix_sales_dept_amount ON dbo.sales(dept, amount DESC) INCLUDE (region, sale_date);加了索引之后窗口排序可以直接走索引有序流执行计划里的Sort算子会消失或者大幅减少。6.2 窗口函数和GROUP BY的关系先聚合再开窗窗口函数在逻辑执行计划上位于GROUP BY之后所以可以先聚合再窗口但不能把未聚合列直接放进窗口子句。常见的报错场景-- 错误dept 不在GROUP BY中窗口内也不能直接引用未聚合明细列 SELECT dept, region, SUM(amount) OVER (PARTITION BY dept) AS dept_total FROM dbo.sales GROUP BY dept;这里需要明确意图如果你要做的是“每个部门的聚合窗口”必须先GROUP BY dept后再在聚合结果上开窗如果你要的是“每行明细都带部门总额”就不要GROUP BY直接对明细表开窗。6.3 CASE WHEN和NULL的传导问题窗口函数算出的NULL会向后传导。比如LAG返回NULL后直接做减法、拼接、比较都会变成NULL或报错。我习惯在所有偏移函数后面补一个COALESCE或CASE宁可返回0也不要返回NULL给下游尤其是下游还要用这个字段做除法的时候。6.4 SQL Server 2022带来的两个小惊喜如果项目已经升级到2022有两个新特性值得用起来LAG/LEAD支持IGNORE NULLS可以跳过空值取上一个非空行。窗口聚合支持更多框架控制写移动窗口时更顺手。-- SQL Server 2022 写法 LAG(amount) IGNORE NULLS OVER (PARTITION BY dept ORDER BY sale_date) AS prev_non_null以前处理“取上一个非空值”要做子查询辅助现在一个关键字搞定确实方便。6.5 窗口函数不是银弹什么时候该绕开窗口函数很好用但也不是所有场景都该无脑用如果只是求一个简单的分组总和GROUP BY远比窗口函数轻量。如果分区数极少、排序键极大窗口排序的代价可能高于自连接。如果查询结果要再和另一张大表join先把窗口计算结果压缩成临时表或CTE固定下来避免多次计算同一个窗口。另外我习惯在写完窗口SQL后顺手看一眼实际执行计划把带Sort的窗口操作和整个查询的成本做个对比。很多时候瓶颈不是窗口函数本身而是索引缺失让排序变贵了。最后分享一个个人习惯凡是窗口函数里的PARTITION BY和ORDER BY列我都会写进注释和业务口径对齐清楚。比如“这里是按销售日期升序累加不是部门总额”——一行注释能省下后面接手同事一上午的排查时间。窗口函数语法不难真正难的是搞清楚每个窗口到底框住了哪些行。把这一层想透了写出来的SQL基本不会错。