oracle中关于分区查找求百分数的问题:

发布网友发布时间：2022-04-09 01:40

共1个回答

热心网友时间：2022-04-09 03:10

create table test1(time varchar2(10),month varchar2(10),sales number);

insert into test1 values('20130411','201304','100');
insert into test1 values('20130420','201304','500');
insert into test1 values('20130502','201305','300');
insert into test1 values('20130522','201305','700');
insert into test1 values('20130521','201305','900');
insert into test1 values('20130617','201306','200');
insert into test1 values('20120717','201207','666');

SQL> select time,month,sales, ratio_to_report(sales) over(partition by month) a,
ratio_to_report(sales) over() b from test1;

TIME MONTH SALES A B
---------- ---------- ---------- ---------- ----------
20120717 201207 666 1 .197860963
20130420 201304 500 .833333333 .148544266
20130411 201304 100 .166666667 .029708853
20130521 201305 900 .473684211 .267379679
20130502 201305 300 .157894737 .08912656
20130522 201305 700 .368421053 .207961973
20130617 201306 200 1 .059417706

已选择7行。