简介这是一份面向数据库管理员、数据分析师及开发人员的SQL窗口函数速查表PDF文档系统梳理了窗口函数的核心概念、三类常用函数排名、行序号、聚合的语法与参数说明并配有大量可直接套用的示例代码可帮助读者在处理分组排名、环比同比、移动计算等复杂查询时快速查阅与高效编写。压缩包共1个文件为841KB的PDF文件内容按功能分类编排结构清晰便于按需定位。已有255人学习下载适合希望提升SQL查询技能、尤其是需要处理复杂数据集的技术人员日常参考或系统学习。通过该速查表读者可以快速掌握ROW_NUMBER、RANK、LEAD/LAG等函数的用法差异理解PARTITION BY与ORDER BY的配合规则并通过示例理解窗口范围的设置逻辑是一份精炼实用的SQL进阶工具。1. SQL窗口函数为什么它能算 GROUP BY 算不出的逐行上下文SQL窗口函数最反直觉的一点SUM()不写OVER时二十行明细被折叠成一行汇总一旦带上OVER()同样的SUM()反而把每一行原样保留只在旁边多贴一个全表总数。GROUP BY 是数据压缩器窗口函数是数据展开器。它解决的是 SQL 里最棘手的一类问题算排名、算增量、同比环比、移动平均。这些计算每一行都要借用别的行的上下文而普通聚合算完明细行就没了。搞懂它执行计划里的 WindowAgg、Sort 节点就不再是黑匣子。《SQL窗口函数速查表》按函数族整理出了这条路径的完整语法与示例正适合做这份机制认知的对照。2. 从 GROUP BY 到 OVER()一列汇总值是怎么贴到每一行上去的2.1 折叠问题GROUP BY 赢了汇总丢了明细先落一个最常见的业务场景每一笔订单后面跟着这个订单所属月份的订单总额。在窗口函数普及之前标准答案长这样SELECT o.order_id, o.amount, m.monthly_amount FROM orders o JOIN ( SELECT DATE_TRUNC(month, order_date) AS month, SUM(amount) AS monthly_amount FROM orders GROUP BY 1 ) m ON DATE_TRUNC(month, o.order_date) m.month;这段查询把订单表读了两遍一遍算月汇总一遍拿汇总结果去匹配明细。数据量上了千万行这个自连接就是第一层损耗。比性能更难受的是语义——先压缩、再展开读这段 SQL 得在脑内绕一个弯聚合结果表被连接回明细表每一行临时借用一下自己所属月份的总数。GROUP BY 的设计目标就是把多行折叠成一行输出组的统计属性。它没有做错任何事只是行上下文在这种模型里被销毁了。分析型需求恰恰相反行要保留上下文要能引用。这两件事天然互斥SQL 标准在 2003 年版本里正式把窗口函数变成了一等公民用的就是OVER这套语法来调和矛盾。翻译成大白话每一行在算自己的结果时能不能临时借用一下别的行的汇总值窗口函数说可以而且不需要 JOIN。2.2 空窗口 OVER()最小实现与执行计划里的 WindowAgg窗口函数的最小形态是一个空窗口SELECT order_id, amount, SUM(amount) OVER() AS total_amount FROM orders;OVER()括号里什么都没有含义是整个结果集作为一个窗口。每一行都输出自己的order_id、amount再附上全表的total_amount行数一行不减。这个例子虽然简单却揭示了窗口函数的本质它不是过滤也不是分组而是在每一行上追加一列上下文。执行逻辑要分两层看。第一层FROM和WHERE照常执行生成一个元组流第二层窗口算子拿到这个元组流扫一遍维护一个累加器再把累加器的值作为新列拼接进去。在很多开源引擎里这个算子叫 WindowAgg窗口聚合节点它的职责就是追加窗口列不改变行数、不做过滤。EXPLAIN SELECT order_id, amount, SUM(amount) OVER() AS total_amount FROM orders;执行计划里能看到一个独立节点里面写着窗函数的计算逻辑。看到这个节点再配合它下面的排序节点基本就能还原一条带窗口的查询是怎么跑的。这个执行顺序还解释了窗口函数的一条硬约束它只能出现在 SELECT 列表和 ORDER BY 里不能出现在 WHERE。因为WHERE过滤发生在窗口计算之前窗口列在那时还不存在。这和聚合函数不能直接进 WHERE是同一个层次的问题后面第 5 章还会遇到它的具体翻车现场。2.3 语法结构为什么窗口定义要挂在函数调用后面速查表里的通用语法是这样的function_name([DISTINCT | ALL] expression) OVER ( [PARTITION BY partition_expr, ...] [ORDER BY sort_expr, ...] [frame_clause] )组成作用省略时的行为PARTITION BY决定窗口按什么切分整个结果集是一个窗口ORDER BY决定窗口内行的顺序顺序不定窗口按全范围frame_clause决定当前行能看到窗口的哪些行有 ORDER BY 时默认到当前行OVER放在函数后面是一种后缀修饰读作对某列做 SUM作用在某个窗口上。为什么要这么设计而不是写成OVER(function(...))除了自然语言的语序外更重要的动机是语法上的超集SUM()本来就是聚合函数加上OVER后缀就变成了窗口函数不需要为窗口场景发明一整套新关键字。聚合和窗口共享同一批函数名降低学习成本这是 SQL 标准在语法设计上非常务实的一笔。语法里还有一项window_nameSQL 标准允许先用WINDOW子句定义一个命名窗口多个窗口函数共用。实际引擎支持程度不一但一旦支持对减少重复OVER很有用。不过要注意括号里的DISTINCT选项——大多数窗口函数对DISTINCT的支持是有限制的不同数据库差异很大速查表给的是标准通用形式落到具体引擎前要先验证。3. PARTITION BY 与 ORDER BY窗口边界、排序和默认范围由谁决定3.1 PARTITION BY分区不是分组行数一行不少窗口函数的边界由PARTITION BY决定。看这个例子SELECT region, order_date, amount, SUM(amount) OVER(PARTITION BY region) AS region_total FROM orders;每个region一个窗口区域内的每一行都带着自己的region_total。和GROUP BY region对比后者输出的是每个区域一行汇总这里有二十行明细就输出二十行聚合结果只是作为一列附加信息出现在每行旁边。执行机制上有两种常见实现。单机引擎通常把元组流按分区键排序后切成段相同分区键的行连续排列段内累计状态分布式引擎则先按分区键做哈希分发把数据打到不同节点各节点独立计算窗口状态。无论哪种分区键的粒度都直接影响性能分区切得越细窗口状态的重置越频繁算子启动清理的开销越大。用时间戳到秒做分区键常常会莫名慢一个量级就是这个原因。这个特性也决定了 PARTITION BY 的选型原则分区键要能反映业务边界又要避免过细。想清楚哪些行应该共享同一份上下文窗口就切对了一半。3.2 ORDER BY窗口里的排序悄悄改了默认范围OVER里的ORDER BY是个伏笔它的副作用常被忽略SELECT order_id, amount, SUM(amount) OVER (ORDER BY order_id) AS running_total FROM orders;这个running_total不是全表总额而是从窗口起点累计到当前行的和每一行看到的数值都在增长。原因藏在 SQL 标准的默认帧规则里当frame_clause省略且ORDER BY存在时默认窗口帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——也就是从分区起点到当前行的前缀窗口。只有完全没有ORDER BY时默认窗口才是整个分区。提示看到OVER里出现ORDER BY先问一句这是要累计值还是要全窗口值两者语义完全不同写错就变成越算越大的经典事故。帧语法的第一处差异也在ORDER BY上体现默认用的是RANGE而不是ROWS。RANGE按排序键的值域界定窗口排序键相同的行会被当作同一范围纳入ROWS按物理行数计算不会看键值。同样是OVER(ORDER BY date)RANGE会把所有同日期行一起包进窗口引擎处理起来也更保守。理解这一点是读懂默认行为的前提。3.3 分区加排序组内排名是怎么被算出来的把分区和排序组合起来就是最常见的组内排名场景SELECT department, employee_id, salary, ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS rn FROM employees;执行时被拆成三步按department分区、各分区内按salary DESC排序、从每个分区第一行开始编号。关键在第二步的实现——窗口算子通常只需要按(分区键, 排序键)做一次复合排序而不是分区一次、排序一次。分区键作为复合排序的前缀天然让相同分区的行连续算子扫描时只要发现分区键变化就知道要切换窗口、重置状态。这也是窗口算子能保持近似线性的根本原因一次有序扫描解决所有分区的计算不需要回表不需要随机访问。反过来如果你在EXPLAIN里看到多个 Sort 节点往往是几个不同的OVER各自要求的排序无法复用每个窗口规格都独立排了一遍这正是第 5 章要讲的内存和性能隐患。4. 排名、偏移与聚合三类函数在窗口算子层分别做了什么4.1 排名三兄弟差异在一个比较器排名函数是三兄弟最容易混的一组看结果最直观SELECT score, ROW_NUMBER() OVER(ORDER BY score DESC) AS rn, RANK() OVER(ORDER BY score DESC) AS rnk, DENSE_RANK() OVER(ORDER BY score DESC) AS drnk FROM exam_scores;scorerow_numberrankdense_rank95111902229032285443ROW_NUMBER()是最朴素的每读一行计数器加一完全不看排序键的值只认物理位置。RANK()和DENSE_RANK()则多了比较这一步——拿当前行的排序键和上一行比键相同就沿用上一个名次键不同才更新。区别只在更新方式RANK()跳到当前行号所以出现并列后会留出空档DENSE_RANK()把名次计数器加一名次永远连续。用伪代码看三者差异更清晰prev_key None row_no 0 rank 0 dense_rank 0 for row in sorted_rows: row_no 1 if prev_key is None or row.key ! prev_key: rank row_no # RANK键变化时跳到当前行号 dense_rank 1 # DENSE_RANK键变化时名次 1 # 键相同时rank 和 dense_rank 都保持不变 prev_key row.keyROW_NUMBER()根本没有比较器RANK()和DENSE_RANK()的区别只在键变化后如何更新排名值。选哪个取决于业务要的是名次连续还是并列后让位。4.2 LAG 与 LEAD不相邻的行是怎么被拉过来的速查表把LAG()和LEAD()归在行内偏移/取前后行一类。它们本质上不是计算函数而是取值函数——把当前行的前一行或后一行的值直接拿过来用SELECT payment_id, amount, LAG(amount) OVER(ORDER BY payment_date) AS prev_amount, amount - LAG(amount) OVER(ORDER BY payment_date) AS day_diff FROM payments;LAG取上一行的amountLEAD取下一行amount - LAG(amount)就是相邻两笔的差额。实现机制上窗口分区并排序后算子会维护一个行缓存常见实现是环形缓冲区滞后 n 行的值直接从缓存里读不需要回表查。LAG(x, n, default)的第二个参数是偏移量第三个参数是窗口边界上的兜底值比如第一行的prev_amount没有上一行可取就会返回default或NULL。对比自连接方案优势很明显只需要一次有序扫描内存占用大约 O(n)n 是偏移量和表的大小无关。这就是每行和上一行比这类问题的最优解形态。需要注意LAG/LEAD的分区边界它们在PARTITION BY划定的窗口内偏移跨分区时不会读到上一个分区末尾的行。如果你的业务语义是全表按时间连续比较,就不要随手加一个不必要的时间分区键。4.3 聚合窗口化累加器、滑动窗口与维护成本SUM、AVG、COUNT、MIN、MAX放进窗口算的还是同一个聚合逻辑区别只在作用范围从组变成了窗口。以SUM为例窗口算子在分区内维护一个累加器每读一行把当前值加进去。窗口不滑动时累加器一路走到分区末尾窗口滑动时还得把离开窗口的值再从累加器里扣掉。AVG维护的是累加和 计数器两个状态MIN和MAX在滑动窗口里最麻烦——离开窗口的值恰好可能是当前最小值算子需要额外的结构单调队列或堆才能知道谁是新最小值。所以移动最大值往往比移动总和慢内存分配也更活跃。这是窗口算子的常见实现思路遇到性能瓶颈可以先往这个方向排查。移动平均是ROWS帧最典型的应用SELECT trade_date, price, AVG(price) OVER(ORDER BY trade_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7 FROM daily_prices;ROWS BETWEEN 6 PRECEDING AND CURRENT ROW把窗口固定为物理上最近的 7 行每前进一行窗口起点跟着移动这是真正的滑动。如果换成RANGE按值域切窗口SELECT trade_date, price, AVG(price) OVER(ORDER BY trade_date RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW) AS ma_7d FROM daily_prices;RANGE纳入的是排序键值落在区间内的所有行行数不固定——某天没有交易记录窗口就只剩 6 行甚至更少。ROWS数行RANGE数值域这是理解frame_clause的分水岭也是移动平均最容易算错的地方。5. 窗口函数避坑结果差一行、排名跳号、性能翻车的常见问题窗口函数出问题的两种典型一种是结果不对但表现得很合理另一种是结果对了但慢到没法用。下面几条是实际调数时踩过最多的坑按现象 → 原因 → 解决整理多数都能在速查表的分类页找到对应位置。5.1 排名跳号1、1、3 到底是不是算错了现象用RANK()给成绩排名结果出现 1、1、3业务方说名次有空洞怀疑算错。原因RANK()的语义就是并列后跳过名次。它把并列的人算同一个名次并让下一位从 3 开始这样第 3 名的名次等于前面的人数 1本质上是为了保留奖牌语义。解决名次必须连续就换DENSE_RANK()只要唯一编号就换ROW_NUMBER()要并列后留位才用RANK()。写之前先在注释里写清业务要的是哪一套。5.2 SUM 越算越大窗口聚合为什么变成了累计现象SUM(amount) OVER(ORDER BY order_date)每行递增和当月总额对不上。原因第 3 章讲过ORDER BY一出现默认帧就变成RANGE UNBOUNDED PRECEDING AND CURRENT ROW聚合被悄悄改成了累计很多人没写frame_clause就以为它等于全窗口。解决要全窗口总和去掉ORDER BY写成OVER()或者显式写ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING要累计或移动平均才保留ORDER BY并明确帧的范围。5.3 窗口函数写进 WHERE凭什么直接报错现象在WHERE里写ROW_NUMBER() OVER(...) 1各引擎几乎统一报错。原因窗口计算发生在WHERE之后过滤执行时窗口列根本还不存在这是执行顺序决定的语法边界。解决包一层子查询先算窗口列再到外面过滤。取每组最新一条记录就是标准姿势SELECT user_id, order_id, created_at FROM ( SELECT user_id, order_id, created_at, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders ) t WHERE rn 1;这个写法把分组取最新从聚合语义转换成窗口 过滤语义是窗口函数最常用的实战场景之一速查表里通常把它放在ROW_NUMBER()的示例区。5.4 慢查询一个 OVER 为什么让秒级变分钟级现象同一张表不加窗口函数毫秒级返回加一个OVER(ORDER BY ...)直接慢一个量级。原因窗口算子对输入的元组流有排序要求如果表上索引顺序和窗口的ORDER BY不一致执行器只能现场做一次全量排序。多个不同OVER还会追加多个排序。解决用EXPLAIN数 Sort 节点一个 Sort 对应一个排序需求能利用已有索引的有序性最好不行就把WHERE过滤放在窗口之前让进入窗口算子的数据尽可能小。窗口函数不是不能配合过滤而是要先把廉价过滤做掉。5.5 内存暴涨多个不同 OVER 的排序为什么不能复用现象一个查询里同时写三四个窗口函数有的按日期排、有的按部门排内存吃满甚至落盘。原因不同OVER的PARTITION BY/ORDER BY各自需要独立的排序状态两个不同排序键没法用一次排序同时满足执行器只能同时维护多套窗口状态。解决把相同(PARTITION BY, ORDER BY)的窗口函数合并到同一个OVER下结构差异大的拆成多个 CTE 阶段让每一层只做一种排序。内存爆炸的查询九成都能靠这两步收敛。6. 设计取舍窗口函数用上下文换来了什么代价又是什么6.1 表达力与优化空间的交易窗口函数带来的不是新算法而是新的行依赖一行的结果不只由自己的列决定还由窗口内的其他行决定。这层依赖让语义表达力大幅提升一张对比表就能看明白它换来了什么维度GROUP BY窗口函数行数折叠成组一行不少上下文组内聚合值窗口内任意行跨行引用自连接LAG/LEAD执行位置早可下推靠后难下推代价藏在最后一行执行位置靠后意味着谓词下推、索引利用都受限它又是有状态的算子分布式引擎里窗口状态在节点之间的迁移也是成本。所以各 OLAP 引擎都在给窗口算子做专门优化而不是简单交给通用排序节点。6.2 三种不依赖运气的验证方法方法一EXPLAIN数 WindowAgg 和 Sort 节点的数量确认没有多余的排序。方法二把OVER里的PARTITION BY和ORDER BY逐个删掉观察结果变化能直观摸到默认帧的边界在哪。方法三造一个 5 行的最小数据集手工推一遍预期结果再和查询输出对比——窗口函数是确定性计算手工一定可推。三种验证都花不了几分钟但能把排名跳号、SUM 越算越大这类翻车场景挡在发布之前。我自己写窗口函数强制先回答三个问题再动手分区边界是什么、窗口内顺序是什么、当前行能看到窗口的哪些行。回答完才写OVER子句写完再对着速查表的分类页确认一遍函数有没有归错族。从那以后被窗口函数坑掉的报表少了一大半移动平均、组内排名这些高频场景基本一遍过。希望帮到你。本文还有配套的精品资源点击获取