Oracle分析函数学习笔记3

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

目的：以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
  &nb

[1] [2] [3]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18