oracle分析函數系列之ratio_to_report:計算占總數百分比

資料庫如何用Oracle RATIO_TO_REPORT計算總數百分比

除報告詳細資料外，許多報告中還包括每行總數的百分比。例如，每名客戶的訂單相對于總訂單的百分比，或每位銷售代表的銷售額相對于總銷售額的百分比。

傳統上，Oracle計算百分比的方法是在總計報告的子查詢中使用SUM函數總計報告，然後把那個結果放到細節表中相除來計算百分比。你還可以用一個子查詢作為SELECT語句表達式：

RATIO_TO_REPORT解析函數使得這種類型的查詢更容易編碼。Oracle 8i中引入了這個函數，它的格式如下：

RATIO_TO_REPORT (expr) OVER (query_partition_clause)

  清單A說明一個簡單的例子。對163号銷售代表來說，每位客戶的銷售額占這名銷售代表的總銷售額的百分比是多少呢？在這種情況下，查詢分區子句（partition clause）為空，是以得到的結果是對傳回的所有行計算得出。

  清單B增加了一個查詢分區子句，進一步按客戶細分報告。PARTITION BY customer_id子句重新安排每位客戶的總數，進而說明每名客戶的哪次訂單最為關鍵。你可以檢視客戶102的情況，他的兩個訂單相對平衡；

但客戶103的一個訂單占這名客戶總訂單額的很大一部分。

Listing A

SELECT

   sales_rep_id, customer_id, order_total,

   ratio_to_report(order_total) OVER () pct_total

FROM

   orders

WHERE

   sales_rep_id = 163

ORDER BY

   sales_rep_id, customer_id, order_id

/

SQL> @ratioreport_a

SALES_REP_ID CUSTOMER_ID ORDER_TOTAL  PCT_TOTAL                                

------------ ----------- ----------- ----------                                

         163         102      5610.6 .043747539                                

         163         102       10523 .082051002                                

         163         103          78  .00060819                                

         163         103       13550 .105653433                                

         163         105      1926.6 .015022281                                

         163         106      5546.6 .043248512                                

         163         117      3878.4 .030241054                                

         163         147      1500.8  .01170219                                

         163         149        9055 .070604564                                

         163         156       68501  .53412294                                

         163         157      7110.3 .055441152                                

         163         160       969.2 .007557144                                

12 rows selected.

===========================================================

Listing B

col order_total format 999,999.00

col pct_total format 999.00

SELECT

   sales_rep_id,

   customer_id,

   order_total,

   ROUND(100*ratio_to_report(order_total)

      OVER (PARTITION BY customer_id),2) pct_total

FROM

   orders

WHERE

   sales_rep_id = 163

ORDER BY

   sales_rep_id, customer_id, order_id/

SQL> @ratioreport_b

SALES_REP_ID CUSTOMER_ID ORDER_TOTAL PCT_TOTAL                                 

------------ ----------- ----------- ---------                                 

         163         102    5,610.60     34.78                                 

         163         102   10,523.00     65.22                                 

         163         103       78.00       .57                                 

         163         103   13,550.00     99.43                                 

         163         105    1,926.60    100.00                                 

         163         106    5,546.60    100.00                                 

         163         117    3,878.40    100.00                                 

         163         147    1,500.80    100.00                                 

         163         149    9,055.00    100.00                                 

         163         156   68,501.00    100.00                                 

         163         157    7,110.30    100.00                                 

         163         160      969.20    100.00                                 

12 rows selected.

===============================================================

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr

   FROM employees

   WHERE job_id = 'PU_CLERK';

LAST_NAME                     SALARY         RR

------------------------- ---------- ----------

Khoo                            3100 .223021583

Baida                           2900 .208633094

Tobias                          2800 .201438849

Himuro                          2600  .18705036

Colmenares                      2500 .179856115