Oracle分析函数之ratio_to_report()详解

 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,
        ROUND(100*ratio_to_report(score) over(),1) as  "占所有科目的百分比",
        ROUND(100*ratio_to_report(score) over(partition by kemu),1) as  "占各科目的百分比"
   from test ;

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；

 

嘿嘿，还是没有那个方便，估计效率也不咋的。