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完全忽略并列强行给每行分配唯一序号连续递增分页、去重、稳定的取前三RANK并列时同名次占参与排名的行数位次按并列人数跳过体育排行榜、含并列的奖项名单DENSE_RANK并列时同名次下一位恒等于并列名次加一积分段位、销售阶梯比如按销售额对一批门店排序排名前三的门店销售额完全一样此时 ROW_NUMBER 会输出 1、2、3RANK 会输出 1、1、1而且第四名的行变成了 4DENSE_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_amountPARTITION 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 (...) 1MySQL 直接报错。原因是窗口函数在 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 下的输出。把这些基础结果吃透了后面不管遇到多复杂的“排名 聚合”需求都能很快拆出正确的写法。窗口函数说到底就是“给每一行配一个计算上下文”上下文对了结果就不会跑偏。