oracle求和函数的写法,Oracle over函数的学习笔记三 求和函数的使用

再来看求和函数SUM：

功能描述：该函数计算组中表达式的累积和。

sum函数如果不加order子句，窗口为每个partition分组内的数据记录集合，如果加了order子句，则默认窗口为rows between unbounded preceding and current row.

如果需要求该组所有的值，则需要显式的改变order的窗口，增加rows between unbounded preceding and unbounded following，或者干脆不要order by子句.

例如：表中有两个部门的记录：department_id＝10和20

1.原表信息：

SQL> break on deptno skip 1 --为效果更明显，把不同部门的数据隔段显示。SQL> select deptno,ename,sal2 from emp 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,sum(sal) over (order by ename)连续求和,

sum(sal) over ()总和,--此处sum(sal) over ()等同于sum(sal)，但每条--记录都要求一遍，很没效率 但和上面的sum(sal) over (order by ename)是不同的100*round(sal/sum(sal) over (),4) "份额(%)"—over()后面空白默认全部from emp;

DEPTNO ENAME   SAL连续求和总和份额(%)

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

20 ADAMS   1100    1100     29025    3.79

30 ALLEN    1600    2700      29025    5.51

30 BLAKE    2850    5550     29025    9.82

10CLARK 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

10MILLER 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

30WARD 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

,sum(sal) over (partition by deptno order by ename)部门连续求和,--各部门的薪水"连续"求和,

sum(sal) over (partition by deptno)部门总和, --部门统计总和100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",

sum(sal) over (order by deptno,ename)连续求和, --所有部门的薪水"连续"求和sum(sal) over ()总和, --此处sum(sal) over ()等同于sum(sal)，薪水总和100*round(sal/sum(sal) over (),4) "总份额(%)"

from emp

/

DEPTNO ENAME SAL部门连续求和 部门总和 部门份额(%)连续求和总和 总份额(%)

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

10CLARK  2450  2450 8750   28    2450 29025   8.44

KING  5000  7450   8750   57.14   7450 29025   17.23

MILLER1300  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 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

MARTIN1250  6650     9400   13.3   26275 29025   4.31

TURNER1500  8150     9400  15.9    2777529025   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,sum(sal) over (order by deptno,sal) sum 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 JAMES950   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--此处错,应该3000 12400FORD     3000     6000    15400--实际运行结果也如此10G

JONES      2975      8975    18375

ADAMS      1100     10075    19475

MITH      800      10875    20275

10KING      5000      5000    25275

CLARK      2450      7450    27725

MILLER      1300      8750    29025

已选择14行。

6.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 ENAMESALSUM1SUM2SUM3bal%

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

10 CLARK24502450875037508.44

10KING500074508750875017.23

10MILLER13008750875013004.48

20 ADAMS110011001087519003.79

20 FORD30004100108751087510.34

20JONES2975707510875487510.25

20 SCOTT300010075108751087510.34

20SMITH800 10875108758002.76

30 ALLEN16001600940065505.51

30 BLAKE28504450940094009.82

30 JAMES950540094009503.27

30 MARTIN12506650940034504.31

30TURNER15008150940049505.17

30 WARD12509400940034504.31

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

SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,sum(sal) over (order by deptno,sal) sum from emp 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行