Oracle開發之報表函數

2020-07-26 14:11:41

字體：大中小

來源：轉載

供稿：網友

一、回顧一下前面《Oracle開發之窗口函數》中關于全統計一節，我們使用了Oracle提供的：

復制代碼代碼如下:

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

來統計全年的訂單總額，這個函數會在記錄集形成的過程中，每檢索一條記錄就執行一次，它總共執行了12次。這是非常費時的。實際上我們還有更簡便的方法：

復制代碼代碼如下:

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函數的空括號表示該記錄集的所有記錄都應該被列入統計的范圍，如果使用了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函數允許我們計算每條記錄在其對應記錄集或其子集中所占的比例。

以上就是Oracle報表函數用法的全部內容，希望能給大家一個參考，也希望大家多多支持武林網。

上一篇：Oracle開發之分析函數總結

下一篇：Oracle開發之窗口函數