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ā)專題之：報表函數(shù)20Oracle開發(fā)專題之：分析函數(shù)總結22Oracle開發(fā)專題之：26個分析函數(shù)24PLSQL開發(fā)筆記和小結28分析函數(shù)簡述60說明: 1)Oracle開發(fā)專題99%收集自: /pengpenglin/(偶補充了一點點1%);2) PLSQL開發(fā)筆記和小結收集自/cheneyfree/3)分析函數(shù)簡述收集自/7607759/昆明小蟲/ 收集,并補充了一點點1%Oracle開發(fā)專題之：分析函數(shù)(OVER) 目錄：=1.Oracle分析函數(shù)簡介2. Oracle分析函數(shù)簡單實例3.分析函數(shù)OVER解析一、Oracle分析函數(shù)簡介：在日常的生產環(huán)境中，我們接觸得比較多的是OLTP系統(tǒng)(即Online Transaction Process)，這些系統(tǒng)的特點是具備實時要求，或者至少說對響應的時間多長有一定的要求；其次這些系統(tǒng)的業(yè)務邏輯一般比較復雜，可能需要經過多次的運算。比如我們經常接觸到的電子商城。在這些系統(tǒng)之外，還有一種稱之為OLAP的系統(tǒng)(即Online Aanalyse Process)，這些系統(tǒng)一般用于系統(tǒng)決策使用。通常和數(shù)據(jù)倉庫、數(shù)據(jù)分析、數(shù)據(jù)挖掘等概念聯(lián)系在一起。這些系統(tǒng)的特點是數(shù)據(jù)量大，對實時響應的要求不高或者根本不關注這方面的要求，以查詢、統(tǒng)計操作為主。我們來看看下面的幾個典型例子：查找上一年度各個銷售區(qū)域排名前10的員工按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶查找上一年度銷售最差的部門所在的區(qū)域查找上一年度銷售最好和最差的產品我們看看上面的幾個例子就可以感覺到這幾個查詢和我們日常遇到的查詢有些不同，具體有：需要對同樣的數(shù)據(jù)進行不同級別的聚合操作需要在表內將多條數(shù)據(jù)和同一條數(shù)據(jù)進行多次的比較需要在排序完的結果集上進行額外的過濾操作分析函數(shù)語法:FUNCTION_NAME(,.)OVER()例:sum(sal) over (partition by deptno order by ename) new_aliassum就是函數(shù)名(sal)是分析函數(shù)的參數(shù),每個函數(shù)有03個參數(shù),參數(shù)可以是表達式,例如:sum(sal+comm)over 是一個關鍵字,用于標識分析函數(shù),否則查詢分析器不能區(qū)別sum()聚集函數(shù)和sum()分析函數(shù)partition by deptno 是可選的分區(qū)子句,如果不存在任何分區(qū)子句,則全部的結果集可看作一個單一的大區(qū)order by ename 是可選的order by 子句,有些函數(shù)需要它,有些則不需要.依靠已排序數(shù)據(jù)的那些函數(shù),如:用于訪問結果集中前一行和后一行的LAG和LEAD,必須使用,其它函數(shù),如AVG,則不需要.在使用了任何排序的開窗函數(shù)時,該子句是強制性的,它指定了在計算分析函數(shù)時一組內的數(shù)據(jù)是如何排序的.1)FUNCTION子句ORACLE提供了26個分析函數(shù),按功能分5類分析函數(shù)分類等級(ranking)函數(shù):用于尋找前N種查詢開窗(windowing)函數(shù):用于計算不同的累計,如SUM,COUNT,AVG,MIN,MAX等,作用于數(shù)據(jù)的一個窗口上例: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ù)同名,作用于一個分區(qū)或一組上的所有列例:sum(t.sal) over () running_total2,sum(t.sal) over (partition by t.deptno ) department_total2制表函數(shù)與開窗函數(shù)的關鍵不同之處在于OVER語句上缺少一個ORDER BY子句!LAG,LEAD函數(shù):這類函數(shù)允許在結果集中向前或向后檢索值,為了避免數(shù)據(jù)的自連接,它們是非常用用的.VAR_POP,VAR_SAMP,STDEV_POPE及線性的衰減函數(shù):計算任何未排序分區(qū)的統(tǒng)計值2)PARTITION子句按照表達式分區(qū)(就是分組),如果省略了分區(qū)子句,則全部的結果集被看作是一個單一的組3)ORDER BY子句分析函數(shù)中ORDERBY的存在將添加一個默認的開窗子句,這意味著計算中所使用的行的集合是當前分區(qū)中當前行和前面所有行,沒有ORDER BY時,默認的窗口是全部的分區(qū)在Order by 子句后可以添加nulls last,如:order by comm desc nulls last表示排序時忽略comm列為空的行. 4)WINDOWING子句用于定義分析函數(shù)將在其上操作的行的集合Windowing子句給出了一個定義變化或固定的數(shù)據(jù)窗口的方法,分析函數(shù)將對這些數(shù)據(jù)進行操作默認的窗口是一個固定的窗口,僅僅在一組的第一行開始,一直繼續(xù)到當前行,要使用窗口,必須使用ORDER BY子句根據(jù)2個標準可以建立窗口:數(shù)據(jù)值的范圍(RANGES)或與當前行的行偏移量.5)Rang窗口Range 5 preceding:將產生一個滑動窗口,他在組中擁有當前行以前5行的集合ANGE窗口僅對NUMBERS和DATES起作用,因為不可能從VARCHAR2中增加或減去N個單元另外的限制是ORDER BY中只能有一列,因而范圍實際上是一維的,不能在N維空間中例:avg(t.sal) over(order by t.hiredate asc range 100 preceding) 統(tǒng)計前100天平均工資6)Row窗口利用ROW分區(qū),就沒有RANGE分區(qū)那樣的限制了,數(shù)據(jù)可以是任何類型,且ORDER BY 可以包括很多列7)Specifying窗口UNBOUNDED PRECEDING:這個窗口從當前分區(qū)的每一行開始,并結束于正在處理的當前行CURRENT ROW:該窗口從當前行開始(并結束)Numeric Expression PRECEDING:對該窗口從當前行之前的數(shù)字表達式(Numeric Expression)的行開始,對RANGE來說,從從行序值小于數(shù)字表達式的當前行的值開始.Numeric Expression FOLLOWING:該窗口在當前行Numeric Expression行之后的行終止(或開始),且從行序值大于當前行Numeric Expression行的范圍開始(或終止)range between 100 preceding and 100 following:當前行100前,當前后100后注意:分析函數(shù)允許你對一個數(shù)據(jù)集進排序和篩選,這是SQL從來不能實現(xiàn)的.除了最后的Order by子句之外,分析函數(shù)是在查詢中執(zhí)行的最后的操作集,這樣的話,就不能直接在謂詞中使用分析函數(shù),即不能在上面使用where或having子句!二、Oracle分析函數(shù)簡單實例：下面我們通過一個實際的例子：按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶，來看看分析函數(shù)的應用?！?】測試環(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】測試數(shù)據(jù)：SQLselect*fromorders_tmp;CUST_NBRREGION_IDSALESPERSON_IDYEARMONTHTOT_ORDERSTOT_SALES-11711 20017212204454 200110237802767 200123375010682001122169110672001234262415712200056241279 20006250658152 20003244494151 2000927486425420003235060254 2000446454251 200010435580454 20001223919013rowsselected.【3】測試語句： 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解析：請注意上面的綠色高亮部分，group by的意圖很明顯：將數(shù)據(jù)按區(qū)域ID，客戶進行分組，那么Over這一部分有什么用呢？假如我們只需要統(tǒng)計每個區(qū)域每個客戶的訂單總額，那么我們只需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶所在區(qū)域的訂單總額，這一點和前面的不同：需要在前面分組的基礎上按區(qū)域累加。很顯然group by和sum是無法做到這一點的(因為聚集操作的級別不一樣，前者是對一個客戶，后者是對一批客戶)。這就是over函數(shù)的作用了！它的作用是告訴SQL引擎：按區(qū)域對數(shù)據(jù)進行分區(qū)，然后累積每個區(qū)域每個客戶的訂單總額(sum(sum(o.tot_sales)?，F(xiàn)在我們已經知道2001年度每個客戶及其對應區(qū)域的訂單總額，那么下面就是篩選那些個人訂單總額占到區(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)在我們已經知道這些大客戶是誰了！哦，不過這還不夠，如果我們想要知道每個大客戶所占的訂單比例呢？看看下面的SQL語句，只需要一個簡單的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總結：Over函數(shù)指明在那些字段上做分析，其內跟Partition by表示對數(shù)據(jù)進行分組。注意Partition by可以有多個字段。Over函數(shù)可以和其它聚集函數(shù)、分析函數(shù)搭配，起到不同的作用。例如這里的SUM，還有諸如Rank，Dense_rank等。Oracle開發(fā)專題之：分析函數(shù)2(Rank, Dense_rank, row_number) 目錄=1.使用rownum為記錄排名2.使用分析函數(shù)來為記錄排名3.使用分析函數(shù)為記錄進行分組排名一、使用rownum為記錄排名：在前面一篇Oracle開發(fā)專題之：分析函數(shù)，我們認識了分析函數(shù)的基本應用，現(xiàn)在我們再來考慮下面幾個問題：對所有客戶按訂單總額進行排名按區(qū)域和客戶訂單總額進行排名找出訂單總額排名前13位的客戶找出訂單總額最高、最低的客戶找出訂單總額排名前25%的客戶按照前面第一篇文章的思路，我們只能做到對各個分組的數(shù)據(jù)進行統(tǒng)計，如果需要排名的話那么只需要簡單地加上rownum不就行了嗎？事實情況是否如此想象般簡單，我們來實踐一下?！?】測試環(huán)境：SQLdescuser_order;NameNull?Type-REGION_IDNUMBER(2)CUSTOMER_IDNUMBER(2)CUSTOMER_SALESNUMBER【2】測試數(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會有什么樣的后果呢？ 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.請注意上面的綠色高亮部分，這里生動的演示了3種不同的排名策略：對于第一條相同的記錄，3種函數(shù)的排名都是一樣的：12當出現(xiàn)第二條相同的記錄時，Rank和Dense_rank依然給出同樣的排名12；而row_number則順延遞增為13，依次類推至第三條相同的記錄當排名進行到下一條不同的記錄時，可以看到Rank函數(shù)在12和15之間空出了13,14的排名，因為這2個排名實際上已經被第二、三條相同的記錄占了。而Dense_rank則順序遞增。row_number函數(shù)也是順序遞增比較上面3種不同的策略，我們在選擇的時候就要根據(jù)客戶的需求來定奪了：假如客戶就只需要指定數(shù)目的記錄，那么采用row_number是最簡單的，但有漏掉的記錄的危險假如客戶需要所有達到排名水平的記錄，那么采用rank或dense_rank是不錯的選擇。至于選擇哪一種則看客戶的需要，選擇dense_rank或得到最大的記錄三、使用分析函數(shù)為記錄進行分組排名：上面的排名是按訂單總額來進行排列的，現(xiàn)在跟進一步：假如是為各個地區(qū)的訂單總額進行排名呢？這意味著又多了一次分組操作：對記錄按地區(qū)分組然后進行排名。幸虧Oracle也提供了這樣的支持，我們所要做的僅僅是在over函數(shù)中order by的前面增加一個分組子句：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)在我們看到的排名將是基于各個地區(qū)的，而非所有區(qū)域的了！Partition by 子句在排列函數(shù)中的作用是將一個結果集劃分成幾個部分，這樣排列函數(shù)就能夠應用于這各個子集。前面我們提到的5個問題已經解決了2個了(第1,2)，剩下的3個問題(Top/Bottom N，F(xiàn)irst/Last, NTile)會在下一篇講解。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)一文中，我們已經知道了如何為一批記錄進行全排列、分組排列。假如被排列的數(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，但居然排在第一名了！顯然這不符合情理。所以我們重新調整完善一下我們的排名策略，看看下面的語句：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