做数据分析的人应该都有过这种经历想查一下每个部门薪资最高的前三名手里明明是员工表编号、姓名、部门、薪资一个GROUP BY甩过去出来的却是每个部门的汇总数明细行全没了。再换个思路先按部门分组找出最大值再用这个最大值去关联原表SQL越写越长遇到薪资并列还得小心翼翼处理。这个场景我遇过很多次后来把窗口函数彻底用熟这类需求基本十行内解决。窗口函数英文叫Window Function也叫分析函数是专门在结果集上保留明细、同时做分组计算的一类SQL函数。MySQL从8.0开始支持PostgreSQL、SQL Server、Oracle、Hive、Spark SQL也都有。它和普通聚合函数最大的区别在于聚合函数会把多行压成一行窗口函数却在每一行上都返回一个计算结果行的总数不变。就凭这一点它能处理掉一大批GROUP BY写不了、自连接写起来又臭又长的查询。这篇内容适合刚接触窗口函数、想系统搞明白它到底怎么用的同学也适合写过一些窗口函数但总在排名并列、移动累计这类细节上翻车的朋友。我会把核心语法、常见函数分类、真实业务场景、以及帧边界那些容易踩的坑一次性讲透。1. 窗口函数和GROUP BY的本质区别先弄懂它解决什么问题1.1 一个GROUP BY做不了的需求先看一个最典型的例子。员工表结构如下emp_idemp_namedepartmentsalary1张三技术部120002李四技术部100003王五技术部100004赵六技术部80005孙七市场部90006周八市场部7000“每个部门薪资最高的人是谁”很多人的第一反应是SELECT department, MAX(salary) FROM employee GROUP BY department;结果确实拿到了部门和最高薪资但拿不到这个人的emp_name。想拿到完整行信息就得做子查询或者自连接SQL变成这样SELECT e1.* FROM employee e1 JOIN ( SELECT department, MAX(salary) AS max_salary FROM employee GROUP BY department ) e2 ON e1.department e2.department AND e1.salary e2.max_salary;如果同一个部门有两个人都拿最高工资这个JOIN写法能查出两行逻辑上将将能跑通但SQL的可读性已经差了不少。如果需求改成“每个部门薪资前3名”这种JOIN写法的复杂度会直接爆炸。窗口函数的核心价值就在这里它允许你在不减少查询行数的情况下对每一行附加上一个“按分组计算的结果”。1.2 窗口函数在SQL执行顺序中的位置理解窗口函数之前必须先把SQL的执行顺序搞清楚。一个查询的真实执行顺序大致是FROM - WHERE - GROUP BY - HAVING - 窗口函数 - SELECT - DISTINCT - ORDER BY - LIMIT窗口函数的计算发生在WHERE、GROUP BY、HAVING之后又在普通的SELECT输出之前。这一点引出了两个很实际的结论第一WHERE子句里不能直接过滤窗口函数的结果。因为WHERE执行时窗口函数还没有计算出来。这就是为什么WHERE rn 3永远会报“Unknown column rn”这类错误必须套一层子查询。第二SELECT里定义的别名不能在同一条SQL的窗口函数里直接引用。你想写SUM(amount) OVER (ORDER BY order_date) AS running_total然后在同一层的另一个窗口函数里引用running_total行不通。窗口函数只能基于FROM阶段已有的原始列或者基于同一层表达式重复计算。1.3 语法拆解OVER子句到底在做什么窗口函数的标准语法长这样窗口函数名(参数) OVER ( [PARTITION BY 列1, 列2...] [ORDER BY 列1, 列2...] [ROWS/RANGE BETWEEN 帧起点 AND 帧终点] )三个部分分别是函数名决定你要做的计算类型。可以是SUM、AVG、COUNT这类聚合函数也可以是ROW_NUMBER、RANK、DENSE_RANK这类排名函数还可以是LAG、LEAD这类取值函数。PARTITION BY决定怎么分区。可以理解成“分组但不合并”分区内的每一行都会得到一个计算结果。ORDER BY决定窗口内的排列顺序。对排名函数这是必须的对聚合函数则会触发“从分区第一行到当前行”的默认累计行为。窗口帧进一步限定计算范围比如只看前3行。这部分的默认行为恰恰是初学者最容易踩坑的地方后面第4章专门讲。我第一次学窗口函数时把OVER子句想象成在每一行上方开了一扇观察窗当前计算只在这扇窗能看到的数据范围里进行算完结果直接贴到这一行旁边。这个比喻帮我省了很多理解成本也推荐给你。2. 三大类窗口函数逐个拆解聚合、排名、取值2.1 聚合窗口函数SUM、AVG、COUNT在每行上的应用聚合函数加上OVER之后行为和普通GROUP BY完全不同。看这个SQLSELECT emp_name, department, salary, SUM(salary) OVER (PARTITION BY department) AS dept_total FROM employee;结果会保留全部员工行每一行都多了一列dept_total同部门所有人拿到的部门总工资是一样的。行数完全没有减少。这个特性非常适合算占比。比如想看每个员工的工资占部门总工资的比例SELECT emp_name, department, salary, SUM(salary) OVER (PARTITION BY department) AS dept_total, ROUND(salary / SUM(salary) OVER (PARTITION BY department), 4) AS salary_ratio FROM employee;同样的窗口函数写了两遍。SQL没有限制一个查询里只能写一个窗口函数你可以为每个函数分别指定不同的PARTITION BY和ORDER BY。如果OVER里什么都不写比如SUM(salary) OVER ()那么窗口就是整个查询结果集。这个写法在做“总额对比”“全局占比”时非常顺手。2.2 排名窗口函数ROW_NUMBER、RANK、DENSE_RANK的差异排名函数是实际工作中使用频率最高的一类窗口函数三个核心函数的区别必须烂熟于心函数相同值怎么排示例薪资100、100、80下一个排名典型用途ROW_NUMBER相同值也给不同序号1、2、33去重、编号、分页RANK并列占同一名次1、1、33竞赛榜单允许跳号DENSE_RANK并列占同一名次1、1、22连续排名不跳号用一个SQL把三者放在一起对比最直观SELECT emp_name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rk FROM employee;如果张三12000、李四10000、王五10000、赵六8000结果会是emp_namedepartmentsalaryrow_numrkdense_rk张三技术部12000111李四技术部10000222王五技术部10000322赵六技术部8000443看出区别了吗ROW_NUMBER完全不理会并列10000的两个人都给了不同编号RANK给并列者相同名次但下一个名次跳过了2DENSE_RANK给并列者相同名次但下一个名次是连续的。选哪个取决于业务语义如果只是取前3条用ROW_NUMBER如果要求“薪资相等的人必须平级”用RANK或DENSE_RANK。这个选择在榜单场景中尤其重要。排名类里还有NTILE(n)它把每个分区尽量平均地切分成n桶可以用于给用户分群、抽样、打分区间分段。PERCENT_RANK和CUME_DIST则偏统计场景知道有这两个函数就行。2.3 取值窗口函数LAG、LEAD、FIRST_VALUE、LAST_VALUE取值类函数负责访问其他行。LAG(column, n, default)取当前行往前第n行的值LEAD(column, n, default)取往后第n行的值。最典型的应用是环比计算SELECT order_date, amount, LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount, amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS month_growth FROM monthly_sales;这里LAG的第二个参数1表示往前看一行第三个参数0表示前面没有数据时返回0。如果不写第三个参数第一行的prev_amount会是NULL报表里出现NULL往往还要额外处理不如直接给0省心。FIRST_VALUE和LAST_VALUE分别取窗口内第一行和最后一行的值。但LAST_VALUE有个著名的大坑默认窗口帧的终点是“当前行”所以它经常返回当前行本身而不是整个分区的最后一行。这个问题到第4章细说。取值类函数都依赖一个明确的ORDER BY。没有排序LAG/LEAD取到的是哪一行就完全不确定这种错误特别隐蔽写完后检查结果时要格外留意。3. 真实业务场景实战排行榜、连续登录、移动平均3.1 榜单场景每个部门薪资Top 3的完整写法回到开头的需求用窗口函数解决就是标准的“子查询套窗口函数”结构WITH ranked AS ( SELECT emp_id, emp_name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employee ) SELECT emp_id, emp_name, department, salary FROM ranked WHERE rn 3;为什么必须套这一层因为窗口函数在WHERE之后执行直接写WHERE rn 3不行数据库根本不认识rn。这个报错是初学者遇到最多的一个报错信息一般类似Unknown column rn in where clause如果希望并列薪资的人都进来把ROW_NUMBER换成RANK或DENSE_RANK再设置外层过滤条件即可。凡是“每个/每类 前几”的需求都可以照这个模板套先开窗编号再外层过滤。3.2 连续登录N天用编号差给连续区间分组连续类问题在用户行为分析里非常常见比如“连续签到3天以上的用户”。核心技巧是用窗口函数生成一个分组标识把连续的日期归到同一个组里。假设登录明细表login_log包含user_id和login_date同一用户一天可能登录多次先按用户和日期去重WITH t1 AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM login_log GROUP BY user_id, login_date ), t2 AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS grp FROM t1 ) SELECT user_id, COUNT(*) AS continuous_days FROM t2 GROUP BY user_id, grp HAVING COUNT(*) 3;这个技巧的原理需要稍微绕一下如果一个用户连续三天的登录日期是1日、2日、3日那么这三天对应的rn分别是1、2、3日期减去rn后1日 - 1 前一天 2日 - 2 前一天 3日 - 3 前一天结果是同一个日期所以它们会被GROUP BY分到同一组。如果中间断开比如1日、2日、4日那么4日减去rn3得到的是另一个日期分组自然就断开了。这段逻辑用窗口函数写出来只是几行换成自连接或者多层子查询会痛苦得多。而且这个思路不限于“天”只要是有序的连续性判断都可以用类似思路处理。3.3 累计销售额和7天移动平均窗口帧的初步应用聚合窗口函数配合ORDER BY可以直接生成累计值。比如按日期累计销售额SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS cumulative_amount FROM orders;这里没有写PARTITION BY整张表是一整个分区ORDER BY order_date使得窗口帧默认从第一行到当前行逐行累加。如果不加ORDER BYSUM会作用于整个分区每一行都返回总额累计效果就没了。移动平均则是窗口帧的典型应用。要算7天移动平均物理行数滑动窗口SELECT order_date, amount, AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d FROM orders;ROWS BETWEEN 6 PRECEDING AND CURRENT ROW的意思是从当前行往前数6行到当前行结束包含7行数据。如果你希望按自然天算近7天平均而不是物理7行需要用RANGE方式这个差异在第4章会展开。移动平均在金融、销售、流量指标中用来消除短期波动我自己的经验是先明确业务要的是“物理N行”还是“自然N天”前者用ROWS后者用RANGE别看都是窗口函数结果差异可能很大。4. 窗口帧的边界细节默认行为里藏的坑4.1 ROWS和RANGE的边界规则窗口帧由三部分组成起始边界、结束边界、以及用ROWS还是RANGE来界定。在OVER子句里可写ROWS BETWEEN 2 PRECEDING AND CURRENT ROW物理上往前数2行到当前行。RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW以当前行的排序键值为基准往回算6天范围内的所有行。UNBOUNDED PRECEDING分区第一行。UNBOUNDED FOLLOWING分区最后一行。数据库的默认行为是很多人翻车的根源两条规则必须记住规则1只写PARTITION BY不写ORDER BY时窗口是整个分区。此时SUM、AVG的结果分区内每行都一样。规则2写了ORDER BY时默认窗口帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。即从分区第一行到当前行同时把排序键值相等的行一起纳入。规则2中“把排序键值相等的行一起纳入”这一点就是下一节所有诡异结果的起源。4.2 LAST_VALUE为什么经常返回当前行先看这个SQLSELECT order_date, amount, LAST_VALUE(amount) OVER (ORDER BY order_date) AS last_amount FROM orders;很多人的预期是“每个订单行都能看到整个表最后一行的金额”但实际结果往往每一行返回的都是当前行自己的amount。原因就在默认帧ORDER BY order_date存在时默认帧是“从分区开头到当前行”。LAST_VALUE取“当前帧的最后一行的值”既然帧的终点就是当前行那么帧的最后一行自然也是当前行。等于说LAST_VALUE只看到了眼前没看到整个窗口。要拿到真正意义上“整个分区的最后一个值”必须手动把帧扩展到分区末尾SELECT order_date, amount, LAST_VALUE(amount) OVER ( ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_amount FROM orders;FIRST_VALUE通常没这个问题因为帧的起点始终是分区开头它总是能拿到正确的第一个值。这算是一个典型的“平时不用就不会觉得有问题一用就懵”的函数。4.3 并列排序值会把当前行窗口一起扩大的坑再举个例子。有三行薪资分别是100、100、80按薪资降序做累计求和SELECT salary, SUM(salary) OVER (ORDER BY salary DESC) AS running_total FROM employee;直觉上第一行累计应该是100第二行累计应该是200。实际结果却是salaryrunning_total10020010020080280第一行就变成了200因为默认是RANGE窗口ORDER BY salary DESC时排序键值相等的行全都在当前行之前被纳入两个100同时被算进去了。第二个100还是200因为帧仍然包含两行100。直到80那一行帧才扩展到三行累加到280。如果业务要的是“物理逐行累计”必须显式写ROWSSELECT salary, SUM(salary) OVER ( ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM employee;这次结果才是100、200、280。这个坑在计算累计占比、分配剩余额度时相当致命。我的习惯是凡是写逐行累计一律显式写ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW不依赖数据库的默认行为。5. 性能陷阱、数据库差异与SQL书写避坑清单5.1 窗口函数的书写限制窗口函数好用但限制也不少。常见的几类错误我按出现频率排个序第一想在WHERE里过滤窗口函数结果。已经说过直接报错。正确做法是包一层子查询或CTE。第二想在窗口函数里引用SELECT别名。有些数据库允许部分场景但标准行为是必须写完整的列或表达式。与其依赖数据库方言不如提前把中间结果用CTE算出来。第三多个窗口函数重复写相同的PARTITION BY和ORDER BY。这么做不会报错但SQL冗长执行计划也可能不理想。MySQL 8.0支持WINDOW子句可以先定义命名窗口再复用SELECT emp_name, salary, SUM(salary) OVER w AS dept_total, AVG(salary) OVER w AS dept_avg FROM employee WINDOW w AS (PARTITION BY department);第四聚合先分组再开窗时直接嵌套聚合函数。比如先按部门、日期算出每日销售额再算部门占比有人会忍不住写SUM(SUM(amount)) OVER(...)这在部分数据库里不兼容。更稳的写法是先用CTE聚合再在外层开窗WITH daily AS ( SELECT department, order_date, SUM(amount) AS day_amount FROM orders WHERE order_date 2024-01-01 GROUP BY department, order_date ) SELECT department, order_date, day_amount, ROUND(day_amount / SUM(day_amount) OVER (PARTITION BY department), 4) AS dept_ratio FROM daily ORDER BY department, order_date;这个写法符合SQL执行顺序也避开了嵌套聚合函数的兼容性问题几乎在所有支持窗口函数的数据库里都能跑。5.2 NULL值排序和各数据库的差异窗口函数里的ORDER BY遇到NULL不同数据库的默认行为不一样数据库NULL的默认排序位置MySQLNULL最小ASC排最前DESC排最后PostgreSQLNULL最大ASC排最后DESC排最前OracleNULL最大ASC排最后DESC排最前SQL ServerNULL最小ASC排最前DESC排最后如果一个排名字段可能为NULL建议在窗口函数内部统一处理最省心的办法是COALESCEROW_NUMBER() OVER (ORDER BY COALESCE(salary, 0) DESC)版本支持也要看清MySQL 5.7及更早版本没有窗口函数需要升级到8.0或者用用户变量临时模拟排名。MariaDB从10.2版本开始支持窗口函数。PostgreSQL 9.4起支持比较完整的窗口函数PG 15、16也在持续增强相关能力。SQL Server从2005年开始提供ROW_NUMBER但其他窗口函数在后续版本才逐步补全。Hive、Spark SQL、ClickHouse这些都支持窗口函数语法大体跟标准SQL一致细节差异建议以官方文档为准。5.3 性能上的一些实操经验窗口函数性能问题的核心是排序。PARTITION BY和ORDER BY字段如果经常组合出现建议建复合索引让排序尽量走索引避免文件排序。EXPLAIN看到Extra里有Using filesort就要留意数据量是否足够大、排序是否必要。不需要排序的窗口函数就别加ORDER BY。比如SUM(salary) OVER (PARTITION BY department)本来就该返回整个分组的总额强行加ORDER BY反而会触发不必要的帧计算。大数据量的分页场景窗口函数也比LIMIT OFFSET稳定。LIMIT加很大的偏移量时数据库还是要扫描前面那些行而ROW_NUMBER加外层过滤的方式让排序结果一次生成通常更可控。最后说点个人体会。窗口函数这玩意儿最初我也是边用边问尤其帧边界和并列排序那类问题翻了不止一次文档才彻底想通。后来我给自己立了条规矩写窗口函数之前先在注释里写清楚三个问题——分组的键是什么排序的键是什么窗口范围是从哪到哪。三个问题想明白再动手基本不会再犯错。如果你刚开始接触窗口函数建议先从SUM OVER和ROW_NUMBER两个最常用的入手把PARTITION BY和ORDER BY这两个子句的组合都试一遍然后观察加了ORDER BY之后SUM的结果从“分组总额”变成“逐行累计”的那个变化。等这一层手感熟了LAG、LEAD、RANK这些函数都只是换个函数名的事。