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 MILLER1300;;;;;20 SMITH;;800 ADAMS;1100 FORD;;3000 SCOTT;3000 JONES;2975;;;;;30 ALLEN;1600 BLAKE;2850 MARTIN1250 JAMES;;950 TURNER1500 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 MARTIN1250;;;21175;;;29025;;;;4.31 10 MILLER1300;;;22475;;;29025;;;;4.48 20 SCOTT;3000;;;25475;;;29025;;;10.34 ;;;;;20 SMITH;;800;;;26275;;;29025;;;;2.76 30 TURNER1500;;;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 CLARK2450 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 ADAMS1100 1100;;;10875;;;;10.11;;;;9850; 29025;;;;3.79 ;;;;FORD;3000 4100;;;10875;;;;27.59;;;12850; 29025;;;10.34 JONES2975 7075;;;10875;;;;27.36;;;15825; 29025;;;10.25 SCOTT3000;;;;;10075;;;10875;;;;27.59;;;18825; 29025;;;10.34 SMITH;800;;;;;10875;;;10875;;;;;7.36;;;19625; 29025;;;;2.76;30 ALLEN1600 1600;;;;9400;;;;17.02;;;21225; 29025;;;;5.51 BLAKE2850 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;;SALDEPT_SUM;;;;;SUM---------- ---------- ---------- ---------- ---------- 10 MILLER1300;;;;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 MARTIN1250;;;;3450;;;23075 TURNER1500;;;;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;;SALDEPT_SUM;;;;;SUM---------- ---------- ---------- ---------- ---------- 30 BLAKE;2850;;;;2850;;;;2850 ALLEN;1600;;;;4450;;;;4450 TURNER1500;;;;5950;;;;5950 WARD;;1250;;;;8450;;;;8450 MARTIN1250;;;;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 MILLER1300;;;;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;;SALDEPT_SUM;;;;;SUM---------- ---------- ---------- ---------- ---------- 30 JAMES;;950;;;;;950;;;20575 WARD;;1250;;;;3450;;;23075 MARTIN1250;;;;3450;;;23075 TURNER1500;;;;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 MILLER1300;;;;1300;;;;1300 CLARK;2450;;;;3750;;;;3750 KING;;5000;;;;8750;;;;8750已選擇14行。