本文共 2313 字，大约阅读时间需要 7 分钟。

MySQL8.0 窗口函数解析

1. 窗口函数与聚合函数的区别

窗口函数，随着MySQL8.0的新功能推出，为业务场景带来了全新的数据处理方式。与聚合函数不同，聚合函数通过统计分类汇总数据，而窗口函数则关注于围绕每条记录的分组排序结果，进行特定计算，如环比增长率、百分比分布等。理解这一区别是掌握窗口函数的第一步。

2. 窗口函数的基本语法

MySQL8.0窗口函数的核心组成部分包括：

• 窗口函数：如ROW_NUMBER() OVER w等。
• over子句：定义窗口的别名及规则。
• window子句：明确窗口的范围和计算规则。
• partition子句：定义窗口的分组依据。
• order子句：排序规则以决定窗口内的记录顺序。
• frame子句：定义窗口的计算边界。

示例：

SELECT 
    time, 
    subject, 
    val, 
    FIRST_VALUE(val) OVER w AS 'first', 
    LAST_VALUE(val) OVER w AS 'last', 
    NTH_VALUE(val, 2) OVER w AS 'second', 
    NTH_VALUE(val, 4) OVER w AS 'fourth' 
FROM observations 
WINDOW w AS (
    PARTITION BY subject 
    ORDER BY time 
    ROWS UNBOUNDED PRECEDING
)

3. 常见窗口函数列表

4. 窗口函数的具体使用

4.1 over子句

over子句用于指定窗口的别名及规则，可以与partition和order一起使用：

over(partition by aa order by bb) w as col_name => over() w as col_name

4.2 window子句

window子句定义窗口范围，若不需要分组，可以省略：

window w as (partition by aa)

4.3 frame子句

frame子句确定窗口计算范围，常用行数限定：

ROWS N PRECEDING
ROWS BETWEEN N PRECEDING AND M FOLLOWING

示例：

• window w as (rows unbounded preceding)：窗口从当前记录扩展到数据集末尾。
• window w as (rows between unbounded preceding and unbounded following)：默认模式，涉及所有记录。

5. MySQL8.0 之前窗口函数的替代方案

在 absence of窗口函数，需借助临时表及变量模拟。

5.1 行号生成

无分组排序：

SELECT row_number() over w as row_num, ... FROM score WINDOW w as (order by score desc)

分组排序：

SELECT row_number() from (SELECT @r:=@r+1 as row_num, ... FROM score WINDOW w as (order by score desc, class asc)) r

5.2 密集排位（dense_rank）

无分组排序：

SELECT case when s = score then r else if s := score then r + 1 end as dense_rank, ...

分组排序：

SELECT dense_rank() from (SELECT s, r from score WINDOW w as (partition by class_num order by score desc)) r

5.3 排名（rank）

无分组排序：

SELECT `rank` from (SELECT @r := if(s=score,@r,@c) as rank, ... FROM score WINDOW w as (order by score desc)) r

分组排序：

SELECT `rank` from (SELECT if(class_num = class,@c:=@c+1,s:=score) as class_num,...) r

6. 总结

MySQL8.0窗口函数赋予开发者对数据进行分组排序计算的更大灵活性。理解窗口函数与聚合函数的区别、掌握窗口基本语法及常用函数，能够更高效地解决实际业务需求。在旧版本中，可通过临时表和变量实现类似功能，但功能较为基础。掌握这些技能，将为数据库应用开发提供强大的工具，提升数据分析能力。