那咱们直接开聊。很多人写 SQL 两三年GROUP BY用得滚瓜烂熟子查询和JOIN也信手拈来但一遇到分组内排名累计求和同比环比这类需求就开始挠头最后要么用子查询嵌套得乱七八糟要么把数据拉到程序里用 Java/Python 慢慢算。其实这些活儿SQL 里有一个专门干这个的利器——窗口函数Window Function也叫分析函数Analytic Function。这篇东西我本来没打算写但最近带几个同事做数据需求发现大家对这个函数的理解普遍停留在会用ROW_NUMBER()去个重的水平稍微上点难度的RANGE窗口、跨行引用就懵了。所以干脆把我的实操笔记整理出来从使用场景、语法原理、经典案例到踩坑记录一次性说透。这篇内容适合谁一类是天天写报表 SQL 的数据分析师和开发另一类是想搞懂面试题里窗口函数和 GROUP BY 的区别的求职者。我会尽量把每个为什么都讲清楚顺便给出一堆可以直接改改就用的 SQL 片段。1. 窗口函数到底解决了什么问题1.1 GROUP BY 解决不了的那些分析需求我们先用一个最朴素的场景切入。假设有一张订单表每个订单有用户ID下单日期订单金额三列。现在想算每个用户截至每个订单日的累计下单金额也就是用户的第一单多少、前两单合计多少、前三单合计多少。GROUP BY能做到吗说实话能但极其别扭。你大概得这么写先按用户分组查出每个订单的日期和金额再用订单日 当前订单日的条件去做非等值自关联把小于等于当前日期的订单金额全部SUM一遍。遇到上万行数据这种写法就是灾难关联爆炸不说SQL 读起来也像天书。问题的本质在于GROUP BY会把多行压成一行行与行之间的相对关系随之消失。而报表分析里偏偏大量存在既要保留每一行明细又要在这一行旁边算出群体的聚合值这类诉求。窗口函数就是为了填补这个断层而生的——它让我们在保留明细行的同时对每一行开一个窗窗内可以排序、聚合、偏移引用然后把这个结果作为新列附加在当前行上。1.2 窗口函数的核心思想开窗窗口这个词很形象它描述的是当前行能看到的数据范围。比如SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date)翻译成大白话就是按用户分组组内按日期排好序当前行往下看把从本组第一行到当前行的所有amount累加起来。这里的窗口是一个动态的概念。它不像GROUP BY那样一次算完然后固定不变而是每一行都有自己的一个窗口范围。行不同窗口范围就可能不同——这也是它和普通聚合函数在计算模型上最本质的区别。我把窗口函数的使用拆成三个动作分窗格PARTITION BY决定整个数据集怎么切分成互相独立的组每组一个窗口。排顺序ORDER BY决定窗内行的先后顺序很多函数比如RANK()、累计求和依赖这个顺序才有意义。定范围ROWS或RANGE决定当前行对应的窗口边界是从组头一直延伸到当前行还是只取前后各 N 行。这三步理解了窗口函数你就掌握了一半。剩下的就是对函数本身的选择——排名类的、聚合类的、偏移类的、分布类的。2. 语法拆解与常用函数家族2.1 OVER 子句的三个核心组件OVER()是窗口函数的标志性语法。它控制函数作用的窗口范围里面可以组合三部分内容SUM(amount) OVER ( PARTITION BY user_id -- 分区 ORDER BY order_date -- 排序 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 窗口范围 )PARTITION BY不是必填的。如果不写整个结果集就是一个大窗口对所有行计算。ORDER BY可写可不写取决于业务语义。比如单纯想对全表算一个总和列SUM(amount) OVER()就够了不需要排序。但一旦写了ORDER BY窗口的默认范围会从全组变成从组首到当前行这是一个极其容易踩坑的隐含规则。ROWS部分通常可以省略省略时有默认行为。但如果窗口范围写错了结果会完全不对。这里我多说一句ROWS和RANGE的区别。ROWS是物理行数的概念说取前两行就是前两行RANGE是逻辑值域的概念按当前行的排序键值做比较比如键值在[当前值-10, 当前值]区间的所有行。实话说RANGE用得少它在数值连续区间统计时才有典型意义日常业务里 90% 以上用ROWS就够了。但至少你要知道这两个关键字的存在面试时被问起能说出区别就行。2.2 三大类函数的骨架与选型窗口函数家族大致分三类每类的使用逻辑完全不同。排序/编号类ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()。这组函数用来给窗内行打编号或用分桶。最常用的排序场景就是取每个分组的前 N 名。ROW_NUMBER()无论如何都按顺序编 1、2、3...没有并列严格唯一。RANK()有并列时跳号比如两个并列第一接下来直接是第三名序号是 1、1、3。DENSE_RANK()有并列时不跳号序号是 1、1、2。NTILE(n)把窗内行尽量均匀地分成 n 桶常用于分位数划分。三者的差异用一张对比表看最直观函数行为描述典型场景ROW_NUMBER无并列连续编号去重取第一条RANK并列跳号竞赛排名DENSE_RANK并列不跳号等级划分NTILE(4)均匀分4桶按金额四分位聚合类SUM()、AVG()、COUNT()、MAX()、MIN()直接当作窗口函数用加上OVER()子句即可。它们和普通聚合的唯一区别就是要不要保留明细行。偏移/引用类LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()。这组函数用来访问同一窗口内其他行的值。LAG(amount, 1)表示取当前行前一行的amountLEAD同理取后一行。环比、同比、差值类计算全靠它们。偏移类函数在写同环比时是主力不过它有个细节如果当前行没有前一行比如第一行取LAG返回结果会是NULL。处理办法是在函数外层套COALESCE或者IFNULL兜底这个后面实操部分我会给具体写法。3. 实操案例与核心环节实现3.1 分组 TopN经典中的经典来看一个最常被问到的需求算出每个分类下销量最高的前 3 个商品。表结构sales(category, product, month, qty)每个月每个商品有一条销量记录。先写一个基础版统计每分类下总销量前三SELECT category, product, total_qty, rn FROM ( SELECT category, product, SUM(qty) AS total_qty, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY SUM(qty) DESC ) AS rn FROM sales GROUP BY category, product ) t WHERE rn 3;注意这个 SQL 的结构内层先用GROUP BY category, product算出每个商品的总销量得到一个每人一票的明细表外层再用ROW_NUMBER()对每个分类内的商品按销量排序编号最后过滤rn 3取前三。这里有个容易被忽视的点窗口函数在GROUP BY之后才执行。也就是说内层GROUP BY已经把数据压缩到商品粒度了外层窗口函数是在这个压缩结果上开窗的而不是在原始订单行上开窗。这也是很多新手一上来就把窗口函数和GROUP BY混写导致语法报错的原因——逻辑上得先想清楚数据粒度是什么。如果产品想并列上榜把ROW_NUMBER()换成RANK()即可。实际业务中并列是否算两个名额完全由老板心情决定但 SQL 里只是换一个函数的事。3.2 累计值从开户到滚动余额再来写累计求和的例子。银行的账户流水表account_flow(acct_id, trade_date, trade_amt)我想看每个账户截至每天的交易净额累计值。SELECT acct_id, trade_date, trade_amt, SUM(trade_amt) OVER ( PARTITION BY acct_id ORDER BY trade_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_balance FROM account_flow ORDER BY acct_id, trade_date;这里如果我把ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW直接省略换成SUM(trade_amt) OVER ( PARTITION BY acct_id ORDER BY trade_date )效果一样。因为ORDER BY出现后默认的窗口范围就是UNBOUNDED PRECEDING到CURRENT ROW。但为什么我还是建议你写全因为阅读代码的人不会记住所有默认值。显式写清楚窗口边界代码自解释性更强维护时不容易误判。有一次我接手一个同事的 SQL他把窗口范围省略了但内层查询又对日期做了倒序导致累计方向整个反了排查了半天。所以默认窗口范围这个知识点必须刻在脑子里有ORDER BY默认就是累计语义没有ORDER BY默认是整个分区。3.3 移动平均与同比环比移动平均在金融数据分析里很常见比如近 3 个月的平均销售额用窗口函数只需要把窗口边界改成前 2 行到当前行SELECT month, sales_amount, AVG(sales_amount) OVER ( ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_3m FROM monthly_sales;注意这里的PARTITION BY我没写因为整个表就是月度序列数据不需要分区。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示窗口包含当前行以及它前面两行。如果遇到数据不满 3 个月的情况比如 1 月只有一行那AVG就只在这 1 行上算不会报错。这个特性用于早期数据波动大但不想出现 NULL的场景特别合适。同比环比是偏序列表中最常见的需求SELECT month, sales_amount, LAG(sales_amount, 1) OVER (ORDER BY month) AS prev_month_sales, sales_amount - LAG(sales_amount, 1) OVER (ORDER BY month) AS mom_growth, ROUND( (sales_amount - LAG(sales_amount, 1) OVER (ORDER BY month)) / LAG(sales_amount, 1) OVER (ORDER BY month) * 100, 2 ) AS mom_growth_rate FROM monthly_sales;这个 SQL 里的LAG(sales_amount, 1)会取当前行之前的第 1 行数据。注意第一行没有之前结果会是NULL所以mom_growth_rate对于第一条数据也是NULL。前端展示时通常会显示为空下拉线不影响。如果要做同比只需要把窗口内的排序字段从月换成年-月并改用LAG(sales_amount, 12)取去年同期。这种写法在按月建表、没有历史分区表的小型数仓里非常方便因为实际上大部分报表都是宽表一年甚至两年的记录都存在同一张表里LAG偏移 12 行就是去年的同一月份前提是数据按月连续且无缺失。3.4 会话分组与时间间隔一个 LAG 的进阶用法LAG()还有一种不常被提但很实用的场景判断相邻事件之间的时间间隔。比如用户行为日志user_log(user_id, log_time, action)需要把间隔超过 30 分钟的行为切分成不同的会话。思路是先按用户和时间排序用LAG(log_time)取上一行为的时间跟当前行计算时间差超过 30 分钟就认为是一个新会话起点然后做会话编号SELECT user_id, log_time, action, SUM(is_new_session) OVER ( PARTITION BY user_id ORDER BY log_time ROWS UNBOUNDED PRECEDING ) AS session_id FROM ( SELECT user_id, log_time, action, CASE WHEN TIMESTAMPDIFF(MINUTE, LAG(log_time) OVER (PARTITION BY user_id ORDER BY log_time), log_time ) 30 OR LAG(log_time) OVER (PARTITION BY user_id ORDER BY log_time) IS NULL THEN 1 ELSE 0 END AS is_new_session FROM user_log ) t;这个写法有点绕我拆开解释。内层先算is_new_session如果当前行与上一行的时间差大于 30 分钟或者当前行是组内第一行LAG为NULL就标记为 1否则为 0。外层用SUM() OVER(... ROWS UNBOUNDED PRECEDING)把会话起点累加起来每个 1 的标记都会让会话号 1直到遇到下一个新会话起点。这样每个用户每次连续 30 分钟内的行为就被分到了同一个会话号里。这种标记起点 累计求和的套路实际就是 SQL 实现分组的一个通用技巧比用变量和游标不知高到哪里去了。有机会遇到类似连续登录天数新客首单归因的需求都可以往这个方向想一想。4. 常见问题与排查技巧实录4.1 默认窗口范围引发的假累计OVER (PARTITION BY col ORDER BY col)默认窗口是RANGE UNBOUNDED PRECEDING TO CURRENT ROW这句话我一直强调但看到的人往往不以为意。真踩坑的时候才知道疼。我遇到过一次最典型的错误想算每月销售额占全年总额的比例我写SELECT month, sales_amount, sales_amount / SUM(sales_amount) OVER (PARTITION BY year) AS ratio_annual FROM sales;这段代码是对的因为PARTITION BY year没有ORDER BY窗口范围是整个分区也就是全年总和。但如果有人手欠在PARTITION BY year后加了个ORDER BY month那SUM就变成了累计到当前月比例结果直接变成当前月占年初至今总和的占比数字完全对不上。解决这个问题的唯一办法就是写窗口函数前先想明白你要的是累计值还是全组值然后照着语义去写是否加ORDER BY。这个习惯比记住任何函数参数都有价值。4.2 并列排名与剔除重复窗口函数唯数不多的坑之一是ROW_NUMBER()在去重时去掉哪一条完全由ORDER BY决定这个顺序通常并不符合业务直觉。比如按user_id去重保留最新一条记录SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY update_time DESC) AS rn FROM user_info ) t WHERE rn 1;看起来没问题但是如果同一个人同一天有多条更新时间相同那结果就是随机的。若系统对数据质量要求高就要在ORDER BY中加入id DESC或create_time DESC等能保证唯一性的字段确保选出来的一定是最新创建的那条。但要注意如果你用了RANK()做并列排名然后在WHERE rn 1时可能取出多行。很多人写 Top1 用RANK()结果返回了两行这就是没有理解并列语义。去重老老实实用ROW_NUMBER()不要想当然用RANK()或DENSE_RANK()。4.3 窗口函数中的过滤顺序极易踩雷窗口函数是在WHERE、GROUP BY、HAVING之后执行的。举例说明我写SELECT user_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) FROM orders WHERE amount 100;窗口函数会在WHERE amount 100过滤之后才对剩余行做排序编号。如果用户最小的订单是 50 元那么这个用户在这个 SQL 里根本不会出现尽管他的其他订单都大于 100。这个逻辑如果没想清楚很容易写错业务取数结果。反过来如果我希望先在全部订单里排序再过滤掉小于等于 100 的订单那就得把窗口函数放在子查询或者CTE里先算排名再做WHERESELECT * FROM ( SELECT user_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM orders ) t WHERE amount 100 AND rn 1;这里我把过滤逻辑分了两层才得到正确结果。窗口函数不是万能的它解决的是计算问题过滤逻辑仍然要放在正确的执行顺序里才能用对。4.4 性能为什么我的窗口函数查询越来越慢窗口函数看起来只是一个函数调用但执行引擎往往需要对分区列和排序列做排序。在千万级大表上如果PARTITION BY和ORDER BY没有索引支撑每次查询都可能触发大规模排序或物化速度自然上不去。优化思路按优先级排PARTITION BY和ORDER BY涉及的列建联合索引让排序尽量走索引。能不PARTITION BY不分区就尽量不分区。全表一个大窗口的排序成本比分成一百个窗口低很多因为分区数越多排序的步数越多。在子查询里先过滤掉不必要的数据再开窗让窗口作用在最小数据集上。如果只是算全局累计值可以试试用普通JOIN改写是否有性能优势但千万别为了形式统一硬用窗口函数这不是炫技的地方。最后提醒一个真实的运维坑很多一次性报表查询会把窗口函数的结果保存到临时表但临时表的字段类型和长度如果和源表不一致可能触发数据截断或隐式转换导致下游算出来的数总是对不齐。建临时表前先CREATE TABLE AS复制一下表结构省得自己手打字段定义。5. 几个容易忽略但很实用的小技巧窗口函数里面还有一个冷门但有用的功能在OVER()里使用多个ORDER BY字段。比如对订单先按用户排序再按时间排序编号时就严格按照先用户后时间的顺序。有些排序类的报表需要这种复合排序而很多人只会写一个ORDER BY这是个不大不小的熟练度分水岭。FILTER (WHERE ...)子句在窗口聚合中也很好用。比如我想同时算出有效订单金额和总额就不需要建两遍窗口SELECT user_id, order_date, SUM(amount) FILTER (WHERE status valid) OVER (PARTITION BY user_id) AS valid_amount, SUM(amount) OVER (PARTITION BY user_id) AS total_amount FROM orders;这个语法在 PostgreSQL 里很好使在 MySQL 8.0 里则没有FILTER需要用条件表达式SUM(CASE WHEN ... THEN amount ELSE 0 END)代替。跨数据库开发时这段差异也值得提前确认免得写完才发现语法不支持白白返工。窗口函数本质上是一个行级 组级混合思维的工具。它反射出一个数据处理的核心思想明细要在聚合也要在二者通过一个窗口共存。掌握了它处理排名、累计、同环比、分组 TopN、行为序列分析时写 SQL 的效率和正确率都会有质的变化。从个人体会说窗口函数是我近年来用得最多、也最让我觉得划算的 SQL 技能。每次把复杂的子查询改写成一个清晰的OVER()代码可读性和运行效率都会提升一个档次。如果你刚开始接触这个语法不用一本正经从理论啃直接拿一张真实的业务表反复折腾几个场景——排名、累计、LAG 取前后值、移动平均。踩过几个坑之后你会发现它和你已经熟透的SELECT一样变成一种下意识的本能。
