SQL窗口函数实战指南：从分组排名到同比环比的核心用法

忘了从什么时候开始，面试问窗口函数几乎成了数据分析岗的标配，工作中写复杂报表也绕不开它。但说实话，我见过不少做了两三年取数需求的同事，窗口函数还停留在ROW_NUMBER()排序的阶段，碰到"分组内TopN""同比环比""连续登录天数"这类需求，第一反应还是自连接、子查询，SQL写得又臭又长，性能还差。这篇文章我打算把窗口函数从头到尾梳理一遍——它到底解决了什么问题、哪些函数最常用、每个函数在什么场景下该用，以及我在实际业务里踩过的那些坑。适合刚接触窗口函数想系统学一遍的新人，也适合日常取数但总在几个函数上打转的初级分析师。

1. 窗口函数解决的三个核心问题

1.1 为什么普通聚合函数做不到的事，窗口函数能搞定

先说个最简单的例子。有一张订单表orders，字段是region（区域）、sales_amount（销售额），我想查每个区域的销售额，以及每个订单占全区域销售额的百分比。

用普通聚合函数写出来长这样：

SELECT region, SUM(sales_amount) AS region_total FROM orders GROUP BY region;

这没问题。但如果需求变成"每个订单保留原始行，同时在旁边加一列显示该区域的总销售额"，普通分组聚合就废了——GROUP BY会把多行压缩成一行，你拿不到订单明细。

这时候就得靠窗口函数：

SELECT order_id, region, sales_amount, SUM(sales_amount) OVER(PARTITION BY region) AS region_total FROM orders;

OVER()关键字就是窗口函数和普通聚合函数最直观的区别：普通聚合把多行揉成一行，窗口函数让每行保留原样，同时把计算结果带在每一行旁边。

用生活类比来说，普通聚合是"全校按班级汇总成绩单，每班一行"，窗口函数是"每个学生手里贴着自己班级的平均分"，学生信息和班级统计信息同时在一条记录里展示。这个能力看起来简单，但在数据分析里非常关键——因为它让"明细+汇总"的同屏展示成为可能。

1.2 窗口函数的执行顺序：先WHERE还是先开窗

很多人会把窗口函数和GROUP BY混用，然后报错，根本原因是不清楚SQL执行顺序。

简单画个执行顺序：

1. FROM/JOIN取数
2. WHERE过滤
3. GROUP BY分组
4. HAVING过滤分组
5. 窗口函数计算（发生在SELECT阶段）
6. ORDER BY排序

这里有个关键点：窗口函数在WHERE之后执行，所以你不能在WHERE里直接对窗口函数的结果做过滤。想过滤窗口计算结果，需要套一层子查询或者WITH临时表。

比如我想查"比同区域平均销售额高的订单"：

SELECT * FROM ( SELECT order_id, region, sales_amount, AVG(sales_amount) OVER(PARTITION BY region) AS avg_region FROM orders ) t WHERE sales_amount > avg_region;

我刚入行的时候写过WHERE sales_amount > AVG(...) OVER(...)，直接报错，后来才知道窗口函数在WHERE之后才轮到算。

1.3 什么场景下你才真正需要窗口函数

这里给你三个我实际工作中最常见的判断信号：

• 保留明细的同时要附带聚合值：比如订单明细旁边带上店铺月销售额、用户订单总额。
• 分组内做排名或取TopN：比如每个品类下销售额前3的SKU，每个用户最近一次下单时间。
• 跨行计算：比如计算前一天销售额的差值、累计到当天的销售额、行与行之间的时间间隔。

如果你看到的需求有这三类信号里的任何一个，优先考虑窗口函数，而不是自连接或子查询。窗口函数写起来更直观，而且多数数据库对窗口函数的执行优化比自关联要好。

2. 五大类窗口函数：用法、语法与典型业务场景

2.1 聚合窗口函数：SUM、AVG、COUNT、MAX、MIN

这组函数就是聚合函数加了个OVER()，语义完全一致。区别在于，窗口函数可以加PARTITION BY做分组，也可以不加——不加就是全表作为一个窗口。

基础用法示例：

SELECT order_id, region, sales_amount, SUM(sales_amount) OVER(PARTITION BY region ORDER BY order_date) AS region_cum_sum, AVG(sales_amount) OVER(PARTITION BY region) AS region_avg, COUNT(*) OVER(PARTITION BY region) AS region_order_cnt FROM orders;

注意上面这个例子，region_cum_sum那列我加了ORDER BY order_date，这时候算的是组内截至当前行的累计值，不是整个组的总和。这是聚合窗口函数最容易被忽视的行为——ORDER BY放进OVER()里会改变计算范围。

提示：SUM(...) OVER(PARTITION BY region)不带ORDER BY算的是全组总和；SUM(...) OVER(PARTITION BY region ORDER BY order_date)算的是从组内第一行到当前行的累计和。两者业务含义完全不同，写之前想清楚。

聚合窗口函数在业务里最常见的用途是算占比和累计值。比如算"各品类销售额贡献占比"：

SELECT category, sales_amount, sales_amount / SUM(sales_amount) OVER() AS pct_of_total FROM products;

OVER()不带任何参数，表示对所有行开窗，也就是全局总和。这个写法比子查询算总和再关联要清爽得多。

2.2 排序窗口函数： ROW_NUMBER、RANK、DENSE_RANK

这三个函数放在一起说，因为它们的语法完全一样，只是排名规则不同：

先记住它们的区别，再对应到业务：

• ROW_NUMBER()：取每组第一名时用。因为即使分数相同，结果也只有一行，适合"每个分类取一个代表"。
• RANK()：适合"并列冠军"场景，比如竞赛排名、成绩排名。
• DENSE_RANK()：适合"名次连续"的榜单，比如积分排行榜，不想因为并列导致后面名次空缺。

实际看一个"每个品类销售额前三的SKU"的例子：

SELECT category, sku_id, sales_amount, ROW_NUMBER() OVER(PARTITION BY category ORDER BY sales_amount DESC) AS rn FROM sku_sales QUALIFY rn <= 3;

如果你用的数据库不支持QUALIFY（比如MySQL），就包一层子查询：

SELECT * FROM ( SELECT category, sku_id, sales_amount, ROW_NUMBER() OVER(PARTITION BY category ORDER BY sales_amount DESC) AS rn FROM sku_sales ) t WHERE rn <= 3;

这里提醒一句：取TopN，默认用ROW_NUMBER()而不是RANK()。业务上"前三名"通常是按名次取三条记录，如果三个人并列第一，RANK()会把并列的都取出来导致超过3条。

2.3 偏移函数： LAG、LEAD——行与行之间的"前后脚"

LAG()取上一行的值，LEAD()取下一行的值。这对函数在分析时间序列数据时几乎天天用，比自连接效率高很多。

基本语法：

LAG(column, offset, default_value) OVER(PARTITION BY ... ORDER BY ...)

offset默认是1，表示上一行；default_value是取不到时用的默认值，通常写0或者NULL。

实际业务里最典型的用法是算环比：

SELECT order_date, SUM(sales_amount) AS daily_sales, LAG(SUM(sales_amount), 1, 0) OVER(ORDER BY order_date) AS prev_day_sales, SUM(sales_amount) - LAG(SUM(sales_amount), 1, 0) OVER(ORDER BY order_date) AS day_over_day_diff FROM orders GROUP BY order_date ORDER BY order_date;

这段SQL先按天聚合销售额，再用LAG拿到前一天销售额，算出差值。如果用自连接，你得先聚合生成临时表，再JOIN自己一天，写法繁琐还容易错。

还有一个高频需求："每个用户相邻两次下单的间隔天数"。这个用LAG写非常漂亮：

SELECT user_id, order_date, LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS prev_order_date, DATEDIFF(order_date, LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date)) AS days_between FROM orders;

第一行prev_order_date是NULL，days_between也是NULL，这正好表示"这是该用户的首次下单"。记得这种数据在后续处理时要过滤NULL。

2.4 首尾取值与访问函数：FIRST_VALUE、LAST_VALUE、NTH_VALUE

这几个函数用于在窗口内取第一行、最后一行、第N行的值。其中FIRST_VALUE比较常用，LAST_VALUE和NTH_VALUE使用时要小心一个经典坑——默认窗口边界问题。

演示一下：

SELECT user_id, order_date, FIRST_VALUE(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS first_order_date, LAST_VALUE(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS last_order_date FROM orders;

猜猜last_order_date是什么？很多人以为是该用户最后一单的日期，但实际结果是当前行对应的订单日期。原因是OVER(PARTITION BY user_id ORDER BY order_date)默认的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——从分组起点到当前行，而不是整个分组。所以LAST_VALUE"看到"的最后一行是当前行自己。

要做对，必须显式声明窗口边界：

LAST_VALUE(order_date) OVER( PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_order_date

这个ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING把窗口扩到整个分组，结果才对。我建议：能用FIRST_VALUE和LAG解决的问题，不要用LAST_VALUE，这个函数默认行为确实容易翻车。

2.5 分组序号函数： NTILE + 辅助定位函数

NTILE(n)把分组内有序数据切成n份，每份尽量等量。它很适合做"分层"分析，比如把用户按消费金额分成高、中、低三档：

SELECT user_id, total_spent, NTILE(3) OVER(ORDER BY total_spent DESC) AS tier FROM ( SELECT user_id, SUM(sales_amount) AS total_spent FROM orders GROUP BY user_id ) t;

tier = 1是消费最高的那批用户，tier = 3是消费最低的。这里NTILE(3)的3代表切成3层，比例基本是 33.3% / 33.3% / 33.3%。

这个方法比CASE WHEN手写阈值要公允得多——手写阈值是拍脑袋定边界，NTILE是看数据自身分布切分。不过要注意：NTILE切分是按记录数均分的，切出来的组人数几乎相等，不代表金额边界均等，要说清楚你在做的是"人数等分"，不是"金额等分"。

顺带提一下，还有一个很冷门但偶尔好用的CUME_DIST()，它算的是当前行值在窗口内的小于等于比例，取值0到1。想快速看某个值排在什么分位，可以偶尔用一下，日常需求用得不多。

3. 窗口帧（Frame）机制：决定你的计算范围

3.1 三种窗口边界与默认行为

窗口函数计算时，真正决定"对哪些行求和"的是窗口帧（frame）。帧就是窗口内进一步圈定的行范围。

可以显式指定帧范围，语法如下：

ROWS BETWEEN <起点> AND <终点>

起点和终点可选的选项包括：

• UNBOUNDED PRECEDING：分组内第一行
• N PRECEDING：往前第N行
• CURRENT ROW：当前行
• N FOLLOWING：往后第N行
• UNBOUNDED FOLLOWING：分组内最后一行

最常见的组合：

SUM(sales_amount) OVER( PARTITION BY region ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount

这就是从分组第一天累加到当前日期的累计金额。

3.2 最后N天的滚动计算

滚动N天求和是窗口帧最经典的应用。比如"最近7天下单金额"，可以这样写：

SELECT order_date, daily_sales, SUM(daily_sales) OVER( ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS rolling_7d_sales FROM daily_sales_table;

这里有个细节要注意：ROWS BETWEEN 6 PRECEDING AND CURRENT ROW是按物理行数往前数6行，如果中间有缺失日期，它算的是"最近6行"而不是"最近6天"。如果要严格按"前6个自然日"来算，得改用RANGE或先补全日期序列。

RANGE和ROWS的区别：ROWS按行数数，RANGE按列值范围算。比如RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW会按日期字段取前6天内所有行，即使有缺失也只有那几天的数据。但RANGE对数据类型和数据库支持有要求，MySQL对RANGE帧只支持部分写法，使用时先查一下版本支持情况。

3.3 聚合窗口函数加不加ORDER BY的坑

说个我真实踩过的坑。最开始我用聚合窗口函数时没太注意ORDER BY的影响，写过这样一个SQL：

AVG(price) OVER(PARTITION BY category) AS avg_category -- 全组平均

这个是全组平均，没问题。但如果你写了：

AVG(price) OVER(PARTITION BY category ORDER BY price) AS avg_to_current

它就从"全组平均"变成"组内从第一行到当前行（按price排序）的平均值"。同样一行函数，只是多排了个序，语义完全变了。业务侧如果没意识到这一点，拿着这个数做对比会得出完全错误的结论。

我的经验是：只有明确想做累计或滚动计算时，才在聚合窗口函数里加ORDER BY；否则一律不加。

4. 窗口函数在真实场景中的经典实战

4.1 场景一：每个品类销量Top 3商品

需求：从商品销售明细表里，取每个品类销量前三的SKU。

步骤拆解：

1. 按category分组，按sales_volume降序排序。
2. 用ROW_NUMBER()标记组内排名。
3. 外层过滤排名≤3。

SELECT category, sku_id, sales_volume FROM ( SELECT category, sku_id, sales_volume, ROW_NUMBER() OVER( PARTITION BY category ORDER BY sales_volume DESC ) AS rn FROM sku_daily_sales WHERE stat_date = '2025-01-01' ) t WHERE rn <= 3;

这段逻辑就是"分组排名取前N"的标准范式，写作顺序也是固定的：先开窗标号，再包一层过滤。记住这个结构，类似需求都能套。

如果数据库支持QUALIFY（如 Databricks、Hive、Snowflake），可以少包一层，直接在WHERE后面写QUALIFY rn <= 3。MySQL和PostgreSQL不支持，统一用子查询写法比较稳妥。

4.2 场景二：用户行为序列中的"最近一次"和"首次次"

需求：找出每个用户最近一次下单的订单信息。这在用户召回和流失分析里很常见。

方法是用ROW_NUMBER()按用户分组、按下单时间倒序，取排名为1：

SELECT user_id, order_id, order_date FROM ( SELECT user_id, order_id, order_date, ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY order_date DESC ) AS rn FROM orders ) t WHERE rn = 1;

"每个用户首次下单"则把ORDER BY order_date DESC改成ASC即可。

这两个需求的本质都是"分组内按时间取极值所在行"。如果你用GROUP BY user_id加MAX(order_date)，你得到的是日期，拿不到那一行订单的完整信息，还得再关联原表。窗口函数一步到位。

4.3 场景三：连续登录天数的判断

需求：找出连续登录超过3天的用户。这个题面试常问，表面是"日期差"问题，本质是"分组"问题。

思路是这样的：

1. 每个用户每天登录去重，得到user_id和login_date。
2. 用ROW_NUMBER()按用户分组、按日期排序，得到序号rn。
3. 关键一步：用login_date减去rn天，得到date_group。如果用户连续登录，date_group会相同；一旦中断，date_group会变化。

WITH user_daily AS ( SELECT DISTINCT user_id, login_date FROM login_log ), with_rn AS ( SELECT user_id, login_date, ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY login_date ) AS rn FROM user_daily ), date_groups AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS date_group FROM with_rn ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM date_groups GROUP BY user_id, date_group HAVING COUNT(*) >= 3;

这个思路的关键在于：login_date每天加1，号数rn也每天加1，两者相减的结果就锁定了"连续区间"。中间断一天，数就对不上了。我第一次看到这个解法时眼前一亮——它把"连续性"这种模糊概念转化为"分组键"的确定性判断。

4.4 场景四：同比环比与移动平均

数据周报里最常见的指标就是"环比昨天"“同比去年同周"“过去7天移动平均”。

前面提过环比用LAG，这里补一个移动平均的写法：

SELECT order_date, daily_sales, AVG(daily_sales) OVER( ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS avg_7d FROM daily_sales_table;

移动平均在平滑短期波动时非常有用。比如某天销售额骤降，一眼看日数据会以为是业务问题，但看7日移动平均就会发现只是常规波动。分析师给老板汇报时，指标背后加一个移动平均列，往往能减少很多误判。

同比的话就要考虑日期偏移了，一般是在日期维度表里关联去年同期的数据，或者用LAG配合固定周期偏移量。比如周维度数据LAG(..., 52)取去年同周，日维度数据LAG(..., 365)取去年同期。注意闰年时用LAG(..., 364)会偏离一天，这一块我处理时会额外加日期校验，免得对不上。

5. 使用窗口函数的五个高频踩坑点

5.1 在WHERE里引用窗口函数：直接报错

开头提过，窗口函数在WHERE之后才计算，所以WHERE rn = 1这种写法会报"unknown column"错误。

正确姿势是子查询或QUALIFY。记住一句话：窗口函数的结果只能用在SELECT、ORDER BY以及套一层的外部查询里。

5.2 PARTITION BY和GROUP BY混用

窗口函数自带分组能力，不需要再写GROUP BY。反过来，如果外层用了GROUP BY，窗口函数的PARTITION BY逻辑就容易和分组冲突，很可能得到你预期之外的行数。

我见过有人这样写：

SELECT category, SUM(sales_amount) OVER(PARTITION BY category) FROM sku_sales GROUP BY category;

这实际能得到每个分类的汇总值，但窗口函数部分很可能"多此一举"。更干净的写法是直接用GROUP BY category接SUM()。窗口函数和GROUP BY不要混用在同一个查询里，除非你非常清楚自己在做什么。

5.3 ORDER BY对帧范围的隐式改变

聚合窗口函数加不加ORDER BY，决定了是"全组聚合"还是"累计到当前行"。排序窗口函数本身必须带ORDER BY，不带会报错。

建议动手写之前先想清楚：我要的是"整体值"，还是"从开始到现在的值"？想清楚再决定要不要加ORDER BY。

5.4 对NULL值的处理

窗口函数计算时，COUNT(col)会忽略NULL，COUNT(*)不会。SUM遇到NULL按0处理，AVG的分母只算非NULL的行数。这些语义和普通聚合完全一致，但在窗口场景更容易被忽视。

比如按用户分组计算客单价，订单金额字段有少量NULL，AVG(sales_amount)不会报错，但它把NULL那单排除在分母之外，算出的客单价可能偏高。如果业务希望NULL按0算，需要COALESCE(sales_amount, 0)后再求均值。

5.5 性能隐患：窗口函数不是银弹

窗口函数比自连接简洁，但不等于任何场景都更快。当数据量大、开窗维度多、排序字段复杂时，窗口函数会导致大量排序和临时计算。你按user_id开窗并ORDER BY order_date，数据库得先按这个排序构建窗口，多组大窗口的排序成本比预聚合高不少。

如果窗口函数跑在大宽表上超时，我的处理思路是：

1. 先在子查询里把字段裁掉，只留分组键、排序列、计算列。
2. 能用LAG不要用自连接，但LAG连续多次跨大窗口时，看看能不能改成两次聚合。
3. 大数据集上取TopN，优先考虑ROW_NUMBER配合过滤，而不是保留全部排序结果再外层截断。

6. 窗口函数的进阶技巧与我的个人体会

6.1 用窗口函数做去重：ROW_NUMBER的隐藏用法

业务表里经常有重复记录，DISTINCT只能去重，却不知道保留哪条。这时候ROW_NUMBER()就有了独特价值：

WITH deduplicated AS ( SELECT *, ROW_NUMBER() OVER( PARTITION BY user_id, order_date ORDER BY created_at DESC ) AS rn FROM orders ) SELECT * FROM deduplicated WHERE rn = 1;

这句的效果是：同一用户同一天有多条订单时，保留created_at最新的那条。比MAX(created_at)分组再关联的写法简洁很多，也是我日常清理数仓重复数据最常用的手段。

6.2 用多个窗口函数组合完成复杂指标

窗口函数可以叠加使用。比如既要用户首次下单日期，又想要用户订单环比增长，可以一条SQL里写多个OVER()：

SELECT user_id, order_date, MIN(order_date) OVER(PARTITION BY user_id) AS first_order_date, LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS prev_order_date, DATEDIFF(order_date, LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date)) AS gap_days FROM orders;

这个查询同时展示：该用户首单时间、上一次下单时间、两次下单间隔。几个字段放在一屏，后续可以直接用于分析用户的复购周期。

6.3 我对窗口函数学习路线的建议

如果让我给新人排一个学习顺序，我会这样排：

1. 先把SUM() OVER(PARTITION BY ...)跑熟，理解"明细带汇总"的感觉。
2. 再练ROW_NUMBER()和RANK()，掌握分组排名TopN的标准写法。
3. 然后上LAG/LEAD，做时间序列的环比、同比。
4. 最后尝试NTILE、帧范围的滚动计算，以及把这些函数组合成更复杂的分析。

每一步都要对着真实业务题练，只看语法书学不会。我当时就是拿线上报表需求反复改SQL，错了再调，慢慢才把窗口函数的执行顺序和帧范围这些抽象概念吃透。

如果你正被某个窗口函数搞得头大，建议先别急着搜"窗口函数大全"，回到需求本身问三个问题：我要不要保留明细行？我是在组内排位还是跨行取值？我想算的是累计值还是整体值？把这三个问题回答清楚，80%的窗口函数场景都能想明白怎么下笔。