公司培訓(xùn)oracle課件chap03-分析函數(shù)講解

1、一樹結(jié)構(gòu)查Selectempno,mgr,enamefromemp Connect by prior empno=mgr;Selectlevel,empno,mgr,enamefromemp Connect by prior empno = mgr;select一樹結(jié)構(gòu)查Selectempno,mgr,enamefromemp Connect by prior empno=mgr;Selectlevel,empno,mgr,enamefromemp Connect by prior empno = mgr;selectfrom startwithconnectbypriororderby se

2、lectfrom startwithempno=(selectempnofromempwheremgrisconnectbypriororderby selectenamefromempgroupbyenamehavingcolumnnameformatselectlevel,empno,mgr,lpad(,3*level-1)|enamefrom startwithempno=(selectempnofromempwheremgrisconnectbypriororderby selectename level,sys_connect_by_path(ename,/)from startse

3、lectename level,sys_connect_by_path(ename,/)from startwithconnectbypriororderby selectenamefromempstartwithconnectbyprior 二、 分析函數(shù)（oracle8.1.6 以后版本支持），為解決Calculate a running total, Findpercentageswithinagroup,Top-Nqueries,Computeamoving 標(biāo)準(zhǔn)的 PL/SQL 可以解決大部分問題然而性能不盡人意分析函數(shù)就是標(biāo)準(zhǔn) SQL的語言插件，不僅編碼簡單，而且性能比純 SQL

4、和 PL/SQL 好。分析函數(shù)已經(jīng)獲ANSI SQLSQL（如：時(shí)間）order by所有的表連接(joins)，groupby,havingselectorder by。OVER (Query-Partition-Order-By- AVG,CORR,COVAR_POP,COUNT,CUME_DIST,_VALUE,LAG,LAST,LAST_VALUE,LEAD,OVER (Query-Partition-Order-By- AVG,CORR,COVAR_POP,COUNT,CUME_DIST,_VALUE,LAG,LAST,LAST_VALUE,LEAD,MAX,MIN,NTILE,PE

5、RCENTILE_CONT, PERCENTILE_DISC, , RATIO_TO_REPORT, STDDEV_POP, , SUM, VAR_POP, , 03partition by和Order byOrderby |Nulls LastWindowsing-clausen andUnbonded preceding1 Unbondedfollowing1 Current row 當(dāng)前行RowsRangeselectdeptno,sum(sal)asdept_sal,sum(sum(sal)over()asfrom groupbySQLSELECTenameEname,deptnoDe

6、ptno,sal ROW_NUMBER()OVER(PARTITIONSQLSELECTenameEname,deptnoDeptno,sal ROW_NUMBER()OVER(PARTITIONBYdeptnoORDERBYsal)FROMORDERBY1: Calculate a TotalSELECT ename Ename, deptno Deptno, sal Sal, SUM(sal) OVER (ORDER BY deptno, ename) Running Total, -根據(jù)deptno,nameSUM(SAL)OVER(PARTITIONBYdeptnoORDERBYena

7、me)Dept-先deptno分區(qū),SUM(SAL)OVER(PARTITIONBYdeptnoORDERBYename)Dept-先deptno分區(qū),再ename排序后結(jié)果集,ROW_NUMBER()OVER(PARTITIONBYdeptnoORDERBYENAME)FROM emp -行數(shù)統(tǒng)計(jì) ORDERBYdeptnoenameEnameDeptnoSalRunningTotaDeptTotal CLARK 10 2450.00 2450 2450 1KING 10 5000.00 7450 7450 MILLER 10 1300.00 8750 8750 ADAMS 20 1100.

8、00 9850 1100 FORD 20 3000.00 12850 4100 312950.00 25025 5400 MARTIN 30 1250.00 26275 6650 TURNER 30 1500.00 27775 8150 WARD 30 1250.00 29025 9400 OVER (ORDER BY deptno, OVER(PARTITIONBYdeptnoORDERBYename)表示針對公司每個(gè)部3.ROW_NUMBER()OVER(PARTITIONBYdeptnoORDERBYENAME2:Top-N Queries(NSELECT*FROMSELECTdeptn

9、o,ename,sal,ROW_NUMBER() OVER (PARTITIONBYdeptnoORDERBYsal)Top3FROM)WHERETop3=DEPTNO ENAME SAL 123456789KING 5000.00 CLARK 2450.00 MILLER 1300.00 SCOTT 3000.00 FORD 3000.00 23DEPTNO ENAME SAL 123456789KING 5000.00 CLARK 2450.00 MILLER 1300.00 SCOTT 3000.00 FORD 3000.00 2312TURNER 1500.00 3（沒有并列的名次例子

10、3：Top-N Queries(前N3-1:SELECT*FROMSELECTdeptno,ename,sal, () OVER (PARTITIONBYdeptnoORDERBYsal)TopNFROM)WHERETopN=ORDERBYdeptno,sal,DEPTNO ENAME SAL 123456789KING 5000.00 CLARK 2450.00 MILLER 1300.00 SCOTT 3000.00 1FORD 3000.00 1231210 30 TURNER 3名的員工的資料（并列的名次3-2:SELECT*FROM,SELECTdeptno,ename,sal,()

11、 OVER (PARTITIONBYdeptnoORDERBYsal)SELECTdeptno,ename,sal,() OVER (PARTITIONBYdeptnoORDERBYsal)Top3FROM)WHERETop3 1981-3-1HIREDATE 1001981-3-1981-4-1981-5-1981-6- 1001981-3-1981-4-1981-5-1981-6-MILLER10ADAMS 1100.00 FORD 3000.00 2975.00 3000.00 800.00 SMITH 1600.00JAMES 2850.00 950.00 MARTIN 1250.00

12、 TURNER 1500.00 WARD 1250.00 SELECTdeptno,SUM(sal)FROM GROUP BY ROLLUP(deptno)DEPTNO SELECTdeptno,ename,SUM(sal)FROMGROUPBYKING CLARK MILLER KING CLARK MILLER 8750 -累加當(dāng)前部門(10)FORD WARD 10-3:SELECTdeptno,job,SUM(sal)FROMemp GROUP BY CUBE(deptno,job)ORDERBYdeptno,jobNULLS DEPTNO JOB CLERK MANAGER YST

13、6000CLERK MANAGER CLERK MANAGER SALESMAN YST 6000CLERK MANAGER SALESMAN 2902510-4:與10-3等價(jià).用到了錄是利用該字段得出的函數(shù)就會返回1,否則返回SELECT decode(GROU(deptno),1,AllDept,to_char(deptno)deptno, (job),1,ALL Job,job) job,SUMSALESMAN 2902510-4:與10-3等價(jià).用到了錄是利用該字段得出的函數(shù)就會返回1,否則返回SELECT decode(GROU(deptno),1,AllDept,to_char(

14、deptno)deptno, (job),1,ALL Job,job) job,SUM(sal)sum_t FROM empGROUPBYORDERBYdeptno,jobNULLSDEPTNO JOB ALL Job CLERK ALL CLERK MANAGER ALL Job CLERK MANAGER SALESMAN ALL Job YST CLERK MANAGER SALESMAN 11. ratio_to_reportSELECTARTITIONBYdeptno),3)ratio FROM empGROUPBYDEPTNO ENAME SUM(SAL) CLARK 2450 K

15、ING 5000 MILLER 1300 ADAMS 1100 FORD 3000 2975 3000 800 DEPTNO ENAME SUM(SAL) CLARK 2450 KING 5000 MILLER 1300 ADAMS 1100 FORD 3000 2975 3000 800 1600 2850 950 MARTIN 1250 TURNER 1500 WARD 1250 例子功能說明SELECTdeptno,ename,empno,sal,MIN(sal) ORDERBYempno)over(PARTITIONBYdeptno)FROMDEPTNO ENAME EMPNO SAL CLARK 7782 2450.00 KING 7839 5000.00 MILLER 7934 1300.00 SMITH 7369 800.00 ADAMS 7876 1100.00 FORD 7902 3000.00 MARTIN 7654 1250.00 JAMES 7900 950.00 TURNER 7844 1500.00 WARD 7521 1250.00 MINytic functionsunboundedratio面denserowsbet rows n 1 preceding and 1 followi