Oracle中rank,over partition函數(shù)的使用方法

排列（rank()）函數(shù)。這些排列函數(shù)提供了定義一個(gè)集合（使用 PARTITION 子句），然后根據(jù)某種排序方式對(duì)這個(gè)集合內(nèi)的元素進(jìn)行排列的能力，下面以scott用戶(hù)的emp表為例來(lái)說(shuō)明rank over partition如何使用。

1）查詢(xún)員工薪水并連續(xù)求和

select deptno,ename,sal,

sum(sal)over(order by ename) sum1, 
sum(sal)over() sum2,             
100* round(sal/sum(sal)over(),4) "bal%"
from emp

結(jié)果如下：

    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

結(jié)果如下：

    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()函數(shù)主要用于排序，并給出序號(hào)

dense_rank()：功能同rank()一樣，區(qū)別在于，rank()對(duì)于排序并的數(shù)據(jù)給予相同序號(hào)，接下來(lái)的數(shù)據(jù)序號(hào)直接跳中躍，dense_rank()則不是，比如數(shù)據(jù)：1，2，2，4，5，6.。。。。這是rank()的形式

1，2，2，3，4，5，。。。。這是dense_rank()的形式

1，2，3，4，5，6.。。。。。這是row_number()函數(shù)形式

row_number()函數(shù)則是按照順序依次使用，相當(dāng)于我們普通查詢(xún)里的rownum值

其實(shí)從上面三個(gè)例子當(dāng)中，不難看出over(partition by ... order by ...)的整體概念，我理解是

partition by：按照指字的字段分區(qū)，如果沒(méi)有則針對(duì)全體數(shù)據(jù)
order by：按照指定字段進(jìn)行連續(xù)操作（如求和(sum),排序(rank()等)，如果沒(méi)有指定，就相當(dāng)于對(duì)指定分區(qū)集合內(nèi)的數(shù)據(jù)進(jìn)行整體sum操作。

以上就是rank,over partition函數(shù)的基本使用方法，希望對(duì)大家有所幫助。