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,                           /*相当于求和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

 

 

  Oracle分析函数四——函数RANK,DENSE_RANK,FIRST,LAST… 收藏
原文地址：http://space.itpub.net/6517/viewspace-611065

Oracle分析函数——函数RANK,DENSE_RANK,FIRST,LAST…

RANK

功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为1，则没有序数2，序列将给组中的下一行分配值3，DENSE_RANK则没有任何跳跃。

SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与DENSE_RANK函数的区别）

 

DENSE_RANK

功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。密集的序列返回的时没有间隔的数

SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与RANK函数的区别）

SELECT

 department_id,

 first_name||' '||last_name employee_name,

 salary,

 RANK() OVER (ORDER BY salary) AS RANK_ORDER,

 DENSE_RANK() OVER (ORDER BY salary) AS DENSE_RANK_ORDER 

FROM employees

 

 

SELECT

 department_id,

 first_name||' '||last_name employee_name,

 salary,

 RANK() OVER (PARTITION BY department_id ORDER BY salary) AS RANK_PART_ORDER,

 DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS DENSE_RANK_PART_ORDER  

FROM employees

 

 

FIRST

功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录

SAMPLE：下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值

 

LAST

功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录

SAMPLE：下面例子中DENSE_RANK按雇用日期排序，FIRST取出salary最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出雇用日期最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值

SELECT

 department_id,

 first_name||' '||last_name employee_name,

 hire_date,

 salary,

 MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date) OVER (PARTITION BY department_id) "Worst",

 MAX(salary) KEEP (DENSE_RANK LAST ORDER BY hire_date) OVER (PARTITION BY department_id) "Best"

FROM employees

 

 

FIRST_VALUE

功能描述：返回组中数据窗口的第一个值。

SAMPLE：下面例子计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字

 

LAST_VALUE

功能描述：返回组中数据窗口的最后一个值。

SAMPLE：下面例子计算按部门分区按薪水排序的数据窗口的最后一个值对应的名字，如果薪水的最后一个值有多个，则从多个对应的名字中取缺省排序的最后一个名字

SELECT

 department_id,

 first_name||' '||last_name employee_name,

 hire_date,

 salary,

 FIRST_VALUE(first_name||' '||last_name) OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal,

 LAST_VALUE(first_name||' '||last_name) OVER(PARTITION BY department_id ORDER BY salary) AS highest_sal

 FROM employees

 

 

看起来last_value和first_value的标准似乎有些不一样，不过单独执行就很清楚了，呵呵

SELECT

 department_id,

 first_name||' '||last_name employee_name,

 hire_date,

 salary,

 FIRST_VALUE(first_name||' '||last_name) OVER(PARTITION BY department_id ORDER BY salary ) AS lowest_sal,

 FIRST_VALUE(first_name||' '||last_name) OVER(PARTITION BY department_id ORDER BY salary DESC) AS highest_sal,

 LAST_VALUE(first_name||' '||last_name) OVER(PARTITION BY department_id ORDER BY salary ) AS last_sal,

 LAST_VALUE(first_name||' '||last_name) OVER(PARTITION BY department_id ORDER BY salary DESC) AS last_sal_desc

 FROM employees

 

 

LAG

功能描述：可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD

SAMPLE：下面的例子中列prev_sal返回按hire_date排序的前1行的salary值

 

LEAD

功能描述：LEAD与LAG相反，LEAD可以访问组中当前行之后的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行）

SAMPLE：下面的例子中列prev_sal返回按hire_date排序的后1行的salary值

SELECT

 first_name||' '||last_name employee_name,

 hire_date,

 salary,

 LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal,

 LEAD(salary, 1,0) OVER (ORDER BY hire_date) AS "next_sal"

 FROM employees

 

 

ROW_NUMBER

功能描述：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。

SAMPLE：下例返回每个员工再在每个部门中按员工号排序后的顺序号

SELECT

 department_id, 

 first_name||' '||last_name employee_name,

 employee_id,

 ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id

FROM employees

 

 

发表于 @ 2009年12月06日　14:03:00 | 评论( 0 ) | 编辑| 举报| 收藏

旧一篇:Oracle分析函数三——SUM,AVG,MIN,MAX,COUNT | 新一篇:Oracle分析函数五——统计分析函数
查看最新精华文章 请访问博客首页相关文章
oracle的常用函数对Oracle分析函数的初步理解Oracle lead 和 lga 两个函数的用途和用法约束和排序数据110个Oracle常用函数总结(二)Virtual Columns in Oracle Database 11gOracle分析函数二——函数用法Oracle分析函数六——数据分布函数及报表函数发表评论 表 情：           评论内容：  用 户 名： 登录 注册 匿名评论 匿名用户验 证 码：   重新获得验证码     Copyright © a9529lty
Powered by CSDN Blog  

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/a9529lty/archive/2009/12/06/4950968.aspx