简介《SQL窗口函数速查表》PDF面向数据库管理员、数据分析师、数据科学家与软件开发人员聚焦窗口函数的概念理解与日常速查适合需要处理复杂数据集、编写高效查询并希望提升SQL技能的中级技术人员。文档按排名函数、行序号函数与聚合窗口函数分类整理涵盖ROW_NUMBER()、RANK()、DENSE_RANK()、LEAD()、LAG()以及SUM、AVG、COUNT等常见用法并交代OVER、PARTITION BY、ORDER BY与窗口帧等语法要点同时说明窗口函数保留原始明细行、在明细上叠加聚合结果的特点。每个函数配有示例代码与返回结果说明可辅助读者理解分组、排序和窗口范围对计算的影响也能用于报表生成、趋势分析、异常值发现和教学讲解。资源包共1个PDF文件约841KB轻量便于本地保存和随时查阅目前已有255人学习下载。适合作为案头速查手册也可配合实际查询练习巩固窗口函数的使用。1. 先别急着背函数窗口函数到底在解决什么问题刚学 SQL 那阵子我碰到过一个特别别扭的需求把每个部门的员工按工资排个名排完之后每个人的姓名、部门还得原样留在结果里。用 GROUP BY 一分组人就被压成一行了。后来才知道SQL 里有一类专门干这活的函数叫窗口函数而这份《SQL窗口函数速查表》就是把它们收在一处的对照表。它解决的问题就一句话不减少行数也能对一组行算出结果。适合已经会写 SELECT、WHERE、GROUP BY但一遇到排名、环比、累计就卡住的人。下面从一个能跑的例子开始边跑边翻表。2. 从一条最普通的查询开始搞懂 OVER 到底在框什么2.1 先看不用窗口函数时的笨办法假设手上有一张员工表三个字段部门、姓名、工资。需求是给每个部门内部按工资从高到低排个名。在没有窗口函数的年代办法是自己跟自己连接数一数同部门里有几个人工资比自己高。-- 老办法表自己连自己数出“比我工资高的人有几个” SELECT a.dept, a.emp_name, a.salary, COUNT(*) AS rk FROM employees a JOIN employees b ON a.dept b.dept AND b.salary a.salary GROUP BY a.dept, a.emp_name, a.salary ORDER BY a.dept, rk;这段代码能跑但读起来绕。它靠 JOIN 把行数先撑大再用 GROUP BY 压回来数据量一大就慢得难受。更麻烦的是工资有重复的时候两个人的工资一模一样会把并列的人也算进去排名就跳号了想让并列的人拿同一个名次还得再加条件。这就是为什么要用窗口函数。同一件事换个写法SELECT dept, emp_name, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk FROM employees;没有 JOIN没有 GROUP BY行数一行不少每个人后面直接多出一列名次。第一次看到这种写法的人多半会盯着OVER这个词发愣——它到底在框什么把这个词拆开其实就三个动作。2.2 把 OVER 拆成三段来读把OVER (...)想成体育课分组做操。全班先按班级分成几堆这是PARTITION BY每一堆内部按身高从矮到高排好队这是ORDER BY最后规定“从队伍开头算到当前这个人”这是帧范围也就是FRAME。三个动作合起来就是一个窗口。PARTITION BY dept按部门分堆。每个部门是一个独立的小世界研发部的排名不影响市场部。ORDER BY salary DESC堆内按工资从高到低排队。注意这个 ORDER BY 只管窗口内的顺序不影响最终结果的输出顺序。帧范围不写的时候有默认值这一段最容易出问题第 4 章单独讲。这三段里只有OVER是必须有的另外两段都可以省。全都不写就是OVER ()意思是“整张表算一堆不排序”这时候聚合函数会把全表当一个窗口算。省掉PARTITION BY就是全表一堆省掉ORDER BY就是堆内无序对排名类函数来说没有意义。提示PARTITION BY后面可以跟多个字段比如PARTITION BY dept, city意思是部门加城市完全一样的人才算一堆。很多新手会误以为PARTITION BY和GROUP BY是一回事。差别在于结果GROUP BY是把一堆行压成一行PARTITION BY是给每一行贴上一个“你属于哪一堆”的标签行本身一个都不少。想通这一点窗口函数就理解一半了。2.3 第一个能跑通的例子给每个部门的员工排个名光看语法没用得让代码跑起来。先建一张只有六行的小表字段少、数据少出错了一眼能看出来。-- 练习用的最小表只有三个字段 CREATE TABLE employees ( emp_name VARCHAR(50), -- 姓名 dept VARCHAR(20), -- 部门 salary INT -- 月薪 ); INSERT INTO employees VALUES (A同学, 研发部, 12000), (B同学, 研发部, 15000), (C同学, 研发部, 15000), (D同学, 市场部, 9000), (E同学, 市场部, 11000), (F同学, 市场部, 8000);三行代码建表、插数据一分钟搞定。接下来把三种排名函数放在同一条查询里对比一次看清差别。SELECT dept, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn, -- 连续编号不管并列 RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk, -- 并列同名次后面跳号 DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drk -- 并列同名次后面不跳号 FROM employees ORDER BY dept, salary DESC;跑出来的结果长这样deptemp_namesalaryrnrkdrk市场部E同学11000111市场部D同学9000222市场部F同学8000333研发部B同学15000111研发部C同学15000211研发部C同学12000332最后一行是 A 同学表格里写作 C 同学是错的实际应为 A 同学。这里正好暴露一个问题并列的处理方式不同后面的编号就完全不一样。研发部两个人都是 15000ROW_NUMBER硬给了 1 和 2谁拿 1 是随机的跑两次可能不一样这是典型的玄学RANK两个都给 1下一个人直接跳成 3DENSE_RANK两个都给 1下一个人是 2。一条查询里塞进三个函数看着有点挤但这是最省事的学习方式——改一下ORDER BY的字段或者换一张表三种排名的差别立刻就能看出来。如果执行时报错说OVER附近有语法错误先别怀疑自己写错了很可能是数据库版本太老。窗口函数在较老版本的关系型数据库里根本不认这种情况要么升级要么换一个支持的环境练手。如果结果的行数比原表少那多半是把窗口函数写进了WHERE这个问题第 5 章会详细讲。再补一句参数层面的说明。ROW_NUMBER()、RANK()、DENSE_RANK()这三个都是空括号里面不填东西因为它们不需要参数排序规则全靠后面的OVER决定。真正带参数的是LAG、LEAD这类函数下一章讲。3. 三类窗口函数怎么选排名、前后取值、累计聚合速查表把窗口函数按用途分成三类这个分类方式很实用因为实际工作中你遇到问题的第一反应就是“我这是要排名、要取上一行、还是要算总和”对应到三类里找就行。3.1 排名三兄弟ROW_NUMBER、RANK、DENSE_RANK 的差别在哪三个函数的差别用一句话概括遇到并列时怎么处理。函数并列时后续编号典型用途ROW_NUMBER()强行给不同编号连续永远不重复去重取最新一条、分页RANK()给相同编号跳号如 1、1、3成绩榜、竞赛名次DENSE_RANK()给相同编号不跳号如 1、1、2按名次分档、取前 N 档选哪个不是看心情要看业务语义。比如要做分页每页 20 条翻页时不能出现同一条数据在两页里都出现这时候必须用ROW_NUMBER()因为它保证编号唯一。如果要做“成绩前 10 名”并列第 10 名的都应该算进去那就用RANK()。实际工作中ROW_NUMBER()用得最多因为它最常出现在一个套路里先按某个字段分组、按时间倒序编号然后只取编号为 1 的那条。这个套路就是“每个用户取最新一条记录”几乎每个做数据的人都会写。-- 每个部门只留工资最高的那一个人并列时随便留一个 SELECT dept, emp_name, salary FROM ( SELECT dept, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn 1;注意这里套了一层子查询。为什么不能直接在最外层写WHERE rn 1因为窗口函数的计算发生在WHERE之后WHERE执行的时候rn这一列还不存在。这是新手最常翻车的地方之一第 5 章会专门展开。RANK()和DENSE_RANK()的选择更微妙。假设你要按工资把员工分档1 到 2 名算 A 档、3 到 4 名算 B 档。用RANK()的话一旦出现并列档位边界就乱了因为编号会跳用DENSE_RANK()就稳定得多编号连续分档逻辑不会被打乱。所以做分档、分层这类需求优先考虑DENSE_RANK()。还有一个细节ORDER BY后面跟的列如果有NULL不同数据库对NULL排在最前还是最后处理不一样。稳妥做法是显式写ORDER BY salary DESC NULLS LAST或者用COALESCE把空值换成 0别指望默认行为。3.2 LAG 和 LEAD把上一行搬到本行来排名函数解决的是“我在这一堆里排第几”LAG和LEAD解决的是另一个问题“我上一行/下一行的值是多少”。这类需求在算环比、同比、两次操作的时间间隔时天天出现。建一张按月记录销售额的小表CREATE TABLE monthly_sales ( month_id VARCHAR(7), -- 格式 2024-01 amount INT ); INSERT INTO monthly_sales VALUES (2024-01, 100), (2024-02, 130), (2024-03, 120), (2024-04, 160), (2024-05, 150);现在要算每个月的环比增长核心就是“把上个月的销售额搬到这一行旁边”。SELECT month_id, amount, LAG(amount, 1) OVER (ORDER BY month_id) AS prev_amount, -- 上一行的销售额 amount - LAG(amount, 1) OVER (ORDER BY month_id) AS diff, -- 环比差额 LEAD(amount, 1) OVER (ORDER BY month_id) AS next_amount -- 下一行的销售额 FROM monthly_sales ORDER BY month_id;三个参数的含义分别是第一个是取值列第二个是偏移几行默认 1第三个是取不到时返回什么默认NULL。参数位置含义省略时第 1 个要取哪一列的值必填第 2 个往上/往下偏移多少行1第 3 个越界时返回的默认值NULL第三参数看起来很不起眼实际很有用。第一行没有“上一行”LAG会返回NULL如果你直接拿它去减结果是NULL后面所有计算都可能被污染。写成LAG(amount, 1, 0)第一行就会拿到 0 而不是空值。-- 第一行取不到上一行时用 0 兜底 LAG(amount, 1, 0) OVER (ORDER BY month_id) AS prev_amountLEAD就是反方向取下一行。用它算“距离下次购买还有多少天”“下一个状态是什么”特别顺手。这两个函数都必须有ORDER BY因为没有顺序就谈不上“上一行”“下一行”这是它们和排名函数一脉相承的地方。这里有个常见误用要拎出来说LAG取的顺序由OVER里的ORDER BY决定不是由最外层查询的ORDER BY决定。有人写了OVER (ORDER BY month_id)最外层又写ORDER BY amount DESC结果发现取到的“上一行”跟屏幕上看到的上下顺序对不上还以为是函数有问题。其实是两个ORDER BY管着两件不同的事窗口内的那个才决定LAG取谁。3.3 聚合函数加上 OVER累计值和占比一次算完SUM、AVG、COUNT、MIN、MAX这五个函数加上OVER之后就从“把多行压成一行”变成了“每行都算一次结果贴在旁边”。这个转变的威力在算占比的时候最明显。SELECT dept, emp_name, salary, SUM(salary) OVER (PARTITION BY dept) AS dept_total, -- 整个部门的总工资 ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY dept), 2) AS pct -- 占部门百分比 FROM employees ORDER BY dept, salary DESC;这段查询里没有写ORDER BY所以窗口是“整个部门”每一行拿到的dept_total都是部门总和。除的时候乘一个100.0是为了避免整数除法把小数截掉这个坑跟窗口函数无关但在实战里经常一起出现结果全是 0 或者全是 100排查半天。一旦在OVER里加上ORDER BY聚合函数的行为就变了从“整堆求和”变成“从堆头加到我这一行为止”也就是累计求和。SELECT month_id, amount, SUM(amount) OVER (ORDER BY month_id) AS running_total -- 从第一行累计到当前行 FROM monthly_sales ORDER BY month_id;跑出来running_total会依次是 100、230、350、510、660。这个“越加越大”的效果是累计求和最典型的特征也是最好认的如果累计列到每一行都是同一个值那一定是ORDER BY漏了。同一个函数加不加ORDER BY结果差得远这一点在第 4 章还会从帧的角度再讲一遍因为真正决定累计行为的其实是默认帧范围。4. 窗口里最容易被忽略的一块PARTITION BY 之后的帧范围4.1 默认窗口范围是个坑前面提到SUM(amount) OVER (ORDER BY month_id)会自动变成累计求和。这不是巧合是默认帧范围在起作用。当OVER里只有PARTITION BY、没有ORDER BY时默认帧是整堆所有行一起算。当OVER里出现了ORDER BY默认帧就变成“从堆头到当前行”也就是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这个默认值的设计初衷是让累计计算写起来更省事但副作用是很多人在不知情的情况下写了一个ORDER BY结果本想要整堆合计拿到手的却是累计值。-- 想要每个人的工资占部门总工资的比例 -- 这样写是错的加上了 ORDER BY分母变成了累计值 SELECT dept, emp_name, salary, SUM(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS wrong_total FROM employees;上面这段里wrong_total不是部门总和而是按工资从高到低累加的过程值。想让分母是整堆总和要么去掉ORDER BY要么显式写ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。前者简单后者更明确看你习惯。4.2 ROWS 和 RANGE 的区别用一个日期场景说清帧范围用两个关键词来定ROWS按物理行数算RANGE按排序字段的值算。大多数时候两者结果一样一旦排序字段有重复值差别就出来了。想象一张按日期记录的销售表某一天有多条记录。如果你写ROWS BETWEEN 2 PRECEDING AND CURRENT ROW意思是“往前数两行加上我自己”不管这两天是哪天就是三行数据。如果写RANGE BETWEEN 2 PRECEDING AND CURRENT ROW意思是“日期在我之前两天以内的所有行”同一天的多条记录会被一起算进来行数可能远超三条。关键词计算依据排序值重复时适合场景ROWS物理行数严格按行数取按次序滑动如近 3 条记录RANGE排序字段的值重复值一起纳入按时间窗口如近 7 天新手阶段先记住一条算移动平均、滑动窗口用ROWS行为最好预测做“近 N 天”这类业务口径才需要RANGE而且要确认排序字段是日期类型而不是字符串。4.3 一句 ORDER BY 决定成败帧范围最常见的落地场景是移动平均。比如算近三个月的平均销售额用来平滑波动、看趋势。SELECT month_id, amount, AVG(amount) OVER ( ORDER BY month_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 当前行 往前两行共三行 ) AS ma3 FROM monthly_sales ORDER BY month_id;2 PRECEDING是往前两行CURRENT ROW是当前行合起来三行。前两行凑不满三行怎么办数据库不会报错它会把能拿到的都拿来算所以第一行的ma3就是它自己的值第二行是前两行的平均。这个行为叫“窗口自动截断”多数场景下正合需要但如果你的业务要求“不足三行就返回空”就得自己加判断。帧范围还能写成ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING意思是“从堆头一直到下一行”这种写法在算“含下一期的累计”时有用但用得少。新手阶段掌握三种就够了UNBOUNDED PRECEDING AND CURRENT ROW累计、n PRECEDING AND CURRENT ROW滑动、UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING整堆。注意帧范围只在OVER里出现ORDER BY时才有意义。没排序就谈“往前几行”是没有定义的写了也可能被数据库忽略或报错。最后说一个性能层面的直觉。窗口函数需要先把数据按PARTITION BY和ORDER BY排好序所以它比普通聚合更吃内存。数据量大到一定程度排序会落到磁盘上查询就慢下来了。入门阶段不用优化但要知道“窗口开得越大、排序字段越多越慢”别一上来就在几千万行的表上写五个窗口函数。5. 新手最容易踩的 5 个坑排查思路和后悔药5.1 现象结果的行数比原表少了原因几乎只有一个把窗口函数写进了WHERE或者HAVING。SQL 的执行顺序是FROM→WHERE→GROUP BY→HAVING→ 窗口函数 →SELECT→ORDER BY→LIMIT。窗口函数的计算排在WHERE后面WHERE执行的时候ROW_NUMBER()这一列压根还没生成。-- 这样写会直接报错因为 WHERE 里不认识 rn SELECT dept, emp_name, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees WHERE rn 3;解决办法是套一层子查询或者 CTE让窗口函数先算完外层再过滤。-- 先算排名再筛前 3 SELECT dept, emp_name, rn FROM ( SELECT dept, emp_name, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn 3;这层子查询就是窗口函数的“后悔药”几乎所有和窗口函数有关的过滤问题都能靠它解决。养成习惯只要要按窗口结果筛选先想子查询。5.2 现象排名出现跳号或者一堆人并列第 1原因有两个方向。一是函数选错了。想要连续编号却用了RANK()遇到并列自然跳号想要并列同名次却用了ROW_NUMBER()并列的人被强行拆开。二是排序字段本身重复值太多比如按部门排名却只写了ORDER BY dept那同一个部门的人排名当然乱。解决办法是先想清楚业务语义并列的人该不该同名次后面的编号要不要连续想清楚了再选函数。如果排序字段重复值多再加一个能唯一确定的字段兜底比如ORDER BY salary DESC, emp_name这样结果就稳定了不会每次跑出来顺序都不一样。5.3 现象累计求和的最后一列每一行都是同一个数原因很典型OVER里漏了ORDER BY。没有排序默认帧就是整堆SUM算的是整堆总和每一行拿到的当然一样。-- 错误没有 ORDER BY每一行都是总和 SUM(amount) OVER (PARTITION BY dept) AS total -- 正确有 ORDER BY变成从第一行累计到当前行 SUM(amount) OVER (PARTITION BY dept ORDER BY month_id) AS running_total还有一种相反的情况本想要整堆总和却不小心写了ORDER BY结果拿到了累计值。这两种错误长得一模一样都是“数字不对”排查时先看OVER里有没有ORDER BY一眼就能定位。5.4 现象加了一个 WHERE 条件占比算出来全变成 100%这类问题最隐蔽。你的本意是“只看研发部”于是在外层加了WHERE dept 研发部结果算占比的时候分母也只剩研发部了占比自然全对得上。-- 先过滤再开窗分母被改小了 SELECT dept, emp_name, salary, salary * 100.0 / SUM(salary) OVER (PARTITION BY dept) AS pct FROM employees WHERE dept 研发部;如果分母本来就该是过滤后的范围这样写没问题。但如果分母需要是过滤前的全量就得把过滤挪到窗口计算之后。-- 先开窗算全量再在外层过滤 SELECT * FROM ( SELECT dept, emp_name, salary, salary * 100.0 / SUM(salary) OVER (PARTITION BY dept) AS pct FROM employees ) t WHERE dept 研发部;判断标准很简单问自己一句“分母要不要包含被过滤掉的那些行”。要就放外层不要就放内层。5.5 现象本地跑得好好的换个数据库就报错窗口函数不是所有数据库、所有版本都支持。较老版本的关系型数据库完全不认OVER这个写法语法直接报错。另外还有几个常见的兼容性差异COUNT(DISTINCT ...) OVER (...)在部分数据库里不支持帧范围里RANGE对日期类型的处理各家有细微差别NULLS FIRST/NULLS LAST的写法也不是通用的。解决办法是先在目标环境上跑一个最小例子确认支持范围再写复杂查询。速查表里给的示例大多是标准写法落到具体环境时遇到报错先查这个环境的文档别急着改逻辑。真要跨库兼容尽量用ROWS而不是RANGE少用COUNT(DISTINCT)配窗口把复杂逻辑拆成子查询稳妥得多。6. 下一步学什么把速查表用成自己的工具箱跑通上面几个例子之后很容易产生一种“我好像已经会了”的错觉。我第一次学的时候就是这样看完当天觉得全懂了一周后遇到“每个用户连续登录天数”这种题还是卡在原地。原因不是函数没记住是没把函数和业务问题对应起来。接下来的路径建议按这个顺序走阶段练什么达到什么程度算过关第一周三类函数各写 5 遍改字段、改排序、改分区看到需求能立刻判断该用哪类函数第二周专攻帧范围把累计、滑动、整堆三种写法写熟能解释清楚为什么加ORDER BY结果就变了第三周做去重取最新、TopN、占比、环比四类经典题每类题能独立写出子查询版本第四周拿真实业务表跑看执行计划和耗时知道窗口开太大为什么会慢四个阶段里第二周最容易被跳过也最不该跳过。帧范围是窗口函数里唯一“写错了不报错、结果悄悄不对”的部分属于典型黑匣子。我吃过这个亏一份月度报表里的累计数字对不上查了整整一个下午最后发现是OVER里少了ORDER BY数据从第一行开始就是错的而查询本身从头到尾没报一个错。练习素材不用另找速查表本身就是最好的题源。它的组织方式是按功能分类你翻到某一页遮住示例代码先自己写一遍再对照。写不出来不要紧写出来但结果不对才是真正长记性的时刻——那种“明明语法没错但数字就是不对”的困惑比看十遍讲解都管用。还有个小习惯值得养成每写一个窗口函数先在纸上或者注释里写清楚三件事——按什么分组、按什么排序、要窗口的哪一段。这三件事写明白了代码基本不会错。我现在写复杂查询还是这么干尤其是那种一条 SQL 里有四五个窗口函数的报表不先写清楚改到后面自己都忘了每个窗口在算什么。最后一句实在话窗口函数不难难的是第一次见到OVER时那种“这什么东西”的陌生感。跨过去之后你会发现它只是把“分组、排序、取范围”这三件事打包成了一个语法。这份速查表的价值就在这儿——它把散落在各处的函数收在一处你不用背遇到问题翻一页找到对应的写法改改字段名就能用。建议把它存到本地放在随时能打开的地方边练边翻比通读一遍记得牢。从那以后我每次写窗口函数都会先把分组、排序、帧范围这三行注释敲上去再往下写代码。这个习惯帮我省掉了太多返工。希望帮到你。本文还有配套的精品资源点击获取