oracleconnectby和分析函數(shù)總結(jié)-資料下載頁(yè)
2025-06-25 07:15本頁(yè)面
【正文】 0139。,39。ddmonyyyy39。)andto_date(39。31jul200139。,39。ddmonyyyy39。)為了對(duì)指定范圍進(jìn)行統(tǒng)計(jì)，Oracle使用關(guān)鍵字range、interval來(lái)指定一個(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子句，它能夠通過(guò)和當(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。一、報(bào)表函數(shù)簡(jiǎn)介sum(sum(tot_sales))over(orderbymonthrowsbetweenunboundedprecedingandunboundedfollowing)來(lái)統(tǒng)計(jì)全年的訂單總額，這個(gè)函數(shù)會(huì)在記錄集形成的過(guò)程中，每檢索一條記錄就執(zhí)行一次，它總共執(zhí)行了12次。這是非常費(fèi)時(shí)的。實(shí)際上我們還有更簡(jiǎn)便的方法：SQLselectmonth,2sum(tot_sales)month_sales,3sum(sum(tot_sales))over(orderbymonth4rowsbetweenunboundedprecedingandunboundedfollowing)win_sales,5sum(sum(tot_sales))over()rpt_sales6fromorders7groupbymonth。二、RATIO_TO_REPORT函數(shù)：需求：列出上一年度每個(gè)月的銷售總額、年底銷售額以及每個(gè)月的銷售額占全年總銷售額的比例：方法①:selectall_sales.*,100*round(cust_sales/region_sales,2)||39。%39。Percentfrom(selectcustomer,region,sum()cust_sales,sum(sum())over(partitionby)region_salesfromorders_tmpowhere=2001groupby,)all_saleswhere*方法②：selectregion_id,salesperson_id,sum(tot_sales)sp_sales,round(sum(tot_sales)/sum(sum(tot_sales)) over(partitionbyregion_id),2)percent_of_regionfromorderswhereyear=2001groupbyregion_id,salesperson_idorderbyregion_id,salesperson_id。方法③selectregion_id,salesperson_id,sum(tot_sales)sp_sales,round(ratio_to_report(sum(tot_sales))over(partitionbyregion_id),2)sp_ratiofromorderswhereyear=2001groupbyregion_id,salesperson_idorderbyregion_id,salesperson_id。Oracle提供的Ratio_to_report函數(shù)允許我們計(jì)算每條記錄在其對(duì)應(yīng)記錄集或其子集中所占的比例。
數(shù)學(xué)相關(guān)推薦
鄂ICP備17016276號(hào)-1