oracle中over()分析函数的用法

摘自： http://www.poluoluo.com/jzxy/201004/81921.html

 

百度文库也记载了oracle中over()分析函数的用法

在泡坛子的时候中无意中发现了这个函数，才知道oracle分析函数是如此的强大，其中over() 函数的用法又尤为的特别，所以将自己的研究结果记录一下。

　　个人理解：over() 函数 是对 分析函数的一种条件解释，直接点就是 给分析函数加条件吧。

　　在网上看见比较常用的就是 与 sum()、rank() 函数使用。接下来就用分析下两种函数结合over的用法。

　　以下测试使用的orace默认的 scott用户下的emp表数据。

　　1.sum()结合over()

　　Sql代码

　　select a.empno as 员工编号

　　,a.ename as 员工姓名

　　,a.deptno as 部门编号

　　,a.sal as 薪酬

　　,sum(sal) over (partition by deptno) 按部门求薪酬总和

　　from scott.emp a;

　　此段sql 执行的结果为：

　　员工编码 员工姓名  部门编号  薪酬  按部门求薪酬总和

　　7934        MILLER      10         1300          8750

　　7782        CLARK        10        2450         8750

　　7839        KING           10        5000         8750

　　7369        SMITH         20        800           10875

　　7876        ADAMS       20        1100         10875

　　7566        JONES         20       2975         10875

　　7788        SCOTT         20       3000         10875

　　7902        FORD           20       3000          10875

　　7900       JAMES          30       950             9400

　　7654       MARTIN        30       1250           9400

　　7521       WARD            30      1250           9400

　　7844       TURNER        30      1500           9400

　　7499       ALLEN            30      1600          9400

　　7698     BLAKE 30 2850 9400

　　可以从结果上看到sum()函数对部门区分进行了求和统计。其中“partition by”官方点的说法叫做"分区"，其实就是统计的范围条件。

　　下面在给把上面的sql语句改造下 给 over() 函数加上 “order by sal” 会看到一个更过瘾的效果：

　　Sql代码

　　select a.empno as 员工编号

　　,a.ename as 员工姓名

　　,a.deptno as 部门编号

　　,a.sal as 薪酬

　　,sum(sal) over (partition by deptno) 按部门求薪酬总和

　　,sum(sal) over (partition by deptno order by sal) 按部门累计薪酬

　　from scott.emp a;

　　结果为：

　　 员工编码   员工姓名  部门编号  薪酬  按部门求薪酬总和  按部门累计薪酬

　　7934           MILLER        10      1300               8750                  1300

　　7782          CLARK          10       2450              8750                   3750

　　7839          KING             10       5000               8750                  8750

　　7369          SMITH          20        800                10875                 800

　　7876           ADAMS        20       1100               10875                 1900

　　7566          JONES         20        2975               10875                 4875

　　7788          SCOTT         20        3000               10875               10875

　　7902          FORD           20        3000               10875               10875

　　7900         JAMES          30        950                  9400                   950

　　7654          MARTIN       30        1250                9400                  3450

　　7521         WARD           30        1250                9400                 3450

　　7844         TURNER       30       1500                 9400                 4950

　　7499          ALLEN         30        1600                9400                 6550

　　7698         BLAKE          30        2850                9400                 9400

　　从结果中可以看的 加了 “order by”后对 统计进行一个累加，这里个人理解为对统计范围规定了个统计顺利，一步一步的统计。

　　注：此sql语句结尾处不要加“order by”，应为使用的分析函数的(partition by deptno order by sal)

　　里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费解了。

　　2.rank()结合over()

　　rank函数是分级函数，这个函数必须与 over 函数使用，否则会报一个"缺少窗口函数的错"。我测试sql如下：

　　Sql代码

　　select a.empno as 员工编号,

　　a.sal as 薪资,

　　a.job as 岗位,

　　rank() OVER(partition by a.job ORDER BY a.sal desc) as 岗位薪资等级

　　from scott.emp a;

　　查询结果为:

　　员工编号 薪资 岗位 等级

　　7902      3000 ANALYST 1

　　7788      3000 ANALYST 1

　　7934      1300 CLERK 1

　　7876      1100 CLERK 2

　　7900       950 CLERK 3

　　7369 800 CLERK 4

　　7566 2975 MANAGER 1

　　7698 2850 MANAGER 2

　　7782 2450 MANAGER 3

　　7839 5000 PRESIDENT 1

　　7499 1600 SALESMAN 1

　　7844 1500 SALESMAN 2

　　7654 1250 SALESMAN 3

　　7521 1250 SALESMAN 3