ratio_to_report分析函數求占比

 drop table test;

 create table test

 (

 name varchar(20),

 kemu varchar(20),

 score number

 );

 insert into test values('testa','yuwen',10);

 insert into test values('testa','英語',100);

 insert into test values('testb','yuwen',60);

 insert into test values('testb','yuwen',120);

 insert into test values('testc','yuwen',40);

 select name,

  score,

  ratio_to_report(score) over() as  "占所有科目的百分比",

  ratio_to_report(score) over(partition by kemu) as  "占各科目的百分比"

 from test ;

NAME                      SCORE 占所有科目的百分比 占各科目的百分比

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

testa                        10          .03030303       .043478261

testb                        60         .181818182       .260869565

testc                        40         .121212121       .173913043

testb                       120         .363636364        .52173913

testa                       100         .303030303                1

drop table test;

試想下假設我們沒有這個分析函數，實作就有可能如下：

 select name,score,

  (score/sum(score) over())   as "占所有科目的百分比",

  (score/sum(score) over(partition by kemu))   as "占所有科目的百分比"

 from test

 group by name,score,kemu

 order by 2；

嘿嘿，還是沒有那個友善，估計效率也不咋的。

總結：1. 有了ratio_to_report分析函數，我們避免了還需要寫分析函數，自己相除的寫法，SQL簡單實作了。

 2. site:download.oracle.com ratio_to_report 搜尋oracle官方文檔