Oracle开发专题之：报表函数

目录 211462.html
=========================================
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)