Oracle開(kāi)發(fā)之報(bào)表函數(shù)

2024-08-29 13:59:05

字體：大中小

供稿：網(wǎng)友

一、回顧一下前面《Oracle開(kāi)發(fā)之窗口函數(shù)》中關(guān)于全統(tǒng)計(jì)一節(jié)，我們使用了Oracle提供的：

復(fù)制代碼代碼如下:

	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)便的方法：

復(fù)制代碼代碼如下:

	SQL> select month,
	         sum(tot_sales) month_sales,
	         sum(sum(tot_sales)) over(order by month
	         rows between unbounded preceding and unbounded following) win_sales,
	         sum(sum(tot_sales)) over() rpt_sales
	    from orders
	   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ù)的情況。例如在銷(xiāo)售報(bào)告中經(jīng)常會(huì)出現(xiàn)這樣的需求：列出上一年度每個(gè)月的銷(xiāo)售總額、年底銷(xiāo)售額以及每個(gè)月的銷(xiāo)售額占全年總銷(xiāo)售額的比例：

方法①:

復(fù)制代碼代碼如下:

	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;

這是一種笨方法也是最易懂的方法。

方法②：

復(fù)制代碼代碼如下:

	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;

方法③

復(fù)制代碼代碼如下:

	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)記錄集或其子集中所占的比例。

以上就是Oracle報(bào)表函數(shù)用法的全部?jī)?nèi)容，希望能給大家一個(gè)參考，也希望大家多多支持VeVb武林網(wǎng)。

注：相關(guān)教程知識(shí)閱讀請(qǐng)移步到oracle教程頻道。

上一篇：Oracle開(kāi)發(fā)之分析函數(shù)(Rank, Dense_rank, row_number)

下一篇：Oracle開(kāi)發(fā)之窗口函數(shù)