一、窗口函数简介1.1 什么是窗口函数窗口函数Window Function是 MySQL8.0 重磅推出的 SQL 分析函数用于分组内计算、保留明细行。和普通聚合函数的核心区别普通聚合sum/count/avg多行合并为一行丢失明细数据窗口函数行数不变每一行都可以展示「自身明细 分组统计结果」窗口函数彻底解决 MySQL5.7 痛点分组TOPN、排名、环比、累计统计不再需要复杂子查询、自连接、变量脚本SQL 简洁且性能更高。1.2 核心语法结构函数名() OVER ( [PARTITION BY 分组字段] [ORDER BY 排序字段] [ROWS 滑动窗口范围] ) AS 别名PARTITION BY分区/分组类似 GROUP BY各组独立计算ORDER BY组内排序影响排名、累计统计结果ROWS自定义滑动窗口范围实现滚动统计二、准备实战测试数据统一案例底表创建学生成绩表后续所有窗口函数案例均基于该表运行结果统一可复现。-- 创建测试表 CREATE TABLE student_score ( id INT, name VARCHAR(20), class VARCHAR(20), score INT ); -- 插入测试数据 INSERT INTO student_score VALUES (1,张三,一班,95), (2,李四,一班,95), (3,王五,一班,88), (4,赵六,二班,92), (5,孙七,二班,85), (6,周八,二班,78);表数据可视化模拟截图效果idnameclassscore1张三一班952李四一班953王五一班884赵六二班925孙七二班856周八二班78三、五大类窗口函数全实例讲解3.1 排名函数面试必考TOP1三个排名函数核心差异配合实例一目了然ROW_NUMBER()连续序号 1,2,3,4无并列、无跳号RANK()并列排名、跳号 1,2,2,4榜单排名专用DENSE_RANK()并列排名、连续号 1,2,2,3竞赛排名专用需求按班级分组对成绩倒序排名SELECT name,class,score, ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS row_num, RANK() OVER(PARTITION BY class ORDER BY score DESC) AS rk, DENSE_RANK() OVER(PARTITION BY class ORDER BY score DESC) AS dense_rk FROM student_score;运行结果模拟截图nameclassscorerow_numrkdense_rk张三一班95111李四一班95211王五一班88332赵六二班92111孙七二班85222周八二班78333场景总结取每组第一条数据、分页ROW_NUMBER排行榜并列后跳名次RANK竞赛评优并列后不跳名次DENSE_RANK3.2 偏移函数 LAG / LEAD环比分析刚需LAG(col,n)获取当前行「向上第n行」数据LEAD(col,n)获取当前行「向下第n行」数据新建销售测试表适配时间环比场景CREATE TABLE sales ( dt DATE, amount INT ); INSERT INTO sales VALUES (2026-01-01,100), (2026-01-02,200), (2026-01-03,150), (2026-01-04,300);SELECT dt,amount, LAG(amount,1) OVER(ORDER BY dt) AS prev_day, LEAD(amount,1) OVER(ORDER BY dt) AS next_day FROM sales;运行结果dtamountprev_daynext_day2026-01-01100NULL2002026-01-022001001502026-01-031502003002026-01-04300150NULL生产场景计算日环比、月环比、数据前后行差异对比。3.3 首尾取值函数 FIRST_VALUE / LAST_VALUEFIRST_VALUE取分组排序后第一条数据LAST_VALUE取分组最后一条数据存在经典坑易错点带 ORDER BY 的窗口默认范围是「首行到当前行」直接用 LAST_VALUE 只能取当前行。正确写法手动指定全局窗口范围SELECT name,class,score, FIRST_VALUE(score) OVER(PARTITION BY class ORDER BY score DESC) AS class_max, LAST_VALUE(score) OVER(PARTITION BY class ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS class_min FROM student_score;3.4 滑动聚合窗口累计/滚动统计聚合函数 窗口 不合并行的统计计算1累计求和默认窗口SELECT dt,amount, SUM(amount) OVER(ORDER BY dt) AS total_sum FROM sales;2高阶7日滚动统计生产报表刚需SELECT dt,amount, SUM(amount) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_total FROM sales;逻辑当前行 向前6行组成7天滑动窗口。3.5 分桶函数 NTILE将有序数据均匀分为 N 个桶用于用户分层、数据抽样。SELECT name,score, NTILE(2) OVER(ORDER BY score DESC) AS level_bucket FROM student_score;效果自动将成绩分为高低两个等级。四、两大经典生产实战场景场景1分组取每组最新/最优数据高频面试需求每个班级取成绩最高的学生SELECT * FROM ( SELECT name,class,score, ROW_NUMBER() OVER(PARTITION BY class ORDER BY score DESC) AS rn FROM student_score ) t WHERE t.rn 1;场景2计算环比增长率SELECT dt, amount, LAG(amount) OVER(ORDER BY dt) AS prev_amount, ROUND((amount - LAG(amount) OVER(ORDER BY dt)) / LAG(amount) OVER(ORDER BY dt) * 100,2) AS环比增长率 FROM sales;五、窗口函数核心踩坑大全生产必避坑1LAST_VALUE 默认窗口范围错误带 ORDER BY 窗口默认只统计到当前行必须手动指定全局窗口范围才能取分组最后一条数据。坑2窗口函数不能用于 WHERE窗口函数执行顺序在 WHERE/GROUP BY/HAVING 之后无法直接过滤必须嵌套子查询或 CTE。坑3忽略 PARTITION BY 导致全局计算不写 PARTITION BY 会将全表作为一个窗口计算大表全局排名、累计统计性能极差。坑4三大排名函数混用场景需要唯一序号用 ROW_NUMBER榜单跳名次用 RANK竞赛连续名次用 DENSE_RANK六、执行顺序面试高频FROM WHERE GROUP BY HAVING 窗口函数 ORDER BY LIMIT七、速记总结窗口函数是 MySQL8.0 核心分析能力通过 PARTITION BY 分组、ORDER BY 排序、ROWS 自定义窗口实现排名、偏移、累计、滚动统计。保留明细行、SQL简洁高效彻底替代5.7复杂子查询是报表统计、数据分析、分组TOPN场景的最优解。
