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