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;
ratio_to_report(a)函数用法
Ratio_to_report() 括号中就是分子,
over() 括号中就是分母 分母缺省就是整个占比
总结:1. 有了ratio_to_report分析函数,我们避免了还需要写分析函数,自己相除的写法,SQL简单实现了。
转载请注明原文地址: https://ju.6miu.com/read-700003.html