学SQL窗口函数这段日子我算是把命苦两个字体会得明明白白。翻开学习笔记满页都是OVER、PARTITION BY、ROWS BETWEEN这些词一开始看它们长得跟普通函数差不多真上手写业务查询的时候才发现完全不是那么回事。窗口函数说白了就是SQL里专门用来做组内计算的一类函数它能在不合并行的情况下给每一行附加一个基于分组或排序的计算结果。这篇文章不讲虚的就用我踩过坑、挠过头、最后终于想通的经历把窗口函数拆开揉碎讲清楚给正在学SQL、或者被各种统计需求折磨的朋友一份能直接抄的实操笔记。1. 窗口函数到底是什么从一个极其相似的兄弟说起1.1 为什么学窗口函数这么命苦GROUP BY留下的思维惯性咱们学SQL基本都是从聚合函数入手的SUM、COUNT、AVG这些东西配合GROUP BY用得多。GROUP BY的逻辑很直白先按某个字段把数据分成几堆然后对每一堆算一个结果出来。问题是算完之后原来的明细行全没了只剩下一行汇总结果。比如有个订单表按日期统计订单金额写出来的结果就只有日期和金额合计原始的每笔订单详情根本看不到。这种分组后折叠的模式用久了脑子里会形成一个定势凡是要统计就必须分组分组就必须牺牲明细。可实际业务经常是既要明细、又要统计。销售额表里既要看到每一笔销售记录又要看到每个业务员的总销售额、每个门店的累计销售额如果强行用GROUP BY明细行直接消失再想拿回来就得做JOIN回原表麻烦不说SQL还越写越长。窗口函数就是专门来解决这个矛盾的它让你不用折叠数据也能做分组统计但不改变原始表的行数和内容。命苦的根源就在于我们的思维被GROUP BY焊得太死了得花点时间把这条路重新走通。1.2 窗口函数的三个关键部件窗口、分区、排序窗口函数的语法结构可以拆成三块来看一个叫窗口函数本身一个叫OVER子句还有一个就是写在OVER里面的分区和排序条件。以最常用的SUM为例SELECT product_id, sale_date, sale_amount, SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total FROM sales;这里PARTITION BY product_id负责把同一个产品的所有销售记录归到一组ORDER BY sale_date负责在组内按日期排序SUM(...) OVER负责把这组数据按日期从前往后累加。关键区别在于GROUP BY会把多行压成一行而窗口函数是每行都保留计算窗口的结果会像贴标签一样贴到每一行上。打个比方GROUP BY就像老师把全班同学按小组统计完平均分只给你看每个小组一个分数窗口函数则是每个同学的成绩单上都备注一行字你所在小组的平均分是多少单个同学的成绩还在页面上一个都不少。这个例子我当时想通之后窗口函数算是迈出了第一步。2. 窗口函数三大家族聚合、排名、取值2.1 聚合类窗口函数SUM、AVG、COUNT、MAX、MIN的花式用法聚合类窗口函数听名字跟普通聚合函数一样但因为加上了OVER子句能力完全不是一回事。最常见的用法有三种。第一种是累加型。SUM配合ORDER BY实现累计值比如算每个月的累计销售额、每笔交易后的账户余额。它跟普通SUM最大的区别是普通SUM只给你一个总数窗口SUM在一行一行往下推进时会自动把当前行之前的所有值加进来产生一个不断变大的中间结果这个结果就叫移动累计。第二种是移动计算型。通过ROWS BETWEEN来限定计算范围比如算最近三天的平均销量就能写AVG(sale_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)这个写法我在做日常报表时经常用到。第三种是分组极值型。MAX、MIN配上PARTITION BY可以给每个分组贴一个最大最小值标签。比如员工表里想知道每个员工和自己的部门最高工资差多少只要把部门最高工资算出来再减一下就行全程不用JOIN子查询。使用聚合窗口函数时有个容易踩的坑如果只写OVER (PARTITION BY ...)而不写ORDER BY那窗口就是整个分组一旦写了ORDER BY窗口就默认从分组第一行到当前行顺序不同计算结果也完全不同。这个默认行为是SQL标准定的但很多新手不知道经常在累加和全组统计之间搞混。2.2 排名类窗口函数ROW_NUMBER、RANK、DENSE_RANK、NTILE的区别排名函数是窗口函数里最刚需的一类做TopN报表、榜单排行、去重标记都得用它们。我一开始最迷糊的就是ROW_NUMBER、RANK、DENSE_RANK三者到底有什么区别后来在纸上写了一遍结果才彻底记住。ROW_NUMBER就是按顺序编个号永远不重复哪怕有两个人成绩一样也会强行分个1、2、3。RANK遇到并列排名会跳号比如两个人并列第一那第三名的排名就是3中间2被跳过了。DENSE_RANK不跳号并列第一之后第二名还是2不会出现名次空缺。同样的业务场景用在取前三条上三个函数差别不大但如果业务上需要保留并列的名次就必须想清楚用哪个。比如评选优秀员工奖金按名次梯度发并列第一应该拿同样的钱那就用DENSE_RANK或者RANK如果只是系统内部给数据编个唯一序号那用ROW_NUMBER最合适。NTILE这个函数稍微冷门一些但做分桶分析时特别有用。NTILE(4) OVER (ORDER BY score DESC)会把数据尽量平均分成4桶然后告诉你每行在第几桶。比如用户按消费金额分成高、中、低三档就可以用NTILE(3)来快速打标。2.3 取值类窗口函数LAG、LEAD与前一行后一行的哲学取值类函数用来访问当前行之前或之后的数据行最常用的就是LAG和LEAD。LAG可以拿到组内上一行的某个字段值LEAD拿下一行的值。我最早用LAG是因为要做环比计算。周报里要算本周销售额跟上周比涨了多少以前的做法是先GROUP BY按周汇总然后用自连接把上周数据拼进来语法又长又容易出现匹配错误。用LAG的话只要一步SELECT week_num, sale_amount, LAG(sale_amount, 1) OVER (ORDER BY week_num) AS prev_week_amount, sale_amount - LAG(sale_amount, 1) OVER (ORDER BY week_num) AS week_diff FROM weekly_sales;LAG还可以指定取前几行第二个参数写2就是取两行前的值。FIRST_VALUE和LAST_VALUE则用来取窗口内第一个或最后一个值比如计算当前日期距离该用户首单日期多少天就可以用FIRST_VALUE(order_date) OVER (PARTITION BY user_id ORDER BY order_date)直接拿到首单日期。3. 窗口函数上手实操四个高频场景的落地写法3.1 累加求和从订单明细到账户余额的演变累加求和是窗口函数最日常的应用也是最容易理解的入门案例。假设有一张订单明细表每行是一个订单字段是order_date和order_amount现在想输出每天订单金额和截至当天的累计订单金额。SELECT order_date, order_amount, SUM(order_amount) OVER (ORDER BY order_date) AS cumulative_amount FROM orders ORDER BY order_date;这个查询执行时会按照order_date排序然后逐行累加。注意这里的ORDER BY有两个一个是OVER子句里的排序它决定累加顺序另一个是查询最外层的ORDER BY它只影响最终展示顺序。两者必须同时写否则展示顺序对不上累加顺序看着会很别扭。银行账户余额表也是一样的逻辑。每一笔交易都有入账或者出账金额余额就是历史所有交易金额的累加。之前在一个模拟项目里做账户流水查询用SUM(transaction_amount) OVER (PARTITION BY account_id ORDER BY transaction_time)就能算出每笔交易时刻的账户余额。这个方案比在应用层循环累加高效得多SQL一执行完余额列全部生成好了。3.2 分组TopN每个班级挑出前三名的标准写法分组TopN应该是窗口函数排名家族最经典的面试题了。需求是查每个部门工资最高的三位员工。常规的子查询写法是先按部门找工资门槛再来回JOIN一串SQL写得又臭又长窗口函数只需两步先用RANK或ROW_NUMBER生成组内排名再在外面套一层查询过滤排名小于等于3。SELECT department_id, employee_name, salary FROM ( SELECT department_id, employee_name, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn 3 ORDER BY department_id, salary DESC;这里要把排名查询放到子查询里在外面用WHERE过滤。为什么不能直接在外面写WHERE rn 3因为WHERE是在窗口函数计算之前执行的如果直接拼在同一个查询层里SQL根本不知道rn是什么。这个先子查询再过滤的模式几乎适用于所有TopN需求值得反复练习。我曾经在这个坑里卡了一个晚上写出来的SQL一直报错说找不到rn字段后来才反应过来是执行顺序的问题。3.3 环比与同比用LAG解决上一周期数据的对比难题环比的本质是跟上一个周期比同比是跟去年同期比。如果数据本身是带日期的订单流窗口函数压力不大。以月度环比为例SELECT month_id, total_amount, LAG(total_amount, 1) OVER (ORDER BY month_id) AS prev_month_amount, ROUND( (total_amount - LAG(total_amount, 1) OVER (ORDER BY month_id)) / LAG(total_amount, 1) OVER (ORDER BY month_id) * 100, 2 ) AS mom_change_percent FROM monthly_sales ORDER BY month_id;同比就是把LAG的第二个参数从1改成12前提是month_id是连续的月份编号比如202501、202502这种格式。如果月份不连续LAG直接取上一行会拿到错误的数据这时候要先做一个按月去重的子查询保证month_id连续且没有缺失。另外计算增长率时要注意除零问题上一个周期金额为0的话整个表达式会报错一般要用CASE WHEN或者NULLIF做保护。3.4 移动平均平滑波动曲线的实战技巧移动平均常用于消除短期波动、观察趋势。比如看某个门店近7天的销售额趋势日波动太大看三天或者七天的平均值会更平滑。写法是SELECT sale_date, sale_amount, AVG(sale_amount) OVER ( ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_3day FROM sales WHERE store_id ST001;ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示窗口从当前日期往前推两行一直包含当前行。如果数据有缺失日期用ROWS按行数滑动会出现前两行不一定是前两天的问题这时候要想别的办法补齐日期或者改用RANGE按值滑动。移动平均在数据量大的时候还要注意首尾几行的窗口是不完整的第一行前面没有数据平均值可能偏低需要结合业务决定是否要过滤掉这些边缘数据。4. 窗口范围的进阶理解与性能隐患4.1 ROWS和RANGE按行数框窗口还是按值框窗口窗口函数里ROWS BETWEEN和RANGE BETWEEN都能限定计算范围区别在于一个是按行数一个是按排序字段的值。ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING的意思是当前行的前一行、后一行和当前行自己一共三行参与计算。RANGE BETWEEN则跟排序字段的实际值有关比如INTERVAL 7 DAY PRECEDING表示从当前日期往前7天内的所有行都算进窗口不考虑具体行数。RANGE在处理日期类数据时更符合直觉比如算最近7天平均销量用RANGE比用ROWS更准确因为ROWS可能因为数据缺失而少算行数RANGE是真正按时间跨度选数据。两者的区别还体现在处理并列值上。RANGE模式下列名相同窗口会包含所有并列行ROWS模式则不会。默认窗口范围在没有写ROWS或RANGE的情况下是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW含义是从分组第一行到当前行。这个默认值跟很多人以为的整个分组不一样理解不到位累加结果就容易出错。4.2 窗口函数的计算顺序它发生在WHERE之后、ORDER BY之前不少初学者把窗口函数当成跟普通函数一样的执行单元忽略了它特殊的计算顺序。一条SELECT语句的实际执行顺序大致是FROM、WHERE、GROUP BY、HAVING、窗口函数、SELECT、DISTINCT、ORDER BY、LIMIT。窗口函数在WHERE之后才执行所以不能在WHERE里直接引用窗口函数生成的别名。这解释了为什么写WHERE rn 3会报错因为执行WHERE那一步时rn还没有被算出来。另一方面窗口函数是在GROUP BY和HAVING之后执行的所以它能处理已经聚合好的结果。这意味着可以先在GROUP BY里按天汇总再用窗口函数在汇总结果上做排序和累加。很多复杂的统计报表都是这种先GROUP BY聚合再OVER计算的组合套路。4.3 窗口函数性能优化为什么慢怎么提速窗口函数虽然写起来方便数据量大时性能问题也真实存在。最耗资源的地方在于OVER子句里的ORDER BY每次窗口计算都可能涉及一次全量排序。优化方向可以从几个方面入手。第一减少排序字段的宽度。排序字段尽量用整数类型避免用超长字符串如果必须排序可以先在子查询里把数据量压缩下去再开窗口。第二合理使用PARTITION BY分区字段会让引擎把数据分成多个独立的小组分别排序理论上比一个大全表的排序更可控。第三避免在窗口函数里加上不必要的DISTINCT去重本身就会额外引入排序。第四在真实项目中为排序字段建索引有时候能减少排序成本但要根据数据库的优化器行为具体分析。我在一个模拟项目里处理过一张几百万行的流水表第一次执行用窗口函数做分组累加跑了快两分钟。排查之后发现问题出在一个文本类型的关联字段排序上。后来把文本字段换成ID作为PARTITION BY的辅助列整体查询时间降到了十几秒。窗口函数不是银弹用的时候要时刻提醒自己功能方便了代价可能藏在性能里。5. 新手易踩的坑常见错误与排查思路5.1 窗口函数里能不能用WHERE过滤窗口函数的结果不能直接在WHERE里使用这个前面已经提过。正确的思路是先生成窗口结果再在外面包一层查询过滤。有些朋友会尝试在OVER子句里写WHERE比如SUM(sale_amount) OVER (PARTITION BY product_id WHERE sale_amount 100)这是不行的OVER子句里没有WHERE这个语法槽位。想实现只统计满足条件的行要么先在子查询里过滤要么用CASE WHEN配合窗口函数比如SUM(CASE WHEN sale_amount 100 THEN sale_amount ELSE 0 END) OVER (PARTITION BY product_id)这种写法可以在不丢弃明细行的前提下只把符合条件的金额计入汇总。5.2 PARTITION BY和GROUP BY到底能不能混用窗口函数里的PARTITION BY和普通聚合的GROUP BY是两个不同层面的东西可以出现在同一条SQL里但要清楚各自的作用。GROUP BY负责把明细行压成一行然后窗口函数再对这个已经压缩过的结果集做分组计算。比如先GROUP BY department_id算出部门总人数再在结果上用SUM(...) OVER (ORDER BY ...)做累计排序两种用法完全不冲突。容易出错的是在GROUP BY之后的SELECT里直接引用非分组字段的原始值这是SQL语法不允许的跟窗口函数没有直接关系。5.3 NULL值在窗口函数里怎么排序排序时NULL值的位置跟不同数据库的默认规则有关。有的数据库NULL排在最前面有的排在最后面。如果业务上要求NULL排最后通常可以在ORDER BY后面加NULLS LAST关键字比如ORDER BY salary DESC NULLS LAST。累加类函数遇到NULL值也要注意SUM在累加时会跳过NULL但AVG会把它当作不存在的行处理如果业务上需要把NULL当成0得先用COALESCE把NULL转成0再参与计算。这个细节我在写销售报表时踩过某天某个门店没营业销售金额是NULL平均数的分母直接少了一天数据对不上排查了半天才明白是这个原因。5.4 ROW_NUMBER与DISTINCT的重复数据问题用ROW_NUMBER给数据标号之后再DISTINCT经常出现标号列导致所有行都不一样的问题。比如想给每个用户取一条最新记录先按用户分区按时间排序编号再过滤rn1这一步本身没问题。但如果写成了SELECT DISTINCT user_id, ROW_NUMBER() ...那每一行编号都不同DISTINCT就形同虚设了。正确的做法是把ROW_NUMBER放进子查询过滤后再取需要的字段不要再把编号列丢到外层的DISTINCT里。另一个相关坑是用ROW_NUMBER去重时如果两条数据真的完全重复编号会随机分配没关系反正只保留一条但如果你还想保留重复数据的存在性标志应该用COUNT窗口函数而不是ROW_NUMBER。5.5 排名字段并列名次时到底用RANK还是DENSE_RANK选错排名函数会导致名次断层或者错乱。考试业务要求前两名并列那第三名应该记作第3名还是第2名之后的下一个名次RANK会跳号DENSE_RANK不会。做班次排名、竞赛榜单时一般用RANK比较符合人们对外展示的习惯因为人们普遍认可并列跳过名次做内部数据存储、生成唯一排名维度时用DENSE_RANK会更方便运算。我自己的经验是涉及前N名筛选无论RANK还是DENSE_RANK在结果集超过N行的时候都要慎重要结合业务定义说清楚第1名有3个人并列要的是前3名还是前3个名额这个业务问题没解决函数选啥都是白搭。6. 学习窗口函数的路径与笔记心得6.1 从看得懂到用得对的三阶段跳跃学习窗口函数大概会经历三个阶段。第一个阶段是看得懂能照着网上的例子改参数知道每条语法在干嘛。第二个阶段是拆得开碰到一个复杂的窗口查询能一眼看出哪个字段是分区、哪个是排序、哪个是范围限定能把一段SQL拆成几个独立步骤来理解。第三个阶段是建得准拿到一个新的统计需求能迅速判断该用聚合窗口、排名窗口还是取值窗口并且能在脑子里把最终结果想象出来。大多数人卡在第二阶段到第三阶段之间。我给自己的练习方法是每个案例都先手写一遍预期结果再运行SQL比对。比如随便造10行订单数据先用班级排名的方式写出预期的分组Top2再运行看对不对。手写结果这一步倒逼我真正理解了窗口函数的计算过程而不是停留在复制成功的幻觉里。6.2 一套值得反复套用的思考框架拿到一个需要窗口函数的需求我一般按四步走。第一步先确定要不要保留明细行需要保留那窗口函数大概率是正解不需要保留优先考虑普通GROUP BY。第二步确定窗口的边界是整组、累计到当前行、还是固定行数的滑动窗口对应的就是OVER子句里写不写ORDER BY、写不写ROWS BETWEEN。第三步确定用哪一类函数想算累加和分组汇总用聚合类想排名用排名类想跨行取值用LAG、LEAD。第四步确认结果是否需要二次过滤需要则包一层子查询。这套框架一开始用起来很慢但用了七八次之后就变成了肌肉记忆。有一次同事拿一个复杂的月度留存表来问我按这套思路先定位到每月的每个用户要跟首月比直接想到FIRST_VALUE取首月日期再判断月度间隔整套逻辑几分钟理清楚不像以前看到复杂需求就乱试。6.3 命苦踩坑后的防坑清单学习过程中我整理了一份自己的防坑清单。第一写窗口函数前先确认数据库方言支持的写法不同数据库对窗口函数支持程度有差异MySQL用窗口函数时尤其要注意版本。第二ORDER BY和PARTITION BY不要写反写反了分组维度跟排序维度会完全错乱。第三窗口函数里用了ORDER BY之后默认窗口范围就变了不是全组统计了这点最容易疏忽。第四子查询里的窗口列别名不能在外层直接用必须再包一层才能过滤。第五大数据量跑窗口查询前先在中小数据集上验证结果不然一个错误的窗口范围定义可能在几百万行数据上白跑十分钟。这份清单是我对着执行计划和报错信息一点一点攒出来的。学习SQL这件事最痛苦的不是语法记不牢而是明明写了条SQL看着对跑出来的结果却永远是错的。窗口函数放大了这种痛苦因为它牵扯到执行顺序、窗口范围、分区排序这些概念任何一个细节没想清楚结果就全是歪的。6.4 一个建议从业务场景反向理解窗口函数给正在学窗口函数的朋友一个建议先别扎进函数文档里找一个真实的业务场景来做反向梳理。我自己的转折点是在处理一个电商订单数据的时候业务要一个每个用户按购买时间排序的第1单和第2单金额差。这个需求一开始听起来复杂实际用ROW_NUMBER加LAG一组合就写出来了ROW_NUMBER按用户和时间编号取出1和2两条LAG取上一单金额做差值。做完这个案例之后我对窗口函数的理解一下子立体起来。反过来如果只看函数定义记了一堆ROWS RANGE的规则如果没有业务场景兜着过几天就忘了。SQL是工具语言窗口函数是工具里的扳手扳手好不好用得拿到实际的螺丝上去试。我建议试着拿自己手头的任何一张表造一个按分类累加或取组内Top3的需求做完再做跟上一行比做完再试试按7天滑动平均一套流程下来窗口函数的大半用法就都跑到肌肉记忆里了。学习窗口函数确实是段命苦的历程但等哪一天你能随手写出分组TopN、累加余额、跨行对比的查询时回头看那些熬夜调SQL的日子会觉得一切都值了。这篇笔记是记录我学习SQL的命苦笔记的第一篇后面还会继续整理更多实战中摸爬滚打出来的内容希望这些踩坑经验能帮你少走一点弯路。