企业官网建设流程全解析

前言

上一篇我们入门了三大排名类窗口函数（ROW_NUMBER/RANK/DENSE_RANK），掌握了全局排名、分组 TopN 等基础场景。本篇继续深挖 MySQL 8.0 窗口函数的进阶用法，聚焦窗口帧（Frame）、聚合型窗口函数、行间取值函数三大核心能力。

在报表统计、时序数据分析、业务同比 / 环比计算中，累计求和、移动平均、前后行数据对比是刚需。传统写法需要大量子查询、变量拼接，代码臃肿且性能差，而窗口函数结合分区、排序、窗口帧，一行代码就能实现复杂统计逻辑。

本篇结合电商销售、员工薪资、时序数据等真实场景，从语法、规则、案例、坑点逐层拆解，同时对比传统写法，帮你彻底吃透窗口函数进阶能力

一、本章知识点汇总

1. 窗口函数完整结构：分区 + 排序 +窗口帧（Frame）全解析
2. 窗口帧核心关键字：ROWS/RANGE、PRECEDING/FOLLOWING/CURRENT ROW
3. 聚合函数作为窗口函数：SUM/AVG/COUNT/MAX/MIN进阶用法
4. 主流实战场景：全局累计、分组累计、移动求和、移动平均
5. 行间取值函数：LAG/LEAD上下行数据提取（环比 / 同比必备）
6. FIRST_VALUE/LAST_VALUE窗口首尾数据获取
7. 窗口函数默认帧规则与手动帧改写
8. 窗口函数与传统子查询 / 变量写法性能对比
9. 综合实战：销售报表全套统计（累计 + 均值 + 行间对比）
10. 避坑指南 + 课后练习题

二、各知识点详解

1. 窗口函数完整语法（新增窗口帧）

上一篇我们使用了PARTITION BY（分区）和ORDER BY（排序），完整窗口函数还包含窗口帧（Frame），用于精准划定当前行参与计算的数据范围，这是进阶核心。

sql

函数名() OVER ( [PARTITION BY 字段1, 字段2] -- 分区：横向拆分数据集 [ORDER BY 排序字段 ASC|DESC] -- 分区内排序 [帧单位 BETWEEN 帧起始 AND 帧结束] -- 窗口帧：纵向划定计算行范围 ) AS 别名;

1.1 窗口帧基础关键字

表格

1.2 默认帧规则（高频易错点）

1. 若OVER()内无 ORDER BY：默认帧 =UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING（整个分区所有行参与计算）
2. 若OVER()内有 ORDER BY：默认帧 =UNBOUNDED PRECEDING AND CURRENT ROW（分区首行到当前行，累计场景默认规则）
3. 排名类函数（ROW_NUMBER/RANK）忽略窗口帧，设置帧不生效MySQL。

2. 聚合窗口函数（SUM/AVG/COUNT/MAX/MIN）

普通聚合函数搭配OVER()后，从 “整组合并” 变为保留所有明细行，同时附加聚合结果，分为两大使用模式：

1. 全分区聚合：无窗口帧，计算分区整体统计值；
2. 逐行累计聚合：搭配窗口帧，实现累计、移动计算。

2.1 SUM 累计求和（职场最高频）

分为全局累计、分组累计、移动求和三大场景，是日报、流水报表核心用法。

• 全局累计：全表按时间逐行累加；
• 分组累计：每个分区内独立累加（如每个用户、每个品类单独累计）；
• 移动求和：限定前后行数，计算区间和（如近 3 天销售额）。

2.2 AVG 移动平均

基于窗口帧限定行范围，计算周期平均值，常用于股价、销量、流量等时序数据分析。

2.3 COUNT/MAX/MIN 窗口用法

• COUNT：累计计数、分区总行数统计；
• MAX/MIN：逐行获取分区内最大值、最小值（如每个阶段历史最高值）。

3. 行间取值函数（LAG / LEAD）

专门用于获取当前行的上一行 / 下一行数据，是实现环比、同比、前后数据对比的核心函数。

3.1 LAG () 语法

sql

LAG(字段, 偏移量=1, 默认值=NULL) OVER([分区][排序])

• 作用：取当前行往前偏移 N 行的数据；
• 默认偏移量为 1（上一行），无数据返回 NULL，可自定义默认值。

3.2 LEAD () 语法

sql

LEAD(字段, 偏移量=1, 默认值=NULL) OVER([分区][排序])

• 作用：取当前行往后偏移 N 行的数据；
• 常用于查看下一期数据、预测参考。

4. 首尾取值函数（FIRST_VALUE / LAST_VALUE）

用于提取窗口内第一行、最后一行的数据，注意LAST_VALUE受默认窗口帧影响，必须手动指定帧范围才能获取分区最后一行。

• FIRST_VALUE(字段)：分区排序后，取第一行数据；
• LAST_VALUE(字段)：默认仅取 “当前行”，需手动设置帧为UNBOUNDED FOLLOWING才能取分区末行。

5. 拓展：窗口函数 VS 传统写法

以 “累计求和” 为例：

• 传统写法：变量 + 子查询，代码冗长、可读性差、大表性能低，仅兼容 MySQL 5.7 及以下；
• 窗口函数：语法简洁、逻辑清晰、MySQL 底层优化，优先推荐。

三、实战环境准备

我们创建电商销售表，包含日期、品类、单日销售额，覆盖累计、移动平均、行间对比全场景，所有案例共用此表：

sql

-- 电商每日销售表 CREATE TABLE daily_sales ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键', sale_date DATE NOT NULL COMMENT '销售日期', category VARCHAR(20) NOT NULL COMMENT '商品品类', sale_amount DECIMAL(10,2) NOT NULL COMMENT '单日销售额' ) COMMENT '每日销售统计表'; -- 插入测试数据（2个品类，连续日期，模拟时序数据） INSERT INTO daily_sales (sale_date, category, sale_amount) VALUES ('2026-05-01','数码',1200.00), ('2026-05-02','数码',1800.00), ('2026-05-03','数码',1500.00), ('2026-05-04','数码',2100.00), ('2026-05-01','服饰',800.00), ('2026-05-02','服饰',950.00), ('2026-05-03','服饰',1100.00), ('2026-05-04','服饰',780.00); -- 基础查询 SELECT * FROM daily_sales ORDER BY category, sale_date;

四、应用案例及结果分析

案例 1：SUM 全局累计求和（默认窗口帧）

需求：按日期排序，计算全平台销售额逐行累计总额。

sql

SELECT sale_date AS 销售日期, category AS 品类, sale_amount AS 单日销售额, -- 全局累计：默认帧 首行 -> 当前行 SUM(sale_amount) OVER (ORDER BY sale_date) AS 全局累计销售额 FROM daily_sales ORDER BY sale_date;

结果分析

1. 无PARTITION BY，整张表为一个窗口；
2. 开启ORDER BY，触发默认帧规则，从最早日期逐行累加；
3. 每一行都会展示截止到当天的总销售额，流水报表标准写法。

案例 2：SUM 分组累计求和（PARTITION BY + 累计）

需求：按品类分区，每个品类内部按日期独立累计销售额（数码、服饰分开统计）。

sql

SELECT sale_date AS 销售日期, category AS 品类, sale_amount AS 单日销售额, -- 分区内累计：每个品类单独累加 SUM(sale_amount) OVER (PARTITION BY category ORDER BY sale_date) AS 品类累计销售额 FROM daily_sales ORDER BY category, sale_date;

结果分析

1. PARTITION BY category将数据拆分为数码、服饰两个独立窗口；
2. 两个品类各自从 0 开始累计，互不干扰；
3. 适用场景：分部门、分产品线、分区域独立统计累计数据。

案例 3：手动窗口帧 - 移动求和（近 2 日销售额）

需求：计算当天 + 前 1 天的两日移动销售额，手动定义窗口帧。

sql

SELECT sale_date AS 销售日期, category AS 品类, sale_amount AS 单日销售额, -- 窗口帧：往前1行 ~ 当前行（共2行） SUM(sale_amount) OVER ( PARTITION BY category ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS 两日移动销售额 FROM daily_sales ORDER BY category, sale_date;

结果分析

1. 第一行无前置数据，仅计算当前行；
2. 第二行及之后，自动累加前 1 行 + 当前行；
3. 扩展：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW可实现近 3 日求和。

案例 4：AVG 移动平均（时序数据分析）

需求：计算每个品类连续 3 日移动平均销售额（前 1 行 + 当前行 + 后 1 行）。

sql

SELECT sale_date AS 销售日期, category AS 品类, sale_amount AS 单日销售额, ROUND( AVG(sale_amount) OVER ( PARTITION BY category ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ), 2 ) AS 三日移动平均值 FROM daily_sales ORDER BY category, sale_date;

结果分析

1. 首尾行因缺少前置 / 后置数据，仅计算现有行均值；
2. ROUND(数值,2)保留 2 位小数，报表展示必备；
3. 适用场景：商品销量趋势、网站流量分析、金融数据统计。

案例 5：LAG / LEAD 行间取值（环比计算基础）

需求：查询每个品类每日销售额，并获取前一日销售额、后一日销售额。

sql

SELECT sale_date AS 销售日期, category AS 品类, sale_amount AS 当日销售额, -- 获取上一行数据（前1天销售额） LAG(sale_amount, 1, 0) OVER (PARTITION BY category ORDER BY sale_date) AS 前一日销售额, -- 获取下一行数据（后1天销售额） LEAD(sale_amount, 1, 0) OVER (PARTITION BY category ORDER BY sale_date) AS 后一日销售额 FROM daily_sales ORDER BY category, sale_date;

结果分析

1. 分区内按日期排序，LAG提取上一行，首行无数据填充默认值 0；
2. LEAD提取下一行，末行无数据填充 0；
3. 延伸：基于此结果可直接计算环比增长率 = (当日 - 前一日)/ 前一日。

案例 6：FIRST_VALUE / LAST_VALUE 首尾数据提取

需求：每个品类，展示每日销售额、品类首日销售额、品类末日销售额。

sql

SELECT sale_date AS 销售日期, category AS 品类, sale_amount AS 单日销售额, -- 分区第一行（首日销售额） FIRST_VALUE(sale_amount) OVER (PARTITION BY category ORDER BY sale_date) AS 品类首日销售额, -- 手动指定窗口帧，获取分区最后一行（末日销售额） LAST_VALUE(sale_amount) OVER ( PARTITION BY category ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS 品类末日销售额 FROM daily_sales ORDER BY category, sale_date;

结果分析

1. FIRST_VALUE不受默认帧影响，直接取分区排序后首行；
2. LAST_VALUE必须手动设置窗口帧为全分区，否则仅返回当前行（高频坑点）。

案例 7：综合实战 - 完整销售报表

需求：输出全维度销售报表，包含：单日销售额、品类累计、两日移动和、前一日销售额、品类总销售额。

sql

SELECT sale_date AS 销售日期, category AS 品类, sale_amount AS 单日销售额, -- 品类累计销售额 SUM(sale_amount) OVER (PARTITION BY category ORDER BY sale_date) AS 品类累计, -- 两日移动求和 SUM(sale_amount) OVER (PARTITION BY category ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS 两日移动和, -- 前一日销售额 LAG(sale_amount,1,0) OVER (PARTITION BY category ORDER BY sale_date) AS 前一日销售额, -- 品类整体总销售额（无排序，全分区聚合） SUM(sale_amount) OVER (PARTITION BY category) AS 品类总销售额 FROM daily_sales ORDER BY category, sale_date;

结果分析

一条 SQL 实现 5 类统计，明细 + 多维度聚合共存，代码极简，是企业运营报表标准写法。

五、注意事项（避坑指南）

1. 牢记默认窗口帧规则有ORDER BY默认是「首行到当前行」，LAST_VALUE必须手动扩展帧范围，否则取值错误。
2. ROWS 优先于 RANGE99% 业务场景使用ROWS（物理行），RANGE按数值分组，仅特殊时序场景使用。
3. LAG/LEAD 偏移量规范偏移量为非负整数，偏移行数超过分区数据量时，返回默认值（建议设置 0 或 “无”）。
4. 窗口函数不能用于 WHERE执行顺序限制，过滤统计结果必须嵌套子查询 / CTE。
5. 分区 + 排序字段建议建索引大表千万级数据下，复合索引可大幅提升窗口函数执行速度。
6. 区分累计与全分区聚合无ORDER BY的聚合窗口函数，计算分区整体值；有ORDER BY为逐行累计值。
7. 兼容版本限制所有进阶窗口函数仅 MySQL 8.0+ 支持，5.7 及以下版本需改用变量、子查询。
8. 移动计算帧范围书写规范移动平均 / 移动求和，严格使用BETWEEN 前N行 AND 后N行，避免范围混乱。

六、核心总结

1. 窗口函数完整结构：分区 (PARTITION BY) → 排序 (ORDER BY) → 窗口帧 (ROWS/RANGE)，三层控制计算范围。
2. 窗口帧核心
   • 有排序：默认首行到当前行（累计专用）；
   • 无排序：默认整个分区（整体聚合专用）；
   • 移动计算：手动指定前后行范围。
3. 聚合窗口函数
   • SUM/AVG：累计、移动求和、移动平均（时序报表核心）；
   • COUNT/MAX/MIN：累计计数、分区极值。
4. 行间取值函数
   • LAG：取上一行，LEAD：取下一行，环比、同比计算必备；
   • FIRST_VALUE：取分区首行，LAST_VALUE 需手动改帧取末行。
5. 职场选型时序统计、多维度报表、行间对比一律使用窗口函数，替代传统子查询与变量。

一句话记忆：分区横向切数据，排序纵向定顺序；窗口帧划计算区，ROWS 行级最常用；SUM 累计 LAG 取前，移动平均改帧线；LAST_VALUE 要注意，全帧设置才靠谱。

七、练习题

基于daily_sales销售表完成练习，贴合数据分析真实场景。

题目 1

按日期全局排序，计算近 3 日移动平均销售额（当前行 + 前 2 行），结果保留 2 位小数。

题目 2

使用LAG函数，计算每个品类每日销售额的环比差值（当日销售额 - 前一日销售额）。

题目 3

查询所有数据，同时展示：单日销售额、该品类历史最高销售额（MAX 窗口函数）。

题目 4

简述LAST_VALUE函数的使用坑点，以及对应的解决方案。

参考答案思路

题目 1

sql

SELECT sale_date AS 销售日期, sale_amount AS 单日销售额, ROUND( AVG(sale_amount) OVER ( ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ), 2 ) AS 三日移动平均 FROM daily_sales ORDER BY sale_date;

题目 2

sql

SELECT sale_date AS 销售日期, category AS 品类, sale_amount AS 当日销售额, LAG(sale_amount,1,0) OVER (PARTITION BY category ORDER BY sale_date) AS 前一日销售额, sale_amount - LAG(sale_amount,1,0) OVER (PARTITION BY category ORDER BY sale_date) AS 环比差值 FROM daily_sales ORDER BY category, sale_date;

题目 3

sql

SELECT sale_date AS 销售日期, category AS 品类, sale_amount AS 单日销售额, MAX(sale_amount) OVER (PARTITION BY category ORDER BY sale_date) AS 品类历史最高销售额 FROM daily_sales ORDER BY category, sale_date;

题目 4 参考答案

坑点：当OVER()子句包含ORDER BY时，窗口帧默认为「分区首行到当前行」，导致LAST_VALUE只能获取当前行数据，无法拿到分区最后一行。 解决方案：手动设置窗口帧为ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，将计算范围扩展到整个分区。