mysql|Mysql窗口函数-自用窗口函数|开窗函数|mysql

mysql版本8+ mysql窗口函数语法：select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名)
按照功能划分，可以把MySQL支持的窗口函数分为如下几类：
序号函数： row_number()
rank()
dense_rank()
分布函数： percent_rank():(rank - 1) / (rows - 1),其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
cume_dist():分组内小于等于当前rank值的行数/分组内总行数
前后函数： lag(expr,n) / lead(expr,n) : 分区中位于当前行前n行（lead）/后n行(lag)的记录值。
头尾函数： first_val(expr) / last_val(expr):查询截止到当前行，分区中的第一个/最后一个指定参数的值。
其他函数： nth_value(expr,n) 查询截止到当前行，分区中的第n个expr值/ nfile():将分区中的有序数据分为n个桶，记录桶号
表结构：

文章图片

1、按班级排名，rank()、dense_rank()、row_number()

select *,
rank() over (partition by class_id order by score desc) as ranking
from english;

文章图片

select *,
dense_rank() over (partition by class_id order by score desc) as ranking
from english;

文章图片

select *,
row_number() over (partition by class_id order by score desc) as ranking
from english;

文章图片

2、按年级排名

select *,
rank() over (order by score desc) as ranking
from english;

文章图片

select *,
dense_rank() over (order by score desc) as ranking
from english;

文章图片

select *,
row_number() over (order by score desc) as ranking
from english;

文章图片

3、查每班成绩最好的两个人，并显示年级排名

select * from
(
select *,
row_number() over (partition by class_id order by score desc) as class_ranking,
dense_rank() over (order by score desc) as grade_ranking
from english
) as sub_query
where class_ranking <= 2
order by class_id,class_ranking

文章图片

4、其他函数用法示例

select *,
percent_rank() over (partition by class_id order by score desc) as percent_ranking,
lag(score,1) over (partition by class_id order by score desc) as lag_ranking,
lead(score,1) over (partition by class_id order by score desc) as lead_ranking,
first_value(score) over (partition by class_id order by score desc) as first_score,
last_value(score) over (partition by class_id order by score desc) as last_score,
nth_value(score,2) over (partition by class_id order by score desc) as nth_score,
ntile(3) over (partition by class_id order by score desc) as ntile_score
from english;

另一种写法：

select *
from
(
select
student_no,class_id,score,
percent_rank() over w as percent_ranking,
lag(score,1) over w as lag_ranking,
lead(score,1) over w as lead_ranking,
first_value(score) over w as first_score,
last_value(score) over w as last_score,
nth_value(score,2) over w as nth_score,
ntile(3) over w as ntile_score
from english
WINDOW w AS (partition by class_id order by score desc)
) sub_query

文章图片

疑问区：
1、select 和 where 哪个先执行？
select *,
dense_rank() over (partition by class_id order by score desc) as ranking
【mysql|Mysql窗口函数-自用】from english where class_id > 1;