分析函式牛刀小試

dbhelper發表於2014-11-27
函式
今天有個同事問我一個問題，想透過一條sql語句完成一個稍顯複雜的查詢。
結構如下面所示。需要算出tax apply 的值，但是需要彙總charge_amount列和tax_amount列的值
  CDL_CRD Charge_Amount Tax_Amount Tax apply
  50 20 5 50*5/(100+18)
  50 30 6 50*6/(100+18)
  50 50 7 50*7/(100+18)
Total 50 100 18  

比如第一條資料。
50*5/(100+18) 其中100=20+30+50  18=5+6+7

因為資料量很小，就幾千條，所以我決定使用分析函式來，要不按照以前的方法，建臨時表之類的還是有些麻煩了。
按照要求建了一個簡單的表來測試一下。
SQL> create table test(cdl_crd number,charge_amount  number,tax_amount number);
Table created.
SQL> insert into test values(50,20,5);
insert into test values(50,30,6);
1 row created.
SQL> 
1 row created.
SQL> insert into test values(50,50,7);
1 row created.
SQL> commit;
Commit complete.

檢視資料分佈情況，和示例的一樣。
SQL> select *from test;
   CDL_CRD CHARGE_AMOUNT TAX_AMOUNT
---------- ------------- ----------
        50            20          5
        50            30          6
        50            50          7
開始計算。
SQL> select cdl_crd*tax_amount/sum(tax_amount+charge_amount) over(partition by cdl_crd) apply_amount from test;
APPLY_AMOUNT
------------
  2.11864407
  2.96610169
  2.54237288

關於分析函式確實能省去不少表的自連線帶來的困擾，而且效能也不賴，在充分的測試之後使用其實還是很不錯的。
         

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1349347/，如需轉載，請註明出處，否則將追究法律責任。

相關文章