今天在做oracle转pg的过程碰到一个oracle有，而pg没有的函数--- ratio_to_report()。
create table a_0807test
 (
 name varchar(20),
 kemu varchar(20),
 score number
 );

  truncate table a_0807test
  insert into a_0807test values('joy','语文',87);
  insert into a_0807test values('joy','英语',100);
  insert into a_0807test values('lucy','语文',98);
  insert into a_0807test values('lucy','语文',92);
  insert into a_0807test values('lily','语文',86);
 
 select name,kemu,score from a_0807test ;
 http://s3/mw690/4c197d42tx6BODeBOhkd2&690

 select name,score,
   ratio_to_report(score) over()as  "占所有科目的百分比",
   ratio_to_report(score) over(partition by kemu) as  "占各科目的百分比"
 from a_0807test ;
 http://s9/mw690/4c197d42tx6BODdwLva38&690


 如果没有这个函数
  select name,score,
  (score/sum(score) over())   as "百分比_占所有科目",
  (score/sum(score) over(partition by kemu))   as "百分比_占各科目"
 from a_0807test
 group by name,score,kemu;
  http://s12/mw690/4c197d42tx6BODfQ0Cnbb&690


  select name,score,
  sum(score) over() as "所有科目总分",
  sum(score) over(partition by kemu)  as "该科目总分"
 from a_0807test
 group by name,score,kemu;
http://s12/mw690/4c197d42tx6BODgutvR5b&690


或者
 select name,score,score/all_per as "百分比_所有科目",score/area_per as "百分比_各科目"
 from (
   select name,score,
  sum(score) over() as all_per,
  sum(score) over(partition by kemu)  as area_per
 from a_0807test
 )
http://s10/mw690/4c197d42tx6BODhcT5D09&690


  truncate table a_0807test;
  insert into a_0807test values('joy','语文',87);
  insert into a_0807test values('joy','英语',100);
  insert into a_0807test values('lucy','语文',98);
  insert into a_0807test values('lucy','语文',92);
  insert into a_0807test values('lily','语文',86);
  insert into a_0807test values('joy','英语',88);
  insert into a_0807test values('joy','语文',99);
  insert into a_0807test values('lucy','英语',80);
  insert into a_0807test values('lucy','英语',84);
  insert into a_0807test values('lily','英语',78);
 
 
 select name,score,kemu,
 ratio_to_report(score) over(partition by kemu) as rtr,
 score/(sum(score) over(partition by kemu)) as ss
 from a_0807test
http://s11/mw690/4c197d42tx6BODhVQKe6a&690

 select name,score,kemu,
 ratio_to_report(score) over(partition by name) as rtr,
 score/(sum(score) over(partition by name)) as ss
 from a_0807test
http://s7/mw690/4c197d42tx6BODkRWIe56&690

 select name,score,kemu,
 ratio_to_report(score) over(partition by kemu) as rtrk,
 score/(sum(score) over(partition by kemu)) as ssk,
 ratio_to_report(score) over() as rtr,
 score/(sum(score) over()) as ss
 from a_0807test
http://s14/mw690/4c197d42tx6BODvti1fbd&690

Ratio_to_report() 括号中的就是分子，over() 括号中的就是分母，
如果分母缺省就是整个占比。