Oracle Over函数

jxdiscuz

　　环境：windows 2000 server + Oracle8.1.7 + sql*plus
　　

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


Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。也就是说对主查询的每行都返回一个over函数运算得出的聚合值。比如，直接使用sum函数，只返回一行函数，如果与其它非聚合的列一起返回就要报错，但是over函数处理后的聚合值，能够跟普通列，并列存储，每列一个聚合值。　　类似 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
　　
        10 KING             5000
　　
        10  MILLER          1300
　　

　　
        20 SMITH             800
　　
        20 ADAMS            1100
　　
        20 FORD             3000
　　
        20 SCOTT            3000
　　
        20 JONES            2975
　　

　　
        30 ALLEN            1600
　　
        30 BLAKE            2850
　　
        30 MARTIN           1250
　　
        30 JAMES             950
　　
        30 TURNER           1500
　　
        30 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 () 总和,
　　
  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行。
　　

　　注：sum(sal) over (order by ename) 和
　　sum(sal) over () 的区别 可以看见
　　可以看见：“sum(sal) over (order by ename)”的作用是：“连续求和”字段是“sal”字段依次累加起来的
　　而“sum(sal) over ()”相当于“sum(sal)”，是将所有列的sal字段求和，由于没有在over函数中给参数。但是这里只是相当于“sum(sal)”而不相同，因为sum(sal)只会返回一条结果，而不会像“sum(sal) over ()”一样每条记录对应一个求和结果。
　　

　　
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)。
　　

　　注：sum(sal) over (partition by deptno)的详细意思是：依据deptno进行分区域(逻辑上的，并不会将其在查询结果中显示的体现出他的区域)，把deptno相同的员工的工资求和
　　sum(sal) over (order by deptno)的详细意思是：按deptno升序排列，然后对所有员工的工资依次累加
　　
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
　　
    10 KING    5000         7450       8750       57.14       7450  29025      17.23
　　
    10 MILLER  1300         8750       8750       14.86       8750  29025       4.48
　　

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

　　
    30 ALLEN   1600         1600       9400       17.02      21225  29025       5.51
　　
    30 BLAKE   2850         4450       9400       30.32      24075  29025       9.82
　　
    30 JAMES    950         5400       9400       10.11      25025  29025       3.27
　　
    30 MARTIN  1250         6650       9400        13.3      26275  29025       4.31
　　
    30 TURNER  1500         8150       9400       15.96      27775  29025       5.17
　　
    30 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
　　
        10 CLARK            2450       3750       3750
　　
        10 KING             5000       8750       8750
　　

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

　　
        30 JAMES             950        950      20575
　　
        30 WARD             1250       3450      23075
　　
        30 MARTIN           1250       3450      23075
　　
        30 TURNER           1500       4950      24575
　　
        30 ALLEN            1600       6550      26175
　　
        30 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
　　
        30 ALLEN            1600       4450       4450
　　
        30 TURNER           1500       5950       5950
　　
        30 WARD             1250       8450       8450
　　
        30 MARTIN           1250       8450       8450
　　
        30 JAMES             950       9400       9400
　　

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

　　
        10 KING             5000       5000      25275
　　
        10 CLARK            2450       7450      27725
　　
        10 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
　　
        30 WARD             1250       3450      23075
　　
        30 MARTIN           1250       3450      23075
　　
        30 TURNER           1500       4950      24575
　　
        30 ALLEN            1600       6550      26175
　　
        30 BLAKE            2850       9400      29025
　　

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

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

　　

　　
已选择14行
　　注：文中绝大部分是摘抄至其它博客，我只是做了相应修改和批注
　　

　　参考资料：
　　http://www.cnblogs.com/liguiqing/archive/2007/11/20/966003.html
　　http://www.blogjava.net/loocky/archive/2007/11/13/160213.html
　　

• select fid,
  
•        t.fsenduser,
  
•        t.fmbno,
  
•        to_char(t.fsendtime, 'yyyy-mm-dd hh24:MM:ss'),
  
•        rank() over(partition by t.fsenduser order by t.fsendtime)
  
•   from t_inf_smsoutbox2 t
  

　　

　　这句sql的意思是按照t.fsenderuser进行分区域，并将每一区域fsendtime字段从小大大排序，然后依次编号，rank()就是编号函数。但如果同一区域中fsendtime相同，他们的编号也就相同。注意假设有两个编号都唯一，那么接下来就为3.而如果想要接下来编号连续，也就是两个1过后是2，那么用dense_rank()函数。
　　

• select fid,
  
•        t.fsenduser,
  
•        t.fmbno,
  
•        to_char(t.fsendtime, 'yyyy-mm-dd hh24:MM:ss'),
  
•        row_number() over(partition by t.fsenduser order by t.fsendtime)
  
•   from t_inf_smsoutbox2 t
  

　　

　　效果同上，row_number在编号的时候会根据排序依次连续的编上序号，不会因为fsendtime相同，而编号相同