MySQL5.7版本使用变量实现窗口函数
在MySQL5.7版本中使用变量来实现8.0和Hive中有的窗口函数
row_number 和 dense_rank 和 rank的区别
1)row_number有相同的数据,会递增
2)dense_rank有相同的数据时,可以出现并列的情况,并且下一个排名只增加1位,即 1 1 2 的排名。
3)rank有相同的数据时,可以出现并列的情况,并且下一个排名会增加相同的个数,即 1 1 3 的排名。
dense:密集的
row_number
1.score表数据如下:
2.代码实现:
set @rownum := 0;
set @class :='';
select class,english,
@rownum := if(@class = class,@rownum := @rownum+1,1) as rownumber,
@class := class as last_class
from score
order by class,english desc;
3.结果:
dense_rank
1.score表数据如下:
2.代码实现:
set @dense_rank := 0;
set @english := 0;
set @class :='';
select class,english,
@dense_rank := if(@class = class,if(@english = english,@dense_rank := @dense_rank,@dense_rank := @dense_rank+1),1) as dense_rank,
@english := english as last_english,
@class := class as last_class
from score
order by class,english desc;
3.结果:
rank
1.score表数据如下:
2.代码实现:
set @rank := 0;
set @num :=1;
set @english := 0;
set @class :='';
select class,english,
@rank := if(@class = class,if(@english = english,@rank := @rank,@rank := @rank + @num),1) as rank,
@num := if(@class = class,if(@english = english,@num := @num+1,@num := 1),1)as num,
@english := english as last_english,
@class := class as last_class
from score
order by class,english desc;
3.实现结果: