【转】sum(XXX) over(partition by YYY order by ZZZ)

原文地址：http://blog.sina.com.cn/s/blog_54eeb5d901000a6p.html

 

 

SQL> select n1,v1,nid,sum(nid) over(order by nid) as sum
  2  from t1;

 

        N1 V1                NID        SUM
---------- ---------- ---------- ----------
         1 aa                 61         61
         2 aa                 62        123
         3 aa                 63        186
         4 aa                 64        250

取nid列的累积和，即下面以emp表为例的按部门“连续”求总和

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

按v1分组取nid的和

 

SQL> select v1,sum(nid) over (partition by v1 order by v1) as sum_nid from t1;

 

V1            SUM_NID
---------- ----------
aa                187
aa                187
aa                187
bb                 83

按v1分组取nid的和，并重复行只显示一行

 

SQL> select distinct * from (select v1,sum(nid) over (partition by v1) as sum_nid from t1);

 

V1            SUM_NID
---------- ----------
aa                187
bb                 83

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

再以emp为例

使用子分区查出各部门薪水连续的总和。注意按部门分区 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 () 总和,
        sum(sal) over (order by deptno,ename) 连续求和,
        100*round(sal/sum(sal) over (),4) 总份额
    from emp;

 

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

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

        10 CLARK            2450         2450       8750         28      29025       2450      8.44

           KING             5000         7450       8750      57.14      29025       7450     17.23

           MILLER           1300         8750       8750      14.86      29025       8750      4.48

        20 ADAMS            1100         1100      10875      10.11      29025       9850      3.79

           FORD             3000         4100      10875      27.59      29025      12850     10.34

           JONES            2975         7075      10875      27.36      29025      15825     10.25

           SCOTT            3000        10075      10875      27.59      29025      18825     10.34

           SMITH             800        10875      10875       7.36      29025      19625      2.76

        30 ALLEN            1600         1600       9400      17.02      29025      21225      5.51

           BLAKE            2850         4450       9400      30.32      29025      24075      9.82

           JAMES             950         5400       9400      10.11      29025      25025      3.27

           MARTIN           1250         6650       9400       13.3      29025      26275      4.31

           TURNER           1500         8150       9400      15.96      29025      27775      5.17

           WARD             1250         9400       9400       13.3      29025      29025      4.31

 

已选择14行。

 

综合的例子，求和规则有按部门分区的，有不分区的例子

 

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

 

    DEPTNO ENAME         SUM_SAL SUM_DEPT_SAL
---------- ---------- ---------- ------------
        10 MILLER           1300         1300
           CLARK            3750         3750
           KING             8750         8750

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

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

已选择14行。

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

 

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

 

    DEPTNO ENAME             SAL SUM_SAL_ORDER        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行。