• 售前

  • 售后

热门帖子
入门百科

postgresql rank() over, dense_rank(), row_number()用法区别

[复制链接]
爱喝水的姑娘 显示全部楼层 发表于 2021-10-26 12:40:23 |阅读模式 打印 上一主题 下一主题
如下弟子表student,弟子表中有姓名、分数、课程编号,必要按照课程对弟子的结果进行排序
  1. select * from jinbo.student;
  2. id | name | score | course
  3. ----+-------+-------+--------
  4. 5 | elic | 70 |  1
  5. 4 | dock | 100 |  1
  6. 3 | cark | 80 |  1
  7. 2 | bob | 90 |  1
  8. 1 | alice | 60 |  1
  9. 10 | jacky | 80 |  2
  10. 9 | iris | 80 |  2
  11. 8 | hill | 60 |  1
  12. 7 | grace | 50 |  2
  13. 6 | frank | 70 |  2
  14. 6 | test |  |  2
  15. (11 rows)
复制代码
1、rank over () 可以把结果雷同的两名是并列,如下course = 2 的效果rank值为:1 2 2 4 5
  1. select name,
  2.   score,
  3.   course,
  4.   rank() over(partition by course order by score desc) as rank
  5. from jinbo.student;
  6. name | score | course | rank
  7. -------+-------+--------+------
  8. dock | 100 |  1 | 1
  9. bob | 90 |  1 | 2
  10. cark | 80 |  1 | 3
  11. elic | 70 |  1 | 4
  12. hill | 60 |  1 | 5
  13. alice | 60 |  1 | 5
  14. test |  |  2 | 1
  15. iris | 80 |  2 | 2
  16. jacky | 80 |  2 | 2
  17. frank | 70 |  2 | 4
  18. grace | 50 |  2 | 5
  19. (11 rows)
复制代码
2、dense_rank()和rank over()很相似,可以把弟子结果并列不中断顺序排名,如下course = 2 的效果rank值为:1 2 2 3 4
  1. select name,score,
  2.   course,
  3.   dense_rank() over(partition by course order by score desc) as rank
  4. from jinbo.student;
  5. name | score | course | rank
  6. -------+-------+--------+------
  7. dock | 100 |  1 | 1
  8. bob | 90 |  1 | 2
  9. cark | 80 |  1 | 3
  10. elic | 70 |  1 | 4
  11. hill | 60 |  1 | 5
  12. alice | 60 |  1 | 5
  13. test |  |  2 | 1
  14. iris | 80 |  2 | 2
  15. jacky | 80 |  2 | 2
  16. frank | 70 |  2 | 3
  17. grace | 50 |  2 | 4
  18. (11 rows)
复制代码
3、row_number 可以把雷同结果的一连排名,如下 course = 2 的效果rank值为:1 2 3 4 5
  1. select name,score,
  2.   course,
  3.   row_number() over(partition by course order by score desc) as rank
  4. from jinbo.student;
  5. name | score | course | rank
  6. -------+-------+--------+------
  7. dock | 100 |  1 | 1
  8. bob | 90 |  1 | 2
  9. cark | 80 |  1 | 3
  10. elic | 70 |  1 | 4
  11. hill | 60 |  1 | 5
  12. alice | 60 |  1 | 6
  13. test |  |  2 | 1
  14. iris | 80 |  2 | 2
  15. jacky | 80 |  2 | 3
  16. frank | 70 |  2 | 4
  17. grace | 50 |  2 | 5
  18. (11 rows)
复制代码
使用rank over()的时间,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序效果,可以如下:
  1. rank over(partition by course order by score desc nulls last)
复制代码
4、总结

partition by 用于效果集分组,如果没有指定,会把整个效果集作为一个分组
rank 、dense_rank 、row_numer 都是差别方式的效果集组内排序,一样寻常都联合over 字句出现,over 字句里 会有 partition by、order by、last、first 的恣意组合,如下:
  1. rank() over(partition by a,b order by a, order by b desc);
  2. rank() over(partition by a order by b nulls first)
  3. rank() over(partition by a order by b nulls last)
复制代码
增补:Oracle大概PostgreSQL的row_number over 排名语法
PostgreSQL 和Oracle 都提供了 row_number() over() 如许的语句来进行对应的字段排名,非常方便。MySQL却没有提供如许的语法。
这次我提供的表布局如下,
  1.     Table "ytt.t1"
  2. Column |   Type   | Modifiers
  3. --------+-----------------------+-----------
  4. i_name | character varying(10) | not null
  5. rank | integer    | not null
复制代码
我模拟了20条数据来做演示。
  1. t_girl=# select * from t1 order by i_name;        
  2. i_name | rank
  3. ---------+------
  4. Charlie | 12
  5. Charlie | 12
  6. Charlie | 13
  7. Charlie | 10
  8. Charlie | 11
  9. Lily  | 6
  10. Lily  | 7
  11. Lily  | 7
  12. Lily  | 6
  13. Lily  | 5
  14. Lily | 7
  15. Lily | 4
  16. Lucy | 1
  17. Lucy | 2
  18. Lucy | 2
  19. Ytt  | 14
  20. Ytt  | 15
  21. Ytt  | 14
  22. Ytt  | 14
  23. Ytt  | 15
  24. (20 rows)
复制代码
在PostgreSQL下,我们来对如许的排名函数进行三种差别的实验方式1:
第一种:

完整的带有排名字段以及排序。
  1. t_girl=# select i_name,rank, row_number() over(partition by i_name order by rank desc) as rank_number from t1;  
  2. i_name | rank | rank_number
  3. ---------+------+-------------
  4. Charlie  | 13 |   1
  5. Charlie | 12 |   2
  6. Charlie | 12 |   3
  7. Charlie | 11 |   4
  8. Charlie | 10 |   5
  9. Lily  | 7 |   1
  10. Lily  | 7 |   2
  11. Lily  | 7 |   3
  12. Lily  | 6 |   4
  13. Lily  | 6 |   5
  14. Lily  | 5 |   6
  15. Lily  | 4 |   7
  16. Lucy | 2 |   1
  17. Lucy | 2 |   2
  18. Lucy | 1 |   3
  19. Ytt  | 15 |   1
  20. Ytt  | 15 |   2
  21. Ytt  | 14 |   3
  22. Ytt  | 14 |   4
  23. Ytt  | 14 |   5
  24. (20 rows)
复制代码
第二种:

带有完整的排名字段但是没有排序。
  1. t_girl=# select i_name,rank, row_number() over(partition by i_name ) as rank_number from t1;
  2. i_name | rank | rank_number
  3. ---------+------+-------------
  4. Charlie  | 12 |   1
  5. Charlie | 12 |   2
  6. Charlie | 13 |   3
  7. Charlie | 10 |   4
  8. Charlie | 11 |   5
  9. Lily  | 6 |   1
  10. Lily  | 7 |   2
  11. Lily  | 7 |   3
  12. Lily  | 6 |   4
  13. Lily  | 5 |   5
  14. Lily  | 7 |   6
  15. Lily  | 4 |   7
  16. Lucy | 1 |   1
  17. Lucy | 2 |   2
  18. Lucy | 2 |   3
  19. Ytt  | 14 |   1
  20. Ytt  | 15 |   2
  21. Ytt  | 14 |   3
  22. Ytt  | 14 |   4
  23. Ytt  | 15 |   5
  24. (20 rows)
复制代码
第三种:

没有任何排名字段,也没有任何排序字段。
  1. t_girl=# select i_name,rank, row_number() over() as rank_number from t1;
  2. i_name | rank | rank_number
  3. ---------+------+-------------
  4. Lily  | 7 |   1
  5. Lucy | 2 |   2
  6. Ytt  | 14 |   3
  7. Ytt  | 14 |   4
  8. Charlie | 12 |   5
  9. Charlie | 13 |   6
  10. Lily  | 7 |   7
  11. Lily  | 4 |   8
  12. Ytt  | 14 |   9
  13. Lily  | 6 |   10
  14. Lucy | 1 |   11
  15. Lily  | 7 |   12
  16. Ytt  | 15 |   13
  17. Lily  | 6 |   14
  18. Charlie | 11 |   15
  19. Charlie | 12 |   16
  20. Lucy | 2 |   17
  21. Charlie | 10 |   18
  22. Lily  | 5 |   19
  23. Ytt  | 15 |   20
  24. (20 rows)
复制代码
以上为个人履历,盼望能给大家一个参考,也盼望大家多多支持草根技能分享。如有错误或未思量完全的地方,望不吝赐教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作