oracle 排列rank函数.docx
- 文档编号:23538695
- 上传时间:2023-05-18
- 格式:DOCX
- 页数:12
- 大小:17.81KB
oracle 排列rank函数.docx
《oracle 排列rank函数.docx》由会员分享,可在线阅读,更多相关《oracle 排列rank函数.docx(12页珍藏版)》请在冰豆网上搜索。
oracle排列rank函数
排列(rank())函数。
这些排列函数提供了定义一个集合(使用PARTITION子句),然后根据某种排序方式对这个集合内的元素进行排列的能力,下面以scott用户的emp表为例来说明rankoverpartition如何使用
1)查询员工薪水并连续求和
selectdeptno,ename,sal,
sum(sal)over(orderbyename)sum1, /*表示连续求和*/
sum(sal)over()sum2, /*相当于求和sum(sal)*/
100*round(sal/sum(sal)over(),4)"bal%"
fromemp
结果如下:
DEPTNOENAME SAL SUM1 SUM2 bal%
------------------------------------------------------------
20ADAMS 1100 1100 29025 3.79
30ALLEN 1600 2700 29025 5.51
30BLAKE 2850 5550 29025 9.82
10CLARK 2450 8000 29025 8.44
20FORD 3000 11000 29025 10.34
30JAMES 950 11950 29025 3.27
20JONES 2975 14925 29025 10.25
10KING 5000 19925 29025 17.23
30MARTIN 1250 21175 29025 4.31
10MILLER 1300 22475 29025 4.48
20SCOTT 3000 25475 29025 10.34
DEPTNOENAME SAL SUM1 SUM2 bal%
------------------------------------------------------------
20SMITH 800 26275 29025 2.76
30TURNER 1500 27775 29025 5.17
30WARD 1250 29025 29025 4.31
2)如下:
selectdeptno,ename,sal,
sum(sal)over(partitionbydeptnoorderbyename)sum1,/*表示按部门号分氏,按姓名排序并连续求和*/
sum(sal)over(partitionbydeptno)sum2,/*表示部门分区,求和*/
sum(sal)over(partitionbydeptnoorderbysal)sum3,/*按部门分区,按薪水排序并连续求和*/
100*round(sal/sum(sal)over(),4)"bal%"
fromemp
结果如下:
DEPTNOENAME SAL SUM1 SUM2 SUM3 bal%
----------------------------------------------------------------------
10CLARK 2450 2450 8750 3750 8.44
10KING 5000 7450 8750 8750 17.23
10MILLER 1300 8750 8750 1300 4.48
20ADAMS 1100 1100 10875 1900 3.79
20FORD 3000 4100 10875 10875 10.34
20JONES 2975 7075 10875 4875 10.25
20SCOTT 3000 10075 10875 10875 10.34
20SMITH 800 10875 10875 800 2.76
30ALLEN 1600 1600 9400 6550 5.51
30BLAKE 2850 4450 9400 9400 9.82
30JAMES 950 5400 9400 950 3.27
DEPTNOENAME SAL SUM1 SUM2 SUM3 bal%
----------------------------------------------------------------------
30MARTIN 1250 6650 9400 3450 4.31
30TURNER 1500 8150 9400 4950 5.17
30WARD 1250 9400 9400 3450 4.31
3)如下:
selectempno,deptno,sal,
sum(sal)over(partitionbydeptno)"deptSum",/*按部门分区,并求和*/
rank()over(partitionbydeptnoorderbysaldescnullslast) rank,/*按部门分区,按薪水排序并计算序号*/
dense_rank()over(partitionbydeptnoorderbysaldescnullslast)d_rank,
row_number()over(partitionbydeptnoorderbysaldescnullslast)row_rank
fromemp
注:
rang()涵数主要用于排序,并给出序号
dense_rank():
功能同rank()一样,区别在于,rank()对于排序并的数据给予相同序号,接下来的数据序号直接跳中跃,dense_rank()则不是,比如数据:
1,2,2,4,5,6.。
。
。
。
这是rank()的形式
1,2,2,3,4,5,。
。
。
。
这是dense_rank()的形式
1,2,3,4,5,6.。
。
。
。
。
这是row_number()涵数形式
row_number()涵数则是按照顺序依次使用,相当于我们普通查询里的rownum值
其实从上面三个例子当中,不难看出over(partitionby...orderby...)的整体概念,我理解是
partitionby:
按照指字的字段分区,如果没有则针对全体数据
orderby :
按照指定字段进行连续操作(如求和(sum),排序(rank()等),如果没有指定,就相当于对指定分区集合内的数据进行整体sum操作
oracle聚合函数rank()的用法
SQL>select*fromtest_a;
ID PLAYNAME SCORE
--------------------------------------------------
01 aa 100
02 aa 101
02 bb 99
03 bb 98
04 aa 101
02 aa 101
需求是,将score降序排序,打印所有字段,并且如果是同一个playname的score只取出最高分,如果这个playname获得过多个相同的最高分,则只取出其中一个(比如:
aa获得过3次101,则只取其中一个),最终要的结果就是:
RKID PALYNAME SCORE
------------------------------------------------------------
102 aa 101
102 bb 99
本来我想用max函数,结果直接就出来了:
SQL>selectmax(score),palynamefromtest_agroupbypalyname;
MAX(SCORE)PALYNAME
------------------------------
101aa
99bb
但是要打印所有字段…OTL
即使用了嵌套,还是无法解决重复重现最高分的现象:
SQL>selectdistinct*fromtest_atwhere score in (select max(score) from test_a group by palyname)orderbyscoredesc;
ID PALYNAME SCORE
--------------------------------------------------
02 aa 101
04 aa 101
02 bb 99
由于相同的playname对应的id不同,所以用distinct也无法过滤掉相同playname的并列最高分。
于是只好用rank()了
Rank的基本语法为:
RANK()OVER(order_by_clause)
例子1:
TABLE:
A(科目,分数)
数学,80
语文,70
数学,90
数学,60
数学,100
语文,88
语文,65
语文,77
现在我想要的结果是:
(即想要每门科目的前3名的分数)
数学,100
数学,90
数学,80
语文,88
语文,77
语文,70
那么语句就这么写:
select*from(selectrank()over(partitionby科目orderby分数desc)rk,a.*froma)t
wheret.rk<=3;
以科目来分组,然后以分数来排序,给排序的结果分配rank,取前三名的rank
例子2:
有表Table内容如下
COL1 COL2
1 1
2 1
3 2
3 1
4 1
4 2
5 2
5 2
6 2
分析功能:
列出Col2分组后根据Col1排序,并生成数字列。
比较实用于在成绩表中查出各科前几名的信息。
SELECTa.*,RANK()OVER(PARTITIONBYcol2ORDERBYcol1)"Rank"FROMtablea;
结果如下:
COL1 COL2 Rank
1 1 1
2 1 2
3 1 3
4 1 4
3 2 1
4 2 2
5 2 3
5 2 3
6 2 5
这个例子更直观一点,根据col2分组,根据clo1排序,我们可以发现:
5 2 3
5 2 3
6 2 5
即,如果两行记录完全相同,他们会被给予相同的rank,而排在它们之后的那行记录,由于前面的并列第3,使得之后的那条记录变成了第5,而如果我们在这里用的是dense_rank,那么之后的那条会变成第4
例子3:
合计功能:
计算出数值(4,1)在OradeByCol1,Col2排序下的排序值,也就是col1=4,col2=1在排序以后的位置
SELECTRANK(4,1)WITHINGROUP(ORDERBYcol1,col2)"Rank"FROMtable;
结果如下:
Rank
4
通过以上方法,得出col1为4,col2为1的那行数据的rank排名为多少
Dense_rank的例子:
dense_rank与rank()用法相当,但是有一个区别:
dence_rank在并列关系是,相关等级不会跳过。
rank则跳过
例如:
表
A B C
a liu wang
a jin shu
a cai kai
b yang du
b lin ying
b yao cai
b yang 99
例如:
当rank时为:
selectm.a,m.b,m.c,rank()over(partitionbyaorderbyb)liufromtest3m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 4
而如果用dense_rank时为:
selectm.a,m.b,m.c,dense_rank()over(partitionbyaorderbyb)liufromtest3m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 3
那么再回到之前的那个需求,
SQL>selectdistinct*from(selectrank()over(partitionbyplaynameorderbyscoredesc,id)rk,t.*fromtest_at)whererk=1;
RKID PLAYNAME SCORE
------------------------------------------------------------
102 aa 101
102 bb 99
这里orderbyscoredesc,id 以score降序和id这两个字段排序,也就是说,正因为相同的playname对应的id不同,这样相同的playname,相同的score,但是不同的id,这样的2行数据就获得了不同的rank,而rk=1,即是只取rank=1,也就是最高分。
这样就完成了需求
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 排列rank函数 排列 rank 函数