`

oracle中rank(),dense_rank(), partition, over()

阅读更多
引用
聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。

在9i版本之前,只有分析功能(analytic ),即从一个查询结果中计算每一行的排序值,是基于order_by_clause子句中的value_exprs指定字段的。
其语法为:
RANK ( ) OVER ( [query_partition_clause] order_by_clause )

在9i版本新增加了合计功能(aggregate),即对给定的参数值在设定的排序查询中计算出其排序值。这些参数必须是常数或常值表达式,且必须和ORDER BY子句中的字段个数、位置、类型完全一致。
其语法为:
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
  expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
  [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)


引用

rank()对表中的数据进行分级排序.
  譬如有张学生成绩统计单的表STUDENT_RESULT
  name number kemu fenshu
  li 0113101 高数 90
  zhang 0113098 高数 80
  wang 0113077 高数 70
  li 0113101 物理 80
  zhang 0113098 物理 90
  wang 0113077 物理 70
  
  我如果要检索出表中高数的前两名和物理的前两名
  那么可以通过使用 RANK()方法达到目的
  
  --首先 通过kemu进行分类,然后按照fenshu降序排序
  select rank() over(partition by kemu order by fenshu desc) rk,t.* from student_result t
  
  结果
  rk name number kemu fenshu
  1 li 0113101 高数 90
  2 zhang 0113098 高数 80
  3 wang 0113077 高数 70
  1 zhang 0113098 物理 90
  2 li 0113101 物理 80
  3 wang 0113077 物理 70
  
  --然后,检索出rk<=2的记录,即取出前两名
  select * from (select rank() over(partition by kemu order by fenshu desc) rk,t.* from student_result t) as y where y.rk<=2;
  
  结果
  rk name number kemu fenshu
  1 li 0113101 高数 90
  2 zhang 0113098 高数 80
  1 zhang 0113098 物理 90
  2 li 0113101 物理 80
  
  dense_rank()和 rank()的用法完全相同,不同的是在出现分数相同的情况下
  如
  name number kemu fenshu
  li 0113101 高数 80
  zhang 0113098 高数 80
  wang 0113077 高数 70
  li 0113101 物理 80
  zhang 0113098 物理 90
  wang 0113077 物理 70
  
  select rank() over(order by fenshu desc) rk,t.* from student_result t
  的结果为
  rk name number kemu fenshu
  1 zhang 0113098 物理 90
  2 li 0113101 高数 80
  2 zhang 0113098 高数 80
  2 li 0113101 物理 80
  5 wang 0113077 高数 70
  5 wang 0113077 物理 70
  而select dense_rank() over(order by fenshu desc) rk,t.* from student_result t
  结果为
  rk name number kemu fenshu
  1 zhang 0113098 物理 90
  2 li 0113101 高数 80
  2 zhang 0113098 高数 80
  2 li 0113101 物理 80
  3 wang 0113077 高数 70
  3 wang 0113077 物理 70
  
  附 row_number()的使用
  
  select row_number() over(order by fenshu desc) rk,t.* from student_result t
  rk name number kemu fenshu
  1 zhang 0113098 物理 90
  2 li 0113101 高数 80
  3 zhang 0113098 高数 80
  4 li 0113101 物理 80
  5 wang 0113077 高数 70
  6 wang 0113077 物理 70
  
  fetch n rows only
  取出当前记录的前多少行
  
  如
  select row_number() over(order by user_no) from user_files fetch first 5 rows only;


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



1)查询员工薪水并连续求和

select deptno,ename,sal,

sum(sal)over(order by ename) sum1,  /*表示连续求和*/
sum(sal)over() sum2,                           /*相当于求和sum(sal)*/
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
分享到:
评论

相关推荐

    Oracle开发之分析函数(Rank, Dense_rank, row_number)

    Oracle分析函数Rank、Dense_rank和row_number是用于处理数据集的高级工具,它们在数据库查询中发挥着关键作用,特别是在需要对数据进行排序和分组时。这三种函数都有各自的特点,适用于不同的业务场景。 1. **row_...

    Oracle查询中OVER (PARTITION BY ..)用法

    在这个场景中,我们将深入探讨`RANK()`和`DENSE_RANK()`以及`MIN()`和`MAX()`在`OVER (PARTITION BY ..)`中的应用。 首先,让我们来看`RANK()`和`DENSE_RANK()`。这两种函数都是用来对分区内的数据进行排名,但是...

    oracle分析函数over_及开窗函数.txt

    本文将详细介绍Oracle中的`OVER`子句以及几种常用的开窗函数,包括`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`和`LAG()`等。 #### 二、基本概念 - **`OVER`子句**:`OVER`子句用于指定分析函数的作用范围,它可以...

    Oracle中rank,over partition函数的使用方法

    在Oracle数据库中,`RANK()`, `OVER()`, `PARTITION BY` 是窗口函数的重要组成部分,它们在数据处理和分析中发挥着至关重要的作用。本文将深入探讨这些函数的用法,通过实例来帮助理解它们的功能和应用场景。 首先...

    Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数(转载)

    在Oracle数据库中,`OVER (PARTITION BY ...)` 是一种非常强大的功能,它允许用户在数据集上进行窗口操作。这在数据分析、报表制作以及复杂查询中极为常见。 #### 二、开窗函数简介 开窗函数(Window Functions)...

    oracle分组排序统计高级用法

    DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank FROM students WHERE DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) ; ``` #### 三、分析函数:`OVER`子句 **1. `...

    oracle_排列rank()函数

    在Oracle数据库中,`RANK()`函数是一种窗口函数,它被用于在一组数据中为每一行分配一个唯一的排名。此函数特别适用于需要基于特定条件对数据进行排序并为每个记录分配一个名次的情况。在本篇文章中,我们将深入探讨...

    ORACLE_OVER函数

    OVER函数通常与窗口函数(如RANK(), DENSE_RANK(), ROW_NUMBER(), SUM(), AVG()等)一起使用。其基本语法形式如下: ```sql &lt;window_function&gt; OVER ( [window_spec] ) ``` 其中`&lt;window_function&gt;`代表具体的窗口...

    oracle排名函数的使用方法分享

    在oracle中,有rank,dense_rank,row_number,以及分组排名partition。 说明: rank:排名会出现并列第n名,它之后的会跳过空出的名次,例如:1,2,2,4 dense_rank:排名会出现并列第n名,它之后的名次为n+1,例如:1,2,...

    ORACLE 常用分析函数

     ROW_NUMBER () OVER([partition_clause] order_by_clause) dense_rank在做排序时如果遇到列有重复值,则重复值所在行的序列值相同,而其后的序列值依旧递增,rank则是重复值所在行的序列值相同,但其后的序列值从...

    oracle row_number用法

    SELECT DEPTNO, SAL, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL) AS RANK_ORDER FROM SCOTT.EMP ORDER BY DEPTNO; ``` 结果如下: | DEPTNO | SAL | RANK_ORDER | |--------|-------|------------| ...

    oracle统计函数.pdf

    例如,在文档中的例子select deptno, dense_rank() over (partition by deptno order by sal desc) drk, ename, sal from emp中,同样是按照部门编号进行分组,并在每个分组内按薪资降序进行排序,返回排名drk。...

    ORACLE_培训之_分析函数

    - **rank()**, **dense_rank()** 和 **row_number()**: 用于排序,分别返回唯一的排名、无间隙的排名和行号。 - **lag()** 和 **lead()**: 提供对当前行前一行或后一行的访问,常用于时间序列分析。 - **rollup()** ...

    ORACLE分析函数.pdf

    DENSE_RANK() OVER (PARTITION BY NET_TYPE ORDER BY LOCAL_FARE DESC) as DENSE_RANK, ROW_NUMBER() OVER (PARTITION BY NET_TYPE ORDER BY LOCAL_FARE DESC) as ROW_NUMBER FROM t; ``` 3. LAG, LEAD LAG和LEAD...

    oracle分析函数在BI分析中应用事例

    Oracle分析函数在商业智能(BI)分析中扮演着至关重要的角色,它们允许用户对数据集进行复杂的计算和分析,而不必依赖多表联接或子查询。这些函数极大地提升了SQL查询的效率和灵活性,尤其在处理大数据量时。以下是...

    oracle 中的分析函数

    SELECT empno, ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS rank_by_sal FROM emp; ``` 这里,`RANK()`会为每一组相同的工资值分配一个不同的排名,而`DENSE_RANK()`则会给相同工资值的记录分配相同的...

    ORACLE分析函数

    根据提供的文件信息,我们可以深入探讨Oracle分析函数的相关知识点,特别是`SUM()`函数配合`OVER`子句的不同用法,以及`RANK()`, `DENSE_RANK()`, 和 `ROW_NUMBER()` 这三个窗口函数的应用场景。 ### Oracle分析...

    Oracle_adv.zip_Oracle進階_oracle

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统之一,尤其在企业级应用中占据了重要的地位。本文将深入探讨Oracle 10g中的高级特性,包括正则表达式和SQL优化,帮助你提升数据库管理与开发技能。 一、...

Global site tag (gtag.js) - Google Analytics