MySQL8窗口函数的语法与示例详解

一、窗口函数是什么？

窗口函数是一类特殊的函数，配合 over 关键字来使用。窗口函数顾名思义，只在满足条件的窗口内执行，对象是窗口中的每一条记录。

窗口可以理解为记录集合，其中包含数条满足约束条件的记录。窗口又分为静态窗口与动态窗口，静态窗口是指窗口中的记录是按照约束条件确定好的，不会改变的，即窗口大小是固定的；而动态窗口中的记录是会根据当前的记录以及约束条件进行动态变化的，即窗口大小是在变化的。

二、窗口函数的简单应用

1.建表

create table store
(
storeId varchar(255),
storeName varchar(255),
productId varchar(255),
productName varchar(255),
productPrice float,
productNum int
)

表格数据可自取
链接：https://pan.baidu.com/s/1GkJUghCVkUR8CfvJGHiYgg
提取码：yuan
该表是某公司旗下各店铺的书籍销售情况

2. 窗口函数的基本语法

基本语法：序号函数   over( partition by [ 分组列1，分组列2，··· ]   order by [ 排序列1，排序列2，··· ] asc|desc）

over关键字: 用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4种语法来设置窗口。

• partition by [ 分组列1，分组列2，···] :该子句用来指定窗口按照哪些字段（一个或多个）进行分组，
• order by [ 排序列1，排序列2，···] asc|desc:该子句用来指定窗口中的记录按照哪些字段进行排序，当其后存在多个字段时，则优先按照“排序列1”进行排序，在此基础上再按照“排序列2”进行排序。
• frame:该子句是用来指定窗口中分区的，使用场景较少；详情可参考新特性解读 | MySQL 8.0 窗口函数框架用法
• window:当over之后的子句太长时，可以使用window关键字来给当前窗口起别名。

3. 序号函数

（1）row_number()

需求：将每个店铺的销售量进行降序排列，

我们使用group by子句进行排序时，结果只会保留每一组的第一条数据。如下所示：

select * from store 
GROUP BY storeId
ORDER BY productNum desc

---

使用窗口函数实现组内排序，结果会保留组内的每一条数据，轻松实现组内排序

select *,row_number() over w as sorted
from store
window  w  as (partition by storeId ORDER BY productNum desc)--该当前窗口起别名

（2）rank()与dense_rank()

这两个函数与row_number的区别在于，在进行排序时对相同的值的处理不同。

rank()函数

select *,rank() over w as sorted
from store
window  w  as (partition by storeId ORDER BY productNum desc)--该当前窗口起别名

dense_rank()

select *,dense_rank() over w as sorted
from store
window  w  as (partition by storeId ORDER BY productNum desc)

总结：rank（）会产生序号相同的记录，同时可能产生序号间隙；而dense_rank（）也会产生序号相同的记录，但不会产生序号间隙。

4. 分布函数

percent_rank()

用法：和之前的RANK()函数相关，每行按照如下公式进行计算：(rank - 1) / (rows - 1)。 其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

select *,rank() over w as row_rank,percent_rank() over w as percent_row_rank
from store
window  w as (partition by storeId ORDER BY productNum desc)--该当前窗口起别名

从结果看出，percent_row_rank列按照公式(rank - 1) / (rows - 1)带入rank值（row_rank列）和rows值（storeId为1,2,3的窗口都只包含4条记录，即rows= 4）。


cume_dist()

用法：窗口内小于等于当前行rank值的行数/窗口内总行数。

select *,rank() over w as row_rank,cume_dist() over w as cume_rank
from store
window  w as (partition by storeId ORDER BY productNum desc)  --该当前窗口起别名

从结果看出，cume_rank列按照公式：分组内小于等于当前rank值的行数/分组内总行数。读者可自行代入数据验证。

5. 前后函数

用途：lag(字段1,n) 返回窗口中位于当前行之前的第n行记录的字段1对应的值；lead(字段2,n)返回当前行之后第n行(lead)记录的字段2对应的值。

select store.*,
lag(productName,2) over w as lag_product,
lead(productName,2) over w as lead_product ,
row_number() over() as `行号` 
from store
window  w as (partition by storeId ORDER BY productNum desc)

从结果中可以看出共有三个窗口，每个窗口中有四条数据。以第一个窗口为例，第一条数据之前没有数据，所以lag_product属性为NULL，第二条数据之前只有一条数据，所以lag_product属性也为NULL；第一条数据之后的第2条记录为行号等于3的数据该条数据的productName属性值为“操作系统”，所以第一条数据的lead_product属性值为“操作系统”。同理，第二条数据的lead_product属性值为“数据库系统概论”。

6. 头尾函数

头尾函数——first_val(字段)/last_val(字段)。
用途：得到窗口中的第一条/最后一条数据指定字段的值。

select store.*,
first_value(productName) over w as first_productName,
last_value(productName) over w as last_productName
from store
window  w as ( partition by storeId order by productNum desc rows BETWEEN unbounded preceding and unbounded following)
--使用frame子句来指定分区的边界

从结果中可以看出first_value（）可以直接取到当前窗口的第一条数据，last_value（）可以直接取到当前分区的最后一条记录。
然而当不使用frame子句指定分区边界时，last_value（）返回的往往是当前行的数据（当前行即为分区中的最后一行），读者可自行写代码验证。

7.其他函数

nth_value(expr,n)
用法：返回窗口中第N条数据对应的expr的值，expr可以是表达式，也可以是列名。

select store.*,
nth_value(productName,2) over w as second_productName,
nth_value(productName,3) over w as third_productName
from store
window  w as ( partition by storeId order by productNum desc rows BETWEEN unbounded preceding and unbounded following)
--使用frame子句来指定分区的边界

从结果中可以看出，nth_value(expr,n)能够获取获取当前窗口的第你条数据的expr属性对应的值。
但是当我们没有使用frame子句指定当前窗口的边界时，对于每一条记录所对应的窗口的边界是不一样的，这就会影响nth_value的返回值。笔者在这里给出一个示例，其他情况读者可自行验证。

select store.*,nth_value(productName,2) over w as second_productName,nth_value(productName,3) over w as third_productName
from store
window  w as ( partition by storeId order by productNum desc)
--未使用frame子句

结果如下。此函数的应用场景也较多，例如找出某一班级学生的英语第二名，第三名等。

ntile(n)
用法：将窗口中的有序数据分为n个，记录桶号。

select store.*,ntile(2) over w as block
from store
window  w as ( partition by storeId order by productNum desc)

此函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到N个并行的进程分别计算，此时就可以用NTILE(N)对数据进行分组，由于记录数不一定被N整除，所以数据不一定完全平均，然后将不同桶号的数据再分配。

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

select store.*,
sum(productNum) over w as sum_col ,
count(*) over w as count_col, 
avg(productNum) over w as avg_col,
max(productNum) over w as max_col
from store
window  w as ( partition by storeId order by productNum desc)

总结

窗口函数的功能异常强大，应用场景也纷繁复杂，本文只是简单的介绍了一下各类窗口函数的语法与示例。读者们应该在此基础上结合实际业务场景取理解每一个函数的含义与应用。