oracleconnectby和分析函數(shù)總結(jié).doc

1. connect by 用法總結(jié)2一、樹查詢(遞歸查詢)2二、列轉(zhuǎn)行sys_connect_by_path()42.分析函數(shù)總結(jié)61.分析函數(shù)(OVER)72.分析函數(shù)2(Rank, Dense_rank, row_number)93.分析函數(shù)3(Top/Bottom N、First/Last、NTile)94.窗口函數(shù)115.報(bào)表函數(shù)14151. connect by 用法總結(jié)一、樹查詢(遞歸查詢)1. 作用對(duì)于oracle進(jìn)行簡單樹查詢(遞歸查詢)列轉(zhuǎn)行2.基本語法select . from where ：過濾條件，用于對(duì)返回的所有記錄進(jìn)行過濾。start with ：查詢結(jié)果重起始根結(jié)點(diǎn)的限定條件。connect by ; ：連接條件1)例子：select num1,num2from tablestart with num2 = 1008connect by num2 = prior num1;2)解釋:start with：用來標(biāo)識(shí)哪個(gè)節(jié)點(diǎn)作為查找樹型結(jié)構(gòu)的根節(jié)點(diǎn)。若該子句被省略，則表示所有滿足查詢條件的行作為根節(jié)點(diǎn)。prior: 位置很重要（自我總結(jié)，和父在一起 則自底向上，即查父 和子在一起 則自頂向下 查子）例子原始數(shù)據(jù) num1 為父 num2 為子看下面的圖 1. CONNECT_BY_ROOT 返回當(dāng)前節(jié)點(diǎn)的最頂端節(jié)點(diǎn)。2. CONNECT_BY_ISLEAF 判斷是否為葉子節(jié)點(diǎn)，是1，不是0。3. LEVEL 偽列表示節(jié)點(diǎn)深度。4. SYS_CONNECT_BY_PATH函數(shù)顯示詳細(xì)路徑，并用“/”分隔。二、列轉(zhuǎn)行sys_connect_by_path()這個(gè)函數(shù)使用之前必須先建立一個(gè)樹，否則無用sys_connect_by_path(字段名, 2個(gè)字段之間的連接符號(hào))withtmp_a as (select 1 a,0 p from dualunion allselect 2,1 from dualunion allselect 3,1 from dualunion allselect 4,3 from dualunion allselect 5,2 from dualunion allselect 6,5 from dual)- 子全部顯示 根-子 level代表級(jí)別select a,p,sys_connect_by_path(a,-),level from tmp_astart with a = 1connect by p = prior a- 2和2的所有下級(jí)去掉 根-子 (開始就要去掉)select a,p,sys_connect_by_path(a,-) from tmp_astart with p = 1 and a 2connect by p = prior a- 2的所有下級(jí)都去掉 根-子 (connect 時(shí)去掉)select a,p,sys_connect_by_path(a,-) from tmp_astart with a = 1connect by p = prior a and p 2 -去掉2的分枝- 2的下一級(jí)去掉 根-子 (where 中去掉)select a,p,sys_connect_by_path(a,-) from tmp_awhere p 2 start with a = 1connect by p = prior a-顯示最長的 根-子withtmp_tab as (select 中國 s,null b from dualunion allselect 廣東 s,中國 b from dualunion allselect 湖南 s,中國 b from dualunion allselect 衡陽 s,湖南 b from dualunion allselect 廣州 s,廣東 b from dualunion allselect 衡東 s,衡陽 b from dual)select max(sys_connect_by_path(s,/) from tmp_tabstart with s = 湖南connect by prior s = b 2.分析函數(shù)總結(jié)一、統(tǒng)計(jì)方面：Sum( )Over(Partitionby Orderby )Sum( )Over(Partitionby Orderby RowsBetween PrecedingAnd Following)Sum( )Over(Partitionby Orderby RowsBetween PrecedingAndCurrentRow)Sum( )Over(Partitionby Orderby RangeBetweenInterval DayPrecedingAndInterval DayFollowing)二、排列方面：Rank()Over(Partitionby Orderby NullsFirst/Last)Dense_rank()Over(Patitionby Orderby NullsFirst/Last)Row_number()Over(Partitionby Orderby NullsFirst/Last)Ntile( )Over(Partitionby Orderby )三、最大值/最小值查找方面：Min( )/Max( )Keep(Dense_rankFirst/LastPartitionby Orderby )四、首記錄/末記錄查找方面：First_value/Last_value(Sum( )Over(Patitionby Orderby RowsBetween PrecedingAnd Following)五、相鄰記錄之間比較方面：Lag(Sum( ),1)Over(Patitionby Orderby )1.分析函數(shù)(OVER)一.分析函數(shù)語法:FUNCTION_NAME(,.)OVER()例:sum(sal) over (partition by deptno order by ename) new_aliassum：函數(shù)名(sal)：參數(shù) 03個(gè)參數(shù) 可以是表達(dá)式Over：關(guān)鍵字partition by ：（可選）分區(qū)order by ：（可選）LAG和LEAD 需，AVG不需要，如果使用排序的開窗函數(shù)時(shí)，必須加 1)FUNCTION子句 26個(gè)分析函數(shù),按功能分5類分析函數(shù)分類1.等級(jí)(ranking)函數(shù): 用于尋找前N種查詢2.開窗(windowing)函數(shù):用于計(jì)算不同的累計(jì),如SUM,COUNT,AVG,MIN,MAX等,作用于數(shù)據(jù)的一個(gè)窗口上3.制表(reporting)函數(shù):與開窗函數(shù)同名,作用于一個(gè)分區(qū)或一組上的所有列（制表與開窗的區(qū)別：制表的OVER語句上少一個(gè)ORDER BY子句）4.LAG,LEAD函數(shù): 可在結(jié)果集中向前或向后檢索值,為了避免數(shù)據(jù)的自連接,它們是非常用用的.5.VAR_POP,VAR_SAMP,STDEV_POPE及線性的衰減函數(shù):計(jì)算任何未排序分區(qū)的統(tǒng)計(jì)值2)PARTITION子句分組3)ORDER BY子句分析函數(shù)中ORDERBY的存在將添加一個(gè)默認(rèn)的開窗子句,這意味著計(jì)算中所使用的行的集合是當(dāng)前分區(qū)中當(dāng)前行和前面所有行,沒有ORDER BY時(shí),默認(rèn)的窗口是全部的分區(qū)。在Order by子句后可以添加nulls last,如:order by comm desc nulls last表示排序時(shí)忽略comm列為空的行.二、分析函數(shù)簡單實(shí)例：按區(qū)域查找2001年度訂單總額占區(qū)域訂單總額20%以上的客戶【1】測試數(shù)據(jù)：SQLselect*fromorders_tmp;CUST_NBRREGION_IDSALESPERSON_IDYEARMONTHTOT_ORDERSTOT_SALES-11711 200172122044542001102378027672001233750106820011221691106720012342624【2】測試語句：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-45378023780276375068065106643156806511712204122043、 分析函數(shù)OVER解析：請(qǐng)注意上面的綠色高亮部分，group by的意圖很明顯：將數(shù)據(jù)按區(qū)域ID，客戶進(jìn)行分組。假如我們只需要統(tǒng)計(jì)每個(gè)區(qū)域每個(gè)客戶的訂單總額，那么我們只需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶所在區(qū)域的訂單總額，這一點(diǎn)和前面的不同：需要在前面分組的基礎(chǔ)上按區(qū)域累加。group by和sum是無法做到這一點(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)。最終語句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-453780237802100%106643156806594%1171220412204100%2.分析函數(shù)2(Rank, Dense_rank, row_number)遇到相同的數(shù)據(jù)時(shí)用Rank, Dense_rank, row_number排名策略，他們之間的區(qū)別 直接看例子SQLselectregion_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- 818125384011111152122499212121292312249921212139241224992121214103012168581513 153.分析函數(shù)3(Top/Bottom N、First/Last、NTile)一、Top/Bottom N查詢：找出所有訂單總額排名前3的大客戶：SQLselect*SQLfrom(selectregion_id,SQLcustomer_id,SQLsum(customer_sales)cust_total,SQLrank()over(orderbysum(customer_sales)descNULLSLAST)rankSQLfromuser_orderSQLgroupbyregion_id,customer_id)SQLwhererankselectmin(customer_id)2keep(dense_rankfirstorderbysum(customer_sales)desc)first,3min(customer_id)4keep(dense_ranklastorderbysum(customer_sales)desc)last5fromuser_order6groupbycustomer_id;FIRSTLAST-311Min：函數(shù)的作用是用于當(dāng)存在多個(gè)First/Last情況下保證返回唯一的記錄。（去掉會(huì)出錯(cuò)）Keep：從上面的結(jié)果我們已經(jīng)知道Oracle對(duì)排名的結(jié)果只“保留”2條數(shù)據(jù)，這就是keep的作用。告訴Oracle只保留符合keep條件的記錄。dense_rank：是告訴Oracle排列的策略，first/last則告訴最終篩選的條件。三、按層次查詢（NTile）找出訂單總額排名前1/5的客戶。SQLselectregion_id,2customer_id,3ntile(5)over(orderbysum(customer_sales)desc)til4fromuser_order5groupbyregion_id,customer_id;REGION_IDCUSTOMER_IDTILE-10311925110261661818252292336937113534684816467510295515Ntil函數(shù)為各個(gè)記錄在記錄集中的排名計(jì)算比例，所有的記錄分成5個(gè)等級(jí)，假如只需要前1/5的記錄則只需要截取TILE的值為1的記錄就可以了。假如需要排名前25%的記錄(也就是1/4)那么我們只需要設(shè)置ntile(4)就可以了。4.窗口函數(shù)需要隨著遍歷記錄集的每一條記錄的同時(shí)進(jìn)行統(tǒng)計(jì)。也即是說：統(tǒng)計(jì)不止發(fā)生一次，而是發(fā)生多次。統(tǒng)計(jì)不至發(fā)生在記錄集形成后，而是發(fā)生在記錄集形成的過程中。一、窗口函數(shù)示例全統(tǒng)計(jì)：需求：列出每月的訂單總額以及全年的訂單總額rows between . preceding and . following。SQLselectmonth,2sum(tot_sales)month_sales,3sum(sum(tot_sales)over(orderbymonth4rowsbetweenunboundedprecedingandunboundedfollowing)total_sales5fromorders6groupbymonth;MONTHMONTH_SALESTOTAL_SALES-161069763077662428676630776636370316307766454114663077665592935630776665014856307766 .（后面的剩了）unbounded preceding and unbouned following：針對(duì)當(dāng)前所有記錄的前一條、后一條記錄，也就是表中的所有記錄。Preceding前面如果是1，不是從第1條記錄開始的意思，而是指當(dāng)前記錄的前一條記錄。preceding前面的修飾符是告訴窗口函數(shù)執(zhí)行時(shí)參考的記錄數(shù)，如同unbounded就是告訴oracle不管當(dāng)前記錄是第幾條，只要前面有多少條記錄，都列入統(tǒng)計(jì)的范圍。二、窗口函數(shù)進(jìn)階滾動(dòng)統(tǒng)計(jì)(累積/均值)：需求：列出每月的訂單總額以及截至到當(dāng)前月的訂單總額。也就是說3月份要顯示當(dāng)月的訂單總額和1,2,3月份訂單總額的和，依此類推。需要在統(tǒng)計(jì)第N月的訂單總額時(shí)，還要再統(tǒng)計(jì)這N個(gè)月來的訂單總額之和。curreent rowSQLselectmonth,2sum(tot_sales)month_sales,3sum(sum(tot_sales)over(orderbymonth4rowsbetweenunboundedprecedingandcurrentrow)current_total_sales5fromorders6groupbymonth;MONTHMONTH_SALESCURRENT_TOTAL_SALES-16106976106972428676103937336370311676404三、窗口函數(shù)進(jìn)階根據(jù)時(shí)間范圍統(tǒng)計(jì)統(tǒng)計(jì)了當(dāng)天銷售額和五天內(nèi)的評(píng)價(jià)銷售額：selecttrunc(order_dt)day,sum(sale_price)daily_sales,avg(sum(sale_price)over(orderbytrunc(order_dt)rangebetweeninterval2dayprecedingandinterval2dayfollowing)five_day_avgfromcust_orderwheresale_priceisnotnullandorder_dtbetweento_date(01-jul-2001,dd-mon-yyyy)andto_date(31-jul-2001,dd-mon-yyyy)為了對(duì)指定范圍進(jìn)行統(tǒng)計(jì)，Oracle使用關(guān)鍵字range、interval來指定一個(gè)范圍。上面的例子告訴Oracle查找當(dāng)前日期的前2天，后2天范圍內(nèi)的記錄，并統(tǒng)計(jì)其銷售平均值。四、窗口函數(shù)進(jìn)階first_value/last_value：first_value、last_value，用于在窗口記錄集中查找第一條記錄和最后一條記錄。需要顯示當(dāng)前月、上一個(gè)月、后一個(gè)月的銷售情況，以及每3個(gè)月的銷售平均值selectmonth,first_value(sum(tot_sales)over(orderbymonthrowsbetween1precedingand1following)prev_month,sum(tot_sales)monthly_sales,last_value(sum(tot_sales)over(orderbymonthrowsbetween1precedingand1following)next_month,avg(sum(tot_sales)over(orderbymonthrowsbetween1precedingand1following)rolling_avgfromorderswhereyear=2001andregion_id=6groupbymonthorderbymonth;rows between 1 preceding and 1 following:當(dāng)前記錄的前一條、后一條范圍內(nèi)查找并統(tǒng)計(jì)，而first_value和last_value在這3條記錄中至分別找出第一條、第三條記錄五、窗口函數(shù)進(jìn)階比較相鄰記錄：我們想每次顯示當(dāng)月的銷售額和上個(gè)月的銷售額，leg函數(shù)類似于preceding和following子句，它能夠通過和當(dāng)前記錄的相對(duì)位置而被應(yīng)用，在比較同一個(gè)相鄰的記錄集內(nèi)兩條相鄰記錄的時(shí)候特別有用。selectmonth,sum(tot_sales)monthly_sales,lag(sum(tot_sales),1)over(orderbymonth)prev_month_salesfromorderswhereyear=2001andregion_id=6groupbymonthorderbymonth;5.報(bào)表函數(shù)一、報(bào)表函數(shù)簡介sum(sum(tot_sales)over(orderbymonthrowsbetweenunboundedprecedingandunboundedfollowing)來統(tǒng)計(jì)全年的訂單總額，這個(gè)函數(shù)會(huì)在記錄集形成的過程中，每檢索一條記錄就執(zhí)行一次，它總共執(zhí)行了12次。這是非常費(fèi)時(shí)的。實(shí)際上我們還有更簡便的方法：SQLselectmonth,2sum(tot_sales)month_sales,3sum(sum(tot_sales)over(orderbymonth4rowsbetweenunboundedprecedingandunboundedfollowing)win_sales,5sum(sum