posts - 262,  comments - 221,  trackbacks - 0
目录
=========================================

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

参考资料:《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 阅读(5308) 评论(3)  编辑  收藏 所属分类: Oracle 开发


FeedBack:
# re: 【原】Oracle开发专题之:报表函数
2008-09-03 15:54 | yz
博主的ORACLE开发专题,很值得学习,谢谢了!  回复  更多评论
  
# re: 【原】Oracle开发专题之:报表函数
2010-09-01 16:53 | 主程序
学了不少东西,感谢  回复  更多评论
  
# re: 【原】Oracle开发专题之:报表函数
2014-04-17 10:53 | wffger
证件信息zjxx表,记录有经办人jbr,证件种类zjzl,车辆种类clzl 的信息.
如何报表输出:
经办人签发的证件种类为XX、车辆种类为XX的通行证数XX单,对应的车辆数为XX?  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航:
 
<2008年6月>
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345

常用链接

留言簿(19)

随笔分类

随笔档案

BlogJava热点博客

好友博客

搜索

  •  

最新评论

阅读排行榜

评论排行榜