Oracle分析函數(shù)原理及應(yīng)用.doc

《Oracle分析函數(shù)原理及應(yīng)用.doc》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。

1、分析函數(shù)(OVER)1分析函數(shù)2(Rank,Dense_rank,row_number)6分析函數(shù)3(Top/BottomN、First/Last、NTile)10窗口函數(shù)14報表函數(shù)20分析函數(shù)總結(jié)2226個分析函數(shù)24PLSQL開發(fā)筆記和小結(jié)28分析函數(shù)簡述60分析函數(shù)(OVER)目錄：===============================================1.Oracle分析函數(shù)簡介2.Oracle分析函數(shù)簡單實例3.分析函數(shù)OVER解析一、Oracle分析函數(shù)簡介：

2、在日常的生產(chǎn)環(huán)境中，我們接觸得比較多的是OLTP系統(tǒng)(即OnlineTransactionProcess)，這些系統(tǒng)的特點是具備實時要求，或者至少說對響應(yīng)的時間多長有一定的要求；其次這些系統(tǒng)的業(yè)務(wù)邏輯一般比較復(fù)雜，可能需要經(jīng)過多次的運算。比如我們經(jīng)常接觸到的電子商城。在這些系統(tǒng)之外，還有一種稱之為OLAP的系統(tǒng)(即OnlineAanalyseProcess)，這些系統(tǒng)一般用于系統(tǒng)決策使用。通常和數(shù)據(jù)倉庫、數(shù)據(jù)分析、數(shù)據(jù)挖掘等概念聯(lián)系在一起。這些系統(tǒng)的特點是數(shù)據(jù)量大，對實時響應(yīng)的要求不高或者根本不

3、關(guān)注這方面的要求，以查詢、統(tǒng)計操作為主。我們來看看下面的幾個典型例子：①查找上一年度各個銷售區(qū)域排名前10的員工②按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶③查找上一年度銷售最差的部門所在的區(qū)域④查找上一年度銷售最好和最差的產(chǎn)品我們看看上面的幾個例子就可以感覺到這幾個查詢和我們?nèi)粘Ｓ龅降牟樵冇行┎煌唧w有：①需要對同樣的數(shù)據(jù)進(jìn)行不同級別的聚合操作②需要在表內(nèi)將多條數(shù)據(jù)和同一條數(shù)據(jù)進(jìn)行多次的比較③需要在排序完的結(jié)果集上進(jìn)行額外的過濾操作分析函數(shù)語法:FUNCTION_NAME(

4、rgument>,...)OVER()例:sum(sal)over(partitionbydeptnoorderbyename)new_aliassum就是函數(shù)名(sal)是分析函數(shù)的參數(shù),每個函數(shù)有0~3個參數(shù),參數(shù)可以是表達(dá)式,例如:sum(sal+comm)over是一個關(guān)鍵字,用于標(biāo)識分析函數(shù),否則查詢分析器不能區(qū)別sum()聚集函數(shù)和sum()分析函數(shù)partit

5、ionbydeptno是可選的分區(qū)子句,如果不存在任何分區(qū)子句,則全部的結(jié)果集可看作一個單一的大區(qū)orderbyename是可選的orderby子句,有些函數(shù)需要它,有些則不需要.依靠已排序數(shù)據(jù)的那些函數(shù),如:用于訪問結(jié)果集中前一行和后一行的LAG和LEAD,必須使用,其它函數(shù),如AVG,則不需要.在使用了任何排序的開窗函數(shù)時,該子句是強(qiáng)制性的,它指定了在計算分析函數(shù)時一組內(nèi)的數(shù)據(jù)是如何排序的.1)FUNCTION子句ORACLE提供了26個分析函數(shù),按功能分5類分析函數(shù)分類等級(ranking

6、)函數(shù):用于尋找前N種查詢開窗(windowing)函數(shù):用于計算不同的累計,如SUM,COUNT,AVG,MIN,MAX等,作用于數(shù)據(jù)的一個窗口上例:sum(t.sal)over(orderbyt.deptno,t.ename)running_total,sum(t.sal)over(partitionbyt.deptnoorderbyt.ename)department_total制表(reporting)函數(shù):與開窗函數(shù)同名,作用于一個分區(qū)或一組上的所有列例:sum(t.sal)over(

7、)running_total2,sum(t.sal)over(partitionbyt.deptno)department_total2制表函數(shù)與開窗函數(shù)的關(guān)鍵不同之處在于OVER語句上缺少一個ORDERBY子句!LAG,LEAD函數(shù):這類函數(shù)允許在結(jié)果集中向前或向后檢索值,為了避免數(shù)據(jù)的自連接,它們是非常用用的.VAR_POP,VAR_SAMP,STDEV_POPE及線性的衰減函數(shù):計算任何未排序分區(qū)的統(tǒng)計值2)PARTITION子句按照表達(dá)式分區(qū)(就是分組),如果省略了分區(qū)子句,則全部的結(jié)果

8、集被看作是一個單一的組3)ORDERBY子句分析函數(shù)中ORDER?BY的存在將添加一個默認(rèn)的開窗子句,這意味著計算中所使用的行的集合是當(dāng)前分區(qū)中當(dāng)前行和前面所有行,沒有ORDERBY時,默認(rèn)的窗口是全部的分區(qū)?在Orderby子句后可以添加nullslast,如:orderbycommdescnullslast???表示排序時忽略comm列為空的行.???4)WINDOWING子句用于定義分析函數(shù)將在其上操作的行的集合Windowing子句給出了一個定義變化或固定的數(shù)據(jù)窗口的方法,分析函數(shù)將對這