目錄
=========================================
1.報(bào)表函數(shù)簡(jiǎn)介
2.RATIO_TO_REPORT函數(shù)
一、報(bào)表函數(shù)簡(jiǎn)介：
回顧一下前面《Oracle開發(fā)專題之：窗口函數(shù)》中關(guān)于全統(tǒng)計(jì)一節(jié)，我們使用了Oracle提供的：
sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following)
來(lái)統(tǒng)計(jì)全年的訂單總額，這個(gè)函數(shù)會(huì)在記錄集形成的過(guò)程中，每檢索一條記錄就執(zhí)行一次，它總共執(zhí)行了12次。這是非常費(fèi)時(shí)的。實(shí)際上我們還有更簡(jiǎn)便的方法：
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and unbounded following) win_sales,
5 sum(sum(tot_sales)) over() rpt_sales
6 from orders
7 group by month;
MONTH MONTH_SALES WINDOW_SALES REPORT_SALES
---------- ----------- ------------ ------------
1 610697 6307766 6307766
2 428676 6307766 6307766
3 637031 6307766 6307766
4 541146 6307766 6307766
5 592935 6307766 6307766
6 501485 6307766 6307766
7 606914 6307766 6307766
8 460520 6307766 6307766
9 392898 6307766 6307766
10 510117 6307766 6307766
11 532889 6307766 6307766
12 492458 6307766 6307766
已選擇12行。
over函數(shù)的空括號(hào)表示該記錄集的所有記錄都應(yīng)該被列入統(tǒng)計(jì)的范圍，如果使用了partition by則先分區(qū)，再依次統(tǒng)計(jì)各個(gè)分區(qū)。
二、RATIO_TO_REPORT函數(shù)：
報(bào)表函數(shù)特(窗口函數(shù))特別適合于報(bào)表中需要同時(shí)顯示詳細(xì)數(shù)據(jù)和統(tǒng)計(jì)數(shù)據(jù)的情況。例如在銷售報(bào)告中經(jīng)常會(huì)出現(xiàn)這樣的需求：列出上一年度每個(gè)月的銷售總額、年底銷售額以及每個(gè)月的銷售額占全年總銷售額的比例：
方法①:
select all_sales.*,
100 * round(cust_sales / region_sales, 2) || '%' Percent
from (select o.cust_nbr customer,
o.region_id region,
sum(o.tot_sales) cust_sales,
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
group by o.region_id, o.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.2;
這是一種笨方法也是最易懂的方法。
方法②：
select region_id, salesperson_id,
sum(tot_sales) sp_sales,
round(sum(tot_sales) / sum(sum(tot_sales))
over (partition by region_id), 2) percent_of_region
from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;
方法③
select region_id, salesperson_id,
sum(tot_sales) sp_sales,
round(ratio_to_report(sum(tot_sales))
over (partition by region_id), 2) sp_ratio
from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;
Oracle提供的Ratio_to_report函數(shù)允許我們計(jì)算每條記錄在其對(duì)應(yīng)記錄集或其子集中所占的比例。
參考資料：《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004 0-596-00632-2)
-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。
posted on 2008-06-29 12:02
Paul Lin 閱讀(5808)
評(píng)論(3) 編輯 收藏 所屬分類:
Oracle 開發(fā)