MYSQL8.0窗口函数

Mysql8.0的窗口函数其实就是一个分组窗口内部处理每条记录的函数，这个窗口也就是之前聚合操作的窗口。不同的是，聚合函数是把窗口关闭，给一个汇总的结果；而窗口函数是把窗口打开，给分组内每行记录求取对应的聚合函数值或者其他表达式的结果。比如在MYSQL的最新版本MYSQL8已经支持了排名函数RANK，DENSE_RANK和ROW_NUMBER。但是在就旧版本中还不支持这些函数，只能自己实现。实现方法主要用到了条件判断语句（CASE WHEN或IF）和添加临时变量。

一、窗口函数

1. 类别

• 排名函数：row_number()/rank()/dense_rank()
• 分布函数：percent_rank()/cume_dist()
• 前后函数：lag()/lead()
• 头尾函数：first_val()/last_val()
• 其他函数：nth_value()/nfile()

2. 窗口函数基本用法

• 窗口函数的基本用法如下：

window_function_name(expression) 
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    ) 

• 在这个语法中：

首先，指定窗口函数名称，后跟表达式。
其次，指定OVER具有三个可能元素的子句：分区定义，顺序定义和帧定义。
OVER子句后面的开括号和右括号是强制性的，即使没有表达式，例如：

window_function_name(expression) OVER() 

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

• partition_clause 句法

将partition_clause行分成块或分区。两个分区由分区边界分隔。窗口函数在分区内执行，并在跨越分区边界时重新初始化。

partition_clause语法如下所示：

-- 您可以在PARTITION BY子句中指定一个或多个表达式。多个表达式用逗号分隔。
PARTITION BY <expression>[{,<expression>...}] 

• order_by_clause 句法

order_by_clause语法如下：

ORDER BY <expression> [ASC|DESC], [{,<expression>...}] 

ORDER BY子句指定行在分区中的排序方式。可以在多个键上的分区内对数据进行排序，每个键由表达式指定。多个表达式也用逗号分隔。与PARTITION BY子句类似ORDER BY，所有窗口函数也支持子句。但是，仅对ORDER BY顺序敏感的窗口函数使用子句才有意义。

• frame_clause 句法

frame_clause:
frame_units frame_extent
frame_units:
{ROWS | RANGE}

frame_extent:
{frame_start | frame_between}
frame_between:
BETWEEN frame_start AND frame_end
frame_start, frame_end: {
CURRENT ROW
| UNBOUNDED PRECEDING
| UNBOUNDED FOLLOWING
| expr PRECEDING
| expr FOLLOWING
}

这里分为两块，一块是 frame_units （框架单元），另外一块是 frame_extent （框架内容）

• frame_units 框架单元有两个，一个是 rows，一个是 range。

• rows 后面跟的内容为指定的行号，而 range 不同，range 是指的行内容。

框架内容看起来挺多分类，其实就一句话来表达：为了定义分组内对应行记录的边界值来求取对应的计算结果。跟在 partition 和 order by 子句后面。帧是当前分区的子集。要定义子集，请使用frame子句，如下所示：

frame_unit {<frame_start>|<frame_between>} 

frame_unit有两种，分别是ROWS和RANGE：

• 由ROWS定义的frame是由开始和结束位置的行确定的
• 由RANGE定义的frame由在某个值区间的行确定

• 基于行：通常使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

• CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用
• UNBOUNDED PRECEDING 边界是分区中的第一行
• UNBOUNDED FOLLOWING 边界是分区中的最后一行
• expr PRECEDING 当前行之前的expr(数字或表达式)行
• expr FOLLOWING 当前行之后的expr(数字或表达式)行

比如，下面都是合法的范围：

• rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共三行记录。
• rows UNBOUNDED FOLLOWING 窗口范围是当前行到分区中的最后一行
• rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行，等同于不写。

• 基于范围：和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：INTERVAL 7 DAY PRECEDING。Linux中常见的最近1分钟、5分钟负载是一个典型的应用场景。
• 如果未frame_definition在OVER子句中指定，则MySQL默认使用以下框架：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

4. frame讲解

frame子语句比较难理解，这里先举例子说明：

drop table if exists t1;
create table t1(
    id int not null,
    r1 int not null ,
    r2 int not null
);

insert into t1 value (2,1,1);
insert into t1 value (2,2,20);
insert into t1 value (2,3,30);
insert into t1 value (2,4,40);
insert into t1 value (3,3,3);
insert into t1 value (3,2,2);
insert into t1 value (3,10,20);
insert into t1 value (3,30,20);
insert into t1 value (1,1,1);
insert into t1 value (1,2,3);
insert into t1 value (1,3,4);
insert into t1 value (1,10,10);
insert into t1 value (1,15,10);
insert into t1 value (2,15,2);
insert into t1 value (3,15,5);
insert into t1 value (1,9,100);

-- row preceding /following

-- 这里我们求 ID 为 1 的分组记录，基于聚合函数 SUM 来对分组内的行记录按照一定的条件求和。其中 OVER 子句用来定义分区以及相关条件，这里表示只获取分组内排序字段的当前行记录，也就是字段 r1 对应的记录，这是最简单的场景。
select id,r1,sum(r1) over(partition by id order by r1 asc range current row ) as wf_result from t1 where id = 1;

-- unbounded preceding 用来获取表 t1 按照字段 ID 来分组，并且对字段 r1 求和。由于都是以第一行，也就是 r1 = 1 为基础求和，也就是求取上一行和当前行相加的结果，基于第一行记录。
-- 这个例子中 r1 字段的第一行记录为 1，后面的所有求和都是基于第一行来累加的结果。
select id,r1,sum(r1) over(partition by id order by r1 asc rows unbounded preceding) as wf_result from t1 where id = 1;

select id,r1,sum(r1) over(partition by id order by r1 asc rows between unbounded preceding and unbounded following ) as wf_result from t1 where id = 1;
-- 以上用了 rows between 把边界局限在第一行和最后一行，这样每行的求和结果和不带边界一样，也就是下面查询：
select id,r1,sum(r1) over() as wf_result from t1 where id = 1 order by r1;

-- 带表达式的边界只是把无边界换成具体的行号。上面的查询表达的意思是基于分组内每行记录和它上一条记录求和，不累加。可以看到 wf_result 的具体值，
-- 25 对应的是 10 和 15 求和，19 对应的是 9 和 10 求和。
select id,r1,sum(r1) over(partition by id order by r1 asc rows 1 preceding) as wf_result from t1 where id = 1;
-- 以此类推，求每行和它上面两行的和：
select id,r1,sum(r1) over(partition by id order by r1 asc rows 2 preceding) as wf_result from t1 where id = 1;
-- 再来求每行的前两行和后面四行相加的结果(包括其本身)
select id,r1,sum(r1) over(partition by id order by r1 asc rows between 2 preceding and 4 following) as wf_result from t1 where id = 1;


-- range preceding / following
-- 求当前行值范围内的分组记录。这个没有 rows 好理解，rows 对应的是行号，range 对应的行值。看下面

-- 这个例子包含的关键词 range 1 preceding ，是个表达式条件，表示对于分组内每一行来讲：以字段 r1 当前行值减去1的结果为边界来求和。
-- 具体点就是：第一行，r1 的值为 1，那 1-1=0， 由于表 t1 里没有找到 r1 = 0 的结果，所以此时 wf_result = 1，也就是等于当前行值；
-- 对于第五行，由于 r1 对应的值为 10 , 10 – 1 = 9 ,表 t1 里 r1 = 9 是存在的，此时求和结果为 9 + 10 = 19。
select id,r1,sum(r1) over(partition by id order by r1 asc range 1 preceding) as wf_result from t1 where id = 1;


-- 这个例子 over 子句里指定一个边界范围，也就是对每行值减1和加1后对应的记录来求和。
-- 比如第一行：r1 = 1,1 -1 =0,1+1 =2，表 t1 没有 r1 = 0 的记录，但是有 r1 = 2 的记录，所以第一行的窗口求和结果为 3；
-- 再来看看 r1 = 10 的这行，10-1 = 9,10+1 = 11， 表 t1 里有 r1 = 9 的记录，没有 r1 = 11 的记录，所以这里的求和结果为 9+10=19.
select id,r1,sum(r1) over(partition by id order by r1 asc range between 1 preceding and 1 following) as wf_result from t1 where id = 1;

参考文章

6. 窗口函数列表

二、 排名函数

1. 准备数据

create table score(
  student_id varchar(10),
  course_id varchar(10),
  score decimal(18,1)
);
----------------------------------------------------------
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);

 select * from score; 

2. 区别RANK，DENSE_RANK和ROW_NUMBER

• RANK并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。
• DENSE_RANK并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。
• ROW_NUMBER连续排名，即使相同的值，依旧按照连续数字进行排名。

区别如图：

3. 分组排名

将数据分组后排名，区别如图：

4. 例子

• ROW_NUMBER()

-- 即使相同的值，依旧按照连续数字进行排名
SELECT course_id, score, ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC)  score_rank FROM score ;

• RANK()

-- RANK并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名
SELECT course_id, score, RANK() OVER (PARTITION BY course_id ORDER BY score DESC)  score_rank FROM score ;

• DENSE_RANK()

-- 并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名
SELECT course_id, score, DENSE_RANK() OVER (PARTITION BY course_id ORDER BY score DESC)  score_rank FROM score ;

三、其他窗口函数

1. CUME_DIST()=ROW_NUMBER() / total_rows

2. FIRST_VALUE()是一个窗口函数，允许您选择窗口框架，分区或结果集的第一行，作为默认数据列

3. LAST_VALUE() 函数返回有序行集中的最后一行

4. NTH_VALUE() 函数返回从有序结果集中的第N行获取值

5. PERCENT_RANK()函数计算分区或结果集中行的百分位数排名，返回一个从0到1的数字，表示当前行所在的分区内的相对排名，其计算公式为是 (rank() - 1) / (分区总行数 - 1)。

参考文章
参考文章