Rank() OVER(PARTITION BY)用法

排列（rank()）函数。这些排列函数提供了定义一个集合（使用 PARTITION子句），然后根据某种排序方式对这个集合内的元素进行排列的能力，下面以scott用户的emp表为例来说明rank overpartition如何使用

 

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

结果如下：

    DEPTNOENAME            SAL      SUM1      SUM2      bal%
---------- ---------- ---------- ---------- --------------------
       20ADAMS           1100      1100     29025      3.79
       30ALLEN           1600      2700     29025      5.51
       30BLAKE           2850      5550     29025      9.82
       10CLARK           2450      8000     29025      8.44
       20FORD            3000     11000     29025     10.34
       30JAMES            950     11950     29025      3.27
       20JONES           2975     14925     29025     10.25
       10KING            5000     19925     29025     17.23
       30MARTIN          1250     21175     29025      4.31
       10MILLER          1300     22475     29025      4.48
       20SCOTT           3000     25475     29025     10.34

    DEPTNOENAME            SAL      SUM1      SUM2      bal%
---------- ---------- ---------- ---------- --------------------
       20SMITH            800     26275     29025      2.76
       30TURNER          1500     27775     29025      5.17
       30WARD            1250     29025     29025      4.31

 

2）如下：

select deptno,ename,sal,
sum(sal)over(partition by deptno orderby ename) sum1,
sum(sal)over(partition by deptno)sum2,
sum(sal)over(partition by deptno orderby sal) sum3,
100* round(sal/sum(sal)over(),4) "bal%"
from emp

结果如下：

    DEPTNOENAME            SAL      SUM1      SUM2      SUM3      bal%
---------- ---------- ---------- ---------- ---------- --------------------
       10CLARK           2450      2450      8750      3750      8.44
       10KING            5000      7450      8750      8750     17.23
       10MILLER          1300      8750      8750      1300      4.48
       20ADAMS           1100      1100     10875      1900      3.79
       20FORD            3000      4100     10875     10875     10.34
       20JONES           2975      7075     10875      4875     10.25
       20SCOTT           3000     10075     10875     10875     10.34
       20SMITH            800     10875     10875       800      2.76
       30ALLEN           1600      1600      9400      6550      5.51
       30BLAKE           2850      4450      9400      9400      9.82
       30JAMES            950      5400      9400       950      3.27

    DEPTNOENAME            SAL      SUM1      SUM2      SUM3      bal%
---------- ---------- ---------- ---------- ---------- --------------------
       30MARTIN          1250      6650      9400      3450      4.31
       30TURNER          1500      8150      9400      4950      5.17
       30WARD            1250      9400      9400      3450      4.31

 

3）如下：

select empno,deptno,sal,
sum(sal)over(partition by deptno)"deptSum",
rank()over(partition by deptno orderby sal desc nulls last)  rank,
dense_rank()over(partition by deptno orderby sal desc nulls last) d_rank,
row_number()over(partition by deptno orderby 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值