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,?????????????????????????? /*相当于求和sum(sal)*/
100* round(sal/sum(sal)over(),4) "bal%"
from emp
结果如下：
??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? bal%
---------- ---------- ---------- ---------- ---------- ----------
??????? 20 ADAMS??????????? 1100?????? 1100????? 29025?????? 3.79
??????? 30 ALLEN??????????? 1600?????? 2700????? 29025?????? 5.51
??????? 30 BLAKE??????????? 2850?????? 5550????? 29025?????? 9.82
??????? 10 CLARK??????????? 2450?????? 8000????? 29025?????? 8.44
??????? 20 FORD???????????? 3000????? 11000????? 29025????? 10.34
??????? 30 JAMES???????????? 950????? 11950????? 29025?????? 3.27
??????? 20 JONES??????????? 2975????? 14925????? 29025????? 10.25
??????? 10 KING???????????? 5000????? 19925????? 29025????? 17.23
??????? 30 MARTIN?????????? 1250????? 21175????? 29025?????? 4.31
??????? 10 MILLER?????????? 1300????? 22475????? 29025?????? 4.48
??????? 20 SCOTT??????????? 3000????? 25475????? 29025????? 10.34
??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? bal%
---------- ---------- ---------- ---------- ---------- ----------
??????? 20 SMITH???????????? 800????? 26275????? 29025?????? 2.76
??????? 30 TURNER?????????? 1500????? 27775????? 29025?????? 5.17
??????? 30 WARD???????????? 1250????? 29025????? 29025?????? 4.31
2）如下：
select deptno,ename,sal,
sum(sal)over(partition by deptno order by ename) sum1,/*表示按部门号分氏，按姓名排序并连续求和*/
sum(sal)over(partition by deptno) sum2,/*表示部门分区，求和*/
sum(sal)over(partition by deptno order by sal) sum3,/*按部门分区，按薪水排序并连续求和*/
100* round(sal/sum(sal)over(),4) "bal%"
from emp
结果如下：
??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? SUM3?????? bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
??????? 10 CLARK??????????? 2450?????? 2450?????? 8750?????? 3750?????? 8.44
??????? 10 KING???????????? 5000?????? 7450?????? 8750?????? 8750????? 17.23
??????? 10 MILLER?????????? 1300?????? 8750?????? 8750?????? 1300?????? 4.48
??????? 20 ADAMS??????????? 1100?????? 1100????? 10875?????? 1900?????? 3.79
??????? 20 FORD???????????? 3000?????? 4100????? 10875????? 10875????? 10.34
??????? 20 JONES??????????? 2975?????? 7075????? 10875?????? 4875????? 10.25
??????? 20 SCOTT??????????? 3000????? 10075????? 10875????? 10875????? 10.34
??????? 20 SMITH???????????? 800????? 10875????? 10875??????? 800?????? 2.76
??????? 30 ALLEN??????????? 1600?????? 1600?????? 9400?????? 6550?????? 5.51
??????? 30 BLAKE??????????? 2850?????? 4450?????? 9400?????? 9400?????? 9.82
??????? 30 JAMES???????????? 950?????? 5400?????? 9400??????? 950?????? 3.27
??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? SUM3?????? bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
??????? 30 MARTIN?????????? 1250?????? 6650?????? 9400?????? 3450?????? 4.31
??????? 30 TURNER?????????? 1500?????? 8150?????? 9400?????? 4950?????? 5.17
??????? 30 WARD???????????? 1250?????? 9400?????? 9400?????? 3450?????? 4.31
3）如下：
select empno,deptno,sal,
sum(sal)over(partition by deptno) "deptSum",/*按部门分区，并求和*/
rank()over(partition by deptno order by sal desc nulls last)? rank, /*按部门分区，按薪水排序并计算序号*/
dense_rank()over(partition by deptno order by sal desc nulls last) d_rank,
row_number()over(partition by deptno order by sal desc nulls last) row_rank
from emp
注：
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(partition by ... order by ...)的整体概念，我理解是
partition by :按照指字的字段分区，如果没有则针对全体数据
order by????? :按照指定字段进行连续操作（如求和(sum),排序(rank()等)，如果没有指定，就相当于对指定分区集合内的数据进行整体sum操作
oracle聚合函数rank()的用法
SQL> select * from test_a;
ID?????????????????? PLAYNAME????????????????? SCORE
-------------------- -------------------- ----------
01?????????????????? aa????????????????????????? 100
02?????????????????? aa????????????????????????? 101
02?????????????????? bb?????????????????????????? 99
03?????????????????? bb?????????????????????????? 98
04?????????????????? aa????????????????????????? 101
02?????????????????? aa????????????????????????? 101
?
create table test_a(
id number(20),
playname varchar2(30),
score number(20)
)
insert into TEST_A (ID, PLAYNAME, SCORE)
values (1, 'aa', 100);
insert into TEST_A (ID, PLAYNAME, SCORE)
values (2, 'aa', 101);
insert into TEST_A (ID, PLAYNAME, SCORE)
values (2, 'bb', 99);
insert into TEST_A (ID, PLAYNAME, SCORE)
values (3, 'bb', 98);
insert into TEST_A (ID, PLAYNAME, SCORE)
values (4, 'aa', 101);
insert into TEST_A (ID, PLAYNAME, SCORE)
values (2, 'aa', 101);
?
需求是，将score降序排序，打印所有字段，并且如果是同一个playname的score只取出最高分，如果这个playname获得过多个相同的最高分，则只取出其中一个（比如：aa获得过3次101，则只取其中一个），最终要的结果就是：
??????? RK ID?????????????????? PALYNAME????????????????? SCORE
---------- -------------------- -------------------- ----------
???????? 1 02?????????????????? aa????????????????????????? 101
???????? 1 02?????????????????? bb?????????????????????????? 99
本来我想用max函数，结果直接就出来了：
SQL> select max(score),palyname from test_a group by palyname;
MAX(SCORE) PALYNAME
---------- --------------------
?????? 101 aa
??????? 99 bb
但是要打印所有字段…OTL
即使用了嵌套，还是无法解决重复重现最高分的现象：
SQL> select distinct * from test_a t where? score? in? (select? max(score)? from? test_a? group? by? palyname) order by score desc;
ID?????????????????? PALYNAME????????????????? SCORE
-------------------- -------------------- ----------
02?????????????????? aa????????????????????????? 101
04?????????????????? aa????????????????????????? 101
02?????????????????? bb?????????????????????????? 99
由于相同的playname对应的id不同，所以用distinct也无法过滤掉相同playname的并列最高分。
于是只好用rank（）了
Rank的基本语法为：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
例子1：
TABLE：A （科目，分数）
数学，80
语文，70
数学，90
数学，60
数学，100
语文，88
语文，65
语文，77
现在我想要的结果是：（即想要每门科目的前3名的分数）
数学，100
数学，90
数学，80
语文，88
语文，77
语文，70
那么语句就这么写：
select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t
where t.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排序,并生成数字列。比较实用于在成绩表中查出各科前几名的信息。
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
结果如下：
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)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以后的位置
SELECT RANK(4,1) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;
结果如下：
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时为：
select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m
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时为：
select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m
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> select distinct * from (select rank() over(partition by playname order by score desc,id) rk,t.* from test_a t) where rk=1;
??????? RK ID?????????????????? PLAYNAME????????????????? SCORE
---------- -------------------- -------------------- ----------
???????? 1 02?????????????????? aa????????????????????????? 101
???????? 1 02?????????????????? bb?????????????????????????? 99
这里order by score desc,id? 以score降序和id这两个字段排序，也就是说，正因为相同的playname对应的id不同，这样相同的playname，相同的score，但是不同的id，这样的2行数据就获得了不同的rank，而rk=1，即是只取rank=1，也就是最高分。这样就完成了需求。
?
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lky5387/archive/2009/11/20/4839386.aspx
