oracle中over函数

来源：互联网发布：百度云盘老是网络异常编辑：程序博客网时间：2024/04/27 18:00

下面以scott用户为例：

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987-5-23 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

注意over()条件的不同，使用sum(sal) over(order by ename) 查询员工的sal“连续”求和

SQL> select deptno,ename,sal,sum(sal) over(order by ename) from emp;

DEPTNO ENAME SAL SUM(SAL)OVER(ORDERBYENAME)

------ ---------- --------- --------------------------

20 ADAMS 1100.00 1100

30 ALLEN 1600.00 2700

30 BLAKE 2850.00 5550

10 CLARK 2450.00 8000

20 FORD 3000.00 11000

30 JAMES 950.00 11950

20 JONES 2975.00 14925

10 KING 5000.00 19925

30 MARTIN 1250.00 21175

10 MILLER 1300.00 22475

20 SCOTT 3000.00 25475

20 SMITH 800.00 26275

30 TURNER 1500.00 27775

30 WARD 1250.00 29025

先按照ename排序，然后连续求和。

如果over()里面的内容是空的等同于sum(sal) :

SQL> select deptno,ename,sal,sum(sal) over(), sum(sal) over(order by ename) from emp;

DEPTNO ENAME SAL SUM(SAL)OVER() SUM(SAL)OVER(ORDERBYENAME)

------ ---------- --------- -------------- --------------------------

20 ADAMS 1100.00 29025 1100

30 ALLEN 1600.00 29025 2700

30 BLAKE 2850.00 29025 5550

10 CLARK 2450.00 29025 8000

20 FORD 3000.00 29025 11000

30 JAMES 950.00 29025 11950

20 JONES 2975.00 29025 14925

10 KING 5000.00 29025 19925

30 MARTIN 1250.00 29025 21175

10 MILLER 1300.00 29025 22475

20 SCOTT 3000.00 29025 25475

20 SMITH 800.00 29025 26275

30 TURNER 1500.00 29025 27775

30 WARD 1250.00 29025 29025

sum(sal) over(partition by deptno)按照部门编号进行查询:

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno) from emp;

DEPTNO ENAME SAL SUM(SAL)OVER(PARTITIONBYDEPTNO

------ ---------- --------- ------------------------------

10 CLARK 2450.00 8750

10 KING 5000.00 8750

10 MILLER 1300.00 8750

20 JONES 2975.00 10875

20 FORD 3000.00 10875

20 ADAMS 1100.00 10875

20 SMITH 800.00 10875

20 SCOTT 3000.00 10875

30 WARD 1250.00 9400

30 TURNER 1500.00 9400

30 ALLEN 1600.00 9400

30 JAMES 950.00 9400

30 BLAKE 2850.00 9400

30 MARTIN 1250.00 9400

sum(sal) over(order by deptno,ename)不按部门“连续”求总和:

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno,ename) from emp;

DEPTNO ENAME SAL SUM(SAL)OVER(PARTITIONBYDEPTNO

------ ---------- --------- ------------------------------

10 CLARK 2450.00 2450

10 KING 5000.00 5000

10 MILLER 1300.00 1300

20 ADAMS 1100.00 1100

20 FORD 3000.00 3000

20 JONES 2975.00 2975

20 SCOTT 3000.00 3000

20 SMITH 800.00 800

30 ALLEN 1600.00 1600

30 BLAKE 2850.00 2850

30 JAMES 950.00 950

30 MARTIN 1250.00 1250

30 TURNER 1500.00 1500

30 WARD 1250.00 1250

sum(sal) over(partition by deptno order by ename) 按部门连续求和:

SQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by ename) from emp;

DEPTNO ENAME SAL SUM(SAL)OVER(PARTITIONBYDEPTNO

------ ---------- --------- ------------------------------

10 CLARK 2450.00 2450

10 KING 5000.00 7450

10 MILLER 1300.00 8750

20 ADAMS 1100.00 1100

20 FORD 3000.00 4100

20 JONES 2975.00 7075

20 SCOTT 3000.00 10075

20 SMITH 800.00 10875

30 ALLEN 1600.00 1600

30 BLAKE 2850.00 4450

30 JAMES 950.00 5400

30 MARTIN 1250.00 6650

30 TURNER 1500.00 8150

30 WARD 1250.00 9400

总的效果:

SQL> select deptno,ename,sal,

2 sum(sal) over (partition by deptno order by ename) "部门连续求和",

3 sum(sal) over (partition by deptno) "部门总和",

4 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额",

5 sum(sal) over (order by deptno,ename) "连续求和",

6 sum(sal) over () "总和",

7 100*round(sal/sum(sal) over (),4) "总份额(%)" from emp;

DEPTNO ENAME            SAL 部门连续求和   部门总和   部门份额   连续求和       总和总份额(%)
------ ---------- --------- ------------ ---------- ---------- ---------- ---------- ----------
    10 CLARK        2450.00         2450       8750         28       2450      29025       8.44
    10 KING         5000.00         7450       8750      57.14       7450      29025      17.23
    10 MILLER       1300.00         8750       8750      14.86       8750      29025       4.48
    20 ADAMS        1100.00         1100      10875      10.11       9850      29025       3.79
    20 FORD         3000.00         4100      10875      27.59      12850      29025      10.34
    20 JONES        2975.00         7075      10875      27.36      15825      29025      10.25
    20 SCOTT        3000.00        10075      10875      27.59      18825      29025      10.34
    20 SMITH         800.00        10875      10875       7.36      19625      29025       2.76
    30 ALLEN        1600.00         1600       9400      17.02      21225      29025       5.51
    30 BLAKE        2850.00         4450       9400      30.32      24075      29025       9.82
    30 JAMES         950.00         5400       9400      10.11      25025      29025       3.27
    30 MARTIN       1250.00         6650       9400       13.3      26275      29025       4.31
    30 TURNER       1500.00         8150       9400      15.96      27775      29025       5.17
    30 WARD         1250.00         9400       9400       13.3      29025      29025       4.31

oracle over()函数,从oracle 8i开始支持,后面的版本支持的比较好.
通常在做统计分析时我们都想尽可能多滴选择出原始列和统计值列,但是这样group by后面就必须跟随更多的列,使用分析函数可以避免使用group by时选择出来的列名必须出现在group by列表中的痛苦.
--1、over() 注(9i下over括号内必须有内容,不允许为空,本文所有示例均在10g下运行的)
--所有人的总工资
select a.empno, a.ename, sum(a.sal) over() total from emp a;
EMPNO ENAME TOTAL
7369 SMITH 29025
7499 ALLEN 29025
7521 WARD 29025
7566 JONES 29025
7654 MARTIN 29025
7698 BLAKE 29025
7782 CLARK 29025
7788 SCOTT 29025
7839 KING 29025
7844 TURNER 29025
7876 ADAMS 29025
7900 JAMES 29025
7902 FORD 29025
7934 MILLER 29025
--2、over(partition by ...) 分组统计
--统计部门的平均工资
select a.empno,
a.ename,
b.dname,
to_char(round(avg(a.sal) over(partition by b.dname), 2),'$999,999,999.99') dept_avg
from emp a, dept b
where a.deptno = b.deptno;
EMPNO ENAME DNAME DEPT_AVG
7934 MILLER ACCOUNTING $2,916.67
7839 KING ACCOUNTING $2,916.67
7782 CLARK ACCOUNTING $2,916.67
7876 ADAMS RESEARCH $2,175.00
7902 FORD RESEARCH $2,175.00
7566 JONES RESEARCH $2,175.00
7369 SMITH RESEARCH $2,175.00
7788 SCOTT RESEARCH $2,175.00
7521 WARD SALES $1,566.67
7844 TURNER SALES $1,566.67
7499 ALLEN SALES $1,566.67
7900 JAMES SALES $1,566.67
7698 BLAKE SALES $1,566.67
7654 MARTIN SALES $1,566.67
--查询出管理员工人数最多的人的名字和他管理的人的名字
select b.ename, t.ename, t.mgr, t.cnt
from (select a.empno,
a.ename,
a.mgr,
count(1) over(partition by a.mgr) cnt
from emp a) t,
emp b
where t.mgr = b.empno;
ENAME ENAME MGR CNT
JONES SCOTT 7566 2
JONES FORD 7566 2
BLAKE WARD 7698 5
BLAKE TURNER 7698 5
BLAKE ALLEN 7698 5
BLAKE JAMES 7698 5
BLAKE MARTIN 7698 5
CLARK MILLER 7782 1
SCOTT ADAMS 7788 1
KING BLAKE 7839 3
KING JONES 7839 3
KING CLARK 7839 3
FORD SMITH 7902 1
--3、over(order by ...) 排序统计
select a.empno,
a.deptno,
a.ename,
a.sal,
sum(a.sal) over(order by a.ename) sum
from emp a;
EMPNO DEPTNO ENAME SAL SUM
7876 20 ADAMS 1100.00 1100
7499 30 ALLEN 1600.00 2700
7698 30 BLAKE 2850.00 5550
7782 10 CLARK 2450.00 8000
7902 20 FORD 3000.00 11000
7900 30 JAMES 950.00 11950
7566 20 JONES 2975.00 14925
7839 10 KING 5000.00 19925
7654 30 MARTIN 1250.00 21175
7934 10 MILLER 1300.00 22475
7788 20 SCOTT 3000.00 25475
7369 20 SMITH 800.00 26275
7844 30 TURNER 1500.00 27775
7521 30 WARD 1250.00 29025
--4、over(partition by ... order by ...) 分组排序统计
--统计各部门薪水前三名的人员
select t.*
from (select rank() over(partition by b.dname order by a.sal desc) rk,
a.empno,
a.ename,
b.dname,
a.sal
from emp a, dept b
where a.deptno = b.deptno) t
where t.rk <= 3;
RK EMPNO ENAME DNAME SAL
1 7839 KING ACCOUNTING 5000.00
2 7782 CLARK ACCOUNTING 2450.00
3 7934 MILLER ACCOUNTING 1300.00
1 7902 FORD RESEARCH 3000.00
1 7788 SCOTT RESEARCH 3000.00
3 7566 JONES RESEARCH 2975.00
1 7698 BLAKE SALES 2850.00
2 7499 ALLEN SALES 1600.00
3 7844 TURNER SALES 1500.00

Oracle over函数

SQL code:

sql over的作用及用法
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序,
其中PARTITION BY 为分组字段，ORDER BY 指定排序字段

over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
例如：employees表中，有两个部门的记录：department_id ＝10和20
select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。

SQL code:
WITH
OBJ AS(
SELECT
name, type

目的：以oracle自带的scott模式为测试环境，主要通过试验体会分析函数的用法。

类似 sum(...) over ... 的使用

1.原表信息：

SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
2 from emp
3 order by deptno;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
           KING             5000
           MILLER           1300

        20 SMITH             800
           ADAMS            1100
           FORD             3000
           SCOTT            3000
           JONES            2975

        30 ALLEN            1600
           BLAKE            2850
           MARTIN           1250
           JAMES             950
           TURNER           1500
           WARD             1250

已选择14行。

2.先来一个简单的，注意over(...)条件的不同，
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意over (order by ename)如果没有order by 子句，求和就不是“连续”的，
放在一起，体会一下不同之处：

SQL> break on '' -- 取消数据分段显示
SQL> select deptno,ename,sal,
2 sum(sal) over (order by ename) 连续求和,
3 sum(sal) over () 总和,                  -- 此处sum(sal) over () 等同于sum(sal)
4 100*round(sal/sum(sal) over (),4) "份额(%)"
5 from emp
6 /

    DEPTNO ENAME             SAL   连续求和       总和    份额(%)
---------- ---------- ---------- ---------- ---------- ----------
        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
        20 SMITH             800      26275      29025       2.76
        30 TURNER           1500      27775      29025       5.17
        30 WARD             1250      29025      29025       4.31

已选择14行。

3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同，
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。

SQL> break on deptno skip 1 -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
3 sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
4 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
5 sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
6 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
7 100*round(sal/sum(sal) over (),4) "总份额(%)"
8 from emp
9 /

DEPTNO ENAME    SAL 部门连续求和   部门总和部门份额(%)   连续求和   总和总份额(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
    10 CLARK   2450         2450       8750          28       2450 29025       8.44
       KING    5000         7450       8750       57.14       7450 29025      17.23
       MILLER 1300         8750       8750       14.86       8750 29025       4.48

    20 ADAMS   1100         1100     10875       10.11       9850 29025       3.79
       FORD    3000         4100      10875       27.59      12850 29025      10.34
       JONES   2975         7075      10875       27.36      15825 29025      10.25
       SCOTT   3000        10075      10875       27.59      18825 29025      10.34
       SMITH    800        10875      10875        7.36      19625 29025       2.76

    30 ALLEN   1600         1600       9400       17.02      21225 29025       5.51
       BLAKE   2850         4450       9400       30.32      24075 29025       9.82
       JAMES    950         5400       9400       10.11      25025 29025       3.27
       MARTIN 1250         6650       9400        13.3      26275 29025       4.31
       TURNER 1500         8150       9400       15.96      27775 29025       5.17
       WARD    1250         9400       9400        13.3      29025 29025       4.31

已选择14行。

4.来一个综合的例子，求和规则有按部门分区的，有不分区的例子
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp;

    DEPTNO ENAME             SAL   DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
        10 MILLER           1300       1300       1300
           CLARK            2450       3750       3750
           KING             5000       8750       8750

        20 SMITH             800        800       9550
           ADAMS            1100       1900      10650
           JONES            2975       4875      13625
           SCOTT            3000      10875      19625
           FORD             3000      10875      19625

        30 JAMES             950        950      20575
           WARD             1250       3450      23075
           MARTIN           1250       3450      23075
           TURNER           1500       4950      24575
           ALLEN            1600       6550      26175
           BLAKE            2850       9400      29025

已选择14行。

5.来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。

SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,
3 sum(sal) over (order by deptno desc,sal desc) sum
4 from emp;

    DEPTNO ENAME             SAL   DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
        30 BLAKE            2850       2850       2850
           ALLEN            1600       4450       4450
           TURNER           1500       5950       5950
           WARD             1250       8450       8450
           MARTIN           1250       8450       8450
           JAMES             950       9400       9400

        20 SCOTT            3000       6000      15400
           FORD             3000       6000      15400
           JONES            2975       8975      18375
           ADAMS            1100      10075      19475
       MITH             800      10875      20275

        10 KING             5000       5000      25275
           CLARK            2450       7450      27725
           MILLER           1300       8750      29025

已选择14行。

6.体会：在"... from emp;"后面不要加order by 子句，使用的分析函数的(partition by deptno order by sal)
里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了。如：

SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp
4 order by deptno desc;

    DEPTNO ENAME             SAL   DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
        30 JAMES             950        950      20575
           WARD             1250       3450      23075
           MARTIN           1250       3450      23075
           TURNER           1500       4950      24575
           ALLEN            1600       6550      26175
           BLAKE            2850       9400      29025

        20 SMITH             800        800       9550
           ADAMS            1100       1900      10650
           JONES            2975       4875      13625
           SCOTT            3000      10875      19625
           FORD             3000      10875      19625

        10 MILLER           1300       1300       1300
           CLARK            2450       3750       3750
           KING             5000       8750       8750

已选择14行

==================================================================
利用over实现的分页功能：
--假设code1,code2为用来分页的KEY，每页显示5第数据
select code1,code2,code3,
ceil(count(*) over(partition by code1,code2 order by rownum)/5),
count(*) over(partition by code1,code2)
from ma_kbn order by code1,code2

==================================================================

转自：http://www.diybl.com/course/7_databases/oracle/oraclejs/20091005/177878.html

http://www.cnblogs.com/umen/archive/2011/04/11/2012136.html

http://penggle.iteye.com/blog/1122467

http://zhaisx.iteye.com/blog/774206