oracle分析函數(shù).doc

Oracle開發(fā)專題之：分析函數(shù)(OVER)1Oracle開發(fā)專題之：分析函數(shù)2(Rank, Dense_rank, row_number)6Oracle開發(fā)專題之：分析函數(shù)3(Top/Bottom N、First/Last、NTile)10Oracle開發(fā)專題之：窗口函數(shù)14Oracle開發(fā)專題之：報(bào)表函數(shù)20Oracle開發(fā)專題之：分析函數(shù)總結(jié)22Oracle開發(fā)專題之：26個(gè)分析函數(shù)24PLSQL開發(fā)筆記和小結(jié)28分析函數(shù)簡(jiǎn)述60說(shuō)明: 1)Oracle開發(fā)專題99%收集自: /pengpenglin/(偶補(bǔ)充了一點(diǎn)點(diǎn)1%);2) PLSQL開發(fā)筆記和小結(jié)收集自/cheneyfree/3)分析函數(shù)簡(jiǎn)述收集自/7607759/昆明小蟲/ 收集,并補(bǔ)充了一點(diǎn)點(diǎn)1%Oracle開發(fā)專題之：分析函數(shù)(OVER) 目錄：=1.Oracle分析函數(shù)簡(jiǎn)介2. Oracle分析函數(shù)簡(jiǎn)單實(shí)例3.分析函數(shù)OVER解析一、Oracle分析函數(shù)簡(jiǎn)介：在日常的生產(chǎn)環(huán)境中，我們接觸得比較多的是OLTP系統(tǒng)(即Online Transaction Process)，這些系統(tǒng)的特點(diǎn)是具備實(shí)時(shí)要求，或者至少說(shuō)對(duì)響應(yīng)的時(shí)間多長(zhǎng)有一定的要求；其次這些系統(tǒng)的業(yè)務(wù)邏輯一般比較復(fù)雜，可能需要經(jīng)過(guò)多次的運(yùn)算。比如我們經(jīng)常接觸到的電子商城。在這些系統(tǒng)之外，還有一種稱之為OLAP的系統(tǒng)(即Online Aanalyse Process)，這些系統(tǒng)一般用于系統(tǒng)決策使用。通常和數(shù)據(jù)倉(cāng)庫(kù)、數(shù)據(jù)分析、數(shù)據(jù)挖掘等概念聯(lián)系在一起。這些系統(tǒng)的特點(diǎn)是數(shù)據(jù)量大，對(duì)實(shí)時(shí)響應(yīng)的要求不高或者根本不關(guān)注這方面的要求，以查詢、統(tǒng)計(jì)操作為主。我們來(lái)看看下面的幾個(gè)典型例子：查找上一年度各個(gè)銷售區(qū)域排名前10的員工按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶查找上一年度銷售最差的部門所在的區(qū)域查找上一年度銷售最好和最差的產(chǎn)品我們看看上面的幾個(gè)例子就可以感覺到這幾個(gè)查詢和我們?nèi)粘Ｓ龅降牟樵冇行┎煌?，具體有：需要對(duì)同樣的數(shù)據(jù)進(jìn)行不同級(jí)別的聚合操作需要在表內(nèi)將多條數(shù)據(jù)和同一條數(shù)據(jù)進(jìn)行多次的比較需要在排序完的結(jié)果集上進(jìn)行額外的過(guò)濾操作分析函數(shù)語(yǔ)法:FUNCTION_NAME(,.)OVER()例:sum(sal) over (partition by deptno order by ename) new_aliassum就是函數(shù)名(sal)是分析函數(shù)的參數(shù),每個(gè)函數(shù)有03個(gè)參數(shù),參數(shù)可以是表達(dá)式,例如:sum(sal+comm)over 是一個(gè)關(guān)鍵字,用于標(biāo)識(shí)分析函數(shù),否則查詢分析器不能區(qū)別sum()聚集函數(shù)和sum()分析函數(shù)partition by deptno 是可選的分區(qū)子句,如果不存在任何分區(qū)子句,則全部的結(jié)果集可看作一個(gè)單一的大區(qū)order by ename 是可選的order by 子句,有些函數(shù)需要它,有些則不需要.依靠已排序數(shù)據(jù)的那些函數(shù),如:用于訪問(wèn)結(jié)果集中前一行和后一行的LAG和LEAD,必須使用,其它函數(shù),如AVG,則不需要.在使用了任何排序的開窗函數(shù)時(shí),該子句是強(qiáng)制性的,它指定了在計(jì)算分析函數(shù)時(shí)一組內(nèi)的數(shù)據(jù)是如何排序的.1)FUNCTION子句ORACLE提供了26個(gè)分析函數(shù),按功能分5類分析函數(shù)分類等級(jí)(ranking)函數(shù):用于尋找前N種查詢開窗(windowing)函數(shù):用于計(jì)算不同的累計(jì),如SUM,COUNT,AVG,MIN,MAX等,作用于數(shù)據(jù)的一個(gè)窗口上例:sum(t.sal) over (order by t.deptno,t.ename) running_total,sum(t.sal) over (partition by t.deptno order by t.ename) department_total制表(reporting)函數(shù):與開窗函數(shù)同名,作用于一個(gè)分區(qū)或一組上的所有列例:sum(t.sal) over () running_total2,sum(t.sal) over (partition by t.deptno ) department_total2制表函數(shù)與開窗函數(shù)的關(guān)鍵不同之處在于OVER語(yǔ)句上缺少一個(gè)ORDER BY子句!LAG,LEAD函數(shù):這類函數(shù)允許在結(jié)果集中向前或向后檢索值,為了避免數(shù)據(jù)的自連接,它們是非常用用的.VAR_POP,VAR_SAMP,STDEV_POPE及線性的衰減函數(shù):計(jì)算任何未排序分區(qū)的統(tǒng)計(jì)值2)PARTITION子句按照表達(dá)式分區(qū)(就是分組),如果省略了分區(qū)子句,則全部的結(jié)果集被看作是一個(gè)單一的組3)ORDER BY子句分析函數(shù)中ORDERBY的存在將添加一個(gè)默認(rèn)的開窗子句,這意味著計(jì)算中所使用的行的集合是當(dāng)前分區(qū)中當(dāng)前行和前面所有行,沒(méi)有ORDER BY時(shí),默認(rèn)的窗口是全部的分區(qū)在Order by 子句后可以添加nulls last,如:order by comm desc nulls last表示排序時(shí)忽略comm列為空的行. 4)WINDOWING子句用于定義分析函數(shù)將在其上操作的行的集合Windowing子句給出了一個(gè)定義變化或固定的數(shù)據(jù)窗口的方法,分析函數(shù)將對(duì)這些數(shù)據(jù)進(jìn)行操作默認(rèn)的窗口是一個(gè)固定的窗口,僅僅在一組的第一行開始,一直繼續(xù)到當(dāng)前行,要使用窗口,必須使用ORDER BY子句根據(jù)2個(gè)標(biāo)準(zhǔn)可以建立窗口:數(shù)據(jù)值的范圍(RANGES)或與當(dāng)前行的行偏移量.5)Rang窗口Range 5 preceding:將產(chǎn)生一個(gè)滑動(dòng)窗口,他在組中擁有當(dāng)前行以前5行的集合ANGE窗口僅對(duì)NUMBERS和DATES起作用,因?yàn)椴豢赡軓腣ARCHAR2中增加或減去N個(gè)單元另外的限制是ORDER BY中只能有一列,因而范圍實(shí)際上是一維的,不能在N維空間中例:avg(t.sal) over(order by t.hiredate asc range 100 preceding) 統(tǒng)計(jì)前100天平均工資6)Row窗口利用ROW分區(qū),就沒(méi)有RANGE分區(qū)那樣的限制了,數(shù)據(jù)可以是任何類型,且ORDER BY 可以包括很多列7)Specifying窗口UNBOUNDED PRECEDING:這個(gè)窗口從當(dāng)前分區(qū)的每一行開始,并結(jié)束于正在處理的當(dāng)前行CURRENT ROW:該窗口從當(dāng)前行開始(并結(jié)束)Numeric Expression PRECEDING:對(duì)該窗口從當(dāng)前行之前的數(shù)字表達(dá)式(Numeric Expression)的行開始,對(duì)RANGE來(lái)說(shuō),從從行序值小于數(shù)字表達(dá)式的當(dāng)前行的值開始.Numeric Expression FOLLOWING:該窗口在當(dāng)前行Numeric Expression行之后的行終止(或開始),且從行序值大于當(dāng)前行Numeric Expression行的范圍開始(或終止)range between 100 preceding and 100 following:當(dāng)前行100前,當(dāng)前后100后注意:分析函數(shù)允許你對(duì)一個(gè)數(shù)據(jù)集進(jìn)排序和篩選,這是SQL從來(lái)不能實(shí)現(xiàn)的.除了最后的Order by子句之外,分析函數(shù)是在查詢中執(zhí)行的最后的操作集,這樣的話,就不能直接在謂詞中使用分析函數(shù),即不能在上面使用where或having子句!二、Oracle分析函數(shù)簡(jiǎn)單實(shí)例：下面我們通過(guò)一個(gè)實(shí)際的例子：按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶，來(lái)看看分析函數(shù)的應(yīng)用?！?】測(cè)試環(huán)境：SQLdescorders_tmp;Name Null?Type-CUST_NBR NOTNULLNUMBER(5)REGION_ID NOTNULLNUMBER(5)SALESPERSON_IDNOTNULLNUMBER(5)YEAR NOTNULLNUMBER(4)MONTH NOTNULLNUMBER(2)TOT_ORDERSNOTNULLNUMBER(7)TOT_SALES NOTNULLNUMBER(11,2)【2】測(cè)試數(shù)據(jù)：SQLselect*fromorders_tmp;CUST_NBRREGION_IDSALESPERSON_IDYEARMONTHTOT_ORDERSTOT_SALES-11711 20017212204454 200110237802767 200123375010682001122169110672001234262415712200056241279 20006250658152 20003244494151 2000927486425420003235060254 2000446454251 200010435580454 20001223919013rowsselected.【3】測(cè)試語(yǔ)句： SQLselecto.cust_nbrcustomer,2o.region_idregion,3sum(o.tot_sales)cust_sales,4sum(sum(o.tot_sales)over(partitionbyo.region_id)region_sales5fromorders_tmpo6whereo.year=20017groupbyo.region_id,o.cust_nbr;CUSTOMERREGIONCUST_SALESREGION_SALES-4 537802378027 63750 6806510 6643156806511 71220412204三、分析函數(shù)OVER解析：請(qǐng)注意上面的綠色高亮部分，group by的意圖很明顯：將數(shù)據(jù)按區(qū)域ID，客戶進(jìn)行分組，那么Over這一部分有什么用呢？假如我們只需要統(tǒng)計(jì)每個(gè)區(qū)域每個(gè)客戶的訂單總額，那么我們只需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶所在區(qū)域的訂單總額，這一點(diǎn)和前面的不同：需要在前面分組的基礎(chǔ)上按區(qū)域累加。很顯然group by和sum是無(wú)法做到這一點(diǎn)的(因?yàn)榫奂僮鞯募?jí)別不一樣，前者是對(duì)一個(gè)客戶，后者是對(duì)一批客戶)。這就是over函數(shù)的作用了！它的作用是告訴SQL引擎：按區(qū)域?qū)?shù)據(jù)進(jìn)行分區(qū)，然后累積每個(gè)區(qū)域每個(gè)客戶的訂單總額(sum(sum(o.tot_sales)?，F(xiàn)在我們已經(jīng)知道2001年度每個(gè)客戶及其對(duì)應(yīng)區(qū)域的訂單總額，那么下面就是篩選那些個(gè)人訂單總額占到區(qū)域訂單總額20%以上的大客戶了SQLselect*2from(selecto.cust_nbrcustomer,3o.region_idregion,4sum(o.tot_sales)cust_sales,5sum(sum(o.tot_sales)over(partitionbyo.region_id)region_sales6fromorders_tmpo7whereo.year=20018groupbyo.region_id,o.cust_nbr)all_sales9whereall_sales.cust_salesall_sales.region_sales*0.2;CUSTOMERREGIONCUST_SALESREGION_SALES-45378023780210664315680651171220412204SQL現(xiàn)在我們已經(jīng)知道這些大客戶是誰(shuí)了！哦，不過(guò)這還不夠，如果我們想要知道每個(gè)大客戶所占的訂單比例呢？看看下面的SQL語(yǔ)句，只需要一個(gè)簡(jiǎn)單的Round函數(shù)就搞定了。 SQLselectall_sales.*,2100*round(cust_sales/region_sales,2)|%Percent3from(selecto.cust_nbrcustomer,4o.region_idregion,5sum(o.tot_sales)cust_sales,6sum(sum(o.tot_sales)over(partitionbyo.region_id)region_sales7fromorders_tmpo8whereo.year=20019groupbyo.region_id,o.cust_nbr)all_sales10whereall_sales.cust_salesall_sales.region_sales*0.2;CUSTOMERREGIONCUST_SALESREGION_SALESPERCENT-45 3780237802 100%10 6 6431568065 94%11 7 1220412204 100%SQL總結(jié)：Over函數(shù)指明在那些字段上做分析，其內(nèi)跟Partition by表示對(duì)數(shù)據(jù)進(jìn)行分組。注意Partition by可以有多個(gè)字段。Over函數(shù)可以和其它聚集函數(shù)、分析函數(shù)搭配，起到不同的作用。例如這里的SUM，還有諸如Rank，Dense_rank等。Oracle開發(fā)專題之：分析函數(shù)2(Rank, Dense_rank, row_number) 目錄=1.使用rownum為記錄排名2.使用分析函數(shù)來(lái)為記錄排名3.使用分析函數(shù)為記錄進(jìn)行分組排名一、使用rownum為記錄排名：在前面一篇Oracle開發(fā)專題之：分析函數(shù)，我們認(rèn)識(shí)了分析函數(shù)的基本應(yīng)用，現(xiàn)在我們?cè)賮?lái)考慮下面幾個(gè)問(wèn)題：對(duì)所有客戶按訂單總額進(jìn)行排名按區(qū)域和客戶訂單總額進(jìn)行排名找出訂單總額排名前13位的客戶找出訂單總額最高、最低的客戶找出訂單總額排名前25%的客戶按照前面第一篇文章的思路，我們只能做到對(duì)各個(gè)分組的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，如果需要排名的話那么只需要簡(jiǎn)單地加上rownum不就行了嗎？事實(shí)情況是否如此想象般簡(jiǎn)單，我們來(lái)實(shí)踐一下?！?】測(cè)試環(huán)境：SQLdescuser_order;NameNull?Type-REGION_IDNUMBER(2)CUSTOMER_IDNUMBER(2)CUSTOMER_SALESNUMBER【2】測(cè)試數(shù)據(jù)：SQLselect*fromuser_orderorderbycustomer_sales;REGION_IDCUSTOMER_IDCUSTOMER_SALES-51 1511621029 90338367 9715851028 9869649211020541922 1036146816 106846768 114163853 116128655 1169926819 1174421712 1182275711 1190421610 119674869 1208959103012168585 2 12249929 24 12249929 23 1224992 818 1253840715 12555917131322747820 141372266180894954 1878275714 1929774817 1944281925 223270330rowsselected.注意這里有3條記錄的訂單總額是一樣的。假如我們現(xiàn)在需要篩選排名前12位的客戶，如果使用rownum會(huì)有什么樣的后果呢？ SQLselectrownum,t.*2from(select*3fromuser_order4orderbycustomer_salesdesc)t5whererownumselectregion_id,customer_id,sum(customer_sales)total,2rank()over(orderbysum(customer_sales)desc)rank,3dense_rank()over(orderbysum(customer_sales)desc)dense_rank,4row_number()over(orderbysum(customer_sales)desc)row_number5fromuser_order6groupbyregion_id,customer_id;REGION_IDCUSTOMER_IDTOTALRANKDENSE_RANKROW_NUMBER-8181253840111111521224992121212923122499212121392412249921212141030121685815 13 1530rowsselected.請(qǐng)注意上面的綠色高亮部分，這里生動(dòng)的演示了3種不同的排名策略：對(duì)于第一條相同的記錄，3種函數(shù)的排名都是一樣的：12當(dāng)出現(xiàn)第二條相同的記錄時(shí)，Rank和Dense_rank依然給出同樣的排名12；而row_number則順延遞增為13，依次類推至第三條相同的記錄當(dāng)排名進(jìn)行到下一條不同的記錄時(shí)，可以看到Rank函數(shù)在12和15之間空出了13,14的排名，因?yàn)檫@2個(gè)排名實(shí)際上已經(jīng)被第二、三條相同的記錄占了。而Dense_rank則順序遞增。row_number函數(shù)也是順序遞增比較上面3種不同的策略，我們?cè)谶x擇的時(shí)候就要根據(jù)客戶的需求來(lái)定奪了：假如客戶就只需要指定數(shù)目的記錄，那么采用row_number是最簡(jiǎn)單的，但有漏掉的記錄的危險(xiǎn)假如客戶需要所有達(dá)到排名水平的記錄，那么采用rank或dense_rank是不錯(cuò)的選擇。至于選擇哪一種則看客戶的需要，選擇dense_rank或得到最大的記錄三、使用分析函數(shù)為記錄進(jìn)行分組排名：上面的排名是按訂單總額來(lái)進(jìn)行排列的，現(xiàn)在跟進(jìn)一步：假如是為各個(gè)地區(qū)的訂單總額進(jìn)行排名呢？這意味著又多了一次分組操作：對(duì)記錄按地區(qū)分組然后進(jìn)行排名。幸虧Oracle也提供了這樣的支持，我們所要做的僅僅是在over函數(shù)中order by的前面增加一個(gè)分組子句：partition by region_id。SQLselectregion_id,customer_id,sum(customer_sales)total,2rank()over(partitionbyregion_idorderbysum(customer_sales)desc)rank,3dense_rank()over(partitionbyregion_idorderbysum(customer_sales)desc)dense_rank,4row_number()over(partitionbyregion_idorderbysum(customer_sales)desc)row_number5fromuser_order6groupbyregion_id,customer_id;REGION_IDCUSTOMER_IDTOTALRANKDENSE_RANKROW_NUMBER-54187827511152 122499222255 116992633366 178883611169 1208959222610 119674833330rowsselected.現(xiàn)在我們看到的排名將是基于各個(gè)地區(qū)的，而非所有區(qū)域的了！Partition by 子句在排列函數(shù)中的作用是將一個(gè)結(jié)果集劃分成幾個(gè)部分，這樣排列函數(shù)就能夠應(yīng)用于這各個(gè)子集。前面我們提到的5個(gè)問(wèn)題已經(jīng)解決了2個(gè)了(第1,2)，剩下的3個(gè)問(wèn)題(Top/Bottom N，F(xiàn)irst/Last, NTile)會(huì)在下一篇講解。Oracle開發(fā)專題之：分析函數(shù)3(Top/Bottom N、First/Last、NTile) 目錄=1.帶空值的排列2.Top/Bottom N查詢3.First/Last排名查詢4.按層次查詢一、帶空值的排列：在前面Oracle開發(fā)專題之：分析函數(shù)2(Rank、Dense_rank、row_number)一文中，我們已經(jīng)知道了如何為一批記錄進(jìn)行全排列、分組排列。假如被排列的數(shù)據(jù)中含有空值呢？SQLselectregion_id,customer_id,2sum(customer_sales)cust_sales,3sum(sum(customer_sales)over(partitionbyregion_id)ran_total,4rank()over(partitionbyregion_id5orderbysum(customer_sales)desc)rank6fromuser_order7groupbyregion_id,customer_id;REGION_IDCUSTOMER_IDCUST_SALESRAN_TOTALRANK-1031 62389011102618089496238901210271322747623890131030121685862389014102898696462389015102990338362389016我們看到這里有一條記錄的CUST_TOTAL字段值為NULL，但居然排在第一名了！顯然這不符合情理。所以我們重新調(diào)整完善一下我們的排名策略，看看下面的語(yǔ)句：SQLselectregion_id,customer_id,2sum(customer_sales)cust_total,3sum(sum(customer_sales)over(partitionbyregion_id)reg_total,4rank()over(partitionbyregion_idorderbysum(customer_sales)descNULLSLAST)rank5fromuser_order6groupbyregion_id,customer_id;REGION_IDCUSTOMER_IDCUST_TOTALREG_TOTALRANK-10261808949 6238901 1102713227476238901 2103012168586238901 31028986964 623890