mysql8.0新特性 之 窗口函数 使用示例及详解

什么是窗口函数

MySQL从8.0开始支持窗口函数，这个功能在大多数据库中早已支持，有的也叫分析函数。
窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。简单的说窗口函数就是对于查询的每一行，都使用与该行相关的行进行计算。

窗口函数和普通聚合函数很容易混淆，二者区别如下：
    - 聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。
    - 聚合函数也可以用于窗口函数中。

窗口函数功能

名称	描述
ROW_NUMBER()	为分区中的每一行分配一个顺序整数【没有重复值的排序（记录相等也是不重复的），可以进行分页使用】
RANK()	与DENSE_RANK()函数相似，不同之处在于当两行或更多行具有相同的等级时，等级值序列中存在间隙【跳跃排序】
DENSE_RANK()	根据该ORDER BY子句为分区中的每一行分配一个等级。它将相同的等级分配给具有相等值的行。如果两行或更多行具有相同的排名，则排名值序列中将没有间隙【连续排序】
PERCENT_RANK()	计算分区或结果集中行的百分数等级
CUME_DIST()	计算一组值中一个值的累积分布
LAG()	返回分区中当前行之前的第N行的值。如果不存在前一行，则返回NULL
LEAD()	返回分区中当前行之后的第N行的值。如果不存在后续行，则返回NULL
FIRST_VALUE()	返回相对于窗口框架第一行的指定表达式的值
LAST_VALUE	返回相对于窗口框架中最后一行的指定表达式的值
NTH_VALUE()	从窗口框架的第N行返回参数的值
NTILE()	将每个窗口分区的行分配到指定数量的排名组中

将上述函数按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

• 序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
• 分布函数：PERCENT_RANK()、CUME_DIST()
• 前后函数：LAG()、LEAD()
• 头尾函数：FIRST_VALUE()、LAST_VALUE()
• 其他函数：NTH_VALUE()、NTILE()

函数示例

ROW_NUMBER()

语法格式：row_number() over(partition by 分组列 order by 排序列 desc);
在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行。

创建数据表：
create table TEST_ROW_NUMBER_OVER(
       id varchar(10) not null,
       name varchar(10) null,
       age varchar(10) null,
       salary int null
);
select * from TEST_ROW_NUMBER_OVER t;
 
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(5,'d2',17,1800);

1. 对查询结果进行排序

select id,name,age,salary,row_number()over(order by salary DESC) rn
from `TEST_ROW_NUMBER_