企业官网建设流程全解析

MySQL 8.0 窗口函数实战：排名、Top N、环比与滑动窗口一次讲透

窗口函数最有价值的地方，不是让 SQL 看起来更高级，
而是让“保留明细行，同时完成跨行计算”变成一等能力。

本文用一套可直接运行的数据，讲清OVER、排名、偏移、窗口框架、
常见陷阱与性能边界，并说明 MySQL 5.7 应如何替代。

一、为什么有了 GROUP BY 还需要窗口函数

普通聚合会把多行压缩成一行：

SELECTshop_id,SUM(amount)AStotal_amountFROMsales_orderGROUPBYshop_id;

如果需求是“显示每笔订单，同时显示门店总销售额和订单占比”，GROUP BY无法在同一层既保留订单明细，又得到门店汇总。
窗口函数解决的正是这个矛盾：

SELECTorder_id,shop_id,amount,SUM(amount)OVER(PARTITIONBYshop_id)ASshop_total,amount/SUM(amount)OVER(PARTITIONBYshop_id)ASamount_ratioFROMsales_order;

二、准备一套可运行的测试数据

以下示例以 MySQL 8.0 为准：

DROPTABLEIFEXISTSsales_order;CREATETABLEsales_order(order_idBIGINTUNSIGNEDNOTNULLAUTO_INCREMENT,shop_idINTUNSIGNEDNOTNULL,salespersonVARCHAR(32)NOTNULL,order_dateDATENOTNULL,amountDECIMAL(10,2)NOTNULL,PRIMARYKEY(order_id),KEYidx_shop_date(shop_id,order_date,order_id))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;INSERTINTOsales_order(shop_id,salesperson,order_date,amount)VALUES(1,'小林','2026-01-01',100.00),(1,'小周','2026-01-01',100.00),(1,'小林','2026-01-02',180.00),(1,'小陈','2026-01-03',120.00),(1,'小周','2026-01-05',240.00),(2,'小赵','2026-01-01',90.00),(2,'小赵','2026-01-02',160.00),(2,'小孙','2026-01-02',160.00),(2,'小孙','2026-01-04',210.00);

索引idx_shop_date不保证消除所有窗口排序，但它与常见的“按门店、日期读取”路径一致，能减少部分扫描成本。

三、OVER 子句的三个层次

窗口函数的一般形式如下：

函数(参数)OVER(PARTITIONBY分区列ORDERBY排序列ROWS或 RANGE 窗口框架)

三个部分各自回答一个问题：

3.1 PARTITION BY 只分区，不折叠

SELECTorder_id,shop_id,amount,SUM(amount)OVER(PARTITIONBYshop_id)ASshop_totalFROMsales_orderORDERBYshop_id,order_id;

门店 1 和门店 2 分别计算，原订单仍一条不少。分区边界一变，窗口函数的状态就重新开始。

3.2 窗口内 ORDER BY 与最终排序不是一回事

SELECTorder_id,order_date,ROW_NUMBER()OVER(ORDERBYamountDESC,order_id)ASrnFROMsales_orderORDERBYorder_date,order_id;

OVER内的排序决定rn如何生成；最外层ORDER BY决定结果最终怎样展示。二者可以不同，也不会互相替代。

3.3 排序必须有稳定的兜底列

只写ORDER BY amount DESC时，相同金额之间没有确定顺序。执行计划、并行度或数据页变化后，行号可能变化。
需要唯一行号时应写：

ROW_NUMBER()OVER(ORDERBYamountDESC,order_idASC)

order_id是稳定且唯一的最后排序键。这对分页、去重和增量任务尤其重要。

四、三个排名函数到底怎么选

SELECTorder_id,amount,ROW_NUMBER()OVER(ORDERBYamountDESC,order_id)ASrow_no,RANK()OVER(ORDERBYamountDESC)ASrank_no,DENSE_RANK()OVER(ORDERBYamountDESC)ASdense_rank_noFROMsales_order;

金额存在并列时，三者的语义不同：

五、经典场景一：每个门店金额最高的两笔订单

窗口函数不能直接出现在WHERE中，因为逻辑上WHERE过滤早于窗口计算。需要用 CTE 或派生表包一层：

WITHrankedAS(SELECTorder_id,shop_id,salesperson,amount,ROW_NUMBER()OVER(PARTITIONBYshop_idORDERBYamountDESC,order_id)ASrnFROMsales_order)SELECTorder_id,shop_id,salesperson,amountFROMrankedWHERErn<=2ORDERBYshop_id,rn;

如果并列第二的订单都要保留，将ROW_NUMBER()改成RANK()。这就是 Top N 问题最容易漏掉的业务条件。

5.1 为什么不能在 WHERE 里写窗口函数

下面的语句会报错：

SELECTorder_id,ROW_NUMBER()OVER(ORDERBYamountDESC)ASrnFROMsales_orderWHERErn<=10;

简化后的逻辑执行顺序是：

FROM / JOIN → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → ORDER BY → LIMIT

WHERE执行时rn还不存在。MySQL 也没有某些数据库提供的QUALIFY子句，所以外层查询是标准写法。

六、经典场景二：每组保留最新一条

假设同一销售员一天可能产生多笔订单，现在要取每位销售员最近的一笔：

WITHlatestAS(SELECTso.*,ROW_NUMBER()OVER(PARTITIONBYsalespersonORDERBYorder_dateDESC,order_idDESC)ASrnFROMsales_orderASso)SELECTorder_id,salesperson,order_date,amountFROMlatestWHERErn=1;

这里必须把order_id DESC放进排序。只按日期排序时，同一天多笔订单仍然无法确定谁是“最新”。
如果要真正删除重复行，应先查询核对，再在事务中按主键删除：

WITHduplicatedAS(SELECTorder_id,ROW_NUMBER()OVER(PARTITIONBYshop_id,salesperson,order_date,amountORDERBYorder_id)ASrnFROMsales_order)SELECTorder_idFROMduplicatedWHERErn>1;

先输出待删主键，而不是一上来执行DELETE，能避免分区列写错造成不可逆的数据损失。

七、经典场景三：环比、相邻行与断档检测

LAG读取当前行之前的值，LEAD读取之后的值：

WITHdailyAS(SELECTshop_id,order_date,SUM(amount)ASdaily_amountFROMsales_orderGROUPBYshop_id,order_date),comparedAS(SELECTshop_id,order_date,daily_amount,LAG(daily_amount)OVER(PARTITIONBYshop_idORDERBYorder_date)ASprevious_amountFROMdaily)SELECTshop_id,order_date,daily_amount,previous_amount,ROUND((daily_amount-previous_amount)/NULLIF(previous_amount,0)*100,2)ASgrowth_pctFROMcomparedORDERBYshop_id,order_date;

这个写法有三个细节：

1. 先按天汇总，避免把“上一笔订单”错当成“上一天”。
2. 用外层查询复用别名，避免重复写LAG。
3. 用NULLIF防止上一期为 0 时除零。
   LAG/LEAD读取的是相邻记录，不会自动理解自然日、交易日或工作日；断档检测前必须先按业务口径去重并排序。

八、累计值与移动窗口

8.1 累计销售额

WITHdailyAS(SELECTshop_id,order_date,SUM(amount)ASdaily_amountFROMsales_orderGROUPBYshop_id,order_date)SELECTshop_id,order_date,daily_amount,SUM(daily_amount)OVER(PARTITIONBYshop_idORDERBYorder_dateROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)ASrunning_amountFROMdaily;

显式写出窗口框架比依赖默认值更安全，读代码的人能立即知道它是“从分区开头累计到当前行”。

8.2 最近三条记录的移动平均

WITHdailyAS(SELECTshop_id,order_date,SUM(amount)ASdaily_amountFROMsales_orderGROUPBYshop_id,order_date)SELECTshop_id,order_date,daily_amount,AVG(daily_amount)OVER(PARTITIONBYshop_idORDERBYorder_dateROWSBETWEEN2PRECEDINGANDCURRENTROW)ASmoving_avg_3_rowsFROMdaily;

前两行不足三条时，MySQL 会对实际存在的行求平均，不会自动返回NULL。如果业务要求凑满三期才输出，可在同一个命名窗口上增加COUNT(*) OVER w，再由外层查询通过CASE判断窗口行数。

九、ROWS 与 RANGE：最容易被忽略的边界

两者的核心差异不是语法，而是如何理解“当前行”：

SELECTorder_id,order_date,amount,SUM(amount)OVER(ORDERBYorder_date,order_idROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)ASsum_by_rows,SUM(amount)OVER(ORDERBYorder_date RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)ASsum_by_rangeFROMsales_orderWHEREshop_id=1;

RANGE在同一日期的第一行就可能包含当天所有同值行；ROWS则严格按排序后的位置逐行累计。账单流水通常需要确定的逐行结果，优先使用ROWS并补齐唯一排序键。

十、LAST_VALUE 为什么经常“等于当前行”

存在窗口内ORDER BY时，默认框架通常截止到当前行及其 peers。因此下面的LAST_VALUE往往不是分区最后一行：

SELECTshop_id,order_date,LAST_VALUE(amount)OVER(PARTITIONBYshop_idORDERBYorder_date)ASmisleading_lastFROMsales_order;

要取整个分区最后一行，应明确扩展到分区末尾：

SELECTshop_id,order_date,LAST_VALUE(amount)OVER(PARTITIONBYshop_idORDERBYorder_date,order_idROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASpartition_last_amountFROMsales_order;

FIRST_VALUE、LAST_VALUE、NTH_VALUE都要结合 frame 理解，不能只看函数名猜结果。

十一、窗口函数的性能从哪里消耗

窗口函数通常需要完成以下步骤：

1. 按WHERE尽量过滤输入行。
2. 按PARTITION BY + ORDER BY组织数据。
3. 对每个分区维护窗口状态。
4. 如外层还要排序，可能再次排序。
   常见成本包括排序、内部临时表、宽行复制和大分区内存压力。窗口函数消除了复杂自连接，不等于一定“零成本”。

12.1 用 EXPLAIN ANALYZE 看真实执行

EXPLAINANALYZESELECTshop_id,order_date,SUM(amount)OVER(PARTITIONBYshop_idORDERBYorder_date,order_id)ASrunning_amountFROMsales_orderWHEREorder_date>='2026-01-01';

MySQL 8.0.18 起可使用EXPLAIN ANALYZE。重点关注实际扫描行数、排序、临时表以及估算偏差，不要只看到查询“用了索引”就宣布优化完成。

12.2 实用优化顺序

十二、MySQL 5.7 怎么办

MySQL 5.7 不支持窗口函数，也不支持 CTE。常见替代方案包括自连接、相关子查询、用户变量和应用层计算。
例如“每组最大值”可以先聚合再回表：

SELECTso.*FROMsales_orderASsoJOIN(SELECTshop_id,MAX(amount)ASmax_amountFROMsales_orderGROUPBYshop_id)ASmONm.shop_id=so.shop_idANDm.max_amount=so.amount;

但“每组前 N”“稳定排名”“移动窗口”会迅速变复杂。用户变量方案依赖求值顺序，容易产生不稳定结果，不适合作为核心账务逻辑。能升级时，优先升级到 MySQL 8.0，而不是继续堆叠难维护的技巧 SQL。

十三、六个常见误区

误区 1：窗口函数会像 GROUP BY 一样减少行数。
不会。窗口函数默认保留输入行，除非外层再次过滤。
误区 2：ROW_NUMBER()天然稳定。
不稳定。排序键不唯一时，并列行的顺序未定义。
误区 3：最近三行就是最近三天。
不是。ROWS计算物理行，日期缺失或一天多行都会改变含义。
误区 4：LAST_VALUE一定返回分区最后一个值。
不一定。它返回当前 frame 的最后值，默认 frame 经常只到当前行。
误区 5：窗口函数能直接写在 WHERE。
不能。要放进 CTE 或派生表后再过滤。
误区 6：改成窗口函数就一定比自连接快。
未必。大范围排序和临时表同样昂贵，必须看执行计划与真实数据量。

十四、上线前检查清单

• 业务要的是固定 N 行，还是允许并列的前 N 名？
• ORDER BY是否带了唯一兜底列？
• 输入数据是否已经聚合到正确粒度？
• 使用的是ROWS还是RANGE，是否有意为之？
• LAST_VALUE等函数是否显式声明完整 frame？
• 是否通过 CTE 过滤窗口结果，而不是误写在WHERE？
• 大分区是否可能产生排序和临时表压力？
• 是否用EXPLAIN ANALYZE验证了实际扫描与耗时？

十五、小结

• 窗口函数的核心是：保留明细行，对相关行集合做计算。
• PARTITION BY决定分区，窗口内ORDER BY决定计算顺序，frame 决定可见边界。
• ROW_NUMBER、RANK、DENSE_RANK的差别，本质是如何处理并列。
• Top N 和去重必须使用稳定、唯一的排序键。
• 环比前应先确认数据粒度，LAG取的是上一行，不一定是上一天。
• ROWS按物理行推进，RANGE会把相同排序值视作 peers。
• LAST_VALUE的结果由窗口框架决定，不能只凭函数名称判断。
• MySQL 8.0.18+ 应用EXPLAIN ANALYZE验证窗口查询的真实成本。
• MySQL 5.7 没有窗口函数，复杂分析应优先考虑升级，而不是依赖用户变量技巧。