版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
如下学生表student,学生表中有姓名、分数、课程编号,需要按照课程对学生的成绩进行排序
select * from jinbo.student;
id | name | score | course
----+-------+-------+--------
5 | elic | 70 | 1
4 | dock | 100 | 1
3 | cark | 80 | 1
2 | bob | 90 | 1
1 | alice | 60 | 1
10 | jacky | 80 | 2
9 | iris | 80 | 2
8 | hill | 60 | 1
7 | grace | 50 | 2
6 | frank | 70 | 2
6 | test | | 2
(11 rows)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
1、rank over () 可以把成绩相同的两名是并列,如下course = 2 的结果rank值为:1 2 2 4 5
select name,
score,
course,
rank() over(partition by course order by score desc) as rank
from jinbo.student;
name | score | course | rank
-------+-------+--------+------
dock | 100 | 1 | 1
bob | 90 | 1 | 2
cark | 80 | 1 | 3
elic | 70 | 1 | 4
hill | 60 | 1 | 5
alice | 60 | 1 | 5
test | | 2 | 1
iris | 80 | 2 | 2
jacky | 80 | 2 | 2
frank | 70 | 2 | 4
grace | 50 | 2 | 5
(11 rows)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
2、dense_rank()和rank over()很相似,可以把学生成绩并列不间断顺序排名,如下course = 2 的结果rank值为:1 2 2 3 4
select name,score,
course,
dense_rank() over(partition by course order by score desc) as rank
from jinbo.student;
name | score | course | rank
-------+-------+--------+------
dock | 100 | 1 | 1
bob | 90 | 1 | 2
cark | 80 | 1 | 3
elic | 70 | 1 | 4
hill | 60 | 1 | 5
alice | 60 | 1 | 5
test | | 2 | 1
iris | 80 | 2 | 2
jacky | 80 | 2 | 2
frank | 70 | 2 | 3
grace | 50 | 2 | 4
(11 rows)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
3、row_number 可以把相同成绩的连续排名,如下 course = 2 的结果rank值为:1 2 3 4 5
select name,score,
course,
row_number() over(partition by course order by score desc) as rank
from jinbo.student;
name | score | course | rank
-------+-------+--------+------
dock | 100 | 1 | 1
bob | 90 | 1 | 2
cark | 80 | 1 | 3
elic | 70 | 1 | 4
hill | 60 | 1 | 5
alice | 60 | 1 | 6
test | | 2 | 1
iris | 80 | 2 | 2
jacky | 80 | 2 | 3
frank | 70 | 2 | 4
grace | 50 | 2 | 5
(11 rows)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果,可以如下:
rank over(partition by course order by score desc nulls last)
- 1
4、总结
- partition by 用于结果集分组,如果没有指定,会把整个结果集作为一个分组
- rank 、dense_rank 、row_numer 都是不同方式的结果集组内排序,一般都结合over 字句出现,over 字句里 会有 partition by、order by、last、first 的任意组合,如下:
https://blog.csdn.net/u011944141/article/details/78927715
rank() over(partition by a,b order by a, order by b desc);
rank() over(partition by a order by b nulls first)
rank() over(partition by a order by b nulls last)
- 1
- 2
- 3