说出来有点不好意思我学SQL的顺序是先学了GROUP BY然后膨胀得不行觉得自己已经会“分组汇总”了。直到某天看到一道题查出每个部门工资最高的员工工资并列怎么办我憋了半天写出来的玩意儿要么把员工明细弄丢要么只能返回一行。后来才知道这种需求根本不该用GROUP BY硬怼而是要用窗口函数。窗口函数Window Function就是那种你早该学、但一直逃避的东西。它不算新各个主流数据库都有可网上教程要么讲得太浅要么直接甩一堆语法让人背。我学这个过程堪称“命苦”看一遍觉得懂了一写就报错一报错就回查概念来来回回折腾了好几周。这篇笔记就是把当时的血泪过程整理成我自己能看懂的样子顺便给同样在坑里的你一点参考。我是谁、这篇写给谁一个半路出家学数据的普通学习者。如果你正在学SQL已经会SELECT、GROUP BY、JOIN但一碰窗口函数就懵那这篇就是写给你的。1. 为什么窗口函数让我觉得SQL白学了1.1 一次面试被问倒事情是这样的。有阵子我刷题刷到“查找每个部门薪资最高的员工”当时满脑子都是GROUP BY。我写出来的第一版是SELECT dept_id, MAX(salary) FROM employee GROUP BY dept_id;这确实把每个部门的最高薪资拿出来了可问题来了——工资最高的那个员工叫啥、工号是多少、入职日期是哪天我拿不到。因为GROUP BY把部门内的多行压缩成了一行除了分组键和聚合结果其他字段全被丢掉了。我又试JOIN把自己 join 回去勉强能查出来。可如果需求变成“每个部门薪资排名前3的员工分别是谁”我直接傻了。LIMIT只能限制总行数没法在每个组内各取3条。那一瞬间我意识到我的SQL知识里缺了一块这块东西能让你在“不破坏明细行的前提下对每一行及其所在分组做计算”。这块东西就是窗口函数。1.2 窗口函数到底改了什么窗口函数和普通聚合函数长得有点像都是SUM、AVG、COUNT这些但行为完全不一样。普通聚合函数是“多进一出”GROUP BY把一组行并成一行窗口函数是“多进多出”它把每一行都保留同时附加一个计算结果。可以这样理解普通聚合是榨汁机一堆水果进去一杯汁出来窗口函数是给每一颗水果贴标签告诉它“你在这一堆里排第几”“你们这一堆总重量是多少”。所以窗口函数解决的是那种“既要明细又要汇总”的问题。比如算每个月的销售额占总销售额的比例比如给每笔订单打上客户累计消费额再比如“每门课前三名”这类分组取TopN需求。我真正被点醒的瞬间是接受了这句话窗口函数是在每一行上“开一个小窗户”站在当前行朝窗口内的其他行看拿窗口里的数据做计算。窗口是动的——每一行都有自己对应的窗口。这就是它名字里“窗口”两个字的来历。2. 先搭语法骨架OVER、PARTITION BY、ORDER BY各管各的2.1 一个订单表的例子学窗口函数第一件事不是背函数而是搞懂OVER()这个核心语法。几乎所有的窗口函数都长这样函数名(...) OVER ( [PARTITION BY 列名] [ORDER BY 列名] [ROWS/RANGE 窗口框架] )括号里三样东西可以单独出现也可以组合出现也可以一个都不写。我现在拿一个最简单的订单表来说事。假设有张表叫orders字段是order_id、city、order_date、amount。现在我想算“每笔订单金额占所在城市总金额的百分比”。如果没有窗口函数你得先按城市聚合一次再 join 回去。有了窗口函数直接这样SELECT order_id, city, amount, amount / SUM(amount) OVER (PARTITION BY city) AS city_ratio FROM orders;我盯着这段代码看了很久才明白PARTITION BY city就是在每个城市内开了个“窗口组”SUM(amount)是针对这个组算的但结果没有把行合并而是每行都带着一个“本组总金额”。所以它能拿amount直接除以这个组总和得到占比。写到这里顺便说一句PARTITION BY和GROUP BY的关键区别我自己的记忆方法是GROUP BY会“合并行”你的结果行数等于分组个数PARTITION BY只是“划地盘”它把行分成若干组但行数一条不少每一行仍然输出。这个区别是我踩了无数次坑才记住的。2.2 三个省略写法分别是什么意思OVER()括号里写不写东西结果差别巨大。只写空括号OVER()时表示把所有行当作一个窗口算出来的值对所有行都一样。比如SELECT order_id, amount, SUM(amount) OVER () AS total_amount FROM orders;这个场景适合算“每一行占总数的比例”。写PARTITION BY但不写ORDER BY时窗口是“整个分区”。上面的城市占比例子就是这样。写ORDER BY但不写PARTITION BY时窗口从第一行一直“滚”到当前行。这句话有点绕我举个实际例子SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM orders;这个running_total不是所有订单的总额而是“按日期排序截止到当前日期的累计销售额”。这是窗口函数最经典也最容易理解错的地方。我第一次写的时候以为OVER (ORDER BY order_date)只是“顺便按日期排个序”结果跑出来的数一个比一个大还以为是bug。后来我才懂只要OVER()里出现ORDER BY那就不是在给整个表排序而是在给窗口定义“顺序”。顺序一旦定义聚合窗口就不再是“整个分区”而是默认从起点滚动到当前行。这就是为什么我会看到那些“递增的”合计值——它本来就是在做累计。记住OVER里的ORDER BY不是“表最终的排序方式”它是窗口内部的滚动方向。这个误解不纠正后面框架子句你根本学不下去。3. 三个排名函数ROW_NUMBER、RANK、DENSE_RANK3.1 同分时的差别窗口函数家族里除了SUM、AVG这些聚合函数另一大主力就是排名函数。三个最常见的放一起看ROW_NUMBER()纯粹的给行编个号1、2、3、4……永远不重复。RANK()按排序字段给排名有并列时下一个名次会跳。比如两个人并列第一那第三个人显示第3名而不是第2名。DENSE_RANK()并列不跳号。两个人并列第一第三个人还是第2名。我用一个成绩表做例子。表叫exam_score只有三列student_id、subject、score。假设数学这门课有四个学生成绩如下A 95B 95C 90D 85。三个函数跑出来的结果SELECT student_id, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, RANK() OVER (ORDER BY score DESC) AS rk, DENSE_RANK() OVER (ORDER BY score DESC) AS drk FROM exam_score WHERE subject 数学;student_idscorernrkdrkA95111B95211C90332D85443看到差别了吗A和B并列第1RANK里C直接变第3名把第2名空出来了DENSE_RANK里C是第2名名次是连续的。ROW_NUMBER不在乎并列纯粹按物理扫描顺序编号。可以看一下这个例子同时有个小问题极限长度的表格在手机端可能滑动但实际使用中这个表格不算大没问题。实际业务里怎么选我做数据清洗时ROW_NUMBER()用得最多因为它在去重场景里特别好使。比如同一用户重复注册我想保留最早的一条就按用户分组给每条记录编号然后只取编号为1的行。排榜单的时候用RANK()比如“并列冠军”天然就该跳号需要给奖金分档、并且档位连续的时候用DENSE_RANK()。3.2 为什么取前N名非要套一层子查询我一开始写“每门课成绩前三名”特别自然就想这么写SELECT * FROM exam_score WHERE ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) 3;结果数据库直接甩给我一个报错。原因不是语法写错而是SQL的执行顺序问题。你可以简单理解成WHERE是在最开始过滤行的它执行的时候窗口函数还没被算出来。窗口函数在SELECT阶段才生成WHERE根本等不到它。所以想要“取组内前N名”标准做法是先在一个子查询里把排名算出来然后在外面过滤SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS rnk FROM exam_score ) AS t WHERE t.rnk 3;这个坑我踩了不止一次。后来我把SQL的执行顺序简化成FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY。窗口函数在SELECT这一层才生效所以WHERE、GROUP BY、HAVING里都别指望能用它——你只能在SELECT里用它或者在外面再套一层。很多教程会直接给“子查询取TopN”这个答案但不会解释为什么非要套子查询。我在这里把它解释清楚因为一旦明白了执行顺序你很多报错都能自己推断了。4. 移动视线LAG、LEAD与FIRST_VALUE的坑4.1 环比场景排名和聚合算是窗口函数的左膀那右臂就是“取值函数”。最常用的两个是LAG()和LEAD()。LAG(列名, n, 默认值)是向上看n行取那一行的值LEAD是向下看n行。如果那一行不存在就返回你给的默认值不写默认值的话通常返回NULL。我最常用的场景是环比。比如有个每日销售表daily_sales字段是sale_date和amount我要算“今天比昨天增长了多少”写法是SELECT sale_date, amount, LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_amount, amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS diff FROM daily_sales;LAG之所以好用是因为它不用自连接。自己 join 自己也能算但代码又长又慢窗口函数一行搞定。我刚开始学LAG的时候又犯了一个低级错误我以为表里天然有“上一行”的概念。其实SQL表是无序集合没有“上一行”这个说法。你必须在OVER (ORDER BY sale_date)里明确告诉数据库“按日期排完序之后往上一行去取”。不写ORDER BY数据库都不知道上一行是谁。顺便说个真实教训如果ORDER BY的字段有重复值LAG取到的“上一行”可能不是你想象的那一条。所以做这种前后行比较时排序字段最好能唯一确定顺序比如日期加订单号而不是光一个日期。4.2 别对LAST_VALUE抱错期待FIRST_VALUE()和LAST_VALUE()这俩函数看名字特别直白取窗口内第一行/最后一行的某个字段值。但是LAST_VALUE简直是我学窗口函数时最想骂人的一个函数。它的逻辑是在窗口框架范围内取最后一行而窗口框架在ORDER BY出现时默认范围是“从分区起点到当前行”。也就是说默认情况下LAST_VALUE取的是当前行自己而不是整个分区的最后一行我用个例子展示一下这个坑。假设有张表按日期排序我想取“截至当前日期每天的最低价格和最新价格”SELECT sale_date, price, FIRST_VALUE(price) OVER (ORDER BY sale_date) AS first_price, LAST_VALUE(price) OVER (ORDER BY sale_date) AS last_price FROM daily_price;这个first_price没问题它一直是整个序列的第一个价格因为窗口起点固定在分区开始。但last_price算出来每行都等于当前行的price根本不是我要的“最新价”……哦不对仔细想想“截至当前日期的最新价”确实就是当前价所以好像也没错但如果我要的是“整个时间序列里最后一天的价格”那就必须手动把窗口框架改成整个分区LAST_VALUE(price) OVER ( ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_price_overall这里我把窗口框架明确成“从起点到终点”它才是我想要的值。这个教训让我彻底记住了框架子句的存在和威力。也是从这时候开始我才正儿八经去学ROWS和RANGE。5. 框架子句ROWS和RANGE是学习过程中最想哭的部分5.1 默认框架和累计先说一个我反复用到的概念窗口框架frame。它是在分区内再切一段范围决定“当前窗口里到底包含哪些行”。有三条铁律似的规则写了ORDER BY默认窗口是从分区起点到当前行。不写ORDER BY默认窗口是整个分区。不写PARTITION BY、只写ORDER BY整个表是一整个分区但还是默认框架“起点到当前行”。这就是为什么第2章的累计求和能跑起来——SUM(amount) OVER (ORDER BY order_date)窗口是从最早日期滚动到当前日期每一行的结果就是“截至这一行的累计和”。说到框架写法两种最常见的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW意思是窗口从分区第一行开始到当前行结束。看到UNBOUNDED PRECEDING就是要从无限远的最前面开始CURRENT ROW表示截止到当前行。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW这个表示窗口只包含当前行和它前面两行一共三行。我算移动平均的时候特别常用比如“近3天平均销售额”SELECT sale_date, amount, AVG(amount) OVER ( ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_3day FROM daily_sales;注意头两天前面不足两行窗口就只有实际存在的行所以头两天的平均数是按1天、2天算的。这个“边界自动收缩”的特性很合理但也容易让初学的人误以为数据算错了。5.2 RANGE和ROWS的致命区别我这部分学得最痛苦因为RANGE和ROWS长得像行为差很多。ROWS是按物理行数划窗口。说包含前2行就是实打实的前2行。RANGE是按排序字段的值划窗口。它会把所有和当前行“排序值相同”的行都圈进来。举个具体例子。还是销售表假设order_date有重复三行都是同一天。这时候用ROWS BETWEEN 1 PRECEDING AND CURRENT ROW严格只包含物理上的前一行加当前行就算前一行日期和当前行一模一样也只算一行。但如果改成RANGE BETWEEN 1 PRECEDING AND CURRENT ROW那就要把排序字段值小于等于当前值、大于等于当前值减1的所有行全包含进来。如果排序字段是日期那就是“从昨天到今天”的所有行如果字段是数值且一天有多行那几行全都会被框进来。我记忆的方式是ROWS是数人头RANGE是按数值范围圈边界。做移动平均、滑动窗口汇总这类需求ROWS最直观涉及时间区间的比如“算过去30天”RANGE更贴合语义。但是这里有个大坑不同数据库对RANGE的支持程度不一样。有的数据库支持RANGE带数值偏移有的只支持RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW这种最简单写法。我自己就在某个数据库上写过RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW换到另一个数据库直接报语法错误。所以跨库写SQL的时候框架子句这块一定要查一下目标库的文档别默认都一样。6. 写完窗口函数后的自查清单我学窗口函数最大的问题是写的时候很爽写完跑出来一个错又得从头检查。后来我给自己列了一个自查清单每次写完都过一遍。现在分享给你6.1 我的六个检查项第一个检查项窗口函数有没有被放在不该放的位置前面提过WHERE、GROUP BY、HAVING里放窗口函数一定会报错。我现在的习惯是写之前就在脑子里过一遍执行顺序只要发现窗口函数出现在不该出现的位置马上改写成子查询。第二个检查项语句里有没有别名到处引用的毛病窗口函数在SELECT里生成了别名那外面一层能不能引用这个别名不同数据库行为不一样。稳妥的做法是不要指望同层SELECT里另一个窗口函数能用你刚定义的别名必要时再套一层子查询。第三个检查项ORDER BY到底是不是真的必要我经常为了“看起来整齐”在OVER()里乱加ORDER BY结果聚合行为突然从“整个分区总和”变成“累计值”。如果我想算的是一整组的和不是滚动累计就别写ORDER BY或者显式指定ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING把窗口固定成整个分区。这个错我犯过很多次每次都怪SQL“神经病”其实是我没说清楚要什么。第四个检查项三个排名函数选对了吗我自己记忆法是只想编个号去重用ROW_NUMBER比赛排名并列跳号用RANK要连续名次给奖金分档用DENSE_RANK。输出数字到底是几自己心算一次再跑基本不会错。第五个检查项LAG/LEAD的步长和默认值给了吗只写LAG(amount)默认往上1行取不到就是NULL。如果要找“前7天”的数据记得在第2个参数写7。如果没有数据时希望它显示0第三个参数写0。这个坑不算大但会让结果里全是NULL刚开始容易被吓到。第六个检查项大表上开窗会不会很慢窗口函数本质上是给每一行算一遍它窗口里的聚合数据量上来之后开销不小。尤其是PARTITION BY多个列、又带重排序的时候很容易慢。实际操作中我经常先看一下执行计划再决定是加索引还是把预聚合拆出去。6.2 报错信息里最值得盯住的提示说实话窗口函数相关的报错信息写得往往比较抽象。你可能会看到类似“window function is not allowed in WHERE”的报错这反而是最好解决的直接套子查询就行。主要是要注意语句里“窗口函数的位置”和“框架的边界”这两个点。如果看到的是结果不对、不报错那多半是ORDER BY乱加导致窗口变成了滚动累计或者RANK和DENSE_RANK搞混了。7. 如果你也在学我的练习路径写到最后我想把这几周摸索出来的练习顺序整理一下。这不是什么权威路线只是如果让我重新学一遍我会用这个顺序少走弯路。第一阶段先练SUM/COUNT加OVER()。场景是“每行占比”“累计求和”。这个阶段的目标是彻底搞清楚PARTITION BY和ORDER BY在OVER里的作用尤其是那个“不写ORDER BY是整个分区写了就变成滚动累计”的差别。练熟之后你就能理解为什么窗口函数叫“窗口”了。第二阶段练排名三兄弟。找一份成绩表跑ROW_NUMBER、RANK、DENSE_RANK把同一个分组结果并排放一起看。再练“取每组的TopN”记住必须套子查询。第三阶段练LAG/LEAD和FIRST_VALUE/LAST_VALUE。做环比、同比做“当前行对比前一行”的分析。记住LAST_VALUE默认只看到当前行需要整个分区时改框架。第四阶段练ROWS和RANGE框架。用“近3天移动平均”练手再尝试用RANGE做“过去7天累计”。这个阶段可以看看目标数据库文档确认RANGE支持哪些写法顺便把“不同数据库差别大”这个认知刻进脑子里。第五阶段拿真实业务题练。任何你能想到的分析场景都可以“每个客户最近一次订单”“每个门店当月累计销售额”“每个商品最近30天的平均销量”“每个员工与其部门平均工资的差异”。这些题在网上一搜一大把重要的是每个都动手写一遍不要只看别人写的答案。我个人的体会是窗口函数不是一个“看懂”就能会的东西它需要你靠几十条SQL“喂”出直觉。一开始报错报得让人想摔电脑但摔完再看一眼报错信息翻一下执行顺序基本就能定位问题。等你坚持写了几周回头看那些曾经让你头疼的“求组内占比”“求前N名”报表会发现思路前所未有的清晰——遇到这类需求第一反应不再是写一坨又臭又长的自连接子查询而是自然而然地想到“这里可以开个窗”。那种感觉值得你把这套“命苦”熬过去。