Oracle排名函数(Rank)实例详解

2024-03-21 11:26:59

--已知：两种排名方式(分区和不分区):使用和不使用partition

--两种计算方式(连续,不连续),对应函数：dense_rank,rank

·查询原始数据：学号,姓名,科目名,成绩

select * from t_score

S_ID	S_NAME	SUB_NAME	SCORE
1	张三	语文	80.00
2	李四	数学	80.00
1	张三	数学	0.00
2	李四	语文	50.00
3	张三丰	语文	10.00
3	张三丰	数学
3	张三丰	体育	120.00
4	杨过	JAVA	90.00
5	mike	c++	80.00
3	张三丰	Oracle	0.00
4	杨过	Oracle	77.00
2	李四	Oracle	77.00

·查询各学生科目为Oracle排名(简单排名)

select sc.s_id,sc.s_name,sub_name,sc.score,

rank() over (order by score desc) 名次

from t_score sc

where sub_name='Oracle'

S_ID	S_NAME	SUB_NAME	SCORE	名次
4	杨过	Oracle	77.00	1
2	李四	Oracle	77.00	1
3	张三丰	Oracle	0.00	3

对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名)

select sc.s_id,sc.s_name,sub_name,sc.score,

dense_rank() over (order by score desc) 名次

from t_score sc

where sub_name='Oracle'

S_ID	S_NAME	SUB_NAME	SCORE	名次
4	杨过	Oracle	77.00	1
2	李四	Oracle	77.00	1
3	张三丰	Oracle	0.00	2

·查询各学生各科排名(分区排名)

select sc.s_id,sc.s_name,sub_name,sc.score,

rank() over

(partition by sub_name order by score desc) 名次

from t_score sc

S_ID	S_NAME	SUB_NAME	SCORE	名次
4	杨过	JAVA	90.00	1
4	杨过	Oracle	77.00	1
2	李四	Oracle	77.00	1
3	张三丰	Oracle	0.00	3
5	mike	c++	80.00	1
3	张三丰	数学		1
2	李四	数学	80.00	2
1	张三	数学	0.00	3
3	张三丰	体育	120.00	1
1	张三	语文	80.00	1
2	李四	语文	50.00	2
3	张三丰	语文	10.00	3

·查询各科前2名(分区排名)

·类似：新闻表,求栏目点击率在前3位的新闻。
商品表,求各类别销售额在前10位的商品。

select * from (
select sc.s_id,sc.s_name,sub_name,sc.score,
dense_rank() over
(partition by sub_name order by score desc) 名次
from t_score sc
) x
where x.名次<=2

S_ID	S_NAME	SUB_NAME	SCORE	名次
4	杨过	JAVA	90.00	1
4	杨过	Oracle	77.00	1
2	李四	Oracle	77.00	1
3	张三丰	Oracle	0.00	2
5	mike	c++	80.00	1
3	张三丰	数学		1
2	李四	数学	80.00	2
3	张三丰	体育	120.00	1
1	张三	语文	80.00	1
2	李四	语文	50.00	2

·查询各同学总分

select s_id,s_name,sum(score) sum_score from t_score

group by s_id,s_name

S_ID	S_NAME	SUM_SCORE
1	张三	80.00
2	李四	207.00
3	张三丰	130.00
4	杨过	167.00
5	mike	80.00

·根据总分查询各同学名次

select x.*,

rank() over (order by sum_score desc) 名次

from (

select s_id,s_name,sum(score) sum_score from t_score

group by s_id,s_name ) x

S_ID	S_NAME	SUM_SCORE	名次
2	李四	207.00	1
4	杨过	167.00	2
3	张三丰	130.00	3
1	张三	80.00	4
5	mike	80.00	4

语法：

rank() over (order by 排序字段顺序)

rank() over (partition by 分组字段 order by 排序字段顺序)

1.顺序：asc|desc 名次与业务相关：

示例：找求优秀学员：成绩：降序迟到次数：升序

2.分区字段：根据什么字段进行分区。

问题：分区与分组有什么区别?

·分区只是将原始数据进行名次排列(记录数不变),

·分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意：聚合。

脚本：

create table t_score
(
 autoid number primary key,
 s_id  number(3),
 s_name char(8) not null,
 sub_name varchar2(20),
 score number(10,2)
);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (8, 1, '张三 ', '语文', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (9, 2, '李四 ', '数学', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (10, 1, '张三 ', '数学', 0);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (11, 2, '李四 ', '语文', 50);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (12, 3, '张三丰 ', '语文', 10);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (13, 3, '张三丰 ', '数学', null);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (14, 3, '张三丰 ', '体育', 120);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (15, 4, '杨过 ', 'java', 90);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (16, 5, 'mike ', 'c++', 80);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (3, 3, '张三丰 ', 'oracle', 0);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (4, 4, '杨过 ', 'oracle', 77);
insert into t_score (autoid, s_id, s_name, sub_name, score)
values (17, 2, '李四 ', 'oracle', 77);
commit;

Oracle 分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法

ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的) RANK() 类似,不过RANK 排序的时候跟派名次一样,可以并列2个第一名之后是第3名 LAG 表示分组排序后 ,组内后面一条记录减前面一条记录的差,第一条可返回 NULL BTW: EXPERT ONE ON ONE 上讲的最详细,还有很多相关特性,文档看起来比较费劲 row
Oracle数据库rownum和row_number的不同点

明确对于rownum 来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是二,以此类推,这个为字段可以用于限制查询的返回的总行数,因为rownum总是从1开始,但是1以上的自然数在rownum 做等于判断时都认为是false 条件,所以无法查到 rownum=n (n>1的自然数),所以查找第二行以后的记录可以用子查询方法来解决,给子查询中的rownum取别名;对于小于某个值的情况两种方法都差不多,但是对于某个不等于一的值或者求某个值到某个值之间的情况,用ro
oracle中rownum和row_number()

row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的). 与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序,而row_number()在包含排序从句后是先排序再计算行号码. 一.oracle中rownum 用于从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字
Oracle row_number() over()解析函数高效实现分页

复制代码代码如下: create table T_NEWS ( ID NUMBER, N_TYPE VARCHAR2(20), N_TITLE VARCHAR2(30), N_COUNT NUMBER ) prompt Disabling triggers for T_NEWS... alter table T_NEWS disable all triggers; prompt Loading T_NEWS... insert into T_NEWS (ID, N_TYPE, N_TITLE,
Oracle中rank,over partition函数的使用方法

排列(rank())函数.这些排列函数提供了定义一个集合(使用 PARTITION 子句),然后根据某种排序方式对这个集合内的元素进行排列的能力,下面以scott用户的emp表为例来说明rank over partition如何使用. 1)查询员工薪水并连续求和 select deptno,ename,sal, sum(sal)over(order by ename) sum1, sum(sal)over() sum2, 100* round(sal/sum(sal)over(),4) "bal
深入探讨:oracle中row_number() over()分析函数用法

row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的). 与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码. row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开始排序). rank()是跳跃排序,有两个第二名
Oracle开发之分析函数(Rank, Dense_rank, row_number)

一.使用rownum为记录排名: 在前面一篇<Oracle开发之分析函数简介Over>,我们认识了分析函数的基本应用,现在我们再来考虑下面几个问题: ①对所有客户按订单总额进行排名 ②按区域和客户订单总额进行排名 ③找出订单总额排名前13位的客户 ④找出订单总额最高.最低的客户 ⑤找出订单总额排名前25%的客户按照前面第一篇文章的思路,我们只能做到对各个分组的数据进行统计,如果需要排名的话那么只需要简单地加上rownum不就行了吗?事实情况是否如此想象般简单,我们来实践一下. [1]测试环境
Oracle排名函数(Rank)实例详解

--已知:两种排名方式(分区和不分区):使用和不使用partition --两种计算方式(连续,不连续),对应函数:dense_rank,rank ·查询原始数据:学号,姓名,科目名,成绩 select * from t_score S_ID S_NAME SUB_NAME SCORE 1 张三语文 80.00 2 李四数学 80.00 1 张三数学 0.00 2 李四语文 50.00 3 张三丰语文 10.00 3 张三丰数学 3 张三丰体育 120.00 4 杨过 JAVA 9
Oracle的out参数实例详解

Oracle的out参数实例详解一概念 1.一般来讲,存储过程和存储函数的区别在于存储函数可以有一个返回值:而存储过程没有返回值. 2.过程和函数都可以通过out指定一个或多个输出行.我们可以利用out参数,在过程和函数中实现返回多个值. 3.存储过程和存储函数都可以有out参数. 4.存储过程和存储函数都可以有多个out参数. 5.存储过程可以通过out参数来实现返回值. 6.如果只有一个返回值,用存储函数:否则,就用存储过程. 二实例 --out参数:查询某个员工姓名月薪和职位 /*
Oracle分页查询的实例详解

Oracle分页查询的实例详解 1.Oracle分页查询: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM tab) A WHERE ROWNUM <= 40 ) WHERE RN >= 21; 这个分页比下面的执行时间少,效率高. 2. select * from (select c.*,rownum rn from tab c) where rn between 21 and 40 对比这两种写法,绝大多数的情况下,第一个
C++ 中const对象与const成员函数的实例详解

C++ 中const对象与const成员函数的实例详解 const对象只能调用const成员函数: #include<iostream> using namespace std; class A { public: void fun()const { cout<<"const 成员函数!"<<endl; } void fun() { cout<<"非const成员函数 !"<<endl; } }; int
web开发之对比时间大小的工具函数的实例详解

web开发之对比时间大小的工具函数的实例详解 js时间大小比较,格式yyyy-MM-dd hh:mm:ss 把时间比较封装成一个工具函数: /** * 比较两个时间的大小 * 时间格式:yyyy-MM-dd hh:mm:ss * @param {Object} beginTime 开始时间 * @param {Object} endTime 结束时间 */ var getDataLarge = function(beginTimeString,endTimeString){ var b = be
oracle 的表空间实例详解

oracle 的表空间实例详解查询表空间 SELECT UPPER(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
python里使用正则的findall函数的实例详解

python里使用正则的findall函数的实例详解在前面学习了正则的search()函数,这个函数可以找到一个匹配的字符串返回,但是想找到所有匹配的字符串返回,怎么办呢?其实得使用findall()函数.如下例子: #python 3. 6 #蔡军生 #http://blog.csdn.net/caimouse/article/details/51749579 # import re text = 'abbaaabbbbaaaaa' pattern = 'ab' for match in r
PHP 传输会话curl函数的实例详解

PHP 传输会话curl函数的实例详解前言: 接手公司项目PC端负责人的重担,责任担当重大:从需求分析,画流程图,建表,编码,测试修bug,上线维护等我一个光杆司令一人完成(当然还有一个技术不错的前端配合,感谢主管的帮助),虽然累点加班多点但感觉还行吧,公司都是一个鸟样. 闲话不多说了,因为项目中经常需要调取java那边的接口,既然涉及到请求接口那就有了http的请求方式,PHP常见的是GET/POST两种当然还有其他的比如put等,java那边经常用到GET/POST/PUT/DELETE等
Python 私有函数的实例详解

Python 私有函数的实例详解与大多数语言一样,Python 也有私有的概念: • 私有函数不可以从它们的模块外面被调用 • 私有类方法不能够从它们的类外面被调用 • 私有属性不能够从它们的类外面被访问与大多数的语言不同,一个 Python 函数,方法,或属性是私有还是公有,完全取决于它的名字. 如果一个 Python 函数,类方法,或属性的名字以两个下划线开始 (但不是结束),它是私有的:其它所有的都是公有的. Python 没有类方法保护的概念 (只能用于它们自已的类和子类中).类方
C语言实现进制转换函数的实例详解

C语言实现进制转换函数的实例详解前言: 写一个二进制,八进制,十六进制转换为十进制的函数要求: 函数有两个参数,参数(1)是要转换为十进制的进制数,参数(2)是标示参数(1)是什么进制(2,8,16标示二进制,八进制,十六进制). 要有报错信息,比如参数是1012,但参数(2)是2,显然是进制数表示有错误. 系统表 pg_proc 存储关于函数的信息内部函数在编译之前需要先定义在 pg_proc.h 中,src/include/catalog/pg_proc.h CATALOG(pg_pr

Oracle排名函数(Rank)实例详解

相关推荐

随机推荐