MySQL窗口函数实战：排名与聚合一步到位

很多做数据分析的同学，第一次遇到“既要看每一笔订单，又想在订单旁边列一个当日销售额排名”的需求，第一反应都是先 GROUP BY，再回头连表补齐明细。MySQL 8.0 提供窗口查询以后，这一类“同时保留明细和聚合”的场景就变成了一个 OVER() 语法的事。这篇文章我会围绕窗口查询里最常用的两大方向——排名和聚合——把原理、使用边界和实战套路拆开讲，适合还在用手写变量模拟排名的老开发，也适合刚接触窗口函数、想知道怎么落地的新人。

1. 为什么 GROUP BY 解决不了“既要明细又要排名”的问题

先说一个我经常在业务报表里碰到的真实需求。运营同事拿来一张订单明细表，字段大概是业务日期、门店编号、品类编号、销售额、订单量，要求是“把所有订单明细原样保留，同时给每个门店每个品类在后面加一列销售排名”。

这个需求的本质是：既要明细级的每一行，又要一个基于分组计算出来的结果。GROUP BY 的天然缺陷在于，一旦写了分组和聚合，一行就只剩一组一个值，明细行被折叠。如果非要 GROUP BY 来实现，就得先把分组聚合结果查出来，再通过主键或联合键连回原表，SQL 里凭空多出一次关联和一层子查询。业务维度稍微一多，比如再细分到城市、周、渠道，关联键就会变得很长，漏掉一个维度就可能导致整张结果表的数据翻倍，排查起来非常痛苦。

1.1 窗口函数的运行时机：先完整扫描，再逐行计算

窗口函数的执行位置很特殊。大多数人的理解是“它也算聚合”，其实更准确的说法是：它在 WHERE 已经过滤、GROUP BY 已经分组之后，在 SELECT 阶段对每个窗口分区逐行计算。

这解释了窗口函数最核心的行为：窗口函数不会减少行数。它更像是给每一行贴上了一个“旁边的小标签”，计算时能看到整个分区内的所有行，但输出时仍然保留当前行。如果你平时用惯了 GROUP BY 那种“折叠成一行”的逻辑，第一次看到窗口函数可能反而不适应——原因就在这儿：窗口函数根本没有合并行的能力，它只有给行追加计算值的能力。

1.2 先过滤还是先开窗，结果完全不一样

窗口函数的计算范围受 WHERE 约束。举个例子，需求是“统计上半年每个门店的销售额排名”。如果直接在 WHERE 后面筛掉上半年之前的数据，那么排名只会在筛选后的数据范围内产生；如果先把数据全量查出来，再在嵌套查询里做排名，排名范围就会从“上半年”变成“全部数据”。

这里有一条很关键的实操准则：能在 WHERE 里过滤掉的，就不要放到子查询里再过滤。前面这种写法不仅逻辑清晰，还能让优化器在最早阶段就缩小数据量；后面这种写法一不小心就会得到错误的排名数据，而且在执行计划里多出临时表，性能也会明显变差。

1.3 窗口函数基本语法速览

窗口函数的语法拆开看只有三个部分：

• 函数名：可以是排名函数、聚合函数、取值函数；
• OVER 关键字：标记这是窗口计算；
• OVER 括号里的内容：也就是窗口定义，由 PARTITION BY 分区、ORDER BY 排序、帧范围三部分组成。

SELECT business_day, store_id, category_id, amount, ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY amount DESC) AS row_num FROM order_detail WHERE business_day BETWEEN '2026-01-01' AND '2026-06-30';

很多人会问：PARTITION BY 和 GROUP BY 有什么区别？按我的理解，GROUP BY 是把多行合并后得到一个汇总结果，PARTITION BY 则是把数据先切成若干块，计算时每块内部互相可见，但行并不会被合并。你可以把 PARTITION BY 理解为“给窗口划定计算范围的分区键”，它的作用只在于确定哪些行参与计算。

2. 排名函数三兄弟：ROW_NUMBER、RANK、DENSE_RANK 的差异与常见误区

MySQL 里用户接触最多的排名函数就是这三个。它们都要求 OVER 里带 ORDER BY，作用也都是在指定的分区和排序规则下给每一行分配一个排名数字。但三者的规则差异非常关键，用错了，排行榜的序号会跟预期差出天去。

2.1 三个函数的排序规则差异对比

比如按销售额对一批门店排序，排名前三的门店销售额完全一样，此时 ROW_NUMBER 会输出 1、2、3；RANK 会输出 1、1、1，而且第四名的行变成了 4；DENSE_RANK 会输出 1、1、1，第四名是 2。这个差距在取 Top N 时尤其致命。

2.2 Top N 的选型：硬性取三名用 ROW_NUMBER，含并列上榜用 RANK

需求是“每个品类取销售额前三的门店”，听起来简单，实际写出来很容易出问题。如果用了 ROW_NUMBER，那无论有多少并列，最终只会拿到恰好三行，适合“一定要三名”的硬性取数；但如果业务上认为“只要销售额并列进入第三名，也可以一起展示”，那 ROW_NUMBER 就会把并列的第四行砍掉，榜单反而漏掉了该上榜的门店。此时正确的选择是 RANK 或者 DENSE_RANK。

SELECT store_id, category_id, amount, rn FROM ( SELECT store_id, category_id, amount, RANK() OVER (PARTITION BY category_id ORDER BY amount DESC) AS rn FROM monthly_category_sales ) t WHERE rn <= 3;

这里内层子查询先把排名算出来，外层再过滤 rn <= 3。RANK 的特点是并列时同名次、下一名会跳号，所以结果行数可能比 3 多，但这恰恰符合“并列一起上榜”的业务规则。如果业务要求既保留并列逻辑，又希望下一名序号连续，那我一般会换 DENSE_RANK，看需求更倾向哪一种。

2.3 常见误区：排序方向写反，以及 NULL 值的排位

排名函数里的 ORDER BY 方向决定了“谁是第一名”。ORDER BY amount DESC 表示分区内部金额从高到低滚动，ROW_NUMBER 从 1 开始往下分配；如果写成 ASC，排到 1 的就是金额最低的那个门店。业务里最常见的错误就是把 ASC 和 DESC 搞反，然后发现“第一名”变成了表现最差的那个对象。

另一个容易被忽略的坑是 NULL 值位置。排名函数默认把 NULL 视为最小值，在升序时排最前，在降序时排最后，但不同版本和不同写法下的表现并不完全一致。我的习惯是提前用 COALESCE 把可能为空的数字字段转成 0，例如 COALESCE(amount, 0)，省得环境版本一换，排名升降顺序就变了。

3. 聚合窗口函数：累计值、移动平均、占比，一个 OVER() 全搞定

排名函数只是窗口查询的敲门砖，聚合函数配合 OVER 使用才是真正做业务分析的高级玩法。SUM、AVG、COUNT、MAX、MIN 这些我们熟悉的聚合函数，只要后面跟上 OVER，就全部变成了聚合窗口函数，输出结果不会折叠行。

3.1 累计销售额：ORDER BY 决定“累计截止到今天”

最典型的案例是“每日累计销售额”。业务上既要每天的单日销售额，又想要从月初到当天的累计值。窗口语法里这样写：

SELECT business_day, daily_amount, SUM(daily_amount) OVER (ORDER BY business_day) AS running_amount FROM daily_sales WHERE month = '2026-05';

这里的核心是 OVER 里的 ORDER BY business_day。没有 PARTITION BY，意味着整张结果表是一个大分区；有 ORDER BY，意味着函数不是对全表求和，而是对从最早一行到当前行截止的局部范围求和。这就是“累计”二字的由来。

3.2 分区累计：每个门店各自累计互不干扰

如果需求是“每个门店各自按天累计”，只加一个 PARTITION BY 就行：

SUM(daily_amount) OVER (PARTITION BY store_id ORDER BY business_day) AS store_running_amount

PARTITION BY 的作用就是把一个大分区切成多个小分区，每天每店的累计互不干扰。再进阶一点，可以在 ORDER BY 后面追加 ROWS BETWEEN 来滑动窗口，实现移动平均、移动最大最小等更复杂的计算。

3.3 滑动窗口帧：ROWS BETWEEN 是窗口函数的分水岭

聚合窗口函数默认的帧范围是从分区第一行到当前行。如果我们想要的是“近三天的移动平均”，还按默认范围，那前两天的均值就会因为行数不足而跟预期不一致。解决办法是手动指定帧范围：

AVG(daily_amount) OVER ( PARTITION BY store_id ORDER BY business_day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3d

这个帧范围的写法意思是：以当前行为基准，往前取两行，连上当前行，总共三行参与计算。同理，“ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING”可以计算“从当前行到末尾”的累计值，适合求“剩余目标缺口”。

帧范围是窗口函数里最容易被忽略、也最容易产生诡异结果的部分。没有指定帧范围时，默认行为并不是“整个分区参与计算”，而是“从分区起点到当前行”。对 SUM 来说是累计，对 AVG 来说是动态均值，对 MAX 来说是从起点到当前行的最大值。需求如果没想清楚，直接写 AVG(v) OVER (ORDER BY d)，得出的根本不是想要的固定窗口均值，画出来的曲线会让人完全摸不着头脑。

3.4 占比计算：一个字都不用多写

还有一个高频需求是“计算每个门店占整个集团销售额的比例”。用子查询取整体合计再做除法也能实现，但窗口函数可以很简单地一步到位：

SELECT store_id, category_id, amount, amount / SUM(amount) OVER () AS pct FROM monthly_category_sales;

OVER 括号里什么都不写时，分区就是整张表，SUM(amount) OVER () 的结果是全局总销售额。再用每行的 amount 去除一下，就得到了占比。同理，加上 PARTITION BY 之后也能得到分区占比，比如每个品类内部的门店占比。用窗口函数做占比的另一个好处是，不需要再多写一层关联，可读性也高很多。

4. 往前看、往后比：LAG、LEAD 与 NTILE 的实战用法

除了排名和聚合，窗口查询里还有一类“取值”函数在业务中用得非常多，那就是 LAG、LEAD。它们的作用是把某一列在分区内往前或往后移动，让当前行能够直接引用上一行或者下一行的值。这个能力在时间序列分析里几乎是必需品。

4.1 LAG 计算日环比，告别自关联

假设有一张“门店每日销售额”表，要算每个门店每天的环比增长率。常规思路是把同一张表按 business_day 关联一次，把前一天的销售额取出来。但用 LAG，完全不需要自关联：

SELECT business_day, store_id, sales_amount, LAG(sales_amount, 1) OVER (PARTITION BY store_id ORDER BY business_day) AS prev_amount, (sales_amount - LAG(sales_amount, 1) OVER (PARTITION BY store_id ORDER BY business_day)) / LAG(sales_amount, 1) OVER (PARTITION BY store_id ORDER BY business_day) AS day_over_day FROM store_daily_sales;

LAG 有三个参数：表达式、偏移量、默认值。偏移量默认是 1，表示取上一行；如果当前行已经是分区第一行，LAG 会返回 NULL，就可以用第三个参数指定默认值。比如写成 LAG(sales_amount, 1, 0)，第一行就不会出现分母为零的尴尬情况。

4.2 LEAD：看一眼明天的数据，再决定今天的动作

LEAD 和 LAG 方向相反，它取的是下一行的值。比如我们想判断“未来三天是否可能缺货”，就可以用 LEAD 拿到明天和后天的预测销量，再来和当前库存做对比。逻辑本身不难，关键是写之前想清楚：LAG 是回看，LEAD 是前瞻。

LEAD 也比较适合做“同比上月同期”之类的场景，只要把 ORDER BY 的粒度对齐到月份，偏移量设成 1，就能拿到上一期的数据，再和当前期做减法或除法。

4.3 NTILE：把数据平分成 N 段，做客户分层和 ABC 分析

NTILE 和前面的排名函数不同，它不管具体排名数字，而是把分区内的所有行尽量平均地切成 N 个桶，给每行分配一个桶号。业务里最常见的应用是四分位区间、ABC 分析、RFM 分层。

SELECT customer_id, total_amount, NTILE(4) OVER (ORDER BY total_amount DESC) AS bucket_no FROM customer_gmv;

NTILE(4) 的意思是把客户按消费额从高到低平均分成四段，排名前 25% 是第 1 段，接下来的 25% 是第 2 段，以此类推。注意 NTILE 分配的是桶号，不是排名数字。如果你在需求里看到“前 25% 属于核心客户”“25% 到 50% 属于成长客户”，直接用 NTILE 就很合适。

4.4 多个窗口函数叠加，拼出一个完整分析指标

窗口函数之间是可以叠加使用的。例如“先锁定排名前五的门店，再计算这些门店各自的累计销量”，就可以先通过子查询用 ROW_NUMBER 过滤出前五的门店，再在外面套一层 SUM ... OVER (PARTITION BY ... ORDER BY ...)。

这种嵌套模式在实际业务里非常普遍，销售驾驶舱看板中的大量指标，都是“维度明细 + 时间窗 + 排名 + 累计”组合出来的。嵌套时有一个要点：内层子查询里的 WHERE 已经过滤掉了不属于前五的行，外层做累计时看得见的就是这五行的集合，累计起点也是这五行的第一行。如果你希望累计的基底是所有门店，而只是展示前五名，就需要调整写法，不能简单依赖外层过滤。

5. 窗口查询的优化与坑：排序、临时表、与子查询配合的注意事项

窗口函数最大的隐藏成本是排序。每出现一个带有 ORDER BY 的窗口函数，MySQL 都可能为对应的分区做一次排序；数据量一上来，临时文件和磁盘排序就会成为瓶颈。

5.1 看到 Using filesort 和 Using temporary，先别慌

在一个几百 GB 的大表上跑窗口函数，EXPLAIN 里出现 Using filesort 和 Using temporary 是很正常的。窗口函数本身就需要排序，这不是“错误提示”，真正的优化思路是：

• 尽量在 WHERE 阶段缩小数据范围，不要全表开窗；
• 让索引能和 PARTITION BY、ORDER BY 匹配，例如在 (store_id, business_day) 上建联合索引；
• 只需要最近一段时间的数据就直接过滤，避免把历史全量数据拉进窗口。

单次排序通常可以接受；但如果一个查询里同时出现四五个窗口函数，而且它们的排序键各不相同，MySQL 客观上就要做好几轮排序。这种情况下，我一般会优先考虑“先用子查询把窗口结果缩小一次，再在外面叠加其他窗口”，这样看起来多了一层嵌套，实际执行反而更稳定。

5.2 窗口函数不能直接出现在 WHERE 或 GROUP BY 里

有一个很经典的报错：想写 WHERE ROW_NUMBER() OVER (...) = 1，MySQL 直接报错。原因是窗口函数在 WHERE 之后才执行，WHERE 阶段根本看不到这个计算结果。正确操作是包一层子查询：

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY amount DESC) AS rn FROM monthly_sales ) t WHERE rn = 1;

这个规则同时也提醒我们：窗口函数不是用来过滤的，它更适合用来计算和标注。过滤动作必须在外层完成。

5.3 从“用户变量模拟”迁移到窗口函数，心态要转过来

MySQL 8 之前，排名这类需求往往靠用户变量逐步累加来模拟，写出来的 SQL 像小作文一样长，而且对执行顺序极其敏感。窗口函数出来以后，这类写法基本可以退役了。迁移时只需要记住：用户变量那套写法里“先排序再逐行赋值”的中间状态，在窗口函数里恰好对应“PARTITION BY + ORDER BY + 帧范围”，区别只是现在由 SQL 引擎帮你管理了所有状态。

如果你还在维护老版本 SQL，没办法用窗口函数，那我建议至少把这类逻辑抽成视图或者存储过程，集中管理；一旦数据库支持窗口函数，改动成本会低很多。

5.4 帧范围关键词要手写练熟

帧范围的关键词组合并不多，但一定要亲手写几遍：

• UNBOUNDED PRECEDING：分区起点；
• n PRECEDING：往前 n 行；
• CURRENT ROW：当前行；
• n FOLLOWING：往后 n 行；
• UNBOUNDED FOLLOWING：分区末尾。

这几个组合直接决定了窗口函数到底对哪些行做计算。用错了，累计会变成跳动值，移动平均会变成前缀均值，而且这种错误在数据量小的时候很难一眼看出来，往往要等业务方反馈“数值不对”才回头排查。

最后给刚接触窗口查询的读者一个笨但有效的训练方法：找一张几百行的明细表，分别用 ROW_NUMBER、RANK、DENSE_RANK 跑一遍，再看一下 SUM OVER 和 AVG OVER 在不同 ORDER BY 下的输出。把这些基础结果吃透了，后面不管遇到多复杂的“排名 + 聚合”需求，都能很快拆出正确的写法。窗口函数说到底就是“给每一行配一个计算上下文”，上下文对了，结果就不会跑偏。