ratio_to_report分析函数求占比

来源：互联网发布：买岛国片在淘宝输什么编辑：程序博客网时间：2024/04/29 12:19

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官方文档

转自：http://blog.csdn.net/ningjieshuijing/article/details/5854495

0 0