Oracle中rank,over partition函數的使用方法，oraclerank

Oracle中rank,over partition函數的使用方法，oraclerank

排列（rank()）函數。這些排列函數提供了定義一個集合（使用 PARTITION 子句），然後根據某種排序方式對這個集合內的元素進行排列的能力，下面以scott用戶的emp表為例來說明rank over partition如何使用。

1）查詢員工薪水並連續求和

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

結果如下：

    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操作。

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