SQL窗口函数速查表：从排名到累计的实战避坑指南-北京尧图网络

简介这份《SQL窗口函数速查表》PDF面向数据库管理员、数据分析师、数据科学家及开发人员尤其适合希望提升复杂数据集查询能力的技术人员。内容按功能与用途分类系统梳理窗口函数的基本概念、语法结构与参数说明涵盖ROW_NUMBER()、RANK()、DENSE_RANK()等排名函数LEAD()、LAG()等行序号函数以及SUM、AVG、COUNT、MIN、MAX等窗口聚合函数并配有示例代码帮助理解每个函数的用法与返回结果。资源包共1个PDF文件大小约841KB轻量便携便于随时查阅与记忆。目前已有255人学习下载适合在数据探索、报表生成、应用开发及教学研究中作为快速参考工具帮助读者在处理复杂数据集时编写更高效的查询语句。1. 窗口函数速查表为什么你背了语法还是写不出正确的排名很多人第一次接触 SQL 窗口函数是在面试里被问「怎么查每个部门工资前三名」。背了ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)的模板面试能过真到业务里写月度报表结果对不上——排名重复、累计求和跳数、同一天多笔订单排序随机。问题不在语法在于没搞清「窗口」到底怎么切、函数在切完的框里怎么算。这份速查表要解决的就是这件事把窗口函数按「排序类、聚合类、偏移类」三组拆开每组给出可直接抄的 SQL、参数含义、以及结果不符合预期时该看哪个参数。适合已经会写GROUP BY、但一遇到「组内排名」「行间比较」「滑动累计」就卡住的开发和分析人员。下面所有例子基于一张虚构的订单表orders字段为order_id, dept, sale_date, amount你可以直接替换成自己的表名跑通。2. 窗口函数的三块拼图OVER 子句到底在切什么窗口函数和普通聚合函数最大的区别是聚合函数把多行压成一行窗口函数保留每一行只是在每行旁边多算一列。这个「多算的一列」由OVER()里的内容决定。理解OVER()就理解了窗口函数。2.1 PARTITION BY 决定「跟谁比」PARTITION BY把结果集按某列切成若干独立的小组函数在每个小组内部单独计算。不写PARTITION BY整张表就是一个大组。-- 每个部门内部按销售额排名部门之间互不干扰 SELECT order_id, dept, amount, ROW_NUMBER() OVER ( PARTITION BY dept -- 按部门分组组内独立编号 ORDER BY amount DESC -- 组内按金额从高到低 ) AS rn FROM orders;逻辑说明PARTITION BY dept先把数据按部门切开ORDER BY amount DESC在每个部门内部排序ROW_NUMBER()从 1 开始逐行编号。参数上PARTITION BY可以跟多列比如PARTITION BY dept, YEAR(sale_date)表示「同部门同年度」才算一组。这里最常见的翻车是忘了写PARTITION BY结果全表一起排名每个部门的第一名只有一个。2.2 ORDER BY 决定「怎么排」也决定默认的窗口边界ORDER BY不只是排序它还悄悄改变了聚合窗口的默认范围。带ORDER BY时SUM()、AVG()这类聚合默认从组内第一行累加到当前行不带ORDER BY时默认是整个分组。-- 带 ORDER BY累计求和逐行递增 SELECT order_id, dept, sale_date, amount, SUM(amount) OVER ( PARTITION BY dept ORDER BY sale_date ) AS running_total FROM orders; -- 不带 ORDER BY整个部门的总和每行都一样 SELECT order_id, dept, amount, SUM(amount) OVER (PARTITION BY dept) AS dept_total FROM orders;逻辑说明第一条 SQL 里ORDER BY sale_date让窗口变成「从组内第一行到当前行」所以running_total是累计值。第二条没有ORDER BY窗口是整个分区dept_total每行都等于该部门总额。参数上如果同一天有多笔订单累计求和的顺序会不确定需要加第二排序键比如ORDER BY sale_date, order_id否则结果每次跑可能不一样。2.3 ROWS / RANGE 决定「窗口滑多大」默认窗口是「从第一行到当前行」但做移动平均、前后三行对比时需要显式指定窗口范围。ROWS按物理行数算RANGE按排序值算两者差别很大。-- 最近 3 行移动平均按物理行 SELECT order_id, sale_date, amount, AVG(amount) OVER ( ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS ma_3rows FROM orders; -- 按日期值范围当前行及前 2 天内的所有行 SELECT order_id, sale_date, amount, AVG(amount) OVER ( ORDER BY sale_date RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW ) AS ma_3days FROM orders;逻辑说明ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示窗口包含当前行和它前面的两行不管日期差多少。RANGE版本则按sale_date的值算包含当前日期往前推 2 天内的所有行同一天多笔订单会一起进窗口。参数上UNBOUNDED PRECEDING表示从组内第一行开始UNBOUNDED FOLLOWING表示到最后一行CURRENT ROW是当前行。选ROWS还是RANGE取决于你要的是「固定行数」还是「固定时间范围」。3. 排序类函数ROW_NUMBER、RANK、DENSE_RANK 到底选哪个这三个函数长得像结果在遇到并列值时完全不同。选错一个报表里的「前三名」可能变成五个人。3.1 三个排名函数的并列行为对比用一组有重复金额的数据直接看差异-- 构造并列场景部门 A 的金额为 100, 100, 90, 80 SELECT order_id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn, RANK() OVER (ORDER BY amount DESC) AS rk, DENSE_RANK() OVER (ORDER BY amount DESC) AS dr FROM orders WHERE dept A;结果对照amountROW_NUMBERRANKDENSE_RANK1001111002119033280443逻辑说明ROW_NUMBER不管并列强行给不同编号适合「去重取一条」RANK并列后跳号适合「并列第一下一名是第三」的体育排名DENSE_RANK并列后不跳号适合「等级划分」。参数上没有额外配置关键在ORDER BY的列是否会产生重复值。如果ORDER BY的列唯一三者结果完全一样。3.2 组内 TopN 的标准写法与去重陷阱查「每个部门销售额前 3 名」标准做法是用子查询或 CTE 包一层再过滤排名。WITH ranked AS ( SELECT order_id, dept, amount, ROW_NUMBER() OVER ( PARTITION BY dept ORDER BY amount DESC, order_id -- 加 order_id 保证排序稳定 ) AS rn FROM orders ) SELECT * FROM ranked WHERE rn 3;逻辑说明窗口函数不能直接写在WHERE里因为WHERE执行顺序在窗口函数之前所以必须用 CTE 或子查询先算出排名外层再过滤。参数上ORDER BY amount DESC, order_id里的第二排序键很重要如果两笔订单金额相同不加order_id每次跑出来的「前 3 名」可能不是同一批数据。如果业务允许并列都保留把ROW_NUMBER换成DENSE_RANK过滤条件改成dr 3。3.3 用 NTILE 做等频分箱的坑NTILE(n)把分区内的行尽量平均分成 n 桶常用于用户分层、百分位分析。SELECT order_id, amount, NTILE(4) OVER (ORDER BY amount DESC) AS quartile FROM orders;逻辑说明NTILE(4)按金额从高到低切成 4 桶第 1 桶是金额最高的 25%。参数上如果总行数不能被 4 整除前面的桶会多一行比如 10 行分 4 桶结果是 3、3、2、2。这里容易翻车的地方是NTILE的桶边界按行数算不按金额值算所以金额分布极不均匀时同一桶内可能差距巨大。要做等值分箱得用CASE WHEN手动切。4. 聚合与偏移类函数累计、环比、同环比一次写对排序类解决「第几名」聚合和偏移类解决「跟前后行比」「累计到当前是多少」。这两类在报表里出现频率最高也最容易因为窗口边界写错导致数字对不上。4.1 累计求和与移动平均的窗口写法累计求和用SUM() OVER (ORDER BY ...)移动平均用AVG() OVER (... ROWS BETWEEN ...)。关键是确认「累计」是按什么顺序累。SELECT order_id, dept, sale_date, amount, -- 部门内按日期累计 SUM(amount) OVER ( PARTITION BY dept ORDER BY sale_date, order_id ) AS dept_running_total, -- 全表按日期累计 SUM(amount) OVER ( ORDER BY sale_date, order_id ) AS global_running_total FROM orders;逻辑说明第一条加了PARTITION BY dept每个部门单独累计部门切换时重新从零开始。第二条没有分区全表一条累计线。参数上ORDER BY sale_date, order_id保证同一天多笔订单有确定顺序否则累计结果不稳定。如果业务要求「按日期累计同一天算在一起」把ROWS换成RANGE或者先按日期聚合再累计。4.2 LAG / LEAD 做环比偏移量怎么设LAG取当前行往前第 n 行的值LEAD取往后第 n 行。做环比、同比时偏移量决定跟哪一行比。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, ROUND( (amount - LAG(amount, 1) OVER (ORDER BY sale_date)) / NULLIF(LAG(amount, 1) OVER (ORDER BY sale_date), 0) * 100, 2 ) AS growth_pct FROM daily_sales ORDER BY sale_date;逻辑说明LAG(amount, 1)取上一行的金额第二个参数是偏移量默认 1。NULLIF(..., 0)防止除零。参数上LAG还支持第三个参数作为默认值比如LAG(amount, 1, 0)第一行没有上一行时返回 0 而不是 NULL。做同比时如果数据是按天排的偏移量要设成 365 或按业务周期调整但更稳妥的做法是先按周期聚合再用LAG比。4.3 FIRST_VALUE / LAST_VALUE 的默认窗口陷阱FIRST_VALUE取窗口内第一行的值LAST_VALUE取最后一行的值。LAST_VALUE有个经典坑默认窗口是「到当前行」所以它返回的往往是当前行自己。SELECT order_id, dept, amount, FIRST_VALUE(amount) OVER ( PARTITION BY dept ORDER BY amount DESC ) AS dept_max, LAST_VALUE(amount) OVER ( PARTITION BY dept ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS dept_min FROM orders;逻辑说明FIRST_VALUE默认窗口从组内第一行到当前行取第一行就是组内最大值没问题。LAST_VALUE如果不加ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING窗口只到当前行取最后一行就是当前行结果全错。参数上凡是LAST_VALUE几乎都要显式写完整窗口范围。这个坑我在第一次写部门最高最低工资对比时踩过排查了半天才发现是窗口边界问题。5. 避坑与排查窗口函数结果不对时先看这五处窗口函数写错SQL 不一定报错但数字会悄悄错。下面五条是实际排查中最常遇到的。5.1 排名结果每次跑不一样现象同一个查询两次执行返回的「前 3 名」订单号不同。原因ORDER BY的列有重复值数据库没有稳定排序并列行的顺序随机。解决在ORDER BY里加一个唯一列作为决胜键比如ORDER BY amount DESC, order_id。如果业务上并列都要保留改用DENSE_RANK并调整过滤条件。5.2 累计求和到某一行突然跳变现象running_total在某个日期突然多了一大截。原因同一天有多笔订单ORDER BY只写了日期同一天内的累计顺序不确定或者分区键写错导致跨部门累计。解决补上第二排序键order_id并检查PARTITION BY是否覆盖了所有分组维度。如果业务要求同一天合并计算先用子查询按天聚合再累计。5.3 LAST_VALUE 返回的是当前行现象LAST_VALUE算出来的「组内最后一名」跟当前行金额一样。原因默认窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW最后一行就是当前行。解决显式写ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。这个坑几乎每个用LAST_VALUE的人都会踩一次。5.4 窗口函数写在 WHERE 里报错现象WHERE ROW_NUMBER() OVER (...) 3直接语法错误。原因WHERE的执行顺序在窗口函数之前数据库还没算排名就开始过滤。解决用 CTE 或子查询包一层外层再过滤排名列。如果只是想去重取一条也可以用QUALIFY部分数据库支持但跨库兼容性不如子查询。5.5 分区键选错导致组内数据混入其他组现象部门排名里出现了别的部门的订单。原因PARTITION BY的列有 NULL 值或者分区键跟业务分组维度不一致。解决检查分区列是否有 NULL必要时用COALESCE(dept, 未知)兜底确认分区键跟GROUP BY的业务口径一致。如果业务上需要按多个维度分组PARTITION BY要写全比如PARTITION BY dept, region。6. 把速查表用成肌肉记忆我的三个固定习惯窗口函数写多了会发现真正省时间的不是背函数名而是固定几个检查动作。我自己的习惯是先写PARTITION BY再写ORDER BY最后补窗口范围排名类先问「并列要不要保留」再决定用哪个函数聚合类先确认「累计还是全量」再决定加不加ORDER BY。一个具体技巧是把常用窗口函数写成一段模板改表名和列名就能跑。比如下面这个「组内 TopN 累计占比」的组合在报表里复用率极高。WITH base AS ( SELECT dept, order_id, amount, SUM(amount) OVER (PARTITION BY dept) AS dept_total FROM orders ), ranked AS ( SELECT dept, order_id, amount, dept_total, ROW_NUMBER() OVER ( PARTITION BY dept ORDER BY amount DESC, order_id ) AS rn, SUM(amount) OVER ( PARTITION BY dept ORDER BY amount DESC, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cum_amount FROM base ) SELECT dept, order_id, amount, dept_total, ROUND(cum_amount / NULLIF(dept_total, 0) * 100, 2) AS cum_pct FROM ranked WHERE rn 5 ORDER BY dept, rn;逻辑说明第一层 CTE 算出每个部门总额第二层同时算排名和累计金额累计窗口显式写了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW保证按金额降序累加外层过滤前 5 名并算累计占比。参数上NULLIF(dept_total, 0)防止部门总额为零时除零报错。这段模板改掉dept、amount、orders就能套到大部分「组内排名占比」场景。验证方法上我一般会拿一个部门的手工计算结果跟 SQL 结果对一遍重点看并列值、边界行和累计跳变点。如果对不上先查ORDER BY是否唯一再查窗口范围是否写全。这个习惯帮我省掉了大量「数字看起来差不多但就是不对」的排查时间。最后说个血泪教训窗口函数最怕的不是不会写是写完了不验证。有一次做月度累计报表ORDER BY少写了一个决胜列结果同一天多笔订单的累计顺序随机报表数字每天跑出来都不一样被业务追着问了一周。从那以后凡是带ORDER BY的窗口函数我都会问自己一句排序列唯一吗不唯一就补决胜键。希望帮到你。本文还有配套的精品资源点击获取

SQL窗口函数速查表：从排名到累计的实战避坑指南

相关推荐

AI机器人PPT模板制作全流程：从主视觉到脚本生成

2027 冷面星球 —— 烤冷面外卖店铺智能网站

STM32按键控制LED从GPIO输入模式到消抖的完整工程实践

更多资讯

国内GEO五强服务商实力盘点：2026年10月排行榜与企业选型参考

CWR图形引擎源码解析:OpenGL 3.3后端、渲染队列与纹理加载机制全解

Vivado雷达LFM脉冲压缩：匹配滤波FPGA实现与工程避坑指南

cs336_note_and_hw笔记体系导览：6章PDF学习路线与Llama-3.1-8B实验环境搭建清单

3559A开发环境搭建全攻略：从工具链到烧录的避坑指南

安卓跳动频谱播放器：Visualizer+FFT+均衡器联动方案

今日更新

小波包能量谱原理与MATLAB实现：从频带分解到故障特征提取

基于LSTM的网易云音乐评论情感分析：数据清洗与模型调优实战

Alpha-Beta剪枝与极大极小值算法：博弈树搜索原理与实战优化

本周精选

交通拥堵预测毕设：基于Python+LSTM的车辆流量预测系统全流程解析

MATLAB/Simulink搭建10机39节点电力系统暂态稳定仿真实战

无人机航拍三维重建全流程：从SfM到网格生成的避坑指南

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？