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

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

本篇文章到此结束，如果您有相关技术方面疑问可以联系我们技术人员远程解决，感谢大家支持本站！

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 Oracle中rank,over partition函数的使用方法