MySQL中的窗口函数-CSDN博客

本文链接：https://blog.csdn.net/xiang_gina/article/details/147688411

一、窗口函数简介

窗口函数（Window Functions）是 MySQL 8.0 及以上版本引入的一种强大功能，它允许用户在不减少查询结果行数的情况下，对一组相关的行（称为"窗口"）执行计算。

1、窗口函数特点

窗口函数是在查询结果的特定"窗口"（一组相关行）上执行计算的函数，它具备以下特点：

不折叠行：与GROUP BY不同，窗口函数会保留所有原始行
定义窗口：通过OVER()子句指定计算的数据范围（“窗口”）
逐行计算：为每一行返回一个基于其所在窗口的计算结果

2、基本语法

窗口函数名([参数]) OVER (
    [PARTITION BY 分区表达式, ...]
    [ORDER BY 排序表达式 [ASC|DESC], ...]
    [frame_clause]
)

3、与GROUP BY的关键区别

特性	窗口函数	GROUP BY
行数	保持原行数	合并行
计算方式	基于窗口计算	基于分组计算
结果	每行都有计算结果	每组一行结果
使用场景	排名、累计、移动平均等分析需求	汇总统计、分组聚合

4、窗口函数的应用场景

掌握窗口函数可以让你解决许多传统SQL难以处理的分析问题，如：

计算同比/环比增长率
识别数据趋势
处理复杂的排名和分组分析
计算各种滑动窗口指标

二、窗口定义三要素

窗口函数的强大之处在于可以精确控制"窗口"范围：

函数() OVER(
    [PARTITION BY 分组列]
    [ORDER BY 排序列]
    [ROWS/RANGE 框架]
)

1. PARTITION BY

将数据分成多个组，函数在每个组内独立计算（类似GROUP BY但不合并行）

2. ORDER BY

定义分区内的排序方式，影响序号分配和滑动窗口计算

3. 窗口框架

窗口框架（Window Frame）是 SQL 窗口函数中一个高级但极其有用的功能，它允许你 更精细地控制计算范围，而不仅仅是按 PARTITION BY 分组或按 ORDER BY 排序。

窗口框架的作用
在 OVER() 子句中，除了 PARTITION BY 和 ORDER BY，你还可以用 ROWS 或 RANGE 来定义：

计算时包含哪些行（例如：当前行 + 前3行）
是否包含当前行
是否包含未来的行（FOLLOWING）

典型应用场景
✅ 移动平均（Moving Average）
✅ 累计计算（Running Total）
✅ 前后行对比（Lag/Lead 分析）
✅ 滑动窗口统计（如最近5天的总和）

基本语法

SUM(column) OVER(
    [PARTITION BY ...]
    [ORDER BY ...]
    ROWS|RANGE BETWEEN <start> AND <end>
)

ROWS → 按 物理行 计算（固定行数）
RANGE → 按 逻辑范围 计算（如相同值的行视为同一组）

窗口框架的边界选项

选项	含义
`UNBOUNDED PRECEDING`	从分区的第一行开始
`n PRECEDING`	当前行之前的 `n` 行
`CURRENT ROW`	当前行
`n FOLLOWING`	当前行之后的 `n` 行
`UNBOUNDED FOLLOWING`	直到分区的最后一行

3. 实际案例
案例1：计算3天移动平均（ROWS）

SELECT 
    date,           -- 日期列
    revenue,        -- 当天的收入
    AVG(revenue) OVER(  -- 计算收入的移动平均值
        ORDER BY date   -- 按日期排序
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 包含当前行 + 前2行
    ) AS moving_avg_3day  -- 结果列名
FROM sales;

关键点：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
• 2 PRECEDING = 当前行的前2行
• CURRENT ROW = 当前行
→ 合起来就是当前行 + 前2行，共3行数据

假设数据是这样的：

date	revenue
2023-01-01	100
2023-01-02	150
2023-01-03	200
2023-01-04	250

查询结果会是：

date	revenue	moving_avg_3day	计算方式
2023-01-01	100	100.0	(100) / 1（只有1天数据）
2023-01-02	150	125.0	(100 + 150) / 2
2023-01-03	200	150.0	(100 + 150 + 200) / 3
2023-01-04	250	200.0	(150 + 200 + 250) / 3

前几行不够怎么办？
• 比如第1天（2023-01-01），前面没有数据，就只算它自己。
• 第2天（2023-01-02），只有前1天的数据，就只算2天的平均。

案例2：计算累计到当前行的总和（RANGE）

# 计算销售数据的累计收入（running total），也就是从最早日期到当前日期的收入总和
SELECT 
    date,
    revenue,
    SUM(revenue) OVER( #对 revenue 列求和
        ORDER BY date #按日期排序
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        # 计算范围是从最早日期（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）
    ) AS running_total
FROM sales;

关键点解析

RANGE vs ROWS：
• RANGE：按逻辑范围计算（相同日期的行会被合并统计）。
• ROWS：按物理行计算（严格按行数计算，即使日期相同也会分开统计）。
UNBOUNDED PRECEDING：
• 表示从分区的第一行开始计算（这里是按日期排序后的最早日期）。
CURRENT ROW：
• 计算到当前行为止。

假设原始数据：

date	revenue
2023-01-01	100
2023-01-02	150
2023-01-03	200

查询结果：

date	revenue	running_total	计算逻辑
2023-01-01	100	100	100
2023-01-02	150	250	100 (前一天) + 150
2023-01-03	200	450	250 (前累计) + 200

案例3：计算当前行 + 前后各1行的总和（滑动窗口）

SELECT 
    date,
    revenue,
    SUM(revenue) OVER(
        ORDER BY date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS sliding_sum
FROM sales;

结果示例：

date	revenue	sliding_sum
2023-01-01	100	250
2023-01-02	150	450
2023-01-03	200	600
2023-01-04	250	450

4. ROWS vs RANGE 的区别

类型	行为	适用场景
`ROWS`	按物理行计算（固定行数）	移动平均、滑动窗口
`RANGE`	按逻辑范围计算（相同值的行视为同一组）	处理重复值（如相同日期的数据）

示例（RANGE 处理相同日期的数据）：

SELECT 
    date,
    revenue,
    SUM(revenue) OVER(
        ORDER BY date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales;

如果 2023-01-01 有2行数据（100 和 150），RANGE 会同时计算这两行，而 ROWS 会逐行计算。

5. 总结
• 窗口框架（Window Frame）让你可以更灵活地定义计算范围，而不仅仅是按分区或排序计算。
• ROWS → 适用于固定行数的计算（如移动平均）。
• RANGE → 适用于逻辑范围的计算（如相同日期的数据）。
• 常见用途：移动平均、累计计算、滑动窗口统计。

三、窗口函数家族

1. 排名类函数

1、`ROW_NUMBER()`

ROW_NUMBER() 是 MySQL 窗口函数中的一种行号分配函数，用于为结果集中的每一行分配一个唯一的连续序号（从1开始）。它不考虑值是否相同，严格按照排序顺序编号。

基本语法

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC|DESC], ...
)

参数说明

PARTITION BY：可选，定义分区，在每个分区内独立进行编号
ORDER BY：必需，定义排序规则，决定行的编号顺序

工作原理

首先按照 PARTITION BY 对数据进行分组（如果指定）
在每个分组内按照 ORDER BY 对数据进行排序
为每一行分配一个唯一的连续序号（从1开始）
即使值相同，也会分配不同的行号

使用示例

-- 为员工按部门分组并按薪资降序编号
SELECT 
    department_id,
    employee_name,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS dept_salary_rank
FROM employees;

示例数据表 (employees)

employee_id	employee_name	department_id	salary
1	张三	1	85000
2	李四	1	85000
3	王五	1	95000
4	赵六	2	75000
5	钱七	2	75000
6	孙八	2	105000

查询结果

department_id	employee_name	salary	`dept_salary_rank`
1	王五	95000	1
1	张三	85000	`2`
1	李四	85000	`3`
2	孙八	105000	1
2	赵六	75000	`2`
2	钱七	75000	`3`

特点

• 总是生成唯一的连续序号（即使排序值相同）
• 不跳过任何数字（与RANK()不同）
• 结果值范围是从1开始的无间隔整数序列
• 常用于需要严格排序的场景

与RANK()、DENSE_RANK()的区别

• ROW_NUMBER()：相同值也分配不同序号（1,2,3,4,…）
• RANK()：相同值分配相同序号，后续序号跳过（1,2,2,4,…）
• DENSE_RANK()：相同值分配相同序号，后续序号不跳过（1,2,2,3,…）

实际应用场景

（1）分页查询（获取第N到M条记录）

问题：传统 LIMIT 分页在大数据量时性能差，且无法实现"按某列排序后取中间某段数据"

解决方案：

-- 获取薪资排名第11-20名的员工
WITH ranked_employees AS (
    SELECT 
        employee_name,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_num
    FROM employees
)
SELECT * FROM ranked_employees
WHERE rank_num BETWEEN 11 AND 20;

优势：
• 先排序编号再筛选，比 LIMIT offset 性能更好
• 可以灵活获取任意区间的数据

（2）数据去重（`保留每组的第一条记录`）

问题：表中有重复数据，需要每组保留一条（如相同产品ID保留最新记录）

解决方案：

-- 每个部门保留薪资最高的一条记录
WITH deduplicated AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) AS rn
    FROM employees
)
SELECT * FROM deduplicated WHERE rn = 1;

执行过程：
• 按部门分组，按薪资降序编号
• 只保留每组编号为1的记录

（3）生成唯一标识序号

问题：需要为结果集添加连续序号列，不受后续操作影响

解决方案：

-- 为查询结果添加永久行号
SELECT 
    ROW_NUMBER() OVER (ORDER BY hire_date) AS serial_num,
    employee_name,
    hire_date
FROM employees;

特点：
• 比应用层生成序号更可靠
• 序号会随排序规则变化而变化

（4）需要严格排序的报表制作

问题：制作报表时要求严格的行顺序，且可能有并列情况

解决方案：

-- 生成严格按薪资排序的报表（即使薪资相同也区分顺序）
SELECT 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS strict_rank,
    employee_name,
    salary,
    department_name
FROM employees
JOIN departments USING (department_id);

对比：
• 如果用 RANK()，相同薪资会有相同名次
• ROW_NUMBER() 确保每行有唯一位置，适合打印报表

实际案例

(1)电商场景：获取每个品类销量前三的商品

WITH product_ranking AS (
    SELECT 
        product_id,
        category_id,
        sales_volume,
        ROW_NUMBER() OVER (
            PARTITION BY category_id 
            ORDER BY sales_volume DESC
        ) AS rank_in_category
    FROM products
)
SELECT * FROM product_ranking 
WHERE rank_in_category <= 3;

(2)日志分析：获取每个用户最近一次登录记录

WITH user_logins AS (
    SELECT 
        user_id,
        login_time,
        ip_address,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY login_time DESC
        ) AS login_seq
    FROM login_logs
)
SELECT * FROM user_logins WHERE login_seq = 1;

2、`RANK()`

RANK() 是 MySQL 窗口函数中的一种排名函数，用于为结果集中的每一行分配一个排名序号。与 ROW_NUMBER() 不同，RANK() 会为相同值的行分配相同的排名，并跳过后续序号（1,2,2,4…）。

RANK() 函数特别适合需要处理数值相同但又要反映真实排名位置的场景，是数据分析中常用的排名计算工具。

基本语法

RANK() OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC|DESC], ...
)

参数说明

• PARTITION BY：可选，定义分区，在每个分区内独立进行排名
• ORDER BY：必需，定义排序规则，决定行的排名顺序

工作原理

首先按照 PARTITION BY 对数据进行分组（如果指定）
在每个分组内按照 ORDER BY 对数据进行排序
为每一行分配排名序号：
• 相同值获得相同排名
• 下一个不同值会跳过相应的序号（如两个第1名后，下一个是第3名）

使用示例

-- 为员工按部门分组并按薪资排名
SELECT 
    department_id,
    employee_name,
    salary,
    RANK() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS dept_salary_rank
FROM employees;

示例数据表 (employees)

employee_id	employee_name	department_id	salary
1	张三	1	85000
2	李四	1	85000
3	王五	1	95000
4	赵六	2	75000
5	钱七	2	75000
6	孙八	2	105000
7	周九	2	75000

查询结果

department_id	employee_name	salary	dept_salary_rank
1	王五	95000	1
1	张三	85000	2
1	李四	85000	2
2	孙八	105000	1
2	赵六	75000	2
2	钱七	75000	2
2	周九	75000	2

下一个不同值会从5开始

特点

相同值获得相同排名
会跳过后续序号（如两个第2名后，下一个是第4名）
可能有"**排名空缺"**现象
排名序号不一定连续

实际应用场景

• 比赛成绩排名（允许并列名次）
• 销售业绩排名（相同业绩相同名次）
• 学生考试成绩排名
• 任何需要处理并列情况的排名场景

典型应用案例

1. 销售团队月度业绩排名（允许并列）

SELECT 
    salesperson,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM monthly_sales;

2. 奥运会奖牌榜排名

SELECT 
    country,
    gold_medals,
    silver_medals,
    bronze_medals,
    RANK() OVER (ORDER BY gold_medals DESC, silver_medals DESC, bronze_medals DESC) AS rank
FROM medal_table;

3. 按地区分组的房价排名

SELECT 
    region,
    district,
    average_price,
    RANK() OVER (PARTITION BY region ORDER BY average_price DESC) AS price_rank_in_region
FROM housing_prices;

3、`DENSE_RANK()`

DENSE_RANK() 是 MySQL 窗口函数中的一种排名函数，用于为结果集中的每一行分配一个无间隔的排名序号。与 RANK() 不同，DENSE_RANK() 会为相同值的行分配相同排名，但不会跳过后续序号（1,2,2,3…）。

DENSE_RANK() 函数特别适合需要处理数值相同但又要保持排名连续性的场景，是数据分析中常用的排名计算工具。

基本语法

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC|DESC], ...
)

参数说明

• PARTITION BY：可选，定义分区，在每个分区内独立进行排名
• ORDER BY：必需，定义排序规则，决定行的排名顺序

工作原理

首先按照 PARTITION BY 对数据进行分组（如果指定）
在每个分组内按照 ORDER BY 对数据进行排序
为每一行分配排名序号：
• 相同值获得相同排名
• 下一个不同值会继续使用下一个连续序号（不会跳过数字）

使用示例

-- 为员工按部门分组并按薪资密集排名
SELECT 
    department_id,
    employee_name,
    salary,
    DENSE_RANK() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS dept_salary_rank
FROM employees;

示例数据表 (employees)

employee_id	employee_name	department_id	salary
1	张三	1	85000
2	李四	1	85000
3	王五	1	95000
4	赵六	2	75000
5	钱七	2	75000
6	孙八	2	105000
7	周九	2	75000

查询结果

department_id	employee_name	salary	dept_salary_rank
1	王五	95000	1
1	张三	85000	2
1	李四	85000	2
2	孙八	105000	1
2	赵六	75000	2
2	钱七	75000	2
2	周九	75000	2

特点

相同值获得相同排名
不会跳过后续序号（如三个第2名后，下一个是第3名）
排名序号是连续的
适合需要保持排名连续性的场景

实际应用场景

• 学生成绩排名（不允许有名次空缺）
• 比赛奖项设置（金牌1名，银牌可多名，铜牌可多名）
• 销售业绩排名（相同业绩相同名次，但保持名次连续）
• 任何需要密集排名的场景

典型应用案例

学生考试密集排名（不允许跳过名次）

SELECT 
    student_name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM exam_results;

奥运会奖牌密集排名（允许多个并列）

SELECT 
    country,
    gold_medals,
    DENSE_RANK() OVER (ORDER BY gold_medals DESC) AS rank
FROM medal_table;

部门薪资密集排名

SELECT 
    department_name,
    employee_name,
    salary,
    DENSE_RANK() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees
JOIN departments USING (department_id);

4、`NTILE()`

NTILE() 是 MySQL 窗口函数中的一种分桶函数，用于将有序数据集划分为大致相等的若干组（桶），并为每一行分配其所属的组号。NTILE() 适合需要将数据均匀分组的场景。

基本语法

NTILE(bucket_count) OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC|DESC], ...
)

参数说明

• bucket_count：指定要将数据分成的桶数（必须是正整数）
• PARTITION BY：可选，定义分区，在每个分区内独立进行分桶
• ORDER BY：必需，定义排序规则，决定数据如何分配到各桶

工作原理

首先按照 ORDER BY 对数据进行排序
然后将数据尽可能均匀地分配到指定数量的桶中（尽量让每组行数相同）
为每一行分配一个桶编号（从1开始）
不能整除时，前面的组多一行，余数从前往后分配

使用示例

-- 将员工按薪水高低分成4个等级
SELECT 
    employee_name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

示例数据表 (employees)

employee_id	employee_name	salary
1	张三	85000
2	李四	65000
3	王五	95000
4	赵六	75000
5	钱七	55000
6	孙八	105000
7	周九	45000
8	吴十	80000
9	郑十一	70000
10	王十二	60000

查询结果

employee_name	salary	`salary_quartile`
孙八	105000	1
王五	95000	1
张三	85000	1
吴十	80000	2
赵六	75000	2
郑十一	70000	2
李四	65000	3
王十二	60000	3
钱七	55000	4
周九	45000	4

分配逻辑说明
数据总数：10行，分组数：4组

计算基础行数：
• 10 ÷ 4 = 2 余 2
• 基础每组行数 = 2行
• 余数 = 2行
分配余数：
• 余数2表示需要给2个组各多加1行
• 按照NTILE()的规则，余数总是从第1组开始分配
最终分配：
• 第1组：基础2行 + 1行(余数) = 3行
• 第2组：基础2行 + 1行(剩余余数) = 3行
• 第3组：基础2行 (余数已分配完) = 2行
• 第4组：基础2行 = 2行

特点

当总行数不能被桶数整除时，前面的桶会比后面的桶多1行
常用于数据分析中的分位数计算（如四分位、十分位等）
结果值范围是1到bucket_count的整数

实际应用场景

• 客户价值分层（高/中/低价值客户）
• 成绩等级划分
• 销售业绩分组排名
• 数据采样时均匀分组

2. 分析类函数

LEAD(列名, n)：获取当前行后第n行的值
LAG(列名, n)：获取当前行前第n行的值
FIRST_VALUE(列名)：窗口第一个值
LAST_VALUE(列名)：窗口最后一个值
NTH_VALUE(列名, n)：窗口第n个值

1、`LEAD(列名, n)`

LEAD() 是 MySQL 窗口函数中的一种偏移函数，用于访问当前行之后的指定行数据，它允许在不使用自连接的情况下获取后续行的值，是数据分析中实现行间比较的重要工具。

怎么记忆？

LEAD（向前/未来导向）
词源：英语中"lead"作动词意为"引领、领先"
LEAD → 联想"Leader"（领导者）→ 看未来数据
命名逻辑：

函数向前查看（指向当前行之后的"未来"数据）
类似"领头羊"的概念，访问的是后续行（领先于当前行的数据）
例如：LEAD(salary, 1) 获取"下一个"更高薪资（领先当前行的值）

基本语法

LEAD(column_name, offset, default_value) OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC|DESC], ...
)

参数说明

• column_name：要获取后续值的列
• offset：向后偏移的行数（默认为1）
• default_value：当超出范围时返回的默认值（可选）
• PARTITION BY：可选，定义分区范围
• ORDER BY：必需，定义窗口中的行顺序

工作原理

按照PARTITION BY和ORDER BY对数据进行排序
对每一行，返回其后第offset行的指定列值
如果不存在后续行（窗口末尾），返回default_value或NULL

使用示例

-- 获取每个员工的下一个更高薪资
SELECT 
    employee_name,
    salary,
    LEAD(salary, 1) OVER (ORDER BY salary) AS next_higher_salary
FROM employees;

特点

正向偏移（向后查找）
可自定义偏移量
可处理分区边界
默认返回NULL当超出范围

实际应用场景

（1）计算环比增长率

-- 计算月度销售额环比增长率（本月 vs 上月）
SELECT 
    month,
    sales_amount AS current_month_sales,
    LEAD(sales_amount, 1) OVER (ORDER BY month) AS next_month_sales,
    ROUND(
        (LEAD(sales_amount, 1) OVER (ORDER BY month) - sales_amount) / 
        sales_amount * 100, 
        2
    ) AS mom_growth_rate_percent
FROM monthly_sales
ORDER BY month DESC;  -- 按月份降序显示最新月份在前

/* 结果示例：
month      | current_month_sales | next_month_sales | mom_growth_rate_percent
-----------+---------------------+------------------+-----------------------
2023-04-01 | 125000              | 118000           | -5.60      (4月比3月下降5.6%)
2023-03-01 | 118000              | 135000           | 14.41      (3月比2月增长14.41%)
2023-02-01 | 135000              | 120000           | -11.11     (2月比1月下降11.11%)
2023-01-01 | 120000              | NULL             | NULL       (没有上个月数据)
*/

疑问：为什么ROUND中不能用前面已经定义好的别名next_month_sales呢？

SQL 语句的逻辑执行顺序与书写顺序不同，了解这个顺序对于编写高效查询和调试复杂 SQL 非常重要。
SQL 查询的典型执行顺序： FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

在同一个SELECT层级中：
✅ 可以引用表中原有的列名
❌ 不能引用同一层级定义的别名
✅ 可以在ORDER BY或外层查询中引用别名

因此，窗口函数（如LEAD()）在SELECT阶段计算，列别名在同一个SELECT列表中不能互相引用！！

（2）查找连续日期中的缺口

-- 检测用户登录记录中的日期间断
WITH login_dates_with_next AS (
    SELECT 
        user_id,
        login_date,
        #为每个用户的登录记录找出下一次登录的日期
        #"对于每个用户(user_id)，按照登录日期排序，找出每条记录的下一条登录记录的日期"
        LEAD(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS next_login_date
    FROM user_logins
)
SELECT 
    user_id,
    login_date,
    next_login_date,
    DATEDIFF(next_login_date, login_date) AS days_between_logins
FROM login_dates_with_next
WHERE DATEDIFF(next_login_date, login_date) > 1  -- 只显示有间隔的记录
ORDER BY user_id, login_date;

原始数据表 user_logins

user_id	login_date
1001	2023-01-01
1001	2023-01-03
1001	2023-01-03
1001	2023-01-07
1001	2023-01-10
1002	2023-01-02
1002	2023-01-05
1002	2023-01-09
1003	2023-01-01
1003	2023-01-02

中间结果 login_dates_with_next

user_id	login_date	next_login_date
1001	2023-01-01	2023-01-03
1001	2023-01-03	2023-01-03
1001	2023-01-03	2023-01-07
1001	2023-01-07	2023-01-10
1001	2023-01-10	NULL
1002	2023-01-02	2023-01-05
1002	2023-01-05	2023-01-09
1002	2023-01-09	NULL
1003	2023-01-01	2023-01-02
1003	2023-01-02	NULL

最终查询结果

user_id	login_date	next_login_date	days_between_logins
1001	2023-01-01	2023-01-03	2
1001	2023-01-03	2023-01-07	4
1001	2023-01-07	2023-01-10	3
1002	2023-01-02	2023-01-05	3
1002	2023-01-05	2023-01-09	4

结果说明：

过滤掉了间隔≤1天的记录（如1003用户连续两天登录）
保留了所有间隔>1天的登录记录
NULL值被自动排除（最后一条记录无下一条）
重复登录（如1001在1月3日多次登录）只计算日期变化

（3）比较当前值与下一个值

-- 比较股票连续交易日的涨跌幅（当日收盘价 vs 次日收盘价）
SELECT 
    stock_code,
    trade_date,
    close_price AS today_close,
    LEAD(close_price, 1) OVER (PARTITION BY stock_code ORDER BY trade_date) AS next_day_close,
    ROUND(
        (LEAD(close_price, 1) OVER (PARTITION BY stock_code ORDER BY trade_date) - close_price) / 
        close_price * 100, 
        2
    ) AS daily_change_percent,
    CASE 
        WHEN LEAD(close_price, 1) OVER (PARTITION BY stock_code ORDER BY trade_date) > close_price 
        THEN '上涨'
        WHEN LEAD(close_price, 1) OVER (PARTITION BY stock_code ORDER BY trade_date) < close_price 
        THEN '下跌'
        ELSE '平盘'
    END AS price_movement
FROM stock_daily
WHERE stock_code = 'AAPL'  -- 苹果公司股票
  AND trade_date BETWEEN '2023-01-01' AND '2023-01-10'
ORDER BY trade_date DESC;  -- 按日期降序显示最新日期在前

原始数据表 stock_daily

stock_code	trade_date	close_price
AAPL	2023-01-03	125.07
AAPL	2023-01-04	126.36
AAPL	2023-01-05	129.62
AAPL	2023-01-06	129.93
AAPL	2023-01-09	130.15
AAPL	2023-01-10	132.30

查询执行过程分析

首先按 stock_code 分组，按 trade_date 排序
对每条记录计算：
• today_close: 当日收盘价

• next_day_close: 使用 LEAD() 获取次日收盘价

• daily_change_percent: (次日收盘价-当日收盘价)/当日收盘价*100

• price_movement: 判断涨跌状态

查询结果表格

stock_code	trade_date	today_close	next_day_close	daily_change_percent	price_movement
AAPL	2023-01-10	132.30	NULL	NULL	NULL
AAPL	2023-01-09	130.15	132.30	1.65	上涨
AAPL	2023-01-06	129.93	130.15	0.17	上涨
AAPL	2023-01-05	129.62	129.93	0.24	上涨
AAPL	2023-01-04	126.36	129.62	2.58	上涨
AAPL	2023-01-03	125.07	126.36	1.03	上涨

结果解读

2023-01-10 日数据：
• 没有次日数据（next_day_close为NULL）
• 涨跌幅和状态均为NULL
2023-01-09 日数据：
• 当日收盘价130.15
• 次日(1月10日)收盘价132.30
• 涨幅：(132.30-130.15)/130.15*100=1.65%
• 状态：上涨
2023-01-03 日数据：
• 当日收盘价125.07
• 次日(1月4日)收盘价126.36
• 涨幅：(126.36-125.07)/125.07*100=1.03%
• 状态：上涨

关键点说明

LEAD() 函数按交易日期顺序获取下一条记录
最后一条记录没有"次日"数据，所以相关字段为NULL
百分比计算保留2位小数
结果按 trade_date DESC 排序，最新日期显示在最上方
此查询可帮助分析股票的短期价格走势

2、`LAG(列名, n)`

LAG() 是 MySQL 窗口函数中的一种偏移函数，用于访问当前行之前的指定行数据。它与LEAD()相反，常用于比较当前行与历史数据。

怎么记忆？

LAG（向后/历史导向）
• 词源：英语中"lag"作动词意为"滞后、落后"
• 命名逻辑：
• 函数向后查看（指向当前行之前的"历史"数据）
• 类似"拖后腿"的概念，访问的是先前行（滞后于当前行的数据）
• 例如：LAG(salary, 1) 获取"上一个"更低薪资（落后当前行的值）
LAG → 联想"Lag behind"（落后）→ 看历史数据

基本语法

LAG(column_name, offset, default_value) OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC|DESC], ...
)

特点

反向偏移（向前查找）
其他特性与LEAD()类似

实际应用场景

1. 计算日环比增长率（日环比）

-- 计算股票日收益率（今日收盘价 vs 昨日收盘价）
SELECT 
    stock_code,
    trade_date,
    close_price,
    LAG(close_price, 1) OVER (PARTITION BY stock_code ORDER BY trade_date) AS prev_day_close,
    ROUND(
        (close_price - LAG(close_price, 1) OVER (PARTITION BY stock_code ORDER BY trade_date)) / 
        LAG(close_price, 1) OVER (PARTITION BY stock_code ORDER BY trade_date) * 100, 
        2
    ) AS daily_return_pct
FROM stock_daily
WHERE stock_code = 'AAPL'
ORDER BY trade_date DESC;

/* 示例结果：
stock_code | trade_date | close_price | prev_day_close | daily_return_pct
-----------+------------+-------------+----------------+-----------------
AAPL      | 2023-03-03 | 151.03      | 149.93         | 0.73
AAPL      | 2023-03-02 | 149.93      | 148.90         | 0.69
AAPL      | 2023-03-01 | 148.90      | NULL           | NULL
*/

2. 检测数据异常波动

-- 检测温度传感器异常读数（与前一日温差过大）
WITH temperature_with_prev AS (
    SELECT 
        sensor_id,
        record_date,
        temperature,
        LAG(temperature, 1) OVER (PARTITION BY sensor_id ORDER BY record_date) AS prev_day_temp
    FROM temperature_readings
)
SELECT 
    sensor_id,
    record_date,
    temperature,
    prev_day_temp,
    ABS(temperature - prev_day_temp) AS temp_change,
    CASE 
        WHEN ABS(temperature - prev_day_temp) > 5 THEN '异常波动'
        ELSE '正常'
    END AS status
FROM temperature_with_prev
WHERE prev_day_temp IS NOT NULL
ORDER BY temp_change DESC;

/* 示例结果：
sensor_id | record_date | temperature | prev_day_temp | temp_change | status
----------+-------------+-------------+---------------+-------------+---------
S001     | 2023-02-15  | 32.5        | 24.1          | 8.4         | 异常波动
S002     | 2023-02-18  | 18.2        | 22.7          | 4.5         | 正常
*/

3. 计算5日移动平均值

-- 计算销售额的5日移动平均
SELECT 
    sales_date,
    sales_amount,
    LAG(sales_amount, 1) OVER (ORDER BY sales_date) AS prev_day_1,
    LAG(sales_amount, 2) OVER (ORDER BY sales_date) AS prev_day_2,
    LAG(sales_amount, 3) OVER (ORDER BY sales_date) AS prev_day_3,
    LAG(sales_amount, 4) OVER (ORDER BY sales_date) AS prev_day_4,
    ROUND((
        sales_amount +
        LAG(sales_amount, 1) OVER (ORDER BY sales_date) +
        LAG(sales_amount, 2) OVER (ORDER BY sales_date) +
        LAG(sales_amount, 3) OVER (ORDER BY sales_date) +
        LAG(sales_amount, 4) OVER (ORDER BY sales_date)
    ) / 5.0, 2) AS moving_avg_5day
FROM daily_sales
ORDER BY sales_date DESC;

计算示例

原始数据表 daily_sales

sales_date	sales_amount
2023-03-01	10000
2023-03-02	11000
2023-03-03	11500
2023-03-04	12000
2023-03-05	12500
2023-03-06	13000
2023-03-07	11800
2023-03-08	12200

查询结果

sales_date	sales_amount	prev_day_1	prev_day_2	prev_day_3	prev_day_4	moving_avg_5day
2023-03-08	12200	11800	13000	12500	12000	12300.00
2023-03-07	11800	13000	12500	12000	11500	12160.00
2023-03-06	13000	12500	12000	11500	11000	12000.00
2023-03-05	12500	12000	11500	11000	10000	11400.00
2023-03-04	12000	11500	11000	10000	NULL	NULL
2023-03-03	11500	11000	10000	NULL	NULL	NULL
2023-03-02	11000	10000	NULL	NULL	NULL	NULL
2023-03-01	10000	NULL	NULL	NULL	NULL	NULL

计算过程详解（以2023-03-08为例）

获取前几日的销售额：
• prev_day_1 (前1天): 2023-03-07 → 11800

• prev_day_2 (前2天): 2023-03-06 → 13000

• prev_day_3 (前3天): 2023-03-05 → 12500

• prev_day_4 (前4天): 2023-03-04 → 12000

计算5日移动平均：

(12200 + 11800 + 13000 + 12500 + 12000) / 5 
= 61500 / 5 
= 12300.00

更简洁的写法（使用窗口框架）

SELECT 
    sales_date,
    sales_amount,
    AVG(sales_amount) OVER (
        ORDER BY sales_date 
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS moving_avg_5day
FROM daily_sales
ORDER BY sales_date DESC;

sales_date	sales_amount	moving_avg_5day
2023-03-08	12200	12300.00
2023-03-07	11800	12160.00
2023-03-06	13000	12000.00
2023-03-05	12500	11400.00
2023-03-04	12000	NULL
2023-03-03	11500	NULL
2023-03-02	11000	NULL
2023-03-01	10000	NULL

关键观察

前4行有NULL值，因为：
• 2023-03-01没有前4天的数据
• 2023-03-02只有前1天的数据
• 2023-03-03只有前2天的数据
• 2023-03-04只有前3天的数据
完整5日窗口从2023-03-05开始：
• 2023-03-05：使用01-05日数据
• 2023-03-06：使用02-06日数据
• 以此类推…
移动平均线平滑了每日波动，更容易观察趋势

3、`FIRST_VALUE(列名)`

FIRST_VALUE() 是 MySQL 窗口函数中的一种导航函数，用于获取窗口框架中的第一个值，能够返回当前窗口分区中按指定顺序排序后的第一个值。

基本语法

FIRST_VALUE(column_name) OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC|DESC]
    [frame_clause]
)

参数说明

• column_name：要获取第一个值的列
• PARTITION BY：可选，定义分区，在每个分区内独立计算
• ORDER BY：必需，定义排序规则，决定窗口中的行顺序
• frame_clause：可选，定义窗口框架范围（默认为RANGE UNBOUNDED PRECEDING）

工作原理

首先按照PARTITION BY对数据进行分组（如果指定）
在每个分组内按照ORDER BY对数据进行排序
返回窗口框架中的第一个值
如果没有指定窗口框架，默认使用整个分区作为窗口

使用示例

-- 获取每个部门薪资最高的员工信息
SELECT 
    department_id,
    employee_name,
    salary,
    FIRST_VALUE(employee_name) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS highest_paid_employee
FROM employees;

示例数据表 (employees)

employee_id	employee_name	department_id	salary
1	张三	1	85000
2	李四	1	85000
3	王五	1	95000
4	赵六	2	75000
5	钱七	2	80000
6	孙八	2	105000

查询结果

department_id	employee_name	salary	highest_paid_employee
1	王五	95000	王五
1	张三	85000	王五
1	李四	85000	王五
2	孙八	105000	孙八
2	钱七	80000	孙八
2	赵六	75000	孙八

特点

总是返回窗口中的第一个值
结果不受当前行位置影响
默认使用整个分区作为窗口
常与窗口框架配合使用实现特定需求

实际应用场景

• 获取分组内的基准值（如部门最高薪）
• 计算与基准值的差异（如销售额与最高销售额的差距）
• 标记分组内的第一条记录
• 任何需要获取窗口起始值的场景

典型应用案例

1. 计算销售额与部门最高销售额的差距

SELECT 
    department_id,
    salesperson,
    sales_amount,
    FIRST_VALUE(sales_amount) OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS dept_max_sales,
    sales_amount - FIRST_VALUE(sales_amount) OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS diff_from_max
FROM sales_records;

2. 标记每个客户的第一笔订单

SELECT 
    customer_id,
    order_date,
    order_amount,
    CASE 
        WHEN order_date = FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) 
        THEN '首单' 
        ELSE '非首单' 
    END AS order_type
FROM orders;

3. 计算股票价格与月开盘价的涨跌幅

SELECT 
    stock_code,
    trade_date,
    close_price,
    FIRST_VALUE(close_price) OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m') ORDER BY trade_date) AS month_open_price,
    ROUND((close_price - FIRST_VALUE(close_price) OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m') ORDER BY trade_date)) / 
          FIRST_VALUE(close_price) OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m') ORDER BY trade_date) * 100, 2) AS monthly_change_pct
FROM stock_daily
ORDER BY trade_date DESC;

注意事项

使用时要明确指定ORDER BY，否则结果不可预测
默认窗口框架可能不符合预期，必要时需显式指定
与LAST_VALUE()配合使用可实现窗口范围分析
性能优于自连接方式获取分组极值

4、`LAST_VALUE(列名)`

LAST_VALUE(列名)是 MySQL 窗口函数中的一种导航函数，用于获取窗口框架中的最后一个值。与 FIRST_VALUE() 相对应，它能够返回当前窗口分区中按指定顺序排序后的最后一个值，常用于分析数据窗口的结束值或最新值。

基本语法

LAST_VALUE(column_name) OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC|DESC]
    [frame_clause]
)

参数说明

• column_name：要获取最后一个值的列
• PARTITION BY：可选，定义分区，在每个分区内独立计算
• ORDER BY：必需，定义排序规则，决定窗口中的行顺序
• frame_clause：关键参数，必须显式指定窗口框架（默认行为与预期不符）

工作原理

首先按照PARTITION BY对数据进行分组（如果指定）
在每个分组内按照ORDER BY对数据进行排序
根据窗口框架范围确定"最后一个值"
默认框架为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（通常需要修改）

使用示例

-- 获取每个部门薪资排序的当前最后值（需指定正确窗口框架）
SELECT 
    department_id,
    employee_name,
    salary,
    LAST_VALUE(employee_name) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_paid_employee
FROM employees;

示例数据表 (employees)

employee_id	employee_name	department_id	salary
1	张三	1	85000
2	李四	1	82000
3	王五	1	95000
4	赵六	2	75000
5	钱七	2	80000
6	孙八	2	105000

查询结果（使用正确窗口框架）

department_id	employee_name	salary	lowest_paid_employee
1	王五	95000	李四
1	张三	85000	李四
1	李四	82000	李四
2	孙八	105000	赵六
2	钱七	80000	赵六
2	赵六	75000	赵六

特点

必须显式指定窗口框架（默认行为只看到当前行）
通常配合ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING使用
结果反映整个窗口的结束值
比自连接查询性能更高

实际应用场景

• 获取分组内的最新值/结束值
• 计算全窗口的极差（最大值-最小值）
• 标记时间序列的最终状态
• 分析数据窗口的最终结果

典型应用案例

1. 计算股票月内波动幅度（最高 vs 最低）

SELECT 
    stock_code,
    trade_date,
    close_price,
    FIRST_VALUE(close_price) OVER (
        PARTITION BY DATE_FORMAT(trade_date, '%Y-%m') 
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS month_open_price,
    LAST_VALUE(close_price) OVER (
        PARTITION BY DATE_FORMAT(trade_date, '%Y-%m') 
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS month_close_price
FROM stock_daily;

2. 客户最后购买记录标记

SELECT 
    customer_id,
    order_date,
    order_amount,
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_purchase_date,
    CASE WHEN order_date = LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) THEN '最近购买' ELSE '历史订单' END AS order_status
FROM orders;

3. 项目阶段最终状态分析

SELECT 
    project_id,
    status_date,
    status,
    LAST_VALUE(status) OVER (
        PARTITION BY project_id 
        ORDER BY status_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS final_status
FROM project_status_log
ORDER BY project_id, status_date;

注意事项

必须显式指定窗口框架（这是与FIRST_VALUE最大的不同）
性能优于使用子查询获取极值的方式
与FIRST_VALUE()配合使用可分析完整数据窗口
在时间序列分析中特别有用

窗口框架推荐用法

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- 全分区范围
-- 或
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING         -- 从当前行到结束

提示：LAST_VALUE() 的默认行为（不指定框架时）通常不符合预期，这是使用该函数时最常见的错误原因。

3. 聚合函数作为窗口函数

1、`SUM() OVER()`

SUM() OVER() 是窗口函数中的聚合函数，用于计算指定窗口范围内的数值总和。与普通SUM()不同，它不会减少行数，而是为每一行返回累计总和。

基本语法

SUM(column) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC|DESC], ...]
    [frame_clause]
)

参数说明

• PARTITION BY：可选，定义计算总和的分区
• ORDER BY：可选，定义计算累计总和的顺序
• frame_clause：可选，定义窗口框架范围（如ROWS BETWEEN…）

工作原理

按PARTITION BY分组（如未指定则视为一个分区）
按ORDER BY排序（如指定则计算累计和）
计算当前窗口范围内的数值总和

使用示例

-- 计算每个部门的薪资总额及员工薪资占比
SELECT 
    employee_name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total_salary,
    salary/SUM(salary) OVER (PARTITION BY department) AS salary_ratio
FROM employees;

特点

保留原始行数
可计算分区总和或累计总和
支持动态窗口范围定义

实际应用场景

1. 计算累计销售额

基本累计（从开始到当前行）

SELECT 
    sales_date,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sales_date) AS running_total
FROM daily_sales;

按部门分组累计

SELECT 
    department_id,
    sales_date,
    sales_amount,
    SUM(sales_amount) OVER (
        PARTITION BY department_id 
        ORDER BY sales_date
    ) AS dept_running_total
FROM sales_records;

2. 计算百分比/占比

计算单笔销售额占总销售额比例

SELECT 
    order_id,
    order_amount,
    ROUND(
        order_amount / SUM(order_amount) OVER () * 100,
        2
    ) AS percentage_of_total
FROM orders;

计算各部门销售额占比

SELECT 
    department_id,
    SUM(sales_amount) AS dept_sales,
    ROUND(
        SUM(sales_amount) / SUM(SUM(sales_amount)) OVER () * 100,
        2
    ) AS dept_percentage
FROM sales_records
GROUP BY department_id;

计算每月销售额占全年比例

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(order_amount) AS monthly_sales,
    ROUND(
        SUM(order_amount) / SUM(SUM(order_amount)) OVER (PARTITION BY YEAR(order_date)) * 100,
        2
    ) AS monthly_percentage
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m'), YEAR(order_date);

3. 滚动求和计算

7天滚动销售额合计

SELECT 
    sales_date,
    sales_amount,
    SUM(sales_amount) OVER (
        ORDER BY sales_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_sales
FROM daily_sales;

3个月滚动平均销售额

SELECT 
    sales_month,
    monthly_sales,
    AVG(monthly_sales) OVER (
        ORDER BY sales_month 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3month_avg
FROM monthly_sales;

按季度滚动求和（当前季度和前两个季度）

SELECT 
    year,
    quarter,
    quarterly_sales,
    SUM(quarterly_sales) OVER (
        ORDER BY year, quarter 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3quarter_sum
FROM quarterly_sales;

4. 高级应用：组合使用

（1）计算累计占比

SELECT 
    sales_date,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sales_date) AS running_total,
    ROUND(
        SUM(sales_amount) OVER (ORDER BY sales_date) / 
        SUM(sales_amount) OVER () * 100,
        2
    ) AS cumulative_percentage
FROM daily_sales;

（2）带条件的滚动计算（仅计算工作日）

SELECT 
    trade_date,
    stock_price,
    AVG(stock_price) OVER (
        ORDER BY trade_date 
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS 5day_moving_avg,
    CASE WHEN DAYOFWEEK(trade_date) IN (1,7) THEN 'Weekend' ELSE 'Weekday' END AS day_type
FROM stock_prices;

2、`AVG() OVER()`

AVG() OVER() 计算窗口范围内的平均值，保留原始行数。

基本语法

AVG(column) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC|DESC], ...]
    [frame_clause]
)

示例

-- 计算30天移动平均销售额
SELECT 
    date,
    sales,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS 30day_moving_avg
FROM daily_sales;

特点

• 计算分区或窗口平均值
• 常用于趋势分析
• 支持移动平均计算

3、`COUNT() OVER()`

COUNT() OVER() 计算窗口范围内的行数。

基本语法

COUNT(*) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC|DESC], ...]
    [frame_clause]
)

示例

-- 计算每个产品的订单数量
SELECT 
    product_id,
    order_id,
    COUNT(*) OVER (PARTITION BY product_id) AS product_order_count
FROM orders;

特点

• 可计算分区总行数
• 常用于计算频率/密度
• 支持条件计数（需配合CASE WHEN）

4、`MAX() OVER()`

MAX() OVER() 返回窗口范围内的最大值。

基本语法

MAX(column) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC|DESC], ...]
    [frame_clause]
)

示例

-- 查找各部门最高薪资
SELECT 
    employee_name,
    department,
    salary,
    MAX(salary) OVER (PARTITION BY department) AS dept_max_salary
FROM employees;

特点

• 常用于基准比较
• 可计算滚动窗口最大值
• 性能优于自连接查询

5、`MIN() OVER()`

MIN() OVER() 返回窗口范围内的最小值。

基本语法

MIN(column) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC|DESC], ...]
    [frame_clause]
)

示例

-- 计算股票每日最低价
SELECT 
    trade_date,
    stock_code,
    price,
    MIN(price) OVER (PARTITION BY stock_code ORDER BY trade_date) AS historical_low
FROM stock_prices;

特点

• 常用于寻找记录低点
• 支持时间序列分析
• 可计算移动最小值

6、比较总结

函数	功能特点	典型应用场景
`SUM()`	计算窗口总和	累计计算、占比分析
`AVG()`	计算窗口平均值	移动平均、趋势分析
`COUNT()`	计算窗口行数	频率统计、密度分析
`MAX()`	找出窗口最大值	基准比较、峰值检测
`MIN()`	找出窗口最小值	低点分析、记录追踪

所有窗口聚合函数都支持：

保留原始行数
分区计算（PARTITION BY）
排序累计（ORDER BY）
自定义窗口框架（frame_clause）

四、补充

（1）RANGE 和 ROWS 在窗口函数中的区别？

核心区别
• ROWS = 按物理行计算（数"行数"）
• RANGE = 按逻辑范围计算（看"值的大小"）

具体区别

特性	`ROWS`	`RANGE`
计算方式	按绝对行数	按排序字段的值范围
相同值处理	每行独立计算	相同值会被合并计算
性能	更快	较慢（需要额外计算）
典型用途	移动平均、固定行数计算	处理重复值、按实际范围计算

实际例子说明
假设有这样的销售数据：

-- 测试数据
INSERT INTO sales VALUES
 ('2023-01-01', 100),
 ('2023-01-02', 150),
 ('2023-01-02', 200), -- 注意这里有重复日期
 ('2023-01-03', 50);

使用ROWS的查询

SELECT 
    date,
    revenue,
    SUM(revenue) OVER(
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS rows_running_total
FROM sales;

结果：

date       | revenue | rows_running_total
-----------+---------+-------------------
2023-01-01 | 100     | 100    ← 第1行
2023-01-02 | 150     | 250    ← 100+150 (第1+2行)
2023-01-02 | 200     | 450    ← 250+200 (第1+2+3行) 
2023-01-03 | 50      | 500    ← 450+50

使用RANGE的查询

SELECT 
    date,
    revenue,
    SUM(revenue) OVER(
        ORDER BY date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS range_running_total
FROM sales;

结果：

date       | revenue | range_running_total
-----------+---------+--------------------
2023-01-01 | 100     | 100    ← 只有这一天
2023-01-02 | 150     | 450    ← 100+150+200 (所有1月2日的数据)
2023-01-02 | 200     | 450    ← 同上（相同日期被合并计算）
2023-01-03 | 50      | 500    ← 450+50

关键区别图示

数据行： [2023-01-01/100] → [2023-01-02/150] → [2023-01-02/200] → [2023-01-03/50]

ROWS计算：  行1      行1+2     行1+2+3      行1+2+3+4
           [100]    [250]     [450]       [500]

RANGE计算： date<=1/1  date<=1/2          date<=1/3
           [100]      [100+150+200]       [100+150+200+50]
                      [450]重复两次       [500]

什么时候用哪个？
**用 ROWS **
• 需要计算固定行数（如"最近3行"）
• 数据没有重复排序值
• 需要更高性能

用 RANGE
• 排序字段可能有重复值（如相同日期）
• 需要按实际值范围计算（如"所有小于当前值的行"）
• 做时间序列分析时更准确

进阶技巧
可以组合使用：

-- 计算当前日期及前2天（按日期范围）
SUM(revenue) OVER(
    ORDER BY date
    RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW
)

MySQL中的窗口函数

一、窗口函数简介

1、窗口函数特点

2、基本语法

3、与GROUP BY的关键区别

4、窗口函数的应用场景

二、窗口定义三要素

1. PARTITION BY

2. ORDER BY

3. 窗口框架

三、窗口函数家族

1. 排名类函数

1、ROW_NUMBER()

基本语法

参数说明

工作原理

使用示例

特点

与RANK()、DENSE_RANK()的区别

实际应用场景

（1）分页查询（获取第N到M条记录）

（2）数据去重（保留每组的第一条记录）

（3）生成唯一标识序号

（4）需要严格排序的报表制作

实际案例

(1)电商场景：获取每个品类销量前三的商品

(2)日志分析：获取每个用户最近一次登录记录

2、RANK()

基本语法

参数说明

工作原理

使用示例

特点

实际应用场景

典型应用案例

1. 销售团队月度业绩排名（允许并列）

2. 奥运会奖牌榜排名

3. 按地区分组的房价排名

3、DENSE_RANK()

基本语法

参数说明

工作原理

使用示例

实际应用场景

典型应用案例

4、NTILE()

基本语法

参数说明

工作原理

使用示例

特点

实际应用场景

2. 分析类函数

1、LEAD(列名, n)

怎么记忆？

基本语法

参数说明

工作原理

使用示例

特点

实际应用场景

（1）计算环比增长率

（2）查找连续日期中的缺口

（3）比较当前值与下一个值

2、LAG(列名, n)

怎么记忆？

基本语法

特点

实际应用场景

1. 计算日环比增长率（日环比）

2. 检测数据异常波动

3. 计算5日移动平均值

3、FIRST_VALUE(列名)

基本语法

参数说明

工作原理

使用示例

特点

实际应用场景

典型应用案例

1、`ROW_NUMBER()`

（2）数据去重（`保留每组的第一条记录`）

2、`RANK()`

3、`DENSE_RANK()`

4、`NTILE()`

1、`LEAD(列名, n)`

2、`LAG(列名, n)`

3、`FIRST_VALUE(列名)`

4、`LAST_VALUE(列名)`

1、`SUM() OVER()`

2、`AVG() OVER()`

3、`COUNT() OVER()`

4、`MAX() OVER()`

5、`MIN() OVER()`