SQL窗口函数速查与避坑：三件套、分组Top N与累计求和

简介《SQL窗口函数速查表.pdf》是一份面向数据库管理员、数据分析师、数据科学从业者及软件开发者的轻量速查文档重点解决窗口函数概念抽象、语法参数易混、复杂查询构建效率低等问题。压缩包内仅含1个PDF文件大小约841KB篇幅精炼、便于随身查阅。文档按功能分类梳理排名函数、行序号函数与聚合函数并解释ROW_NUMBER()、RANK()、DENSE_RANK()的排名差异以及LEAD()、LAG()取前后行数据的用法同时给出OVER子句配合PARTITION BY、ORDER BY和窗口帧的语法模板。读者可借助示例代码理解各函数返回值与适用场景在报表生成、趋势分析、异常值发现和应用程序查询构建中快速套用。已有255人学习适合希望系统补全SQL窗口函数知识并提升复杂数据集查询能力的技术人员。1. 知识地图SQL 窗口函数在整张查询版图里的位置翻开一份 SQL 窗口函数速查表多数人第一反应是把它当成“一类更高级的聚合函数”硬背背完 ROW_NUMBER 又背 LAG结果真到写查询时还是不知道从哪儿下笔。其实整张地图只有三个旋钮PARTITION BY 决定切几块、ORDER BY 决定块内怎么排、FRAME 决定当前行往回看多远。三类函数只是旋钮的三种用法排名类负责给位置偏移取值类负责拿邻居的值聚合类负责算区间汇总。它真正解决的是“既要明细行、又要汇总值”这个 GROUP BY 天生干不了的活。适合已经会写 JOIN 和 GROUP BY、但在做同环比、分组取 Top N、累计求和时总被卡住的人。2. 三类函数排名、偏移取值、聚合的分工与选型窗口函数看着名字多按返回值形态其实只有三种。第一种返回一个名次或序号整数第二种返回同一列上别的行的值类型跟着原列走第三种返回聚合结果数值或任意可聚合类型。选型时先问自己“我要的是位置、邻居、还是区间汇总”函数名基本就定了不用在速查表里挨个试。2.1 排名函数ROW_NUMBER、RANK、DENSE_RANK 的跳号差异这三个是踩坑重灾区因为它们在数据不重复时结果完全一样一旦出现并列值才分道扬镳。用一组值 100、100、90、80 排序来对照最直观。函数并列时行为上面这组值的返回常见用途ROW_NUMBER()强行拆开绝不重复1, 2, 3, 4去重、分页、每组取一条RANK()并列同名次之后跳号1, 1, 3, 4榜单、竞赛名次DENSE_RANK()并列同名次不跳号1, 1, 2, 3档位划分、取 Top N 个值NTILE(n)按顺序尽量均分成 n 桶分桶结果分位数、A/B 分桶PERCENT_RANK()名次归一化到 0~1小数百分位定位RANK 的跳跃不是 bug是定义两个第一名之后直接是第三名。所以“取排名前 3 的记录”这种需求用 RANK 可能返回 4 行两个并列第一用 DENSE_RANK 可能返回 3 个档位但行数更多只有 ROW_NUMBER 能保证恰好 n 行——代价是并列值的先后顺序由数据库自己决定。-- 每个部门取销售额最高的两条记录每组 Top N 的标准写法 SELECT dept_id, emp_id, amount FROM ( SELECT dept_id, emp_id, amount, ROW_NUMBER() OVER ( PARTITION BY dept_id -- 按部门切窗口 ORDER BY amount DESC, -- 金额降序 emp_id ASC -- tie-breaker并列时保证顺序确定 ) AS rn FROM sales ) t WHERE rn 2;这段的关键不在 ROW_NUMBER而在第二个排序键emp_id。只按 amount 排序时金额相同的两行谁拿 rn1 是不确定的同一条 SQL 跑两次可能给出不同结果报表对不上账多半是这个原因。参数上PARTITION BY 里的列决定“每组”的边界ORDER BY 里的列决定组内次序两者共同定义了一个窗口缺 ORDER BY 时 ROW_NUMBER 的编号没有业务含义。2.2 偏移与首末值LEAD、LAG、FIRST_VALUE、LAST_VALUE、NTH_VALUE这一族负责“横向取数”——在不做自连接的前提下拿到当前行前面或后面某一行的值。同环比、连续登录天数、相邻事件时间差全靠它们。-- 计算每月销售额的环比本月减上月 SELECT month_id, amount, LAG(amount, 1, 0) OVER (ORDER BY month_id) AS prev_amount, amount - LAG(amount, 1, 0) OVER (ORDER BY month_id) AS diff FROM monthly_sales;LAG(amount, 1, 0)三个参数依次是取值的列、往回退几行默认 1、越界时的默认值不写就返回 NULL。第三个参数很容易被忽略第一行没有“上一行”如果不给默认值diff 那一列第一条就是 NULL后续做 SUM 或除法时会连带出错。LEAD 方向相反往前看常用于算“距离下一次事件还有多久”。FIRST_VALUE、LAST_VALUE、NTH_VALUE 取的是窗口帧内的首行、末行、第 n 行的值。它们和 LEAD/LAG 的区别在于依赖帧边界而不是行偏移也正因为如此LAST_VALUE 有一个非常经典的坑后面第 5 章会单独拆。2.3 聚合窗口SUM、AVG、COUNT、MIN、MAX 加 OVER 之后发生了什么普通聚合会把多行压成一行加上 OVER 之后行数一行不少聚合结果被“贴”回每一行。这是窗口函数和 GROUP BY 最本质的分野也是它存在的理由。SELECT order_id, user_id, amount, SUM(amount) OVER (PARTITION BY user_id) AS user_total, -- 该用户总额贴回每行 SUM(amount) OVER (PARTITION BY user_id ORDER BY order_id) AS running_total, -- 累计到当前订单 AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7, -- 7 行移动平均 amount / SUM(amount) OVER () AS ratio -- 占全表比例 FROM orders;四个表达式演示了四种典型用法不带 ORDER BY 的分区求和是“组内总额”带 ORDER BY 后自动变成累计求和加了 ROWS 帧就变成滑动窗口OVER() 里什么都不写则整个结果集是一个窗口。COUNT(*) OVER ()这个写法值得单独记一笔——它能在返回明细的同时附带总行数省掉一次单独的 COUNT 查询做分页总数时特别顺手。聚合窗口函数也接受 DISTINCT但绝大多数数据库只允许在“不带 ORDER BY 的静态窗口”里使用写成SUM(DISTINCT x) OVER (ORDER BY y)往往直接报错这是方言差异较大的一处写之前最好在速查表里确认。3. 语法骨架OVER 里的 PARTITION BY、ORDER BY、FRAME 三件套窗口函数 90% 的困惑来自括号里的内容而不是函数名本身。完整形态是函数(参数) OVER (PARTITION BY ... ORDER BY ... 帧子句)三个部分各自解决一个问题切块、排序、划定行范围。任何一个省略都有明确的默认含义不弄清楚默认值抄来的 SQL 就会在特定数据上悄悄给出错误答案。3.1 PARTITION BY分组但不折叠PARTITION BY 的作用类似 GROUP BY但结果不合并行。它把结果集按指定列切成互不相交的块每个块单独参与计算块与块之间互不可见。-- 对比GROUP BY 折叠行窗口函数不折叠 SELECT dept_id, COUNT(*) AS cnt FROM emp GROUP BY dept_id; -- 每个部门一行 SELECT dept_id, emp_id, COUNT(*) OVER (PARTITION BY dept_id) AS cnt -- 每行都带部门人数 FROM emp;省略 PARTITION BY 时整个结果集视为一个窗口这一点在多维报表里最容易出事本想算“每个门店的排名”忘了写 PARTITION BY就变成了全国排名数字看着合理但业务含义全错。反过来分区列选得越细窗口越小、计算越快所以优先用区分度高且过滤条件里已有的列。3.2 ORDER BY决定排名与默认帧OVER 里的 ORDER BY 和语句末尾的 ORDER BY 是两回事前者只影响窗口内的计算顺序不保证最终输出顺序。它有两个副作用一是给排名函数提供排名依据二是让聚合窗口从“整块汇总”变成“累计汇总”因为一旦有 ORDER BY默认帧就变成从块首到当前行。很多人写SUM(x) OVER (PARTITION BY k ORDER BY t)本意是求组内总和结果拿到的是逐行累加原因就在这里。想两者兼得就把 ORDER BY 留着、显式写出完整帧。3.3 FRAMEROWS 与 RANGE 的分界以及默认帧规则帧子句是整张速查表里信息密度最高的一行ROWS BETWEEN 2 PRECEDING AND CURRENT ROW。它由三部分组成——单位ROWS / RANGE / GROUPS、起点、终点。写法含义ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从块首累加到当前行最常用的累计口径ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING当前行前后各一行共 3 行ROWS BETWEEN 6 PRECEDING AND CURRENT ROW7 行移动窗口RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING整个分区等价于不写帧RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW按时间值滑动 7 天支持时间类型时ROWS 按物理行数数RANGE 按 ORDER BY 列的值数。数据没有重复值时两者结果一致一旦有并列值就分岔RANGE 会把所有“值相同”的行一起纳入或排除ROWS 则是老老实实数行。做“按天累计”时如果一天有多条记录RANGE 给出的是当天结束时的累计ROWS 给出的是逐条累加业务口径差别很大。默认帧规则要背下来有 ORDER BY 时默认RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW没有 ORDER BY 时默认整个分区。前者是 LAST_VALUE 翻车的根源后者是“忘了写 ORDER BY 导致累计变总和”的根源。3.4 命名窗口重复窗口的简写同一个查询里多次使用完全相同的窗口定义时可以用 WINDOW 子句起名减少重复也减少写错的机会。SELECT dept_id, emp_id, amount, SUM(amount) OVER w AS dept_total, AVG(amount) OVER w AS dept_avg, RANK() OVER (w ORDER BY amount DESC) AS rk FROM emp WINDOW w AS (PARTITION BY dept_id);命名窗口可以在引用时追加 ORDER BY但不能覆盖已有的 PARTITION BY。这个写法在 PostgreSQL、MySQL 8.0 起都支持SQL Server 不支持跨库迁移时要注意。实际收益不算大但在一个 SELECT 里出现四五个相同分区时可读性提升明显也避免了“复制粘贴时漏改一处”的低级错误。4. 执行顺序窗口函数为什么进不了 WHERE以及怎么绕写完窗口函数最常遇到的报错是“窗口函数不允许出现在 WHERE 子句中”。这不是某家的限制而是逻辑执行顺序决定的WHERE 阶段行还没排好序、也没分好区窗口根本不存在。理解这一点所有绕行写法都能自己推出来不用死记。4.1 逻辑执行顺序里窗口函数排在哪儿标准顺序大致是FROM → WHERE → GROUP BY → HAVING → 窗口函数在 SELECT 列表里求值→ DISTINCT → ORDER BY → LIMIT。窗口函数处在 SELECT 求值阶段比 WHERE、GROUP BY、HAVING 都晚。-- 错误写法WHERE 里直接引用窗口函数 SELECT emp_id, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn FROM sales WHERE rn 10; -- 报错rn 在这一步还不存在这条 SQL 失败的逻辑很直白WHERE 执行时 SELECT 列表还没算rn 这个别名压根没有。同理窗口函数也不能出现在 HAVING 里因为 HAVING 在窗口之前。但它可以出现在 ORDER BY 里因为 ORDER BY 在 SELECT 之后。4.2 包一层子查询与 CTE 过滤排名结果标准解法是把带窗口函数的查询整体当成一张表在外层过滤。子查询和 CTE 都能做到CTE 可读性更好尤其是需要多次引用同一份排名结果时。-- 写法一子查询 SELECT * FROM ( SELECT emp_id, dept_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY amount DESC, emp_id) AS rn FROM sales ) t WHERE t.rn 1; -- 写法二CTE逻辑更清晰 WITH ranked AS ( SELECT emp_id, dept_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY amount DESC, emp_id) AS rn FROM sales ) SELECT emp_id, dept_id FROM ranked WHERE rn 1;注意一个细节过滤条件写在外层意味着窗口函数是在全量数据上算完再筛的。如果内层能先加 WHERE 把无关数据剔掉比如只保留近三个月排序和分区的数据量会小很多这是最省事的性能优化往往比调索引还直接。4.3 QUALIFY、方言差异与替代写法部分数据库提供了 QUALIFY 子句专门用来过滤窗口函数结果省掉一层嵌套。它在 Snowflake、BigQuery、Teradata 等产品里可用PostgreSQL、MySQL、SQL Server 目前没有直接写会报语法错误。跨库时不要假设它存在子查询写法兼容性最好。能力支持情况窗口函数基本语法主流数据库较新版本均支持老版本需确认QUALIFY 过滤部分分析型数据库支持通用性差NULLS FIRST / LAST多数支持MySQL 需用ISNULL()之类的变通写法模拟GROUPS 帧单位支持面较窄写之前查速查表命名窗口 WINDOWPostgreSQL、MySQL 8.0 起支持SQL Server 不支持方言差异这一块速查表的价值就体现出来了同一个需求在不同库里可能要靠不同写法实现尤其是 NULL 排序和帧单位凭记忆写十有八九要返工。5. 易漏点五个最容易翻车的地方窗口函数的语法学会了不等于用对了下面五条是梳理下来发现频率最高的错误共同特点是“不报错、结果看着合理、但对不上账”。5.1 易漏点一ORDER BY 缺唯一 tie-breakerROW_NUMBER 结果不稳定为什么漏测试数据里金额恰好都不重复跑出来完全正确上线后出现并列值同一份报表两次刷新结果不同排查半天以为是缓存问题。怎么补凡是 ROW_NUMBERORDER BY 里强制补一个唯一列主键、ID、时间戳作为第二排序键。这条当成硬规矩执行宁可多写一个字段。分页查询同理只按创建时间排序翻页时同一秒创建的多条记录会在页与页之间重复或丢失加上主键才能稳定。5.2 易漏点二LAST_VALUE 默认帧只到当前行为什么漏名字叫 LAST_VALUE直觉上以为是“整个分区最后一个值”实际默认帧是“块首到当前行”所谓最后一行就是当前行返回值等于本行自己的值看起来像函数没生效。怎么补显式写全帧ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。如果只是想取分区末行的某个值用 FIRST_VALUE 配倒序排序往往更省事也少一个出错点。-- 错误默认帧到当前行last_amount 恒等于 amount SELECT emp_id, amount, LAST_VALUE(amount) OVER (PARTITION BY dept_id ORDER BY emp_id) AS wrong_last FROM emp; -- 正确显式扩展到分区末尾 SELECT emp_id, amount, LAST_VALUE(amount) OVER (PARTITION BY dept_id ORDER BY emp_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS dept_last FROM emp;5.3 易漏点三RANGE 帧遇到并列值会合并计算为什么漏ROWS 和 RANGE 在无重复值的数据上结果完全一致写惯了 ROWS 的人换成 RANGE 时不觉得有差别。等到一天有多条订单按天累计的曲线就出现“同一天所有行显示同一个累计值”。怎么补先明确口径。要“逐行累加”就用 ROWS要“按值分组后的累计”同一天的多条记录共享当日结束时的累计就用 RANGE。写完之后拿一组含重复值的小数据手工验算前几行比看一百遍文档管用。5.4 易漏点四NULL 参与排序时排名函数的编号错位为什么漏NULL 在排序中的位置由数据库决定有的排最前、有的排最后而且不报错。一旦排名列里有 NULL名次分布就和预期完全不同甚至出现“第 1 名是空值”。怎么补排序时显式指定NULLS LAST或NULLS FIRST方言不支持时用 CASE 或 ISNULL 构造排序键排名前先想清楚“空值算不算参与排名”。如果业务上空值应当排除就在内层 WHERE 里过滤掉别留到窗口函数里处理。5.5 易漏点五滥用窗口函数替代 GROUP BY结果集被悄悄放大为什么漏窗口函数不折叠行写起来又比 GROUP BY 顺手于是本该聚合的场景也用了 OVER最后返回几万行明细前端只展示了一列汇总性能全耗在排序上。怎么补判断标准很简单——结果需要明细行就用窗口函数只需要每组一行就用 GROUP BY。两者也可以配合先用 GROUP BY 把数据压到需要的粒度再在外层用窗口函数算占比和排名数据量能小一个数量级。6. 复习清单一页自测表和查漏补缺的练法复习窗口函数效率最高的方式不是重读速查表而是拿一张自测表逐条过卡在哪条就回去补哪块。下面这张表是我自己用的版本右边留白写“能否一次写对”。检查项自测问题达标标准概念窗口函数和 GROUP BY 的本质区别是什么能说出“不折叠行”并举一个例子三件套OVER 里三个子句各管什么、默认值是什么能背出默认帧的两条规则排名ROW_NUMBER / RANK / DENSE_RANK 在并列时的差异能手写出 1,1,3 与 1,1,2 的对照分组 Top N每组取前 n 条怎么写能一次写对且带 tie-breaker偏移LAG 三个参数的含义、越界默认值能说清不给默认值会出什么问题帧ROWS 与 RANGE 的适用边界能举出含并列值时的差异执行顺序为什么窗口函数不能进 WHERE能画出顺序并写出两种绕行写法方言QUALIFY、NULLS LAST 的支持情况知道自己常用库支持哪些练习方法上我建议准备一张几十行的小表部门、员工、月份、金额四个字段足够然后强制自己用两种写法实现同一个需求一次用窗口函数一次用自连接或 GROUP BY 加 JOIN。写完对比结果集行数和数值差异出现的地方就是理解没到位的地方。尤其是“累计求和”“每组占比”“同环比”这三个需求几乎覆盖了 80% 的实战场景把它们的正确写法和错误写法都跑一遍比刷二十道零散题有效。查漏补缺还有个笨办法很好用把速查表里的函数按“返回位置 / 返回邻居值 / 返回聚合值”重新抄一遍抄的过程中给每个函数标一个自己业务里的真实场景。抄不出来的说明这个函数目前用不上可以先放标不出场景的说明只是记住了名字没理解用途需要回去看示例。说个自己的教训。早些年做月度报表用 RANK 取“销售额前 10 名”上线后发现某些月份返回 12 行、某些月份只有 9 行对账对了一个下午才反应过来是并列值在作祟——那个月正好有两组销售额完全相同。从那以后我每次写排名类查询都会强制走一遍三问有没有并列值、要不要唯一 tie-breaker、口径是行数还是档位。这三问花不到一分钟省下的排查时间却是按小时算的。窗口函数本身不难难的是它错得很安静所以宁可写的时候啰嗦一点也别指望上线后再补后悔药。希望帮到你。本文还有配套的精品资源点击获取