Oracle中row_number()、rank()、dense_rank() 的区别以及partition by_Zero .的博客-程序员ITS203_dense_rank partition by

技术标签: 数据库  

本章来讲解一下经常使用的排序函数,可以用来进行统计成绩排名、售卖情况排名等。

目录

1.row_number函数、rank函数、dense_rank函数的区别和用法

1.row_number函数

2.rank函数

3.dense_rank函数

2.partition by函数的用法

1、over函数的写法:

2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与row_number()的用法

2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与dense_rank()的用法


 

1.row_number函数、rank函数、dense_rank函数的区别和用法

1.row_number函数

row_number的用途非常广泛,排序最好用它,它会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

2.rank函数

rank函数用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。

3.dense_rank函数

dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第三名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。

借助实例能更直观地理解:

假设现在有一张学生表student,学生表中有姓名、分数、课程编号。

select * from student;

 现在需要按照课程对学生的成绩进行排序:

--row_number() 顺序排序
select name,course,row_number() over(partition by course order by score desc) rank from student;

--rank() 跳跃排序,如果有两个第一级别时,接下来是第三级别
select name,course,rank() over(partition by course order by score desc) rank from student;

--dense_rank() 连续排序,如果有两个第一级别时,接下来是第二级别 
select name,course,dense_rank() over(partition by course order by score desc) rank from student;

取得每门课程的第一名:

 

--每门课程第一名只取一个: 
select * from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有: 
select * from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有:
select * from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank=1;

 

  附:每门课程第一名取所有的其他方法(使用group by 而不是partition by):

 

select s.* from student s
  inner join(select course,max(score) as score from student group by course) c
  on s.course=c.course and s.score=c.score; 
--或者使用using关键字简化连接
select * from student s
  inner join(select course,max(score) as score from student group by course) c
  using(course,score);

 

关于Parttion by:

  Parttion by关键字是Oracle中分析性函数的一部分,用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而Group by是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。

  TIPS:

  使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果。

  可以这样: rank over(partition by course order by score desc nulls last)

总结:

  在使用排名函数的时候需要注意以下三点:

  1、排名函数必须有 OVER 子句。

  2、排名函数必须有包含 ORDER BY 的 OVER 子句。

  3、分组内从1开始排序。

2.partition by函数的用法

group by是分组函数,partition by是分区函数(像sum()等是聚合函数),注意区分。

1、over函数的写法:

over(partition by cno order by degree )

先对cno 中相同的进行分区,在cno 中相同的情况下对degree 进行排序

2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与row_number()的用法

例:查询每名课程的第一名的成绩

(1)使用rank()

SELECT	* 
FROM	(select sno,cno,degree,
      	rank()over(partition by cno order by degree desc) mm 
      	from score) 
where mm = 1;

得到结果:
在这里插入图片描述

(2)使用row_number()

SELECT * 
FROM   (select sno,cno,degree,
       row_number()over(partition by cno order by degree desc) mm 
       from score) 
where mm = 1;

得到结果:
在这里插入图片描述

(3)rank()与row_number()的区别

由以上的例子得出,在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果。

2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与dense_rank()的用法

例:查询课程号为‘3-245’的成绩与排名

(1) 使用rank()

SELECT * 
FROM   (select sno,cno,degree,
       rank()over(partition by cno order by degree desc) mm 
       from score) 
where cno = '3-245'

得到结果:
在这里插入图片描述

(2) 使用dense_rank()

SELECT * 
FROM   (select sno,cno,degree,
       dense_rank()over(partition by cno order by degree desc) mm 
       from score) 
where cno = '3-245'

得到结果:
在这里插入图片描述

(3)rank()与dense_rank()的区别

由以上的例子得出,rank()和dense_rank()都可以将并列第一名的都查找出来;但rank()是跳跃排序,有两个第一名时接下来是第三名;而dense_rank()是非跳跃排序,有两个第一名时接下来是第二名。

参考:Oracle中row_number()、rank()、dense_rank() 的区别

参考:分区函数Partition By的用法

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/admin123404/article/details/109144352

智能推荐

bean的加载(八)创建bean的实例_你猜我猜不猜你猜不猜的博客-程序员ITS203_autowireconstructor

下面我们一步步分析doCreatBean方法,首先从createBeanInstance开始。protected BeanWrapper createBeanInstance(String beanName, RootBeanDefinition mbd, Object[] args) { // Make sure bean class is actually resolved at th

D3D9学习笔记(八) .x file_leonwei的博客-程序员ITS203_d3d9 使用x文件

8.1 loadUse the DirectXFileCreate function to create an IDirectXFile object.If templates are present in the DirectX file that you will load, use theIDirectXFile::RegisterTemplates method to

android底部控件会被顶上去,Android中底部菜单被输入法顶上去的解决方案_weixin_39982580的博客-程序员ITS203

安卓手机输入法弹出,消失会触发window.onresize事件,我们一般的解决方法是获取焦点,底部隐藏,失去焦点,底部菜单出现,但是,有些人会点击这个按钮收起键牌那么,这个时候你的失去焦点无效,还有一种方法呢,是把position:fixed;改成position:absoult;这样底部菜单就不会顶上去,但是这种方法,经过我的实验,还是会被输入法顶上去,这两种方法都不要完全解决问题,还有一种是...

三年QA工作的感想与体会_yaoxy的博客-程序员ITS203_qa3年工作规划

工作3年了,我总结一下我自己在QA这行了3年的经历,也许我说的不是对的,也许我的做法还是有很多问题,但是有点是肯定的,那就是我曾经经历过这些,希望能给后来者一些启发。  1.        进入QA行业  其实进入QA这个行业,真的非常的偶然,我是<a onclick="function onclick(){tagshow(event, %C8%ED%BC%FE%B2%E2%CA

解决 笔记本 键盘进水 问题_looklovers的博客-程序员ITS203

    前几日闲来无事就想给自己的键盘贴一张膜,自己的笔记本是联想F40A的从买到现在2年多了,期间没有出过任何问题,散热简直是刚刚地,自己喜欢DIY把他分家至少二十多次,几乎每两个月就想拆开捣鼓捣鼓。加膜之前打算来个彻底清理,还好,那个按键是X构架,拆起来只要小心点是不会出现问题的,只是安装的时候把F2 F3装反了,呵呵    自己的电脑拆了太多次,感觉无聊就想起朋友去年买的同样是联想的C4

基于 Cluster API 管理集群_shida_csdn的博客-程序员ITS203_cluster api

前言Kubernetes Cluster API 提供了一种声明式的集群创建、配置、管理模式,能够对集群的全生命周期进行有效管理(https://cluster-api.sigs.k8s.io/introduction.html)本文提供简单试用记录。准备 Kubernetes 集群(管理集群)节点操作系统IPDocker 版本kubernetes 版本网络nod...

随便推点

练习:(bb)blog_weixin_39660404的博客-程序员ITS203

猪猪的Blog &lt;div class="avatar"&gt; &lt;img src="zhuzhu.png" alt=""&gt;&lt;/div&gt;&lt;div class="blog-title"&gt; &lt;h3&gt;猪猪的Blog&lt;/h3&gt;&lt;/div&gt;&lt;div clas...

人狼羊菜过河问题_Janus_V的博客-程序员ITS203

人狼羊菜过河问题:PTA原题:输入:MWGC -&gt; ........ &lt;- MWGC输出:MWGC -&gt; .....W.C &lt;- M.G.MW.C -&gt; ..G....C &lt;- MWG.M.GC -&gt; .W....G. &lt;- MW.CM.G. -&gt; .W.C.... &lt;- MWGCMWGC -&gt; .....W.C &lt;- M.G.MW.C -&gt; ..G..W.. &lt;- M.GCMWG.

MySQL驱动对应Server版本、JDK版本_weixin_33743880的博客-程序员ITS203

为什么80%的码农都做不了架构师?&gt;&gt;&gt; ...

数据处理OLTP与OLAP的概念_小小架构师的博客-程序员ITS203

数据处理OLTP与OLAP的概念在系统软件出现的初期,数据通常被保存在单个文件中进行管理,但随着业务的不断发展与系统数据处理逻辑的不断复杂,关系型数据库成为了主流的数据管理系统,例如Oracle、Mysql等,随后又出现了各式各样数据管理的解决方案,例如Elasticsearch、Redis、HBase等。然而,无论数据库管理系统如何更新与发展,涉及数据处理的核心术语与概念永远离不开这两个名词,即OLTP与OLAP。=======================================.

怎样合理的设置rpc超时时间_仰望星空的尘埃的博客-程序员ITS203

上面这张监控图,对于服务端的研发同学来说再熟悉不过了。在日常的系统维护中,『服务超时』应该属于监控报警最多的一类问题。尤其在微服务架构下,一次请求可能要经过一条很长的链路,跨多个服务调用后才能返回结果。当服务超时发生时,研发同学往往要抽丝剥茧般去分析自身系统的性能以及依赖服务的性能,这也是为什么服务超时相对于服务出错和服务调用量异常更难调查的原因。这篇文章将通过一个真实的线上事故,系统性地介绍下:在微服务架构下,该如何正确理解并设置RPC接口的超时时间,让大家在开发服务端接口时...

2009年3月份之前的会议信息_ftai08的博客-程序员ITS203

  摘要截稿日期:2009-2-15会议网站:http://www.iciem09.com    The conference includes four sessions:    Session 1: Intelligent Computation  - Computational Life Sc