简介《SQL窗口函数速查表》是一份面向数据分析师、数据库管理员及开发人员的实用参考手册系统梳理了窗口函数的基本概念、三大类型排名、行序号、聚合函数及完整语法结构帮助读者在处理复杂SQL查询时快速上手。资源包内含1个PDF文件压缩包大小841KB体量轻巧便于离线查阅和打印。速查表采用分类表格与示例相结合的方式文档按排名、行序号、聚合等类别清晰分类并配有ROW_NUMBER()、RANK()、DENSE_RANK()、LEAD()、LAG()及SUM、AVG等常用函数的示例代码和结果说明可直观理解每个函数的分组、排序与窗口范围定义方式。无论用于数据库运维、数据探索还是开发报表功能和学术教学这份速查表都能显著提升窗口函数的应用效率是系统学习与日常速查两相宜的实用工具。目前已有255人学习使用。1. 最被低估的SQL能力窗口函数不是聚合是“不折叠明细的聚合”刚接触窗口函数时我最大的误区是把它当成聚合函数的语法糖。直到在某个电商订单库里写分组TopN用子查询加自连接折腾了大半天最后被一条ROW_NUMBER() OVER (PARTITION BY ...)当场解决我才意识到窗口函数的真正价值它在计算完聚合值之后并不会把多行压成一行而是把计算结果原样贴回每一行。这意味着原始数据一条不丢分析逻辑却往前跨了一大步。这份《SQL窗口函数速查表.pdf》就是围绕这个核心差异展开的把排名、偏移、聚合三类窗口函数和OVER语法收进同一份速查里。数据分析师、数仓开发、后端写SQL的人都能用新手对照示例做一遍熟手用来确认默认值和边界行为也省事。2. 先看懂OVER子句窗口函数的底层逻辑与语法结构窗口函数的名字五花八门ROW_NUMBER、RANK、SUM、LEAD各管一摊但它们的共同骨架只有一个函数名后面那个OVER()。速查表的第一部分就把这个核心语法单拎出来讲这也是我觉得整份PDF最值钱的部分——函数名记不住可以现查OVER的语义看不懂写出来的SQL基本靠猜。2.1 窗口函数与传统聚合的本质差异为什么GROUP BY做不到这件事普通聚合函数的行为是“多行进一行出”。比如按部门统计工资总额GROUP BY dept_id会把每个部门折叠成一条记录部门里的员工明细全部消失。窗口函数同样是算总数但写法变成SUM(salary) OVER (PARTITION BY dept_id)结果是每一行员工数据都还在旁边多出一列部门总额。SELECT emp_id, emp_name, dept_id, salary, SUM(salary) OVER (PARTITION BY dept_id) AS dept_total FROM employee ORDER BY dept_id, emp_id;这段SQL我经常拿来给刚入门的同事讲窗口函数。SUM(salary) OVER (PARTITION BY dept_id)先按部门分组再在每组内做求和最后把求和结果附加到组内每一行。注意观察执行结果dept_total在同一个部门的所有员工行上是重复出现的值这正是窗口函数的标志性特征。如果你把PARTITION BY dept_id去掉写成SUM(salary) OVER ()那整个结果集会被当成一个大窗口所有员工行上都会显示全公司的工资总额。这个差异在实际报表里非常有用。比如要同时展示员工个人工资和他所属部门的平均工资传统做法是两条SQL查完再关联窗口函数一条SQL就带出来了。速查表里把这类聚合函数单独归成一类其底层逻辑就是“在窗口内做聚合但保留原始行粒度”。2.2 OVER三件套PARTITION BY、ORDER BY和frame的职责分工所有窗口函数的窗口定义都由三部分组成分区、排序、窗口范围frame。速查表给出的语法结构可以浓缩成下面这个模板function_name(expression) OVER ( [PARTITION BY col1, col2] [ORDER BY col3 [ASC | DESC]] [ROWS | RANGE BETWEEN frame_start AND frame_end] )三个可选部分的作用差异很大我按理解顺序拆开说。PARTITION BY负责横向切分把整个结果集按指定列的值分成若干独立小组各组之间互不干扰。它和GROUP BY的分组概念很像但不做折叠。ORDER BY负责组内排序。注意它只决定窗口内行的先后顺序不会改变查询结果的最终输出顺序除非你在外层再加一个ORDER BY。这是很多新手第一个翻车点以为写了ORDER BY结果集就该排好序实际上结果集的排序由最外层的ORDER BY决定。frame是整个OVER子句里最容易被忽略的部分。它的作用是在已经排好序的组内再划一条“计算边界”决定参与计算的行到底是哪些。下面这张表是速查表里frame部分的常见边界写法边界写法含义UNBOUNDED PRECEDING从分区第一行开始n PRECEDING从当前行往前数n行开始CURRENT ROW从当前行开始n FOLLOWING到当前行往后数n行结束UNBOUNDED FOLLOWING到分区最后一行结束这里有个必须背下来的默认行为写了ORDER BY没写frame时默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW即从分区起点到当前行的所有行没写ORDER BY时frame默认是整个分区。这两种默认值分别对应“累计值”和“组内总值”两种结果很多线上SQL算出来的数不对根源就在这里。2.3 一个能直接抄的完整示例四个窗口函数同屏对比把以上概念放进同一个查询里效果比分开讲直观得多。下面这个订单分析场景我在好几个项目里都用过类似写法SELECT order_id, customer_id, order_date, order_amount, -- 每个客户的所有订单金额合计 SUM(order_amount) OVER (PARTITION BY customer_id) AS customer_total, -- 单笔订单金额占该客户总金额的比例 ROUND(order_amount * 1.0 / SUM(order_amount) OVER (PARTITION BY customer_id), 4) AS pct_of_customer, -- 按订单日期累加的客户累计金额 SUM(order_amount) OVER ( PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM orders ORDER BY customer_id, order_date;逐列看这个查询customer_total用的就是2.1节里的分组窗口每个客户的订单行都带着自己的总额pct_of_customer是单笔金额除以客户总金额算比例时我把order_amount乘以1.0这是为了避免整数除法直接截断成0cumulative_amount才是体现frame威力的地方——ORDER BY order_date之后frame从分区起点到当前行每一行算的都是“截至目前”的累计值。如果你从速查表翻到这一页会发现PDF里的示例和我这个基本同一套路但顺序更细致先演示无排序的组内聚合再演示带排序的累计聚合。建议你拿到手后不要跳着读按它的分类顺序跑一遍大于等于5个函数跑完OVER子句的直觉就建立起来了。3. 排名与偏移函数实战ROW_NUMBER、RANK、DENSE_RANK、LEAD、LAG速查表把排名函数和行序号函数分成两类但实际业务里它们经常出现在同一个查询里。区别在于排名函数解决“这行排第几”偏移函数解决“旁边那行是什么”。两套函数单独看不难放一起用才是报表里的常态。3.1 三个排名函数并列、跳号与连续业务含义完全不同很多人以为RANK和DENSE_RANK只是同一个功能的两种写法这是大错特错。用一个成绩表就能说清楚三者的差异SELECT student_name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, RANK() OVER (ORDER BY score DESC) AS rank_no, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_no FROM exam_score;假设有三名同学同分成绩按降序排列后ROW_NUMBER()会给它们强行分配1、2、3互不重复RANK()会让它们并列第1下一个不同分数的同学直接排到第4名数字上跳号DENSE_RANK()同样让同分并列第1但下一个不同分数的同学排第2名不跳号。三种逻辑对应三种业务口径函数并列时排名是否跳号典型业务场景ROW_NUMBER不允许并列不适用取前N条、分页去重RANK允许并列跳号竞赛排名强调“后面还有多少人”DENSE_RANK允许并列不跳号等级划分如按分数段定级速查表里每条函数下面都标注了返回规则我建议你精读这一块。它还把容易混淆的ROW_NUMBER()和RANK()放在相邻位置就是为了让你一眼看出差异前者每行唯一后者允许并列并跳过后续排名。这里补一个边界行为排序键为NULL时三个函数的处理方式在各个数据库里并不一致。有的数据库把NULL当最大值排在最前有的排到最后还有的允许你通过NULLS LAST等子句手动指定。速查表引用了一些跨数据库兼容性说明我在生产环境里都会显式声明NULL的排序方向否则同一个SQL在两个引擎上跑出完全不同的排名排查起来非常头疼。3.2 LEAD与LAG取前后行数据计算同比环比的核心工具LEAD和LAG是一对相反方向的偏移函数。LAG取当前行之前的第n行LEAD取当前行之后的第n行它们最常见的用途是算环比、同比、时间间隔这类“跟相邻记录比大小”的场景。下面是用员工调薪记录计算每次调薪幅度的标准写法SELECT emp_id, salary_date, salary, LAG(salary) OVER (PARTITION BY emp_id ORDER BY salary_date) AS prev_salary, ROUND( (salary - LAG(salary) OVER (PARTITION BY emp_id ORDER BY salary_date)) * 100.0 / LAG(salary) OVER (PARTITION BY emp_id ORDER BY salary_date), 2 ) AS pct_change FROM salary_history ORDER BY emp_id, salary_date;LAG(salary)只写了一个参数默认取上一行PARTITION BY emp_id保证每个员工单独计算不然不同员工的调薪记录会串行ORDER BY salary_date决定“上一行”的方向是时间上的前一条记录。pct_change就是本条薪资相对上一条的变化率。这两个函数的完整参数形态是LAG(expr, offset, default)和LEAD(expr, offset, default)。第二个参数offset控制偏移行数不写默认1第三个参数default表示越界时返回的值比如第一行没有“上一行”默认返回NULL。速查表把这几个参数列得特别清楚我一开始记不住default参数的位置全靠这张表翻。还有个冷门技巧offset参数可以写负数LAG(x, -1)等价于LEAD(x, 1)但可读性差很多一般不建议这么写。如果想算同比比如电商订单里这个月和上个月同一天的销售额就把它和前12个月的记录比LAG(amount, 12) OVER (PARTITION BY product_id ORDER BY month)。这种情况下offset不再是一行一行数而是按排序键的偏移量前提是你的数据里每个月都有一条记录否则要结合数据补全一起做。3.3 拿到速查表后怎样快速定位一个函数速查表的结构我是这么用的先明确业务要干什么再去对应分类里找函数名最后抄一个示例代码验证。完整的查找路径可以拆成三步。第一步看函数分类。要判断某行在整个结果集里的位次去“排名函数”找要看前后行的值去“行序号函数”找要做累计求和、移动平均去“聚合函数”找。第二步核对函数签名。速查表里每个函数都给了参数列表和返回规则重点看有没有default参数、是否允许省略、排序方向怎么写。第三步拿示例代码跑一遍。PDF里每个函数都附了输入输出对照我会在本地数据库里把示例原样执行一次再把表名换成自己的业务表验证结果是否符合预期。这套流程看着简单真正坚持做的人不多。很多人下载速查表之后直接塞进收藏夹等用到时才翻结果每次查到的函数都记不牢。我的习惯是每隔一两周挑一个不常用的函数比如FIRST_VALUE或NTILE找业务数据试一下试完就记住了。4. 窗口聚合与移动计算SUM、AVG、COUNT配合frame的完整用法排完名、取完相邻值窗口函数最重的应用场景其实是聚合计算。SUM、AVG、COUNT这些函数加不加OVER表现完全是两回事。速查表的聚合函数部分核心就是在讲frame子句怎么控制计算范围。4.1 累计求和与移动平均frame边界决定计算范围先看一个交易流水表的例子同时算累计金额和3日移动平均SELECT trade_date, amount, SUM(amount) OVER ( ORDER BY trade_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount, AVG(amount) OVER ( ORDER BY trade_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3 FROM daily_trade ORDER BY trade_date;cumulative_amount这一列的含义是从第一天的交易额一直累加到当前行因为frame起点是UNBOUNDED PRECEDING终点是CURRENT ROW每天一行数据。移动平均则用了ROWS BETWEEN 2 PRECEDING AND CURRENT ROW意思是只取当前行往前数两行一共三行做平均这就是“3日移动平均”的实现。frame边界的选择完全取决于业务口径。你要做的是累计值起点就必须是UNBOUNDED PRECEDING要做固定窗口的滑动平均值起点就是n PRECEDING。速查表里把五种边界关键字全部列出来并用小图标注了它们指向的位置这张小图对新手来说比任何文字说明都直观。另外注意frame的终点也完全可以超出当前行比如用ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING算“从今天到未来的总和”这在计算剩余额度时会用到。我一般会建议团队把frame部分显式写全不要依赖默认值。写过SUM(x) OVER (ORDER BY y)却忘了加frame的人迟早会被默认的RANGE行为坑一次具体坑法下面单独说。4.2 ROWS与RANGE的区别排序键重复时结果完全不同这是整个窗口函数里最隐蔽的坑之一也是速查表花了不少篇幅解释的内容。ROWS和RANGE都用来定义frame边界但语义完全不同ROWS严格按行号取数据说2 PRECEDING就是物理上的前两行RANGE按排序键的值取数据排序键相同的行会被当成“同一行”一起纳入。比如订单日期不是唯一的同一天有好几笔订单。用SUM(amount) OVER (ORDER BY order_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)算累计值时凡是order_date相同的行frame都会把它们全部包含进来每一行上算出的累计值都相同。而用ROWS版本时每一行严格往前数行每行的累计值都因为包含的行数不同而产生差异。场景ROWS行为RANGE行为排序键有重复严格按物理行数取把所有相同排序键的行一起纳入排序键唯一两者结果一致两者结果一致典型用途移动平均、固定窗口按日期累计、按值区间计算判断自己该用哪个只需要问一个问题我要计算的范围是基于“物理行数”还是“排序键数值”求3日移动平均显然要固定取3行用ROWS按日期算累计值想把同一天的数据视作一个整体用RANGE也可以但必须先确认自己知道这个语义。速查表的示例里两种写法都给了我建议把两个结果并排执行一次亲眼看一下差在哪比背十遍理论都管用。4.3 高频组合场景占比、累计、连续登录的直接模板窗口聚合在实际分析里用得最多的三个模板速查表里都覆盖了我在这里把它们的写法固定下来拿来即用。计算每个分类下的销售额占该分类总额的比例以及截止到当前的累计占比SELECT category, order_date, sales_amount, SUM(sales_amount) OVER (PARTITION BY category) AS category_total, SUM(sales_amount) OVER ( PARTITION BY category ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_sales, ROUND( SUM(sales_amount) OVER ( PARTITION BY category ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) * 100.0 / SUM(sales_amount) OVER (PARTITION BY category), 2 ) AS cumulative_pct FROM sales;另一个高频场景是连续登录天数。思路是先用ROW_NUMBER()给每个用户按登录日期编号再用“登录日期减编号”的差值分组——连续登录的日子差值相等然后按差值计数WITH login_grouped AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ) DAY) AS group_id FROM user_login ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM login_grouped GROUP BY user_id, group_id;这两个模板在很多分析报表里都能直接改表名复用。速查表里每个函数单独看都不难难的是像这样把它们组合起来解决真实问题。我的经验是先照着模板跑通再逐段删减、修改参数观察结果变化很快就能理解每段代码的职责边界。5. 窗口函数五个高频坑从报错到结果翻车窗口函数写起来快排错可不容易。下面五条是我在实际项目中撞过、也在给团队做代码评审时反复见到的坑。速查表虽然不会直接告诉你怎么排查但理解这些坑之后再回头读速查表的语法说明会有一种“原来那段警告是说这个”的豁然感。5.1 坑一WHERE子句不能直接过滤窗口函数的结果现象写SELECT ..., ROW_NUMBER() OVER (...) AS rn FROM t WHERE rn 1数据库直接报错说窗口函数不允许出现在WHERE里。原因SQL的执行顺序里WHERE在SELECT的表达式计算之前执行。窗口函数是在SELECT阶段计算的等它算出rnWHERE早执行完了逻辑上就矛盾。解决用子查询或CTE包一层先算窗口函数再在外面过滤。WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) SELECT * FROM ranked WHERE rn 1;这是取“每个部门工资最高员工”的经典写法把窗口函数放进CTE外层再用普通条件过滤。从那以后我凡是看到窗口函数外面直接套WHERE的代码第一反应就是先给提问的人画执行顺序图。5.2 坑二RANK跳号被业务方当成计算错误现象报表展示排名时两个并列第1名后面直接是第3名业务方一口咬定程序算错了少了一个第2名。原因RANK()的语义就是跳号。两个并列第1占用1和2两个序号所以下一个排名是3这是标准行为不是bug。解决先和业务方确认排名口径。如果强制要求排名连续把RANK()换成DENSE_RANK()它能输出1、1、2而不跳号。速查表里两个函数放在一起对比就是为了让人选之前先想清楚。5.3 坑三忘了写ORDER BY累计求和变成了组内求和现象想算“每个客户按时间累计的订单金额”写了SUM(amount) OVER (PARTITION BY customer_id)结果每一行的累计值都一样等于该客户全部订单总额。原因frame的默认范围与有没有ORDER BY强相关。没写ORDER BY时窗口默认是整个分区写了才能触发从分区起点到当前行的累计逻辑。解决算累计值时写法必须是SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date)并且在排序键可能重复时加上显式的ROWS边界。速查表的语法部分明确标注了这个默认行为我把它当成必背条目。5.4 坑四移动平均结果和Excel手工核对不一致现象用AVG算3日移动平均拿Excel手工拉了一遍发现两边对不上差在日期相同的记录上。原因AVG(amount) OVER (ORDER BY trade_date)没写ROWS时用的是默认RANGE frame日期相同的数据会被视为同一行一起纳入计算导致一天多条订单时窗口里的行数超过了3行。解决移动平均这种对物理行数有严格要求的场景必须显式写ROWS BETWEEN 2 PRECEDING AND CURRENT ROW明确告诉数据库“按行取不看值”。5.5 坑五COUNT(列)与COUNT(*)在窗口内对NULL的处理不同现象同一张表COUNT(*) OVER (PARTITION BY dept_id)返回10COUNT(remark) OVER (PARTITION BY dept_id)返回8少了2条。原因COUNT(列)只统计该列非NULL的行COUNT(*)统计所有行。窗口函数里的行为与普通聚合完全一致remark列里恰好有2条NULL。解决统计行数时确认业务口径。想数“有多少条记录”一律用COUNT(*)想数“有多少个非空值”再用COUNT(列)。速查表在聚合函数说明里专门标注了这个问题我最初没当回事直到线上数对不上才后悔没早看。6. 进阶用法用WINDOW子句和CTE把窗口查询写成能维护的分析模板窗口函数用多了就会发现一段SQL里可能同时挂着四五个OVER每个后面跟着相同的PARTITION BY和ORDER BY改一个分区字段就得全文搜着改容易漏。速查表没展开讲的WINDOW子句恰好是解决这个问题的利器。6.1 WINDOW子句一个窗口定义到处复用SELECT dept_id, emp_id, salary, SUM(salary) OVER w AS dept_total, AVG(salary) OVER w AS dept_avg, MAX(salary) OVER w AS dept_max FROM employee WINDOW w AS (PARTITION BY dept_id) ORDER BY dept_id, emp_id;WINDOW w AS (PARTITION BY dept_id)定义了一个名为w的窗口后面三个OVER子句都引用它。这样定义一次、多处使用的写法比把PARTITION BY dept_id复制三遍清爽得多改分区字段时只需要动WINDOW子句那一行。如果某个窗口还需要排序就在定义里加上ORDER BY但要注意加了ORDER BY之后所有引用该窗口的聚合函数的frame都会变成默认的RANGE累计模式这时候要根据需要决定是否单独覆盖。6.2 窗口函数与CTE组合把复杂分析拆成可读步骤CTE加窗口函数是我写分析SQL最顺手的组合前面的连续登录模板已经演示过一例。核心思路是一个CTE只做一件事窗口函数在其中计算辅助列外层查询负责过滤或二次加工。取全公司每个部门工资排名前两名的员工用两个CTE串联WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ), top2 AS ( SELECT * FROM ranked WHERE rn 2 ) SELECT * FROM top2;每个CTE职责单一排错时只需单独执行ranked那一段看结果问题定位很快。我在做报表需求时基本都会把复杂逻辑拆成几步比写几百行的单条SQL省心太多。说到排错我吃过一次亏在某数据校验流程里用连续登录模板统计用户活跃天数有同事反馈结果比预期少了好几天排查到最后发现是登录日期字段里混入了NULL而COUNT(login_date)直接把NULL跳过去了。从那以后我每次写完窗口函数都强制走一遍固定检查单条SQL在3行以内的模拟数据上跑一遍逐个核对输出值凡是涉及COUNT的先确认清楚要不要计NULL涉及frame的确认ROWS还是RANGE每一步都拿速查表过一遍。这套流程看起来笨但基本没再翻过车。希望这份速查表和学习路径也能帮你少踩几个我已经踩过的坑。本文还有配套的精品资源点击获取