海上月明

除報(bào)告詳細(xì)數(shù)據(jù)外，許多報(bào)告中還包括每行總數(shù)的百分比。例如，每名客戶的訂單相對于總訂單的百分比，或每位銷售代表的銷售額相對于總銷售額的百分比。

傳統(tǒng)上，Oracle計(jì)算百分比的方法是在總計(jì)報(bào)告的子查詢中使用SUM函數(shù)總計(jì)報(bào)告，然后把那個(gè)結(jié)果放到細(xì)節(jié)表中相除來計(jì)算百分比。你還可以用一個(gè)子查詢作為SELECT語句表達(dá)式：

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

RATIO_TO_REPORT (expr) OVER (query_partition_clause)

列表A說明一個(gè)簡單的例子。對163號(hào)銷售代表來說，每位客戶的銷售額占這名銷售代表的總銷售額的百分比是多少呢？在這種情況下，查詢分區(qū)子句（partition clause）為空，因此得到的結(jié)果是對返回的所有行計(jì)算得出。列表B增加了一個(gè)查詢分區(qū)子句，進(jìn)一步按客戶細(xì)分報(bào)告。

PARTITION BY customer_id子句重新安排每位客戶的總數(shù)，進(jìn)而說明每名客戶的哪次訂單最為關(guān)鍵。你可以查看客戶102的情況，他的兩個(gè)訂單相對平衡；但客戶103的一個(gè)訂單占這名客戶總訂單額的很大一部分。

Oracle解析可幫助你節(jié)省編碼時(shí)間，并在執(zhí)行過程中生成更加有效的查詢。

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.

SQL>

<br />

<span style="font-family: Verdana, Arial, Helvetica, sans-serif, 宋體; white-space: normal;"><pre id="aeaoofnhgocdbnbeljkmbjdmhbcokfdb-mousedown">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.

SQL> spool off