文章目录
从 MySQL 8.0 版本开始,MySQL 支持窗口函数,对于查询中的每一行,使用与该行相关的所有行,进行函数计算。
窗口函数概述
窗口函数(Window Function)指的是对于查询结果的每一行,使用与该行相关的所有行,进行函数计算,可得到新的数据列。
对于大多数聚合函数(如 min, max, count, sum, avg 等),如果其后紧跟 over 语句,就会作为窗口函数执行。
窗口函数和聚合函数的区别
- 聚合函数是将分组(group)内的多条记录压缩聚合为一条记录,一般用 group by 分组。
- 窗口函数一般会用 over() 内的 partition by 分区(类似于分组),order by 排序(可能产生隐式的框架边界),以及后面的 frame 子句定义框架。但无论如何,窗口函数都不会因此压缩记录。
也就是说,窗口函数会对查询的每一行进行计算,并为每一行产生一个计算结果。
- 正要进行函数计算的行,被称为当前行。
- 与当前行相关的所有行,组成了当前行的窗口。
- 当前行所在的分区(partition),被称为当前分区。
- 当前行所在的框架(frame),被称为当前框架。
- 当前行的窗口边界,由窗口函数后的 over(partition by …) 分割决定。这个边界可能是整个结果集、分区或者框架。
说明: 如果 over() 语句是空的,就把整个查询结果集看作一个 partition 分区。
使用要点
- 窗口函数只允许出现在 select 列表和 order by 语句中。
- 从 from 语句开始,到 where, group by, having 语句执行完毕,查询结果集才被确定。
- 窗口函数的计算发生在 order by, limit, select distinct 语句执行之前。
- 大多数聚合函数是否被作为窗口函数执行,取决于 over 语句是在场还是缺席。
- 非聚合函数的窗口函数只能作为窗口函数使用,故强制要求有 over 语句。
- 特别注意窗口函数的边界,是整个结果集,还是分区(partition),还是分区内的框架(frame)?多数非聚合的窗口函数是直接忽视框架的。
应用场景
窗口函数的应用场景很广,比如:
- 求公司里各个部门内的员工绩效分数排名。
- 求部门内工资最高的所有员工。
- 求部门内工资前三高的所有员工。
- 求公司每年的利润增长率。
- 求连续出现三次及以上的字段值。
- 求每一行与前后 1 行或 n 行的滚动平均数。
- 求每一行与前后 1 行的差或者和。
- 求每一行与前面行的累计和。
总之,就是应用很广泛,在性能和可读性方面也很不错。
语法结构
使用窗口函数的语法结构为:
# over 语句:
{OVER (window_spec) | OVER window_name}
- 第一种形式,窗口的规范说明直接定义在 over() 语句中。
- 第二种形式,窗口的规范说明是由引用的一个命名窗口提供;命名窗口的定义在查询语句的其它地方。
对于 OVER (window_spec)
语法,窗口规范说明包含以下可选部分:
# window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]
如果 over() 是空的,窗口将包含整个查询结果集,窗口函数就使用结果集中的所有行计算出一个结果,并分配给每一行。否则,小括号内的语句将确定哪些记录行被用于窗口计算,以及如何分区和排序。
- window_name :窗口的名称,由查询语句中其他地方的 window 语句定义。如果 window_name 单独出现在 over 语句中,它将完全定义窗口;如果存在 partition, order, frame 语句&