度量快速开发平台-专业、快速的软件定制快开平台

标题: Oracle中rank,over partition函数的使用方法 [打印本页]

作者: 万望 时间: 2020-5-23 22:42
标题: Oracle中rank,over partition函数的使用方法

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

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值

作者: 万望 时间: 2020-5-23 22:43
其实从上面三个例子当中，不难看出over(partition by ... order by ...)的整体概念，我理解是
partition by：按照指字的字段分区，如果没有则针对全体数据
order by：按照指定字段进行连续操作（如求和(sum),排序(rank()等)，如果没有指定，就相当于对指定分区集合内的数据进行整体sum操作。

作者: 陈晓龙 时间: 2020-5-24 17:25
不经常使用，收藏下！

欢迎光临度量快速开发平台-专业、快速的软件定制快开平台 (http://p.delit.cn/)