MySQL窗口函数实战：分组TopN、连续登录与性能优化

1. 为什么要给SQL开一扇窗GROUP BY做不到的那些事1.1 从一次“分组TopN”需求说起我第一次在MySQL里写分组TopN是在一个销售报表需求里——要按区域找出销售额前5名的门店。当时我的第一反应是GROUP BY加ORDER BY然后发现SELECT出来的全是区域汇总后的单行门店明细被压碎了。后来改用“子查询自连接”的笨办法SQL写了七八十行性能还差得离谱一执行就是全表扫描加隐式临时表。那时候还没有窗口函数这个概念。MySQL 8.0之前想实现“分组内排序后取前几条”这种逻辑只能靠用户变量模拟或者用相关子查询逐行判断排名。这两种写法又绕又难维护面试时讲给候选人听对方也常常一脸茫然。窗口函数正式进入MySQL是在8.0版本它的核心作用可以用一句话概括在每一行数据上开一个“只属于当前分区的滑动窗口”然后在这个窗口内做排序、聚合、偏移取值等运算。说白了它让SQL有能力同时保留“明细行”和“分组上下文”不再需要把明细折叠成聚合结果。1.2 GROUP BY先天不足的三个方面要说清楚窗口函数的价值得先看GROUP BY在哪些场景下确实无能为力。第一是明细与聚合不能共存。GROUP BY一旦执行输出行数就变成了分组数你拿不到“每个分组内的每一行”。但很多业务需求恰恰是“先分组再在组内逐行做顺序计算”——比如计算每笔订单距上一笔订单的间隔天数这种需求用GROUP BY根本无从下手。第二是跨行计算极其别扭。同比环比要拿本月数据跟上月比、移动平均需要往前取N行再求均值这些操作本质是“行与行之间的关联”。GROUP BY是按行集归约天然做不了偏移运算你只能自连接数据表再定义JOIN条件写出来几乎是灾难。第三是所有聚合结果只能输出一份。一张区域销售表同一个区域你可能既想看“总体销售额”又想看“按照店铺排名的顺位号”GROUP BY只能给出总体销售额顺位号必须另写一条SQL再去关联。窗口函数就是在这些痛点上补位的它让每一行都能看到“自己所在分区的统计量”同时行本身还保留着。你可以在一句SELECT里同时拿到本行数据、本行在组内的排名、本组的总计、本行往前N行的值。1.3 一个朴素类比Excel里的“分类汇总透视列”窗口函数的概念老用数据库术语讲容易绕晕。我通常会拿Excel工作簿做类比。想象一张Excel明细表你给表格加了“筛选器”和“分类汇总”同时在最后一列给每个分区填上“序号1、2、3”——这就是窗口函数的雏形。Excel的“分类汇总”会把每一组压成一行而窗口函数更像是在明细表旁边新增了一列“组内排名/组内总和”表里每一行还在只是每行多了几个“组内上下文”的附加字段。这个类比能帮你快速建立直觉窗口函数不产生行数变化它只给每一行增加计算列。理解这一点后后面的执行逻辑、语法细节都会顺很多。2. OVER()三要素与执行顺序窗口函数到底是怎么跑的2.1 语法骨架与三要素窗口函数的标准语法其实非常简洁函数名(参数) OVER ( [PARTITION BY 列1, 列2] [ORDER BY 列3 ASC/DESC] [ROWS/RANGE 窗口框定义] )拿到这条SQL你只需要关注三个东西分区、排序、窗口框。PARTITION BY决定了“窗”按什么维度切开。比如按部门分区那么“窗”里永远只装同一个部门的数据。ORDER BY决定了窗口内的行序。排名函数的结果完全依赖这个排序聚合型窗口函数SUM、AVG等则会按这个顺序逐步累积。窗口框Frame决定了“当前行”能看到的行的范围。可以看到从分区开始到当前行也可以看到前后各N行甚至可以看整个分区。如果不写有ORDER BY时默认是“从分区起点到当前行”没有ORDER BY时整个分区都在窗里。看一个完整示例SELECT emp_id, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn, SUM(salary) OVER (PARTITION BY dept_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM employee;这里rn是每个部门内部按薪资降序排出的序号running_total则是部门内从薪资最低的人开始一路累加的“累计值”。同一个OVER窗口被两个函数复用但各自语义不同——这就是窗口函数最灵活的地方。2.2 MySQL 8.0中的执行顺序窗口函数在哪个阶段生效窗口函数经常被误解成“在GROUP BY之后做二次聚合”其实它的执行位置有严格的顺序规定。一条SELECT语句的完整逻辑执行顺序大致是FROM取数据→ WHERE过滤行→ GROUP BY分组→ HAVING过滤分组→ SELECT阶段此处计算窗口函数→ DISTINCT去重→ ORDER BY排序→ LIMIT截断这意味着几件非常关键的事WHERE里不能用窗口函数。因为WHERE在SELECT阶段之前执行窗口函数那时候还没算出来。想在过滤条件里用排名结果必须先把窗口函数的结果包进子查询或CTE再做外层过滤。-- 错误写法WHERE里使用窗口函数列 WHERE rn 3; -- 正确写法 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn 3;GROUP BY之后窗口函数看到的是“已经分组”的数据。也就是说窗口函数不会破坏GROUP BY的聚合结果它是在GROUP BY结果集之上继续做分区和排序。反过来如果你想对聚合结果再开窗比如按月份分组后再对每个月的销售额做移动平均先GROUP BY再包一层窗口函数是标准玩法。SELECT month, total_sales, AVG(total_sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM ( SELECT DATE_FORMAT(order_date, %Y-%m) AS month, SUM(amount) AS total_sales FROM orders GROUP BY DATE_FORMAT(order_date, %Y-%m) ) t;ORDER BY和窗口函数的关系。窗口函数里的ORDER BY只控制分区内部的排序不会影响最终结果行的输出顺序。最终的输出顺序由SQL最外层ORDER BY决定。如果你发现排名结果不稳定通常是因为外层没有ORDER BY或窗口内排序字段有重复值。2.3 窗口框的边界ROWS与RANGE的差别窗口框是初学者最容易翻车的地方。ROWS和RANGE都用来定义“当前行能看到哪些行”但判断依据完全不同。ROWS是物理偏移精确地按行数取边界。比如ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING不管旁边的行的值是多少就是物理上取当前行之前一行和之后一行。RANGE是逻辑范围按“ORDER BY字段的值”取边界。RANGE BETWEEN 5 PRECEDING AND CURRENT ROW意味着把当前行的排序值减去5凡是落在区间内的所有行都算进窗口。如果多行排序值相同它们会被同时包含或同时排除——因为窗框判断的是值不是行的物理位置。看一个MySQL默认行为的重要细节SUM(salary) OVER (PARTITION BY dept_id ORDER BY salary)这里没有写窗口框但因为有ORDER BYMySQL默认的窗口框是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这个谱系的坑在于如果ORDER BY字段有很多并列值RANGE会把所有并列值都收进“当前行窗口”累加结果可能会一次性跳一大截而不是一行一行累加。如果业务上只需要严格物理叠加务必显式声明ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。FIRST_VALUE和LAST_VALUE对这个细节尤其敏感。你不写窗口框时LAST_VALUE只会取到“当前行”自己因为默认窗框上限是当前行。要取分区最后一行的值必须把窗框拉开LAST_VALUE(salary) OVER ( PARTITION BY dept_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_salary这一行代码很多人没写ROWS结果就是LAST_VALUE等于当前行自身怎么debug都发现不了毛病。3. 六大类窗口函数拆解哪一个场景该用哪一个3.1 排名三兄弟ROW_NUMBER、RANK、DENSE_RANK这是窗口函数里最常被问到的组合也是面试时的标准送分题。三者的区别用一张表就能说清楚函数行为特征并列值的排名处理ROW_NUMBER()按ORDER BY顺序生成连续编号并排列随机定序编号不重复RANK()并列值占同一个名次名次有间隙如1、1、3DENSE_RANK()并列值占同一个名次名次无间隙如1、1、2举例某班成绩98、98、95、93。ROW_NUMBER会输出1、2、3、4两个98分谁排前面取决于排序稳定性不保证一定有规则。RANK会输出1、1、3、4第二名被跳过了。DENSE_RANK会输出1、1、2、3没有跳号。实际业务里如果只想取“前N条物理记录”用ROW_NUMBER如果是比赛排名、绩效等级这类看重“并列”的语义用RANK如果既要并列又要后续名次连续用DENSE_RANK。3.2 聚合窗口函数在窗口内做SUM、AVG、COUNT、MAX、MIN聚合窗口函数不是新函数而是把SUM、AVG这些聚合函数放到OVER子句里。它们和普通聚合最大的区别是普通GROUP BY把多行压成一行窗口聚合保留所有行同时还附上聚合值。最常见的用法是“累加值”和“移动平均”。比如统计每个月末的累计回款金额SELECT month, amount, SUM(amount) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount FROM monthly_sales;移动平均在金融、运营分析里几乎是刚需算过去3个月的订单平均金额来平滑趋势。SELECT month, amount, AVG(amount) OVER ( ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_3m FROM monthly_sales;窗口聚合的计算成本要特别注意它是全分区扫描的。如果分区特别大比如一个PARTITION有几十万行那么每个窗口都要扫一遍该分区的数据CPU和内存都可能吃紧。这个放到第五章细说。3.3 偏移取值函数LAG、LEAD、FIRST_VALUE、LAST_VALUELAG取“分区内上一行”的值LEAD取“分区内下一行”的值。语法为LAG(expr, offset, default) OVER (...)offset控制往前看几行默认是1default是找不到行时返回的兜底值默认是NULL。业务上最经典的用法是“算环比”本月销售额与上月销售额的差值或增长率。SELECT month, amount, LAG(amount, 1, 0) OVER (ORDER BY month) AS prev_month_amount, amount - LAG(amount, 1, 0) OVER (ORDER BY month) AS m_m_diff FROM monthly_sales;FIRST_VALUE取分区的第一行LAST_VALUE取分区最后一行。前面强调过LAST_VALUE默认窗口框只到当前行使用时必须显式扩大窗口框否则会得到错误结果。3.4 分布与分桶函数NTILE、CUME_DIST、PERCENT_RANKNTILE(n)把分区内的行尽量均匀地分到n个桶里返回桶编号1到n。有些公司做“薪资分档”“用户分层”会用它。注意行数不能被桶数整除时前几个桶会比后面的多一行这是正常现象。CUME_DIST()返回当前行在分区内的累积分布取值范围在(0,1]。它表示“小于等于当前行的值所占的比例”。PERCENT_RANK()返回相对排名百分比范围是[0,1]第一行永远是0。这两个函数在统计场景中用得不算太多但面试官偶尔会拿它们考察你“是否真的理解窗口的排序语义”。记住一点CUME_DIST是按值分布PERCENT_RANK是按位置分布很容易混淆。3.5 NTH_VALUE与它的限制NTH_VALUE(expr, n)返回窗口内第n行的值。比如取每个部门薪资第三高的人NTH_VALUE(salary, 3) OVER ( PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )要注意n不能为0或负数而且它同样受窗口框限制。想要取到整个分区的“第3行”必须把窗框终点扩到UNBOUNDED FOLLOWING否则窗框只到当前行时分区后面第3行根本看不见。MySQL对窗口函数的命名表达式支持也需要注意在MySQL 8.0中窗口函数不能嵌套不能直接用别名替代表达式嵌套使用比如NTH_VALUE(prev_amount, 1)这种写法会报错。4. 销售排行榜、连续登录和累加库存三个业务场景练到熟练4.1 场景一学生课程成绩分组TopN这个场景几乎是窗口函数的“敲门砖”。假设有一张学生成绩表student_scorestudent_id, course, score。需求找出每门课程前三名的成绩记录。SELECT student_id, course, score, rn FROM ( SELECT student_id, course, score, ROW_NUMBER() OVER (PARTITION BY course ORDER BY score DESC) AS rn FROM student_score ) t WHERE rn 3;用RANK还是DENSE_RANK取决于业务上“并列第2名算不算第2名”。如果需求是“前三名”通常DENSE_RANK更合理因为不会因为两个并列第1就把第3名挤掉。这个细节我在面试里经常用来判断候选人是否真的理解业务。4.2 场景二连续登录N天问题连续登录是数据分析师的经典题。表结构user_loginuser_id, login_date。要求统计连续登录3天及以上的用户。思路是这样的窗口函数按用户分区按登录日期排序生成行号然后用“登录日期减去行号”得到一个日期值。如果是连续日期减法结果是一样的哪天断了这个差值就会变。SELECT user_id FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS group_date FROM user_login ) t GROUP BY user_id, group_date HAVING COUNT(*) 3;注意MySQL 8.0.19及之前的版本在同一条SQL里写两个相同的窗口表达式会被优化器识别并复用但为了可读性建议把窗口定义提取出来用WINDOW子句SELECT user_id, login_date, ROW_NUMBER() OVER w AS rn, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER w DAY) AS group_date FROM user_login WINDOW w AS (PARTITION BY user_id ORDER BY login_date);WINDOW子句可以一次性定义窗口后续多个函数共用既减少重复书写也降低出错概率。这个技巧在日常开发中很实用。4.3 场景三累加库存与实时余额电商库存台账里常有“入库为正、出库为负”的流水表要算任意时刻的累计库存用窗口聚合最直接。SELECT product_id, op_date, quantity, SUM(quantity) OVER ( PARTITION BY product_id ORDER BY op_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS stock_after_op FROM stock_flow;这里我特意看了执行计划确认没有索引时MySQL会对product_idop_date做排序并落临时表。数据量一大这条SQL就慢。后面第五部分会讲怎么配合索引优化。4.4 场景四窗口函数与分页配合的单号生成生成单据流水号时窗口函数也可以派上用场。比如在订单表上按客户分组生成订单序号再结合JOIN拿到“同一客户的第2笔订单”。窗口函数的结果本来就在SELECT阶段生成不需要额外建表或存储过程写起来比变量法清爽太多。另一个高频场景是“同比环比”综合在一个子查询里先按月汇总销售额再用LAG取上期值最后算增长率。这套组合几乎是经营报表的标准模板值得单独沉淀下来复用。5. 窗口函数的性能隐患与常见坑位跑了几个千万级表之后的总结5.1 窗口函数性能受什么影响窗口函数不是银弹。在千万级数据上跑窗口函数最常见的三个问题就是全分区排序、临时表落盘、内存溢出。窗口函数内部实现靠的是排序算子。每当执行PARTITION BY和ORDER BY时MySQL需要物理上把每个分区中的行排好序。如果PARTITION BY和ORDER BY字段没有合适的索引就会触发Using filesort。分区数量越多、每个分区内排序数据量越大成本和临时文件就越大。另外很多窗口函数如RANK、ROW_NUMBER、NTILE等需要把整个分区数据读进内存才能准确计算排名出结果前无法流式输出。MySQL会优先使用内存临时表内存不够就落盘到磁盘临时表一次大查询可能产生几个GB的临时文件。实测经验一张500万行的订单明细表按客户分区、按下单时间排序计算ROW_NUMBER在没有复合索引时耗时从2秒涨到20秒以上。吃掉的时间几乎全在排序阶段。5.2 基于执行计划优化窗口函数SQL第一步永远是看EXPLAIN。窗口函数相关SQL里你需要重点观察这几项type列是否出现了ALL全表扫描Extra列是否出现Using filesort排序Extra列是否出现Using temporary临时表。有任何一个出现都要先考虑索引。最优策略是建一个和窗口函数分区、排序完全匹配的复合索引ALTER TABLE employee ADD INDEX idx_dept_salary (dept_id, salary DESC);这里索引顺序很关键PARTITION BY的列放前面ORDER BY的列放后面。MySQL的索引匹配遵循最左前缀原则索引先按dept_id分块再在块内按salary排序正好满足窗口函数“按部门分区、按薪水排序”的需求。还有几个进阶想法可以分享先裁剪再开窗。在子查询里先做WHERE过滤掉不需要的数据再对外层结果开窗窗口越小越快。能不用PARTITION BY就不用。有些场景你以为要分区实际上按全局排序也能出结果全局排序比多分区排序的执行计划通常更简单。避免在窗口函数中使用表达式。比如ORDER BY DATE_FORMAT(login_date, %Y-%m)这种写法会让索引失效窗口函数被迫跑全分区排序。如果业务上非要按格式化后的日期排序考虑新建冗余字段。小心行数膨胀。窗口函数本身不改变行数但如果你把窗口聚合结果和明细JOIN起来行数可能随着JOIN成倍膨胀。能在一个SELECT里完成的事不要拆成两条SQL再关联。5.3 截图式的坑位清单容易踩但不自知的陷阱把实际工作中遇到的高频坑整理一下避免大家重复踩。LAST_VALUE的默认窗框陷阱默认只到当前行取的是当前行自身必须显式添加ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。这个坑的隐蔽性很强因为结果不会报错只是悄悄错误。RANGE与ROWS对SUM累计值的差异ORDER BY列有重复值时RANGE会把并列值一起纳入同一窗口导致累计值一步跳变。想严格按物理行累加用ROWS。窗口函数与DISTINCT同时使用时的语义MySQL 8.0中当SELECT DISTINCT与窗口函数同时存在时窗口函数会在去重之前执行。逻辑顺序上窗口函数算完DISTINCT才生效。如果发现去重后的行数不对先想想这个执行顺序。ORDER BY稳定性问题当窗口内ORDER BY字段有大量重复值时ROW_NUMBER的分配顺序不确定。要保证结果可复现在ORDER BY末尾追加一个唯一列比如主键或唯一流水号。PARTITION BY列过多导致数据倾斜分区数量多可能把任务拆散但每个分区单行数过大时单个分区会成为性能热点。遇到这类问题可以结合业务调整分区粒度或者提前按分区键对数据进行预处理。5.4 窗口函数与GROUP BY、自连接的正确选型最后给一个简单的选型判断标准。能在一步GROUP BY解决的事不要为了“炫技”用窗口函数但一旦发现SQL里出现三层以上子查询、多次自连接、反复用用户变量时先停下来思考是不是窗口函数能一步搞定。GROUP BY适合“整体汇总”输出聚合结果不要明细窗口函数适合“分组内计算并保留明细”输出行数与输入一致自连接适合“行与行之间的一对一关联”但性能通常不如窗口函数因为JOIN可能产生多对多中间结果。我自己经历过的真实教训早期用自连接求“每个用户连续登录天数”SQL跑了50秒改成窗口函数后耗时降到3秒。代码量也从40行缩到了15行维护成本大幅下降。从那以后凡是模棱两可的场景我都会优先试试窗口函数。值得一提的是MySQL 8.0.30之后窗口函数的执行计划做得越来越好数据量大时也能更聪明地节省临时表空间。如果你还在用5.7建议在升级数据库版本时优先把这类SQL列为重点回归对象窗口函数带来的查询逻辑简化往往比性能优化本身更值得。我现在的习惯是拿到需求先识别“是不是分组内排序计算”是的话直接想到窗口函数开窗之前先确认分区、排序字段有没有匹配索引写完SQL立刻看执行计划确认没有Using filesort和Using temporary才收工。这套流程跑熟了窗口函数基本不会给你添乱。