Oracle开发课题之：报表函数（转载）

Oracle开发专题之：报表函数（转载）

目录
=========================================
1.报表函数简介
2.RATIO_TO_REPORT函数

一、报表函数简介：

回顾一下前面《Oracle开发专题之：窗口函数》中关于全统计一节，我们使用了Oracle提供的：

sum(sum(tot_sales))?over?(order?by?month?rows?between?unbounded?preceding?and?unbounded?following)

来统计全年的订单总额，这个函数会在记录集形成的过程中，每检索一条记录就执行一次，它总共执行了12次。这是非常费时的。实际上我们还有更简便的方法：

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函数的空括号表示该记录集的所有记录都应该被列入统计的范围，如果使用了partition by则先分区，再依次统计各个分区。

二、RATIO_TO_REPORT函数：

报表函数(窗口函数)特别适合于报表中需要同时显示详细数据和统计数据的情况。例如在销售报告中经常会出现这样的需求：列出上一年度每个月的销售总额、年底销售额以及每个月的销售额占全年总销售额的比例：

方法①:

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函数允许我们计算每条记录在其对应记录集或其子集中所占的比例。

?

转载自：http://www.blogjava.net/pengpenglin/archive/2008/06/29/211462.html

查看更多 下一篇