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

b83j8n2e63

引用
聚合函数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 }]]...
)



引用

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;


引用
排列（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