ratio_to_report 分析函数求占比

来源：互联网发布：win7网络加速器编辑：程序博客网时间：2024/04/29 15:05

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       .043478261testb                        60         .181818182       .260869565testc                        40         .121212121       .173913043testb                       120         .363636364        .52173913testa                       100         .303030303                1drop 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；

0 0