1. 面试官为什么老盯着窗口函数问1.1 一道题就能看出你会不会写 SQL面到数据库这一轮抛出一道“求每个部门薪资前三名的员工”十个候选人里有七个第一反应是写子查询加GROUP BY跑出来发现只能拿到每个部门一个人的最大薪资剩下两个怎么都拿不到。转而写自连接代码很快膨胀成三层嵌套自己都读不下去。这个场景几乎每隔一阵就会在技术群里被翻出来讨论因为它精准地暴露了一个分水岭你能不能把“分组内的排序与排名”这件事用一条清晰、可维护的语句表达出来。窗口函数就是干这个的。它在 MySQL 8.0 里正式进入官方语法专业一点的说法叫 OLAP 函数也叫分析函数。它的核心能力是在不折叠结果行数的前提下对“某个分组、某个顺序”下的一批行做计算然后把结果贴回每一行。这跟GROUP BY的思路完全不同——GROUP BY是“多行归一行”窗口函数是“多行看一行、行数不动”。面试官爱问它原因很直接。第一它同时考察分组、排序、集合运算三个基础功第二它牵扯窗口帧这种需要真正理解执行逻辑才能答对的概念第三它能把“只会写增删改查”和“能处理真实业务报表”的人区分开。你要是在简历上写了“精通 SQL”结果被一道排名题卡住后面的问题基本就不用聊了。1.2 它到底解决了哪些以前的痛在没有窗口函数的年代做数据分析类 SQL 主要靠三条路自连接、用户变量、应用层处理。这三条路各有各的难受。自连接求排名思路是“对每一行数一数有多少行的值比我大”写法上要JOIN自己再GROUP BY数据量一大性能立刻塌方用户变量法是 MySQL 5.7 时代的民间智慧靠rn : rn 1这种手工计数器模拟排名但它对ORDER BY的执行顺序非常敏感稍不注意结果就错位属于“能跑但不敢改”的代码应用层处理就是把全量数据捞到程序里排序数据量一大内存直接爆掉。窗口函数的出现把这三条路统一成一套声明式语法。你只描述“按什么分组、按什么排序、算哪几行”优化器自己去安排执行。可读性、正确性、性能三方面同时改善这也是它成为面试高频考点的根本原因。它不是一个花架子语法糖而是实打实改变了分析型 SQL 的写法。再补一句MySQL 8.0 之前的版本比如 5.7完全不支持窗口函数跑到 5.7 上执行会直接报This version of MySQL doesnt yet support window functions。面试时如果面试官问“你们生产环境是什么版本”你顺口提到这个版本边界会显得你真的在生产环境里踩过。现在很多公司的存量库还在 5.7迁移到 8.0 的节奏并不整齐这一点在实际工作中经常要面对。2. 把语法骨架和窗口帧吃透面试就稳了一半2.1 OVER 子句的三段式结构窗口函数的语法骨架长这样函数名([参数]) OVER ( PARTITION BY 分组列 ORDER BY 排序列 [ROWS|RANGE BETWEEN 起点 AND 终点] )把它拆成三块理解最省事。第一块PARTITION BY可以理解成“重新分组”。它和GROUP BY的分组概念一样但区别在于分组之后行数不变每行都能看到自己所属的那个组的整体信息。不写PARTITION BY时整张结果集算作一个分区。第二块ORDER BY决定分区内部行的排列顺序。注意这个排序只影响窗口函数内部的计算顺序不会改变最终SELECT输出的行顺序除非你在语句末尾再写一个外层的ORDER BY。这是新手最容易混淆的点之一面试里也常被拿来当追问。第三块是窗口帧也就是ROWS/RANGE BETWEEN ... AND ...用来指定“当前行计算时到底看分区的哪一段”。不写的话有默认值默认值在不同场景下还不一样下面单独讲。用一句话串起来先按PARTITION BY切块再按ORDER BY排好最后按窗口帧圈定要参与计算的一段行。三块各管一件事顺序不同结果就不同。2.2 窗口帧才是真正拉开差距的地方窗口帧是面试里区分度最高的一环。很多人会写排名但一问“累计求和为什么第一行就是它自己”就答不上来了。窗口帧有两种模式ROWS按物理行数来圈定范围ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING就是“当前行的前一行到后一行”一共三行。RANGE按值来圈定范围RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示“从分区第一行到当前值相等的所有行”注意是值相等遇到并列值会一起纳入。默认帧的规则必须背下来场景默认窗口帧实际含义有ORDER BYRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区起点到当前行含并列值无ORDER BY整个分区分区内所有行这个默认规则直接解释了两个经典现象。一是SUM() OVER (ORDER BY ...)会变成累计求和二是LAST_VALUE()不加帧的时候返回的是“当前行”而不是分区最后一行——因为默认帧的终点就是CURRENT ROW。想拿真正的最后一行必须显式写LAST_VALUE(salary) OVER ( PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )这个坑我在生产代码里见过不止一次。开发同学写完LAST_VALUE发现结果和FIRST_VALUE差不多排查半天以为是数据问题其实是窗口帧没写。面试时如果你能主动指出这一点面试官对你的评价会明显不一样。2.3 逻辑执行顺序决定了窗口函数能写在哪面试经常追问“为什么WHERE里不能直接用窗口函数”答案是逻辑执行顺序。SQL 语句的逻辑执行顺序大致是FROM→WHERE→GROUP BY→HAVING→SELECT此时计算窗口函数→ORDER BY→LIMIT。窗口函数的计算发生在SELECT阶段也就是WHERE已经执行完之后。WHERE跑的时候窗口函数的结果还没算出来自然引用不到。同理GROUP BY之后行已经被折叠了窗口函数是在折叠后的结果集上再开窗所以两者混用时窗口函数看到的是聚合后的行。这个细节在写“分组占比”类需求时特别关键SELECT dept, SUM(salary) AS dept_total, SUM(SUM(salary)) OVER () AS all_total, ROUND(SUM(salary) / SUM(SUM(salary)) OVER () * 100, 2) AS pct FROM emp_salary GROUP BY dept;这里SUM(salary)是聚合函数外面再套一层SUM(...) OVER ()才是窗口函数。两层嵌套看着别扭但逻辑上完全说得通内层把部门折叠成一行外层在这个结果上算总和。这种写法在报表 SQL 里非常常见面试里也爱考。3. 高频窗口函数逐个拆解顺带说清它们的区别3.1 排名三兄弟ROW_NUMBER、RANK、DENSE_RANK这三个函数是面试出场率最高的没有之一。它们都用在ORDER BY之后但遇到并列值的处理方式完全不同。函数并列值处理排序号示例值 100,100,90,80ROW_NUMBER()不并列强行给唯一序号1, 2, 3, 4RANK()并列跳号1, 1, 3, 4DENSE_RANK()并列不跳号1, 1, 2, 3怎么选看业务语义。“取每个部门薪资最高的三个人”如果两个人薪资并列第一严格来说你只想留三个人那就用ROW_NUMBER()配合WHERE rn 3结果恰好三行如果业务要求“并列第一的人都要保留”那就用RANK()或DENSE_RANK()结果可能是四行甚至更多。这两个需求看起来只差一个字落到 SQL 上就是两个函数。还有个容易忽略的点ROW_NUMBER()在并列值上的排序是不确定的。同一份数据多次执行被排在前面的行可能不一样。如果业务要求结果稳定必须在ORDER BY里加一个唯一列做兜底比如ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id ASC) AS rn我在实际项目里遇到过翻页导出数据对不上的问题根源就是漏了这个兜底列。数据看着没错两次导出结果顺序却不同排查了很久。这个经验面试时提一句含金量很高。3.2 聚合类窗口函数累计、移动平均、占比SUM、AVG、COUNT、MAX、MIN这几个聚合函数加上OVER就变成了窗口聚合。它们的用法无非三种整分区汇总、累计、滑动窗口。整分区汇总最简单SUM(salary) OVER (PARTITION BY dept)会把部门总薪资贴到该部门每一行上。这种写法在算占比时特别顺手SELECT emp_name, dept, salary, ROUND(salary / SUM(salary) OVER (PARTITION BY dept) * 100, 2) AS pct_in_dept FROM emp_salary;累计求和靠默认帧就够了SELECT emp_name, dept, hire_date, salary, SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date) AS running_total FROM emp_salary;滑动窗口要显式指定帧比如算“最近三次记录的移动平均”SELECT emp_name, hire_date, salary, ROUND(AVG(salary) OVER ( ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ), 2) AS ma3 FROM emp_salary;ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示“往前数两行加上当前行”一共三行。注意开头的两行凑不满三行AVG就按实际行数算不会补零也不会返回NULL这个行为符合直觉但要心里有数。注意COUNT(*) OVER ()这种整表计数在数据量大时开销不小因为它需要扫描全部参与行。如果只是想统计总数很多时候单独发一条COUNT查询反而更快。3.3 偏移类LAG、LEAD、FIRST_VALUE、LAST_VALUE偏移类函数用来“看别的行”。LAG往前看LEAD往后看FIRST_VALUE看分区第一行LAST_VALUE看分区最后一行。语法是LAG(列, 偏移量, 默认值)偏移量和默认值都可以省略默认偏移量是 1默认值不写就是NULL。SELECT month, amount, LAG(amount, 1, 0) OVER (ORDER BY month) AS prev_amount FROM sales_month;第一行没有“上一个月”如果不给默认值就是NULL。这个NULL会顺着计算传播——amount - NULL结果还是NULL。做环比计算时很多人忘了处理第一行报表上就出现一个刺眼的空值。给个默认值或者在展示层做处理都能解决。LAST_VALUE的坑前面已经说过默认帧只到当前行所以不加ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING的话它返回的就是当前行的值。这个行为严格来说是对的只是反直觉。我自己第一次用时也以为是 bug查了文档才发现是帧没指定。FIRST_VALUE相对安全因为默认帧的起点就是UNBOUNDED PRECEDING所以它天然返回分区第一行。但如果你显式改了帧的起点它也会跟着变这一点要注意。提示LAG/LEAD只接受常量偏移量不能写LAG(x, rn)这种动态偏移。如果业务需要动态偏移得换思路比如用自连接或者把偏移量算好再匹配。3.4 分布类NTILE、PERCENT_RANK、CUME_DIST这三个函数偏统计口径面试出现频率低于排名类但一旦问到往往是考察你是不是真的用过。NTILE(n)把分区内的行尽量平均分成 n 组返回组号。比如把用户按消费金额分成四档SELECT user_id, amount, NTILE(4) OVER (ORDER BY amount DESC) AS quartile FROM user_orders;结果就是 1、2、3、4 四档1 档是消费最高的那一批。行数不能整除时前面的组会多分一行这是标准行为。PERCENT_RANK()返回相对排名公式是(rank - 1) / (总行数 - 1)取值 0 到 1。CUME_DIST()返回累积分布公式是小于等于当前值的行数 / 总行数取值永远大于 0。两者都常用在“这个值超过了多少人”这类需求上。有个细节PERCENT_RANK在分区只有一行时分母为 0MySQL 会返回 0 而不是报错。这个边界行为不会在文档里用大字标注但实际会遇到尤其是按小维度分区的场景。4. 面试真题实战从建表到出结果一次跑通4.1 先建表造数据方便你直接抄下面这套脚本是我自己用来练手的字段简单但覆盖了排名、累计、偏移等多种场景。CREATE TABLE emp_salary ( id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(32) NOT NULL, dept VARCHAR(32) NOT NULL, salary DECIMAL(10, 2) NOT NULL, hire_date DATE NOT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_dept_salary (dept, salary), KEY idx_dept_hire (dept, hire_date) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4; INSERT INTO emp_salary (emp_name, dept, salary, hire_date) VALUES (张伟, 研发部, 28000.00, 2020-03-01), (李娜, 研发部, 32000.00, 2019-07-15), (王强, 研发部, 32000.00, 2021-01-08), (赵敏, 研发部, 25000.00, 2022-05-20), (陈静, 市场部, 21000.00, 2020-11-03), (刘洋, 市场部, 18500.00, 2021-09-12), (孙磊, 市场部, 21000.00, 2019-02-25), (周婷, 财务部, 16000.00, 2022-08-01), (吴昊, 财务部, 17500.00, 2020-06-18);这套数据特意在研发部和市场部都造了并列薪资方便验证三个排名函数的差异。建好之后跑一遍下面这条语句三个函数的区别一眼就能看出来SELECT emp_name, dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drk FROM emp_salary ORDER BY dept, salary DESC;研发部两个人的薪资都是 32000你会看到rn是 1 和 2rk和drk都是 1 和 1。差异就此清晰。4.2 分组 TopN从三层嵌套到一行搞定“每个部门薪资前三名”是这类题的标杆。用窗口函数写标准解法是这样SELECT dept, emp_name, salary, rn FROM ( SELECT dept, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id ASC) AS rn FROM emp_salary ) t WHERE rn 3 ORDER BY dept, rn;为什么必须先套一层子查询回到执行顺序那节讲的窗口函数在SELECT阶段计算而WHERE在它之前执行。想用rn做过滤只能把它变成子查询的输出列在外层WHERE里筛。这个“窗口函数不能直接进 WHERE”的规则是面试里最常被追问的点答不上来就露馅了。如果需求改成“并列第一的都要”把ROW_NUMBER()换成RANK()即可其余不动。一行改动对应一个语义变化这就是声明式语法的好处。再进阶一点如果还要在结果里带上“该部门总薪资”和“个人占比”可以直接在外层继续引用SELECT dept, emp_name, salary, rn, SUM(salary) OVER (PARTITION BY dept) AS dept_total, ROUND(salary / SUM(salary) OVER (PARTITION BY dept) * 100, 2) AS pct FROM ( SELECT dept, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id ASC) AS rn FROM emp_salary ) t WHERE rn 3;一个注意点这里外层又用了一次PARTITION BY dept但外层只有前几名了SUM算的是前几名的总和不是全部门总和。想要全部门总和得在内层子查询里先把SUM(...) OVER (...)算好带出来否则语义会悄悄变掉。这类陷阱在报表开发里非常隐蔽写完后一定拿真实数据核对一遍。4.3 连续登录与连续区间问题连续 N 天登录是窗口函数的经典应用。核心技巧是用日期减排名构造分组标识如果日期是连续的那么“日期 - 行号”会得到同一个值。先建一张登录表CREATE TABLE user_login ( user_id INT NOT NULL, login_date DATE NOT NULL, PRIMARY KEY (user_id, login_date) ); INSERT INTO user_login VALUES (1, 2024-01-01), (1, 2024-01-02), (1, 2024-01-03), (1, 2024-01-05), (1, 2024-01-06), (2, 2024-01-01), (2, 2024-01-03), (2, 2024-01-04), (2, 2024-01-05);求每个用户连续登录的区间SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ) DAY) AS grp FROM user_login ) t GROUP BY user_id, grp HAVING COUNT(*) 2 ORDER BY user_id, start_date;原理讲透对用户 1 来说日期是 1-01、1-02、1-03、1-05、1-06行号是 1、2、3、4、5。相减得到 12-31、12-31、12-31、1-01、1-01。同一个grp值意味着日期是连续的分组之后取MIN、MAX就是区间。这里有个必须注意的点ROW_NUMBER()返回的是BIGINTDATE_SUB的INTERVAL后面按说需要整数。MySQL 在多数版本里能自动转换但为了保险写INTERVAL CAST(ROW_NUMBER() OVER (...) AS SIGNED) DAY更稳。我在不同的小版本上遇到过行为差异加上CAST之后就没再出过问题。同类问题还有“连续签到多少天”“连续三个月业绩增长”等思路完全一样找到一个单调递增的列减去一个连续序号把断点暴露成分组变化。这个套路一旦掌握面试里遇到连续类问题基本可以秒解。4.4 同比环比、去重取最新与累计占比环比用LAG最直接SELECT month, amount, LAG(amount, 1) OVER (ORDER BY month) AS prev_amount, ROUND( (amount - LAG(amount, 1) OVER (ORDER BY month)) / LAG(amount, 1) OVER (ORDER BY month) * 100, 2 ) AS mom_pct FROM sales_month;同比就是把偏移量改成 12按月份算前提是数据按月连续。去重取最新一条是生产环境里最实用的窗口函数场景。比如每