postgresql rank() over, dense_rank(), row_number()用法区别
如下学生表student，学生表中有姓名、分数、课程编号，需要按照课程对学生的成绩进行排序
select*fromjinbo.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 (11rows)
1、rankover()可以把成绩相同的两名是并列，如下course=2的结果rank值为：12245
selectname, score, course, rank()over(partitionbycourseorderbyscoredesc)asrank fromjinbo.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 (11rows)
2、dense_rank()和rankover()很相似，可以把学生成绩并列不间断顺序排名，如下course=2的结果rank值为：12234
selectname,score, course, dense_rank()over(partitionbycourseorderbyscoredesc)asrank fromjinbo.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 (11rows)
3、row_number可以把相同成绩的连续排名，如下course=2的结果rank值为：12345
selectname,score, course, row_number()over(partitionbycourseorderbyscoredesc)asrank fromjinbo.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 (11rows)
使用rankover()的时候，空值是最大的，如果排序字段为null,可能造成null字段排在最前面，影响排序结果，可以如下：
rankover(partitionbycourseorderbyscoredescnullslast)
4、总结
partitionby用于结果集分组，如果没有指定，会把整个结果集作为一个分组
rank、dense_rank、row_numer都是不同方式的结果集组内排序，一般都结合over字句出现，over字句里会有partitionby、orderby、last、first的任意组合，如下：
rank()over(partitionbya,borderbya,orderbybdesc); rank()over(partitionbyaorderbybnullsfirst) rank()over(partitionbyaorderbybnullslast)
补充：Oracle或者PostgreSQL的row_numberover排名语法
PostgreSQL和Oracle都提供了row_number()over()这样的语句来进行对应的字段排名，很是方便。MySQL却没有提供这样的语法。
这次我提供的表结构如下，
Table"ytt.t1" Column|Type|Modifiers --------+-----------------------+----------- i_name|charactervarying(10)|notnull rank|integer|notnull
我模拟了20条数据来做演示。
t_girl=#select*fromt1orderbyi_name; i_name|rank ---------+------ Charlie|12 Charlie|12 Charlie|13 Charlie|10 Charlie|11 Lily|6 Lily|7 Lily|7 Lily|6 Lily|5 Lily|7 Lily|4 Lucy|1 Lucy|2 Lucy|2 Ytt|14 Ytt|15 Ytt|14 Ytt|14 Ytt|15 (20rows)
在PostgreSQL下，我们来对这样的排名函数进行三种不同的执行方式1：
第一种：
完整的带有排名字段以及排序。
t_girl=#selecti_name,rank,row_number()over(partitionbyi_nameorderbyrankdesc)asrank_numberfromt1; i_name|rank|rank_number ---------+------+------------- Charlie|13|1 Charlie|12|2 Charlie|12|3 Charlie|11|4 Charlie|10|5 Lily|7|1 Lily|7|2 Lily|7|3 Lily|6|4 Lily|6|5 Lily|5|6 Lily|4|7 Lucy|2|1 Lucy|2|2 Lucy|1|3 Ytt|15|1 Ytt|15|2 Ytt|14|3 Ytt|14|4 Ytt|14|5 (20rows)
第二种：
带有完整的排名字段但是没有排序。
t_girl=#selecti_name,rank,row_number()over(partitionbyi_name)asrank_numberfromt1; i_name|rank|rank_number ---------+------+------------- Charlie|12|1 Charlie|12|2 Charlie|13|3 Charlie|10|4 Charlie|11|5 Lily|6|1 Lily|7|2 Lily|7|3 Lily|6|4 Lily|5|5 Lily|7|6 Lily|4|7 Lucy|1|1 Lucy|2|2 Lucy|2|3 Ytt|14|1 Ytt|15|2 Ytt|14|3 Ytt|14|4 Ytt|15|5 (20rows)
第三种：
没有任何排名字段，也没有任何排序字段。
t_girl=#selecti_name,rank,row_number()over()asrank_numberfromt1; i_name|rank|rank_number ---------+------+------------- Lily|7|1 Lucy|2|2 Ytt|14|3 Ytt|14|4 Charlie|12|5 Charlie|13|6 Lily|7|7 Lily|4|8 Ytt|14|9 Lily|6|10 Lucy|1|11 Lily|7|12 Ytt|15|13 Lily|6|14 Charlie|11|15 Charlie|12|16 Lucy|2|17 Charlie|10|18 Lily|5|19 Ytt|15|20 (20rows)
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。