今天是2026年1月26日周一。这篇学习日记260126 就是今天的日期记录的是我一整天从零开始系统啃SQL 窗口函数的过程。起因并不复杂上周帮业务侧搭报表发现一个既要看明细、又要看分组汇总、还要看排名的需求GROUP BY 怎么拼都别扭最后用三四个子查询加两轮 JOIN 才勉强跑通性能还惨不忍睹。我当时就决定必须把窗口函数彻底搞明白。窗口函数能解决什么问题一句话讲清在不合并明细行的前提下给每一行补上它所在分组内计算出来的统计值。比如同一行里既能看到这笔订单金额又能看到这个部门的总销售额还能看到它在部门里的排名和累计进度。适合谁看这篇文章正在学 SQL 的初学者、天天写报表的数据分析师以及想从会 GROUP BY跨到能处理复杂分析需求的后端开发同学。1. 为什么我会专门安排一整天学窗口函数1.1 一个真实需求把我逼到这一步业务那边提的需求其实很常见有一张订单表结构大概是订单号、部门、员工、金额、日期。老板想看的报表长这样每个员工一条明细同时旁边要有这个员工所在部门的总销售额部门内按销售额从高到低的排名部门内从月初到当天的累计销售额。这个需求放在只会 GROUP BY的人手里真的很痛苦。GROUP BY 会把同一个部门的员工合并成一行但老板又要看每个员工两件事本身就是矛盾的。我早期为了在明细行旁边带出部门总量写过这种查询SELECT o1.employee, o1.amount, o1.dept_id, (SELECT SUM(o2.amount) FROM sales_order o2 WHERE o2.dept_id o1.dept_id) AS dept_total, (SELECT COUNT(*) 1 FROM sales_order o3 WHERE o3.dept_id o1.dept_id AND o3.amount o1.amount) AS dept_rank FROM sales_order o1;这种写法的问题太多了。第一SQL 里密密麻麻塞满相关子查询读起来像在看乱码第二表一大了性能直线下降每个子查询都要重新扫一遍表或者依赖很难命中的索引第三一旦再加累计到今天为止这种纵向条件子查询套子查询就会把人彻底绕晕。跑完那次报表之后我就把系统学窗口函数这件事列进了学习清单今天正好轮到它。1.2 窗口函数和 GROUP BY 的本质区别先把这个最核心的认知掰清楚。很多人学窗口函数时老糊涂是因为还带着分组就是 GROUP BY的惯性思维。两者的差别其实一句话就能概括。GROUP BY 是把多行压成一行。它就像一个收纳盒把属于同一个组的所有行倒进去最后只还给你一个总数。比如 8 条订单按部门分组结果就只剩 3 行A、B、C 各一行你再也看不见里面任何一个员工。窗口函数是在原表旁边加一列。它不改变行的数量每一条明细还在只是在旁边多出一个根据某个分组计算出来的值。这个窗口你可以理解成透视框扫描每一行时数据库会以这一行为基准画出一个限定范围的行集合在这个集合内做 SUM、AVG、排名计算完再把结果填回这一行。我做了一张对比表放在日记里和菜鸟时期的自己做了个对照对比项GROUP BY窗口函数输出行数每组合并为一行保持原明细行数能否看到明细不能明细被折叠能只是附加计算列适用范围只需要分组汇总结果明细与汇总同时展示典型场景部门销售总额部门销售总额员工排名累计这个认知一旦建立后面所有语法都是同一个语法函数() OVER (PARTITION BY ... ORDER BY ...)PARTITION BY 相当于按什么分组ORDER BY 决定窗口内怎么排序或累计整句的意思就是对每一行在指定分组内做一次计算。1.3 今天的实验环境和准备工作学习窗口函数不用搞分布式集群本地一个数据库足够。我准备了两个环境一个是本地正式的 MySQL 8.0用来验证语法和踩坑另一个是临时拉起来的 DuckDB用来做快速实验。DuckDB 对窗口函数支持很全而且单机跑几百 GB CSV 都不费劲特别适合核对结果后面我用到时再细说。为了让例子能被复现我建了一张极简订单表。今天一整天的所有实验都围绕它CREATE TABLE sales_order ( order_id INT PRIMARY KEY, dept_id CHAR(1) NOT NULL, employee VARCHAR(20) NOT NULL, amount DECIMAL(10,2) NOT NULL, sale_date DATE NOT NULL ); INSERT INTO sales_order VALUES (1, A, 小王, 1200.00, 2026-01-05), (2, A, 小李, 2200.00, 2026-01-08), (3, A, 小王, 1800.00, 2026-01-12), (4, B, 小张, 1500.00, 2026-01-09), (5, B, 小陈, 2600.00, 2026-01-11), (6, B, 小张, 900.00, 2026-01-16), (7, C, 小刘, 1700.00, 2026-01-15), (8, C, 小刘, 2300.00, 2026-01-20);这 8 行数据量虽小但覆盖了我要玩的三类典型情况多行同组、组内多排序字段、以及不同员工交错出现。后面讲每个函数我都会用真实输出说话而不是只贴语法。提示如果你跟着练别用太大的表。学习窗口函数初期手工能算出来的小表才是最好的实验台。数据一多你根本分不清结果是数据库算错了还是你自己理解错了。2. 核心细节拆解四类窗口函数的学习笔记2.1 排序编号类ROW_NUMBER、RANK 和 DENSE_RANK 差在哪这一组是平时用得最多的。需求里那句按销售额从高到低排个名就该交给它们。但它们三个的细微差别我敢说很多人没真正搞懂。直接上我今天的实验SELECT employee, amount, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS row_no, RANK() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS rank_no, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS dense_no FROM sales_order;假设同部门的两个员工金额恰好相同比如 A 部门小王和小李都是 2200 元。ROW_NUMBER 会毫不客气地给它们分别编为 1 和 2完全无视并列RANK 会让两人并列第 1但下一个人直接跳到第 3DENSE_RANK 同样让两人并列第 1下一个人接着排第 2。一句话记忆法ROW_NUMBER 是唯一编号有先后没人情味RANK 是奥运奖牌逻辑金牌 1、金牌 1、铜牌 3DENSE_RANK 是名次不空档金牌 1、金牌 1、银牌 2。实际选哪个取决于需求。需要给每行一个唯一标识用来分页、去重、取前 N 条明细时选 ROW_NUMBER需要展示排名给用户看并列名次要空档比如第二名缺失选 RANK只关心几档绩效等级不需要空档时选 DENSE_RANK。我当年直接取前 3 名却用 RANK结果因为并列拿到 4 行这个问题到今天不少初级数据分析师还在踩。2.2 偏移访问类LAG 和 LEAD 解决跟前一行比排名完了第二个高频需求是环比和差分这个月跟上个月比涨了多少当前订单跟上一笔订单隔了几天这类问题都要访问另一行的值LAG 和 LEAD 就是干这个的。LAG 取窗口里当前行前面的行LEAD 取后面的行。语法长这样SELECT dept_id, employee, sale_date, amount, LAG(amount, 1) OVER (PARTITION BY dept_id ORDER BY sale_date) AS prev_amount FROM sales_order;这行的逻辑可以这样读每个部门内部按日期排好序之后把上一行订单的金额放到当前行旁边。我在 A 部门的数据上跑出来的结果很直观小王在 2026-01-05 的 prev_amount 是 NULL因为他是部门第一单小李 01-08 那单的 prev_amount 就是 1200小王 01-12 那单的 prev_amount 则是 2200。做环比增长率时最标准的写法是先把差值算出来再用 NULLIF 防除零SELECT dept_id, employee, sale_date, amount, LAG(amount, 1) OVER w AS prev_amount, ROUND( (amount - LAG(amount, 1) OVER w) * 100.0 / NULLIF(LAG(amount, 1) OVER w, 0), 2 ) AS growth_pct FROM sales_order WINDOW w AS (PARTITION BY dept_id ORDER BY sale_date);MySQL 8.0 里可以用 WINDOW 子句把重复的窗口定义抽出来减少一大段同样文字的复制粘贴。注意 LAG 的第二个参数是偏移步长默认是 1如果取倒数第二行就写 2。窗口内没有前一行时返回 NULL所以报表里要记得用 COALESCE 把 NULL 显示成无或者 0别让业务看到一堆空值。2.3 聚合窗口与框架概念SUM OVER 是今天的重头戏如果把今天的知识按难度排序排名类算入门LAG 算进阶那 SUM OVER 与框架frame就是真正的分水岭。我要拿它算部门内累计销售额SELECT dept_id, sale_date, amount, SUM(amount) OVER ( PARTITION BY dept_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS dept_cum_amount FROM sales_order ORDER BY dept_id, sale_date;这里最关键的是最后的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。它定义了一个活动窗口从分组的第一行开始一直到当前行结束。于是每一行的 dept_cum_amount都是截至这一行的累计和。A 部门的结果是 1200、3400、5200每一步都能对上手工计算。为什么必须写这段因为窗口框架是有默认行为的。如果 SUM OVER 只写了 PARTITION BY 没写 ORDER BY那么整个分区就是一个大窗口每行显示的 SUM 都是部门全量的总和。如果你想做移动平均或者最近 3 笔订单的平均值就必须自定义边界SELECT dept_id, sale_date, amount, AVG(amount) OVER ( PARTITION BY dept_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3 FROM sales_order;ROWS BETWEEN 2 PRECEDING AND CURRENT ROW的含义是包括当前行以及它前面的 2 行共 3 行一起求平均。这个写法在做走势曲线的时候特别常用。请把框架四要素背下来起始边界、结束边界、PRECEDING、FOLLOWING。边界既可以是行也可以直接写当前行 CURRENT ROW或者写无边界 UNBOUNDED。2.4 分桶与取值类NTILE、FIRST_VALUE 和 LAST_VALUE最后我还补了两个相对冷门但关键时刻能救命的函数。NTILE 的作用是把分组里的行尽量平均地切分成若干个桶比如把员工按销售额分成四档SELECT dept_id, employee, amount, NTILE(4) OVER (PARTITION BY dept_id ORDER BY amount DESC) AS bucket_no FROM sales_order;这个函数的典型场景是客户分层前 25% 是 VIP后 25% 是沉默户。它跟 RANK 的区别在于RANK 关心第几名NTILE 关心第几档。FIRST_VALUE 和 LAST_VALUE 则是取窗口内第一行或最后一行的值。注意LAST_VALUE 默认的框架结束边界是 CURRENT ROW如果不主动改成 UNBOUNDED FOLLOWING它取到的往往不是整个分组的最后一行而是当前行。我第一遍跑的时候就犯了这个错误结果每行返回的都是自己。正确写法是显式写满边界SELECT dept_id, employee, sale_date, amount, FIRST_VALUE(amount) OVER ( PARTITION BY dept_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS first_amount FROM sales_order;这段练习结束后我把四类函数的用途用一句话各写了一遍排序类负责第几名偏移类负责旁边那行聚合类负责范围内汇总取值类负责边界上取一个值。这句话现在直接记在我笔记第一页。3. 边学边踩坑三个翻车现场和排查思路3.1 NULL 值把排名排乱了我第一遍跑排名时故意往数据里塞了一个金额为 NULL 的员工想测试数据库的行为。结果 MySQL 8.0 和 DuckDB 给我的结果完全相反。MySQL 的默认规则是 NULL 最小按金额降序排列时NULL 会被排到最后而在 DuckDB 里NULL 默认按比任何非空值都大处理降序时 NULL 直接跳到第一名。同样是跑ORDER BY amount DESC两个数据库给出的第一名不一样这要是上线了排名就全错了。解决办法也很简单。MySQL 8.0 不支持NULLS LAST这种标准写法得用一个小技巧SELECT employee, amount, ROW_NUMBER() OVER ( PARTITION BY dept_id ORDER BY (amount IS NULL), amount DESC ) AS rn FROM sales_order;DuckDB 和 PostgreSQL 则可以直接写ORDER BY amount DESC NULLS LAST语义清楚不靠技巧。这个坑本身不难但它提醒我一件事不同数据库的窗口函数默认行为并不完全一致写代码前一定要确认正在用的是哪套引擎。3.2 忘记在窗口里写 ORDER BY累计值变成了分组总和这是今天最典型的脑补翻车。我一开始的想法是既然查询最后有ORDER BY sale_date窗口里的 SUM 是不是也会跟着日期一路累加上去实际跑完才发现不是。看这段错误示范SELECT dept_id, sale_date, amount, SUM(amount) OVER (PARTITION BY dept_id) AS dept_total FROM sales_order ORDER BY sale_date;结果并不如我预期订单还没按日期累积每一行的 dept_total 都是整个部门的总额。原因在于窗口函数的计算发生在外层 ORDER BY 之前而且窗口 SUM 表达式里根本没有 ORDER BY 子句数据库就认为全分区是一个窗口于是每行都返回部门的全部金额。想算累计就一定要在 OVER 内部写ORDER BY sale_date同时配合框架限定为从分区起点到当前行。外层 ORDER BY 只影响最终结果的展示顺序跟窗口内的累计逻辑没有任何关系。这个坑我记性很深因为它是看起来和 SQL 语义低耦合、实际却完全不同的典型。3.3 同一天有多笔订单累计值莫名跳高第三个翻车最有意思。我在临时表里加了同部门同一天两笔订单用了一段简化版累计 SQLSELECT dept_id, sale_date, amount, SUM(amount) OVER (PARTITION BY dept_id ORDER BY sale_date) AS cum_amount FROM sales_order;我预期的结果是 01-08 第一笔累计 2200第二笔累计 3400。但实际跑出来两笔订单的 cum_amount 都是 3400。问题是明明整体看是一行一行往下累为什么第二笔会连第一笔一起算进去原因是默认框架。当 OVER 里有 ORDER BY 但没有显式 ROWS 子句时数据库使用 RANGE 模式所有排序键相同的行会被视作同行即 peers。01-08 这两笔订单排序键完全相同于是它们在整个框架里都属于当前行范围一进来就都被收入窗口导致两行的累计结果相同。修复方式就是我在 2.3 里写的显式把 ROWS 边界写清楚SUM(amount) OVER ( PARTITION BY dept_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )我把这个教训提炼成一句话有相同的排序字段时不用 ROWS 就别说你懂累计。现在我只要看到累计两个字第一反应就是检查有没有显式声明 ROWS BETWEEN。3.4 性能与可读性的两个提醒窗口函数确实好用但它不是免费的。它通常需要对 PARTITION BY 和 ORDER BY 的列做一次全局排序数据量大到几千万行时这种排序会吃掉大量内存和临时磁盘空间。我的经验是线上报表如果只是需要部门总额 明细这种需求先用普通 JOIN 或物化视图评估成本只有在逻辑复杂到 JOIN 写不出来时才上窗口函数。同时能通过索引覆盖排序键就尽量覆盖比如(dept_id, sale_date)这个组合索引在今天的累计例子里就能省掉一次外部显式排序。可读性方面别把窗口函数写成一本流水账。同一个窗口定义重复出现五六次时一定要用 MySQL 的 WINDOW 子句或 PostgreSQL 的WINDOW w AS (...)抽取公共部分。我上个项目里见过一段 200 行的 SQL 没有 WINDOW后面维护的人改一个字段要搜索替换五个位置迟早出事。4. 学完立刻上手两分钟复现一个分组 Top N 需求4.1 Top N 的完整 SQL 与执行逻辑下午我关掉实验脚本用今天学的东西重写之前业务报表里那个每个部门销售额前两名的需求。以前我靠相关子查询吭哧吭哧写半天现在一段 CTE 就搞定WITH ranked AS ( SELECT dept_id, employee, amount, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS rn FROM sales_order ) SELECT dept_id, employee, amount FROM ranked WHERE rn 2 ORDER BY dept_id, rn;这里有个细节必须说清楚为什么不能直接在 WHERE 里写ROW_NUMBER() OVER (...) 2因为 WHERE 是在窗口函数计算之前执行的数据库在过滤时根本还不知道这个编号是多少。正确做法就是先把带编号的结果放进 CTE然后在外面过滤。这个先算后筛的步骤是窗口函数用得熟不熟的分水岭。跑出来的结果很稳。A 部门前两名是 2200 的小李和 1800 的小王B 部门是 2600 的小陈和 1500 的小张C 部门因为只有一个人所以只有 2300 的小刘。拿到结果我又特意加了一条同金额并列的数据做测试确认用的是 ROW_NUMBER 而不是 RANK避免出现前三名返回四行的尴尬。4.2 我自己怎么把今天的学习沉淀成可复用笔记学了这么多如果只是看完就关掉明天必忘。学习日记的作用在这里就体现出来了。我今天的日记格式不是流水账而是按五要素记录这样三个月后翻回来仍然能快速定位。我的学习日记模板是这样的要素今天记录的内容今天要解决的问题明细行上同时展示分组总额、部门内排名、部门内累计核心概念笔记窗口不折叠明细按分组计算后回填到每行亲手跑过的代码排名三件套、LAG 环比、SUM OVER 累计、Top N CTE翻车记录NULL 排序差异、缺少窗口 ORDER BY、RANGE 默认框架重复累加一句话收获累计务必显式写 ROWS BYWHERE 不能直接引用窗口函数编号这个模板最大的好处是错题驱动。以前我写日记总想把知识点面面俱到地抄一遍结果抄完自己都不想看。现在每个知识点都对应一个真实的翻车场景回忆时先想起场景再想起解决方案比背语法快得多。4.3 明天的学习计划今天把主框架打完了但有两个后续问题我明确记在待办里。第一RANGE 模式下的时间窗口。我想继续研究 DuckDB 和 PostgreSQL 里ORDER BY sale_date RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW这类按时间段滑动的写法它和 ROWS 的物理行数限制是完全不同的能力对周期类统计特别有用。第二把窗口函数翻译成 pandas。我平时也写 Python想把今天的 SQL 片段分别映射到groupby.transform和shift这样以后做数分时多一套语言选择也能相互校验结果。5. 上手之后才真正想明白的几件事学到最后我不光会写窗口函数了还把几个抽象概念彻底落地了。第一窗口函数并不可怕可怕的是没有理解行的视角。GROUP BY 是自上而下的汇总视角窗口函数是逐行扫描的观察视角想清楚自己要哪个视角代码自然就出来了。第二默认框架坑太多任何涉及累计、平均、取值边界的需求我都会刻意补上 ROWS 或 RANGE 定义绝不偷懒。第三学习日记最大的价值不是今天学了多少而是今天犯了什么错、为什么错、怎么避免再错。我翻去年写的日记最常回看的恰恰是那些报错记录知识点早就忘了教训还记得牢牢的。今天这份 260126 的学习日记就到这。明天我会带着按时间段滑动窗口这个小目标继续如果中途又踩出新坑再来跟你们同步。