oracle中rank()，dense_rank(), partition, over()

[quote][size=large][color=red]聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。

在9i版本之前，只有分析功能（analytic ），即从一个查询结果中计算每一行的排序值，是基于order_by_clause子句中的value_exprs指定字段的。
其语法为：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )

在9i版本新增加了合计功能（aggregate），即对给定的参数值在设定的排序查询中计算出其排序值。这些参数必须是常数或常值表达式，且必须和ORDER BY子句中的字段个数、位置、类型完全一致。
其语法为：
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)

[/color][/size][/quote]
[quote]
[size=medium][color=green]rank()对表中的数据进行分级排序.
　　譬如有张学生成绩统计单的表STUDENT_RESULT
　　name number kemu fenshu
　　li 0113101 高数 90
　　zhang 0113098 高数 80
　　wang 0113077 高数 70
　　li 0113101 物理 80
　　zhang 0113098 物理 90
　　wang 0113077 物理 70
　　
　　我如果要检索出表中高数的前两名和物理的前两名
　　那么可以通过使用 RANK()方法达到目的
　　
　　--首先 通过kemu进行分类，然后按照fenshu降序排序
　　select rank() over(partition by kemu order by fenshu desc) rk,t.* from student_result t
　　
　　结果
　　rk name number kemu fenshu
　　1 li 0113101 高数 90
　　2 zhang 0113098 高数 80
　　3 wang 0113077 高数 70
　　1 zhang 0113098 物理 90
　　2 li 0113101 物理 80
　　3 wang 0113077 物理 70
　　
　　--然后，检索出rk<=2的记录，即取出前两名
　　select * from (select rank() over(partition by kemu order by fenshu desc) rk,t.* from student_result t) as y where y.rk<=2;
　　
　　结果
　　rk name number kemu fenshu
　　1 li 0113101 高数 90
　　2 zhang 0113098 高数 80
　　1 zhang 0113098 物理 90
　　2 li 0113101 物理 80
　　
　　dense_rank()和 rank()的用法完全相同，不同的是在出现分数相同的情况下
　　如
　　name number kemu fenshu
　　li 0113101 高数 80
　　zhang 0113098 高数 80
　　wang 0113077 高数 70
　　li 0113101 物理 80
　　zhang 0113098 物理 90
　　wang 0113077 物理 70
　　
　　select rank() over(order by fenshu desc) rk,t.* from student_result t
　　的结果为
　　rk name number kemu fenshu
　　1 zhang 0113098 物理 90
　　2 li 0113101 高数 80
　　2 zhang 0113098 高数 80
　　2 li 0113101 物理 80
　　5 wang 0113077 高数 70
　　5 wang 0113077 物理 70
　　而select dense_rank() over(order by fenshu desc) rk,t.* from student_result t
　　结果为
　　rk name number kemu fenshu
　　1 zhang 0113098 物理 90
　　2 li 0113101 高数 80
　　2 zhang 0113098 高数 80
　　2 li 0113101 物理 80
　　3 wang 0113077 高数 70
　　3 wang 0113077 物理 70
　　
　　附 row_number()的使用
　　
　　select row_number() over(order by fenshu desc) rk,t.* from student_result t
　　rk name number kemu fenshu
　　1 zhang 0113098 物理 90
　　2 li 0113101 高数 80
　　3 zhang 0113098 高数 80
　　4 li 0113101 物理 80
　　5 wang 0113077 高数 70
　　6 wang 0113077 物理 70
　　
　　fetch n rows only
　　取出当前记录的前多少行
　　
　　如
　　select row_number() over(order by user_no) from user_files fetch first 5 rows only;[/color][/size]
[/quote]
[quote][size=medium][color=blue]排列（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
[/color][/size][/quote]