2021-01-06-窗口函数-排序

1、rank() over()--1，2，2，4

select name,
        subject,
        score,
        rank()over(partition by subject order by  score desc) rank  
from student_score;

注意:使用rank()over()的时候，空值是最大的，如果排序字段为null,可能造成null字段排在
最前面，影响排序结果，可以采用如下写法进行规避

select name,
        subject,
        score,
        rank()over(partition by subject order by  score desc nulls last) rank  
from student_score;

2、dense_rank() over()--1，2，2，3

select name,
        subject,
        score,
        dense_rank()over(partition by subject order by  score desc) rank  
from student_score;

3、row_number()over()--1,2,3,4

select name,
        subject,
        score,
        row_number()over(partition by subject order by  score desc) rank  
from student_score;