Oracle中over函数的使用示例 (转)
?
over()函数是Oracle的分析函数：语法如下
函数名（[参数]） over（[分区子句][排序子句]）
?
分区子句：partition by
排序子句：order by
?
?
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
? ?? ?? ???SMITH? ?? ?? ?? ? 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行
<script type="text/javascript"></script>
