★ FEATURED ARTICLE 我第一次觉得窗口函数非学不可是被一个看起来平平无奇的报表需求逼的要把每个部门的员工按工资排名但员工的明细行一条也不能少。当时我满脑子都是GROUP BY一算排名就想着“先按部门汇总再自连接对比”结果写出半屏子查询跑出来的数据还不对。后来看到同事用一行RANK() OVER()就解决我才意识到自己一直绕了远路。这篇文章就是写给那些和我当时一样——会基础SQL、但没系统掌握窗口函数的读者不需要你有数学功底只要会SELECT、WHERE、GROUP BY就能跟上。窗口函数和普通聚合函数最大的区别简单说就一句话它能在不合并行的前提下让每一行都“看到”一个整体范围里的计算结果。排名、累计求和、占比、同比环比、组内取前N这些在业务报表里高频出现的需求用窗口函数往往几行SQL就能写出来而用老办法写不是性能差就是逻辑绕。下面我会从为什么需要它讲起把三个核心子句的运行机制拆开再给足排序类、聚合类、偏移类窗口函数的实战例子最后把我自己踩过的坑和排查习惯一并交代清楚。1. 为什么需要窗口函数先看GROUP BY解决不了的那类问题1.1 一个让我彻底入坑的需求保留明细的部门内排名假设有一张员工表employees结构很简单就四列员工ID、姓名、部门、工资。数据大概长这样emp_idemp_namedept_namesalaryE001小杨技术部28000E002小陈技术部26000E003小周技术部24000E004小吴市场部22000E005小郑市场部22000E006小冯市场部18000E007小卫运营部20000E008小蒋运营部16000需求是每个部门内部按工资从高到低排名但最终输出的结果每一行都必须是员工级别的明细不能把同部门的人合并成一条。用GROUP BY直接写第一反应是这样的SELECT dept_name, MAX(salary) AS max_salary FROM employees GROUP BY dept_name;能拿到每个部门的最高工资但拿不到第二名是谁更拿不到员工的姓名。如果需求是“列出每个部门工资最高的前三名”用GROUP BY就得写三条MAX或者用子查询嵌套SQL会越写越长。当时我还试过另一种经典写法用关联子查询模拟排名SELECT e1.*, (SELECT COUNT(*) 1 FROM employees e2 WHERE e2.dept_name e1.dept_name AND e2.salary e1.salary) AS rn FROM employees e1;这段SQL的逻辑是“数一数同部门里比我工资高的人有几个再加一就是我的排名”。数据量小的时候能跑但表一大子查询对每一行都要扫一遍全表性能很快凉掉。而且一旦出现工资并列“比我高的人数加一”的规则其实算不出并列名次逻辑上还得再补一层判断。这种写法不是不能实现是很别扭。窗口函数出来以后这套逻辑压缩成了一行SELECT emp_id, emp_name, dept_name, salary, RANK() OVER(PARTITION BY dept_name ORDER BY salary DESC) AS rn FROM employees;结果里部门和工资都保留着每一行的rn列就是它在自己部门里的排名。技术部小杨是1小陈是2小周是3市场部两个22000的人并列第2下一个直接排第4。这种“既要做聚合级计算又要保留行明细”的需求就是窗口函数最典型的适用场景。1.2 GROUP BY和窗口函数的本质区别谁来“合并行”很多人刚开始学窗口函数时总想把窗口函数理解为“高级版GROUP BY”其实两者的执行思路完全不一样。GROUP BY做的事情是先把符合条件的行按分组键合并成若干个组每组只输出一行。一旦做了GROUP BY组内其他行的详细信息就再也回不来了除非你再回去做自连接。这就是为什么排名需求用GROUP BY写起来这么痛苦——你为了让“每个部门一个排名输出”把员工数据压扁之后又得想办法把员工姓名这些明细捞回来。窗口函数则恰恰相反。它会在FROM、WHERE、GROUP BY这些阶段都跑完以后基于当前结果集再做一次“扫描式计算”但计算完不会合并任何行。每一行都还在原位置只是额外多出一列或多列计算结果。这一列对每一行来说可能是它所在分区的聚合值、可能是它排序后的名次、也可能是它上一行或下一行的某个字段。用一个生活化的类比GROUP BY像广播体操结束后按班级集合每个班级派一个人向总指挥报人数报完其他人原地解散。窗口函数像每个人戴着一块智能手环手环会实时告诉你自己班有多少人、自己在班里排名第几但每个人的位置一直没变。这也是窗口函数一个非常重要的判断标准如果你发现写窗口函数的结果里每一行旁边的结果都是一个“组级数值”那很正常因为窗口函数本来就是在行明细上做组级计算如果你发现行数莫名其妙少了那一定不是窗口函数的锅而是你在哪里用了GROUP BY或DISTINCT。1.3 窗口函数的通用语法骨架记住一张图就够了窗口函数在SQL里的基本结构长这样函数名(参数) OVER( [PARTITION BY 分组列] [ORDER BY 排序列 排序方向] [ROWS BETWEEN 窗口开始 AND 窗口结束] )从左到右拆开看函数名(参数)既可以是专门为窗口设计的排序函数比如RANK()、ROW_NUMBER()也可以是普通聚合函数跑到窗口里用比如SUM(列)、AVG(列)、COUNT(列)。PARTITION BY负责分块作用类似“分组”但和GROUP BY最大的区别是它不合并行。只写PARTITION BY不写ORDER BY窗口函数会在每个分区内部做全分区计算。ORDER BY负责在分区内部排序。排序不只是为了让结果顺眼更关键的是它会影响计算范围和部分函数的取值。窗口框ROWS BETWEEN...AND...进一步限定“在排序之后的哪些行参与计算”。不写它就是数据库自动给你定边界后面我会详细讲这个“默认边界”有多容易坑人。这三个子句不是每次都要写全。比如只求“每个部门的平均工资且不破坏行明细”可以只写PARTITION BY。要算“每月的累计销售额”可以只写ORDER BY。要同时分组、排序、限定计算行数就把三段都写出来。提示窗口函数只能出现在SELECT列表和ORDER BY里不能直接写在WHERE、GROUP BY、HAVING这些子句中。原因和SQL的逻辑执行顺序有关下一章专门讲。2. 窗口函数的执行机制PARTITION BY、ORDER BY与窗口框的缺省行为2.1 窗口函数在SQL逻辑执行顺序中的位置我见过不少朋友写窗口函数报错后一脸懵其实多数问题都出在没搞明白“窗口函数是啥时候被算出来的”。一条SQL的逻辑执行顺序大致是这样的FROM确定数据从哪张表来做必要的关联。WHERE按条件过滤行。GROUP BY按分组键聚合如果需要的话。HAVING过滤聚合之后的分组。SELECT计算要输出的列窗口函数正是在这个阶段执行。ORDER BY对最终结果排序。LIMIT/OFFSET截断行数。也就是说窗口函数能看到的数据已经是WHERE过滤过、GROUP BY分组过之后的数据。这带来一个很实用的推论如果你在WHERE里写“rn 1”数据库根本找不到rn这一列因为窗口函数还没执行。正确的做法是先算窗口函数再在外层子查询或CTE里对结果过滤。举个例子。如果只想查技术部的工资排名先写WHERE dept_name 技术部再写窗口函数那么窗口函数只在技术部这一小撮数据上做分区计算不会跑到市场部那边去。这是很多初学窗口函数的人容易忽略的点窗口函数的PARTITION BY不是覆盖WHERE的“全局分区”它只对你当前结果集负责。2.2 三个核心子句的缺省行为不写不等于没有窗口函数三个子句最让人困惑的地方就是它们的“不写”各有各的含义。我整理了一个我自己很常用的对照表建议收藏写法实际窗口范围典型场景OVER()整张结果集当成一个窗口算总数、总平均然后塞到明细行里OVER(PARTITION BY dept_name)每个部门一个窗口不做区内排序算各部门平均工资、部门人数OVER(ORDER BY month)整个结果集一个窗口但按month排序默认窗口从第一行到当前行累计求和、累计计数OVER(PARTITION BY dept_name ORDER BY salary DESC)每个部门一个窗口部门内按工资排序默认窗口从部门第一行到当前行部门内排名、部门内累计第一行最好理解OVER()一个窗口管全部每个员工旁边都会带上全表工资总额比如所有部门总工资是172000那每一行都有这个数。第四行是最常用的组合但也是误解重灾区。很多人以为OVER(PARTITION BY dept_name ORDER BY salary DESC)是“每个部门一个窗口然后算出整个部门的总和”其实不是。一旦出现ORDER BY默认窗口边界就变成了“从分区第一行到当前行”。想让每一行都看到整个部门的聚合值要么不写ORDER BY要么明确写窗口框ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。这个坑我第5章还会专门讲。2.3 用一个累计求和例子彻底吃透窗口框窗口框这个概念比较抽象我用一张按月收入表来说明。假设有一张sales_monthly表month_idrevenue112000021350003110000415000051600006170000需求是算每个月截至当前的累计收入。窗口函数的写法非常直接SELECT month_id, revenue, SUM(revenue) OVER(ORDER BY month_id) AS cum_revenue FROM sales_monthly;这里只写了ORDER BY month_id没有写PARTITION BY也没有写窗口框。因此窗口范围是整个结果集按month_id升序排列然后从第一行累加到当前行。结果是第1行cum_revenue等于120000第2行等于255000第3行等于365000一路累到最后一行变成所有收入的总和。如果想把窗口改成“最近三个月的滚动求和”就要显式声明窗口框SELECT month_id, revenue, SUM(revenue) OVER(ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3m FROM sales_monthly;这段SQL的意思是对每一行取当前行以及它前面的两行一共三行做SUM。所以第1行只有第1个月第2行有第1、2两个月从第3个月开始才真正稳定成三个月的滚动和。这种移动聚合在观察趋势、平滑波动时特别常用。如果想让每一行都看到整张表的收入总和最稳妥的写法是SELECT month_id, revenue, SUM(revenue) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_revenue FROM sales_monthly;UNBOUNDED PRECEDING表示无限往前UNBOUNDED FOLLOWING表示无限往后合起来就是整个窗口每一行拿到的值都一样等于全表收入总和。2.4 RANGE和ROWS的差别同样排序结果可能差一截窗口框除了ROWS BETWEEN写法还有一种写法叫RANGE BETWEEN。两者看起来只差一个单词底层逻辑完全不同。ROWS是按物理行数计算边界。说“2 PRECEDING”就是物理位置上前两行不管ORDER BY的值长什么样。RANGE是按排序值的大小计算边界。说“2 PRECEDING”是以当前行的排序值为基准往前找到排序值在当前值减去2这个范围内的所有行。入门阶段我不建议在RANGE上花太多时间但要记住一个关键影响如果ORDER BY的列存在重复值RANGE会把所有和当前行同名次的行一起划进窗口而ROWS不会。这会导致同样的函数、同样的排序写ROWS和写RANGE得到不同结果。比如按工资排序算累计人数。工资有5个28000有8个26000用RANGE的话第一个28000那行会把全部28000的人都算进去因为它们的排序值相等处于同一个“值范围”内用ROWS则严格按物理位置前进。实际业务里除非你真的需要按数值区间计算否则我建议优先用ROWS物理边界更好预测排查问题时也省心。3. 排序类窗口函数实战ROW_NUMBER、RANK、DENSE_RANK怎么选3.1 三个函数的字面区别一个表格说清排序类窗口函数是窗口函数的门面也是最容易让人混淆的一组。ROW_NUMBER、RANK、DENSE_RANK长得像三兄弟行为却各不相同。我用一张表把它们捋清楚函数名次是否重复是否跳号典型用途ROW_NUMBER()永不重复无跳号概念连续编号去重、取前N、分页RANK()同名次会重复会跳号标准竞赛排名DENSE_RANK()同名次会重复不跳号产品销量等级、紧凑排名三者的排序依据都是OVER里的ORDER BY不同的是给号规则。ROW_NUMBER根本不管并列按物理顺序强行编号结果永远连续且唯一。RANK遇到并列时并列的都占同一个名次但计算下一名时会跳过占用掉的名次数。DENSE_RANK也认可并列但下一名只是紧接着前一个名次加一不跳号。3.2 用真实成绩场景演示三种结果假设有一组同学的成绩数据我用通用称呼避免带入具体人名成绩依次是95、92、92、87。分别用三个函数取成绩排名结果差异一眼就能看出来原始分数ROW_NUMBER()RANK()DENSE_RANK()95111922229232287443看最后一行87分在RANK里排第4因为前面两个92占掉了第2和第3两个名次在DENSE_RANK里排第3因为它只关心“有几个不同的分数比自己高”不关心中间跳号。实际业务中怎么选如果只是需要唯一编号比如给结果集加一个连续流水号用ROW_NUMBER如果做竞赛榜、绩效考核等级要求并列者同为亚军且第三名不存在用RANK如果做产品价格带分析想让等级尽量紧凑——最贵一档为1、次贵一档为2不关心中间缺了多少个“理论名次”用DENSE_RANK。3.3 实用组合拳组内取前N与按用户去重排序类窗口函数最值的掌握的组合是配合子查询做“组内取前N”。在窗口函数出现之前取“每个部门工资前三名”要写很绕的关联子查询。现在标准做法是SELECT emp_id, emp_name, dept_name, salary, rn FROM ( SELECT emp_id, emp_name, dept_name, salary, ROW_NUMBER() OVER(PARTITION BY dept_name ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn 3;这里有一个必须理解的细节为什么不能让外层直接省略改成WHERE rn 3因为窗口函数在WHERE之后才执行所以窗口函数算出来的rn不能在WHERE里直接用。必须包一层子查询或CTE等窗口函数把rn算好再到外层按rn过滤。这一步是初学窗口函数时最容易被数据库报错“Unknown column”卡住的地方。另一个高频场景是去重。很多业务明细表里没有唯一键比如用户行为日志、版本记录同一主键可能有多行。取每个用户最新一条记录的写法是SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM user_logs ) t WHERE rn 1;用ROW_NUMBER而不是RANK的原因很简单去重要求每行只保留一条不能出现两个并列第一。ROW_NUMBER编号唯一无论排序值是否重复都会强制给出一条。3.4 NTILE分桶函数用窗口函数做“分层”排在榜单之外还有一个容易被忽略但很好用的排序类窗口函数NTILE(n)它会尽可能均匀地把有序数据切成n桶给每一行标一个桶号从1到n。它的典型用途是给用户分层、给商品分价格带。SELECT user_id, spend_amount, NTILE(4) OVER(ORDER BY spend_amount DESC) AS quartile FROM user_spending;这段SQL把用户按消费金额从高到低分成四层quartile等于1的是消费最高的四分之一用户4是最低的四分之一。相比自己写CASE WHENNTILE不需要你手算阈值数据切分自动完成。需要注意如果数据行数不能被桶数整除不同桶之间的行数可能相差1这是正常的不要把它当成分组错误去Debug。除了NTILE窗口函数里还有PERCENT_RANK、CUME_DIST这些用于统计分布的函数入门阶段了解即可真正用到时再查文档效率更高。现阶段把ROW_NUMBER、RANK、DENSE_RANK、NTILE这四兄弟用熟排序场景基本全覆盖。4. 聚合与偏移窗口函数累计求和、占比与同比环比4.1 把SUM、AVG、COUNT搬进窗口明细不丢的多层级汇总窗口函数不只是排序专用SUM、AVG、COUNT这些老朋友同样可以开窗。聚合函数配合窗口最大的价值是能在同一行里同时看到明细值和多个粒度的汇总值。继续用employees表。如果想知道每个员工的工资占部门总工资的比例同时保留员工明细可以这样写SELECT emp_id, emp_name, dept_name, salary, SUM(salary) OVER(PARTITION BY dept_name) AS dept_total_salary, ROUND(salary / SUM(salary) OVER(PARTITION BY dept_name), 4) AS dept_share FROM employees;这里PARTITION BY dept_name把数据按部门切块SUM(salary) OVER(PARTITION BY dept_name)在每一行都返回该部门的工资总和。于是小杨那个部门的总工资是78000小吴那个部门总工资是62000每一行都可以拿自己的salary除以部门总和直接得到占比不需要额外JOIN汇总表。更进阶一点还可以一次算出“部门总和”和“全公司总和”两个粒度SELECT emp_id, dept_name, salary, SUM(salary) OVER(PARTITION BY dept_name) AS dept_total, SUM(salary) OVER() AS company_total FROM employees;这种多层汇总在没有窗口函数时往往要靠两条GROUP BY查询再加一次全连接代码很重。窗口函数让多粒度统计变成“加一列”的事写报表时非常省事。4.2 FIRST_VALUE与LAST_VALUE小心LAST_VALUE的默认边界FIRST_VALUE和LAST_VALUE用来在窗口里取第一个值和最后一个值。比如想看每个部门工资最高的人是谁SELECT emp_id, emp_name, dept_name, salary, FIRST_VALUE(emp_name) OVER(PARTITION BY dept_name ORDER BY salary DESC) AS highest_paid_emp FROM employees;PARTITION BY把部门切开ORDER BY salary DESC保证部门内按工资从高到低排FIRST_VALUE取的就是当前分区第一行也就是工资最高的员工。这个逻辑顺理成章。但如果你照着同一个模式写LAST_VALUE想取工资最低的员工很可能踩坑-- 这段结果会让你意外 SELECT emp_id, emp_name, dept_name, salary, LAST_VALUE(emp_name) OVER(PARTITION BY dept_name ORDER BY salary DESC) AS lowest_paid_emp FROM employees;问题出在默认窗口边界当OVER里写了ORDER BY但没有显式写窗口框时窗口范围默认是“从分区第一行到当前行”RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。对技术部第一行来说窗口只有自己这一行所以LAST_VALUE返回它自己对第二行来说窗口是第一行到第二行LAST_VALUE返回第二行。每一行看到的“最后一个值”其实都是当前累计窗口的最后一行并不是整个部门的最后一行。想要在整个分区内取最后一行必须显式指定窗口框延伸到结尾SELECT emp_id, emp_name, dept_name, salary, LAST_VALUE(emp_name) OVER(PARTITION BY dept_name ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_paid_emp FROM employees;加了ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING窗口才真正覆盖整个分区LAST_VALUE才能取到分区末尾的记录。这个坑我身边至少有三位同学踩过第5章可以归进去。4.3 LAG与LEAD拿到上一行和下一行的数据LAG和LEAD是偏移类窗口函数它们不做聚合做的事非常朴实把同一分区里往前数第N行的某个字段搬过来或者往后数第N行的字段搬过来。LAG(列, N, 默认值)取当前行往前数N行的值。LEAD(列, N, 默认值)取当前行往后数N行的值。第三个参数可以不写取不到时返回NULL。做环比分析时LAG就是神器。计算每个月收入环比是增长了还是下跌了SELECT month_id, revenue, LAG(revenue, 1) OVER(ORDER BY month_id) AS prev_month_revenue, ROUND((revenue - LAG(revenue, 1) OVER(ORDER BY month_id)) / LAG(revenue, 1) OVER(ORDER BY month_id), 4) AS mom_growth FROM sales_monthly;这里LAG(revenue, 1)把上一个月的收入取到当前行第2个月的prev_month_revenue就是第1个月的120000第3个月的prev_month_revenue就是第2个月的135000。拿当期减去上一期再除以上一期环比增速就有了。第1个月因为前面没有数据LAG返回NULL对应的计算也是NULL不会报错。LEAD的用法对称把当前月换成下一个月。判断一条记录“后面还有没有连续记录”时常用比如分析用户连续登录天数可以先取出下一行的登录日期再看当前行和下一行的时间差是不是正好一天。4.4 一次SQL写完同比环比数据连续时的加减法窗口函数在分析场景里最能出彩的写法之一是同时算同比和环比。上个月对环比、去年同期对同比都可以在一个SELECT里完成。假设sales_monthly表里有跨12个月的收入数据结构依然是month_id和revenue两列。环比用LAG的偏移量1同比用LAG的偏移量12SELECT month_id, revenue, LAG(revenue, 1) OVER(ORDER BY month_id) AS prev_month, LAG(revenue, 12) OVER(ORDER BY month_id) AS same_month_last_year, ROUND((revenue - LAG(revenue, 1) OVER(ORDER BY month_id)) * 1.0 / LAG(revenue, 1) OVER(ORDER BY month_id), 2) AS mom_rate, ROUND((revenue - LAG(revenue, 12) OVER(ORDER BY month_id)) * 1.0 / LAG(revenue, 12) OVER(ORDER BY month_id), 2) AS yoy_rate FROM sales_monthly ORDER BY month_id;LAG的第二个参数就是偏移量。对任意一个月往前12行就是去年同月。这样一张SQL把明细、上月值、去年同月值、环比增速、同比增速五列全输出做经营分析月报时非常高效。需要注意的前提LAG按物理偏移取数只有当表格每个月的数据都齐全、连续存在时偏移12才准确对应去年同期。如果中间有断档比如某月缺数据那你需要先按月份字段自己补齐连续序列或在查询里对月份做JOIN匹配否则偏移会错位。这也是LAG相比日期JOIN的一个局限——它只管“行位置”不管“日期值”。5. 窗口函数避坑记录四个我见过的典型翻车现场5.1 坑之一把窗口函数结果放进WHERE子句这个坑我当年第一天学就踩了。想取每个部门工资最高的人顺手就写了SELECT emp_id, dept_name, salary, ROW_NUMBER() OVER(PARTITION BY dept_name ORDER BY salary DESC) AS rn FROM employees WHERE rn 1;数据库直接报错。原因前面讲过SQL逻辑执行顺序里WHERE发生在窗口函数执行之前。WHERE条件过滤时rn这一列根本还不存在。解决办法是包一层SELECT * FROM ( SELECT emp_id, dept_name, salary, ROW_NUMBER() OVER(PARTITION BY dept_name ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn 1;记住一个口诀窗口函数用来“算”过滤窗口结果用“外层查”。以后看到任何“对窗口函数结果再过滤”的需求第一反应就应该是子查询或CTE包一层。5.2 坑之二以为ORDER BY只是排序忽略了默认累计窗口有一次我帮同事看一张累计报表。他写的是SELECT month_id, revenue, SUM(revenue) OVER(ORDER BY month_id) AS total_revenue FROM sales_monthly;他告诉我这张表每个月只有一行他想让每一行都显示“全表总收入”结果发现total_revenue在逐行变大以为数据库出Bug了。其实数据库一点毛病没有。这就是第2章讲过的缺省行为只要写了ORDER BY窗口范围自动变成“第一行到当前行”SUM自然变成累计值。正确的全表总和写法是OVER()或者OVER(ORDER BY month_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)。这个坑的高发原因是“OVER(ORDER BY x)看起来只是排序了一下”实际语义偷偷带了累计窗口。以后写窗口函数先问自己一句我要的是全窗口值还是当前行之前的累计值想清楚再落笔。5.3 坑之三LAST_VALUE取不到全分区最后一个值前面讲FIRST_VALUE和LAST_VALUE时提过。这里我再强调一次故障现场的结构因为遇到的人真的多开发想看“每个部门工资最低的人”写了LAST_VALUE(emp_name) OVER(PARTITION BY dept_name ORDER BY salary DESC)结果每一行返回的都不是部门最低工资员工。这不是函数拼错了是窗口边界没写全。LAST_VALUE和FIRST_VALUE不一样。FIRST_VALUE的默认窗口边界即使只到当前行第一行也永远在窗口内所以结果直觉正确LAST_VALUE要取“末尾值”而默认窗口末尾是当前行而非分区末尾所以每个分区里只有最后一行看得到真正的末尾值。修复方式就是显式写ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。我后来养成了一个习惯凡是使用LAST_VALUE一律显式声明窗口框不依赖数据库默认值。这个习惯帮我省了不少复查时间。5.4 坑之四试图嵌套窗口函数窗口函数的结果能不能当另一个窗口函数的输入SQL标准不允许直接在同一个SELECT里写类似SUM(SUM(x) OVER(...)) OVER(...)这样的嵌套。我见过有朋友想写“部门平均工资在全公司所有部门平均工资里的排名”一口气把AVG套到RANK里结果报错。解决办法是分层计算用CTE或子查询把第一步结果算出来再拿去开第二次窗口WITH dept_stats AS ( SELECT dept_name, AVG(salary) AS avg_salary FROM employees GROUP BY dept_name ) SELECT dept_name, avg_salary, RANK() OVER(ORDER BY avg_salary DESC) AS avg_salary_rank FROM dept_stats;窗口函数嵌套的限制其实是SQL设计上的一种“清洗”一层只负责一层的事。把中间结果显式写成CTE不仅绕开了语法限制可读性也更好。遇到复杂的多层计算不要指望一行SQL把地球转完分步写反而不容易错。5.5 我日常写窗口函数的防守习惯窗口函数踩坑踩多了我形成了一套固定的工作习惯权当送给大家的额外彩蛋。第一优先用小数据集验证。写复杂窗口SQL之前先造个十几行的测试数据按预期逻辑手算两行结果再跑SQL对答案。窗口函数一旦语义理解错肉眼很难从几百行的结果里发现但用小数据一对比立刻现形。第二习惯性写完整窗口框。凡是涉及到LAST_VALUE、FIRST_VALUE这种对边界敏感的用法或者SUM、AVG这类聚合窗口我至少会想一下“当前窗口到底应该覆盖到哪”而不是直接交给数据库默认行为。多写几个单词换来的是结果确定性。第三注意执行计划的排序开销。窗口函数一旦写了ORDER BY数据库往往要额外做一次排序。如果一张大表同时开四五个带ORDER BY的窗口函数查询可能明显变慢。我会先把数据用WHERE和GROUP BY压缩到最小粒度再在结果集上做窗口计算或者在子查询里先过滤到只保留分析所需的列避免窗口函数扫到太多无关字段。MySQL 8、PostgreSQL、SQL Server这类数据库对窗口函数的支持大同小异但性能调优思路是互通的。最后补充一个小建议写窗口函数时分区键和排序列最好都是索引涉及到的字段或者至少在计算前先过滤掉不需要的数据。窗口函数要遍历整个分区来计算分区里的行数越多、重复执行次数越多压力就越大。我在一次几千万行的用户行为表上做排名需求时就是靠先把数据过滤到近七天的子集才把查询时间从分钟级压到秒级。窗口函数本身不复杂真正的功夫在数据裁剪上。说到底窗口函数入门最难的不是语法而是改变一种思维方式从“分组后只看一组”切换到“明细行保留、同时感知整体”。第一次觉得别扭很正常多拿真实业务需求练几次很快就能体会到那种“原来一行OVER就能搞定”的畅快感。希望这篇基于我个人实践总结的内容能帮你少走我当年走过的弯路。
阅读完成 · 觉得有帮助？
