简介《SQL窗口函数速查表》PDF面向数据库管理员、数据分析师、数据科学家与软件开发人员聚焦复杂数据集下的高效查询与统计分析需求适合从入门到进阶的SQL使用者随时查阅。全文按功能分类梳理窗口函数知识先说明窗口函数与普通聚合函数的差异再覆盖ROW_NUMBER、RANK、DENSE_RANK等排名函数LEAD、LAG等行序号函数以及SUM、AVG、COUNT、MIN、MAX等窗口聚合用法并交代OVER、PARTITION BY、ORDER BY与frame_clause的语法结构。资源包共1个PDF文件大小约841KB轻量便携便于本地保存和快速检索。已有255人学习文档以简洁条目配合示例代码呈现可帮助读者对照实际场景理解函数返回值与写法在报表生成、数据探索、应用开发及教学研究中作为速查与排错参考。1. 窗口函数最反直觉的地方算完聚合行数一行没少很多人第一次碰 SQL 窗口函数都会下意识把它当成 GROUP BY 的另一种写法跑完之后却发现结果集行数完全对不上——GROUP BY 把十行压成一行窗口函数却把聚合值原样贴回十行。这个「不塌缩」的特性就是窗口函数的全部价值所在也是它几乎所有坑的源头。速查表这类资源真正值得常备不是因为函数名难记而是 OVER 里那三个子句的组合语义太容易错位错位之后不报错、只给你一个看起来很像对的错结果。搞懂它凭什么能在一趟扫描里既保住明细又算出聚合比背下二十个函数名有用得多。2. 逻辑执行顺序OVER 子句为什么排在 WHERE 之后、ORDER BY 之前2.1 一条 SELECT 的流水线窗口计算被插在哪一步要理解窗口函数的脾气得先接受一件事SQL 的书写顺序和执行顺序是两套东西。你写的是 SELECT ... FROM ... WHERE ...引擎读的是 FROM 先定数据源WHERE 过滤行GROUP BY 折叠HAVING 过滤组然后才轮到窗口计算最后才是 SELECT 投影、DISTINCT、ORDER BY、LIMIT。窗口计算被安放在 HAVING 之后、SELECT 投影之前的这个位置是整件事的关键。因为它拿到的输入已经是「过滤完、聚合完」的结果集所以它天然看不到被 WHERE 剔除的行反过来因为它还没进入投影阶段所以它算出来的值必须依附在现存的行上而不能像 GROUP BY 那样把行合并掉。这也解释了一个高频翻车场景你在 WHERE 里写WHERE ROW_NUMBER() OVER (...) 1引擎直接报错。不是它不想支持而是执行到 WHERE 那一刻窗口根本还没开始算列还不存在。这不是引擎的毛病是顺序决定的。常见的实现思路是引擎把窗口函数当成一个独立的算子节点插在计划树里PostgreSQL 叫 WindowAgg其他引擎多半叫 Window 或者 Analytic名字不同位置一样它的孩子节点是过滤后的扫描结果它的输出带着新增的窗口列继续往上走。-- 错误示范WHERE 阶段窗口列还不存在 SELECT user_id, amount FROM orders WHERE ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) 3; -- 正确姿势先用子查询/CTE 把窗口列算出来再在外层过滤 WITH ranked AS ( SELECT user_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM orders ) SELECT user_id, amount FROM ranked WHERE rn 3;两段代码的差别不在语法糖而在层级。第一段把窗口函数塞进了 WHERE 能看见的作用域第二段把窗口计算下沉到子查询让它在自己的作用域里完成外层再当成普通列来筛。参数上要留意PARTITION BY user_id决定「按人分组」ORDER BY amount DESC决定「组内谁排第一」rn 3才是业务上的取前三条——三个东西分别属于三个不同阶段混在一起写必然出事。2.2 PARTITION BY 与 ORDER BY 的分工一个切窗口一个定帧OVER 括号里能塞的东西就那么几样但每一样都在改语义。PARTITION BY 决定「行被切成几堆堆与堆之间互不相干」ORDER BY 决定「堆内怎么排」而排序一旦存在帧frame的默认值就跟着变了帧再决定「当前行计算时到底看哪几行」。这三者是有依赖关系的不是并列关系。PARTITION BY 是切分ORDER BY 是在切分内部建立有序性帧是在有序性之上划定范围。少了 PARTITION BY整个结果集就是一个大窗口所有行一起参与计算少了 ORDER BY帧退化成「整个分区」排序相关的函数RANK、LAG行为也变得不可预期。-- 同一个 SUM三种 OVER 写法三个完全不同的结果 SELECT dept_id, emp_id, salary, -- 1) 只有分区整个部门的总和每行都一样 SUM(salary) OVER (PARTITION BY dept_id) AS dept_total, -- 2) 分区 排序默认帧是「从分区第一行到当前行」即累计 SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) AS running_total, -- 3) 分区 排序 显式帧只看当前行和前两行 SUM(salary) OVER ( PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS last_3_sum FROM employee;第一列dept_total是整分区聚合靠的是「没有 ORDER BY 时帧默认覆盖全分区」这条规则第二列running_total之所以会累计是因为一旦出现 ORDER BY默认帧就从全分区收窄成RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW第三列才是你显式指定的滑动窗口。很多人以为第二列和第一列只是写法差异实际是语义差异这就是速查表里那句「frame_clause 定义窗口范围」真正的分量。hire_date这里有讲究如果两个员工入职日期相同RANGE语义会把他们当成 peer 一起算进当前行于是同一天入职的人拿到的累计值一样换成ROWS就会按物理顺序硬分先后。这个差别在第 4 章会展开先记住「默认帧是 RANGE 不是 ROWS」。2.3 一趟排序、多窗口复用引擎怎么避免重复扫表写多个窗口函数时一个自然的担心是「是不是每写一个 OVER 就要重排一次表」。答案取决于这些 OVER 之间的分区键和排序键是否兼容。多数引擎的优化策略是把窗口按(PARTITION BY 列, ORDER BY 列)分组签名完全一致的窗口可以共享同一次排序一次扫描、一次排序然后在同一份有序数据上跑多个聚合。签名不同的窗口才需要额外的 Sort 节点。所以在执行计划里你看到的往往不是「一个窗口一个 Sort」而是「一个 Sort 后面挂一串 WindowAgg」。-- 这四个窗口共享同一个 (PARTITION BY dept_id ORDER BY hire_date) 签名 -- 优化器通常只需要一次排序 SELECT dept_id, emp_id, SUM(salary) OVER w AS running_sum, AVG(salary) OVER w AS running_avg, COUNT(*) OVER w AS running_cnt, ROW_NUMBER() OVER w AS seq FROM employee WINDOW w AS (PARTITION BY dept_id ORDER BY hire_date);WINDOW w AS (...)这个子句就是给窗口签名起名字多个函数引用同一个名字语义和写四遍完全一致但可读性和「让引擎明确知道这是同一个窗口」这两点都更好。要注意的是命名窗口只能整体复用不能只复用 PARTITION BY 再各自加不同的 ORDER BY——想改排序就得重新写一份完整的 OVER。一个反直觉的点共享排序不等于共享结果。SUM和ROW_NUMBER即便用同一个窗口走的也是不同的累加逻辑前者维护一个运行中的累加器后者维护一个计数器。引擎省的是排序和扫描不是计算本身。至于内存有序窗口在大多数实现里是流式的数据按分区键排好逐个分区喂进窗口算子分区结束就吐结果、释放状态。这意味着窗口函数对内存的压力通常比想象中小真正的开销集中在排序那一步。反过来说如果分区键基数极高几乎每行一个分区排序的收益就没了退化成一堆小窗口性能不会比逐行处理好多少。3. 排名三兄弟ROW_NUMBER、RANK、DENSE_RANK 的差异根源3.1 并列值怎么产生跳号RANK 的 gap 从哪来这三个函数的名字起得很老实但用起来最容易混。差异只有一个来源遇到排序键相同的行时它们怎么处理。ROW_NUMBER()完全无视并列硬给 1、2、3、4谁前谁后由排序决定RANK()给并列行相同的名次然后跳过被占掉的位置于是出现 1、1、3DENSE_RANK()也给相同名次但不跳号于是 1、1、2。SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, -- 1 2 3 4 5 RANK() OVER (ORDER BY score DESC) AS rk, -- 1 1 3 3 5 DENSE_RANK() OVER (ORDER BY score DESC) AS drk -- 1 1 2 2 3 FROM exam_result;数据是 100、100、90、90、80 时RANK的第三名之所以是 3 而不是 2是因为前两名已经被两行占用了第三行要「排在第 3 个位置上」。这个语义对应的是现实里的「并列第一之后没有第二名」DENSE_RANK对应的是「并列第一之后是第二档」。选哪个不是风格问题是业务问题。做「前 N 名发奖」且允许并列时用RANK更贴近直觉做「分档位、档位连续编号」时用DENSE_RANK做「每个用户只取一条、不关心并列」时用ROW_NUMBER但必须回答一个问题——并列时到底取哪条这时候需要追加一个次级排序键比如 id否则结果不稳定。注意ROW_NUMBER在并列时的取舍是非确定性的同一个查询跑两次可能给出不同的行。写进生产逻辑前务必在 ORDER BY 里补一个唯一键做兜底。3.2 没写 ORDER BY 的 ROW_NUMBER 为什么是玄学ROW_NUMBER() OVER ()这种写法语法合法结果却是玄学。原因在窗口算子的实现上它拿到的是一份无序的输入流编号按到达顺序发而到达顺序取决于扫描方式、并行度、是否走了索引。同一个查询今天跑出 A 在前明天加了条数据可能就 B 在前。有些人靠「不写 ORDER BY 也能跑对」糊弄过去血泪经验是这玩意儿只在数据量小、单线程、恰好走同一条执行路径时成立。数据一上量并行扫描一开编号顺序立刻就变。-- 危险顺序完全依赖扫描顺序 SELECT id, ROW_NUMBER() OVER () AS rn FROM big_table; -- 稳定显式指定唯一且确定的排序键 SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM big_table;判断标准很简单ORDER BY 里的列组合能不能唯一确定行顺序。能结果就是确定的不能就得补列。补一个自增主键或者时间戳加 id 的组合代价是一次排序收益是可复现。这条规则对LAG、LEAD、FIRST_VALUE、LAST_VALUE一视同仁。它们全靠「前一行」「后一行」这种相对位置吃饭位置不确定取出来的值自然不确定。速查表里把 LEAD/LAG 归到「行序号函数」本质就是提醒你这类函数的第一参数是偏移量而偏移量的意义完全建立在排序之上。3.3 分组取 Top N 的三条路子查询、窗口加 CTE、关联计数「每个部门取薪资前三」是窗口函数最经典的入门案例也最能说明设计取舍。第一条路窗口函数加 CTE就是 2.1 节那段代码先分区编号再外层过滤rn 3。一趟排序复杂度大致是 O(n log n)代码短可读性强。第二条路关联子查询数「比我高的有几个人」对每行来说统计同部门里薪资大于它的行数小于 3 就留下。逻辑直观但每行都要扫一遍同部门的数据复杂度接近 O(n²)数据量一大就塌。-- 关联计数法每行统计「同部门里比我高的有几人」 SELECT e.dept_id, e.emp_id, e.salary FROM employee e WHERE ( SELECT COUNT(*) FROM employee e2 WHERE e2.dept_id e.dept_id AND e2.salary e.salary ) 3;第三条路用RANK而不是ROW_NUMBER这样并列的人会一起入选。语义上更贴近「前三名」而不是「三条记录」。三者的差别值得在速查表旁边写一行注ROW_NUMBER保证恰好 N 行RANK可能多于 N 行关联计数在大表上基本不可用。选错不会报错只会让你在复盘时发现「上周的报表多了两个人」。4. 帧frameROWS 与 RANGE 的分水岭4.1 默认帧的两套规则有没有 ORDER BY 完全不同帧是窗口函数里最容易被忽略、又最容易埋雷的部分因为它的默认值会随着 OVER 的内容自动变化而你什么都没写。规则有两条OVER 里没有 ORDER BY帧就是整个分区OVER 里有 ORDER BY帧就是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。第一条对应「整表聚合」的直觉第二条对应「累计」的直觉。问题在于很多人写SUM(x) OVER (PARTITION BY k ORDER BY t)时想的是「整组求和」实际拿到的是「按时间累计」。结果没错只是理解错了等到数据里有重复时间值累计值就开始出现「同时间的人拿到相同累计」才意识到帧在作祟。-- 想清楚这两条语句各自算什么 -- A: 全分区总和每行相同 SELECT SUM(amount) OVER (PARTITION BY uid) FROM txn; -- B: 按时间累计每行不同默认 RANGE 帧 SELECT SUM(amount) OVER (PARTITION BY uid ORDER BY dt) FROM txn; -- C: 想显式锁死「全分区」别靠省略 ORDER BY 来暗示 SELECT SUM(amount) OVER ( PARTITION BY uid ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM txn;C 的写法看起来啰嗦但它把意图写死了我就是要整分区哪怕我加了 ORDER BY 用于别的目的。生产代码里这种「显式帧」比省略更可靠因为半年后有人往 OVER 里补一个 ORDER BY省略版的行为会静默改变显式版不会。4.2 RANGE 的 peer 行为什么同值行会一起被算进来ROWS和RANGE都用于划定边界但量尺不同。ROWS数的是物理行「前 3 行」就是实打实的 3 行RANGE比的是排序键的值「前 3」指的是值域上的偏移而不是行数。在ORDER BY dt的场景里如果三笔订单落在同一天RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW会把这三天里所有同日的行全部算进「当前行」因为它们排序键相等互为 peer。于是同一天的三笔订单累计值一模一样——每一笔都把当天全部算进去了。ROWS就不会这样它严格按顺序切第一笔只看自己第二笔看前两笔。-- 对比 RANGE 与 ROWS 在存在重复排序键时的差异 SELECT dt, amount, SUM(amount) OVER ( ORDER BY dt RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS range_sum, -- 同日多笔每笔都等于当日累计 SUM(amount) OVER ( ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS rows_sum -- 同日多笔逐笔递增 FROM txn;哪个对取决于你要回答的问题。做「日累计曲线」时同一天的多笔交易应该共享同一个日终值RANGE更合适做「按笔累计流水」时必须是ROWS。速查表里 frame_clause 那一行只写了「定义窗口范围」真正的坑在「范围是按行还是按值」。还有一个隐藏差异RANGE的偏移量只能是数值或时间间隔因为它要做值域计算ROWS的偏移量只能是整数行数。写RANGE BETWEEN 3 PRECEDING时3 指的是「排序键值减去 3」不是「往前三行」在字符串排序键上直接不合法。4.3 累计、移动平均与占比三种帧写法的取舍把帧用熟之后大部分分析需求都能归到三种模式上累计、滑动窗口、整分区占比。累计用UNBOUNDED PRECEDING AND CURRENT ROW这是默认帧什么都不用写滑动窗口用于移动平均或近 N 天指标用N PRECEDING AND CURRENT ROW占比是当前值除以整分区总和后者用无 ORDER BY 的 OVER 或者显式UNBOUNDED FOLLOWING。-- 7 日移动平均滑动窗口注意用 ROWS 避免同值行被合并 SELECT dt, amount, AVG(amount) OVER ( ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS ma7, -- 占比分母是整分区分子是当前行 ROUND( amount * 100.0 / SUM(amount) OVER (PARTITION BY category), 2 ) AS pct_of_category FROM sales;6 PRECEDING AND CURRENT ROW一共 7 行这是最容易写错的地方——写成7 PRECEDING就变成 8 行了。另外移动窗口在数据开头会「行数不足」前几行的平均值只基于已有的两三行这是正常的但如果业务要求「必须满 7 天才出值」就得在外层加WHERE过滤掉行号小于 7 的记录。占比那一段用的是无 ORDER BY 的 OVER分母是整分区总和分子逐行变化。这种写法的性能前提是分区基数不要太大否则每个分区一次聚合虽然仍是流式但状态维护的开销会累积。5. 排查清单窗口函数语法没报错、结果却不对的五种情况5.1 在 WHERE 里过滤窗口结果报错背后的执行顺序现象WHERE里引用窗口函数列引擎直接拒绝报「窗口函数不允许出现在 WHERE 中」。原因执行顺序上 WHERE 早于窗口计算那一刻列压根不存在。这不是语法限制是阶段错位。解决包一层子查询或 CTE把窗口计算下沉外层只做普通列过滤。顺带一提HAVING里也一样不行原因相同——HAVING也早于窗口。唯一能用窗口结果做过滤的位置是外层的SELECT、ORDER BY和最外层的WHERE。5.2 PARTITION BY 少写一列跨组串行现象按用户分组的排名结果里出现了别的用户的数据混进来排名连续跨越了用户边界。原因PARTITION BY少了维度。比如业务上「每个用户每个品类单独排名」只写了PARTITION BY user_id品类之间就串了。解决把业务上「互不可比」的维度全部列进PARTITION BY写完回头核一遍如果两组数据在业务上不应该互相比较它们就必须在不同的分区里。判断方法是问自己「这两行会不会争同一个名次」会就得分区。5.3 排序键类型与 NULL排名结果和肉眼不符现象明明 100 分应该排第一结果RANK里排到了最后或者 NULL 行跑到了榜首。原因排序键是字符串类型100按字典序小于9或者 NULL 在排序中的位置与引擎默认约定不一致有的把 NULL 当最大、有的当最小。解决排序前显式转型ORDER BY score::numeric DESC别让隐式转换替你决定处理 NULL 用NULLS LAST/NULLS FIRST明确写出或者用COALESCE(score, 0)先兜底。字符串型数字是最常见的一类尤其在导入的 CSV 数据里字段类型一律是文本。5.4 帧边界省略或写反累计变全量现象本该逐行累计的指标每一行都是同一个数或者累计到某一行突然跳变。原因省略了帧而 OVER 里恰好没有 ORDER BY于是帧退化成整分区或者边界写成CURRENT ROW AND UNBOUNDED FOLLOWING变成「从当前行到末尾」的逆向累计。解决涉及累计和滑动的场景一律显式写帧别依赖默认值。写完之后用三五行数据手算一遍第一行应该是多少、中间一行应该是多少对不上就说明帧错了。这个手工校验的习惯能挡掉大部分静默错误。5.5 大表上的排序与倾斜慢查询的锅不在函数现象加了窗口函数之后查询从秒级变成分钟级执行计划里出现巨大的 Sort 节点。原因窗口计算要以分区键和排序键为序而表上的索引顺序对不上只能全表排序。此外分区键分布极不均匀时单个分区过大窗口算子内存吃紧可能落盘。解决先看执行计划确认排序的输入行数再考虑在(partition_col, order_col)上建索引让扫描阶段就产出有序流省掉 Sort。分区键倾斜则要从业务上拆比如按哈希再切一层而不是硬扛。窗口函数本身很轻重的是它前面那次排序。6. 设计取舍窗口函数、自连接、GROUP BY 该选哪个窗口函数不是一个必须掌握的炫技点它是一笔账。同一件事至少有三种写法选哪个要看数据量、可读性和引擎能力三者的交点。从复杂度看窗口函数的核心成本是一次排序O(n log n)自连接做「比我有几个人」是 O(n²)小表看不出差别十万行以上就开始难受GROUP BY 加 JOIN 回原表是两次扫描复杂度介于两者之间但代码更长、中间结果更多。用不用窗口函数本质上是在问「我愿不愿意用一次排序换掉一次自连接」。从优化器角度看窗口函数有个特殊限制它的结果是 SELECT 阶段才产生的所以不能被下推到 WHERE、不能被索引直接过滤、不能被别的谓词提前剪枝。换句话说你在外层WHERE rn 3过滤掉的九成数据内层窗口计算全都算过了——这一步是省不掉的。这个特性决定了窗口函数适合「算完再筛」的场景不适合「先用索引砍掉大部分数据再算」。真遇到后者应该把过滤条件放进子查询的 WHERE 里让窗口算子只看到它该看的那部分。验证方法很直接看执行计划。以 PostgreSQL 风格的计划为例关注三个节点Sort告诉我们排序了多少行、有没有落盘WindowAgg告诉我们窗口算子处理的行数如果有Segment说明它按分区键做了切段。三者的行数应该逐级递减或持平如果WindowAgg的行数远大于外层最终返回的行数说明大量计算被浪费了。方案时间复杂度是否保留明细适合场景窗口函数O(n log n)是排名、累计、同组占比自关联计数O(n²)是小表、并列语义复杂GROUP BY JOIN 回表O(n log n) 两次扫描是聚合结果需要复用纯 GROUP BYO(n log n)否只要聚合值还有一条容易被忽略的取舍可读性。窗口函数把「分组、排序、取范围」三件事压缩进一个 OVER写的人舒服读的人如果不懂帧语义就会误判。我一般会在复杂窗口旁边补一行注释写清楚「这里是按天累计帧是 RANGE同日的行共享累计值」因为半年后回来改代码的人往往就是自己不会记得当时怎么想的。进阶一点的方向是命名窗口加多层 CTE 的组合把原始过滤、窗口计算、外层筛选拆成三段每段只做一件事。这样做计划会变长但每一步的输入输出都可以单独验证出错时定位成本低得多。我踩过最深的一次坑是在一张日活表上写累计用RANGE帧但排序键是日期字符串其中混了空值和格式不一致的脏数据结果累计曲线在特定几天突然抬升又不回落。排查了两个小时才发现是同一天被拆成了两种字符串形式peer 判定失效。从那以后我每次写带 ORDER BY 的窗口函数都强制先跑一遍SELECT 排序键, COUNT(*) GROUP BY 1 ORDER BY 2 DESC LIMIT 20确认排序键干净、无空值、类型一致再动窗口。希望帮到你。本文还有配套的精品资源点击获取