问题概述
某一个报表数据来自一个复杂业务的查询，其中某类资产的数据需要按百分比展示。同时这类资产的百分比总和加起来应该是100% 。问题比如这个分类是2,2,2 百分比是0.33,0.33,0.33 最终给出的结果是需要修正的即走后一条数据应该修正为0.34；同理的 如果是 1,2,3,1 的百分比计算也需要修正最后一条数据。当时的业务场景下只能用存储过程
问题简化解析
首先加一些简单的测试数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| -- Create table create table OOTEST ( id NVARCHAR2(16), types NVARCHAR2(16), shares NUMBER ) insert into ootest (ID, TYPES, SHARES) values ('1', 'aa', 1); insert into ootest (ID, TYPES, SHARES) values ('2', 'aa', 1); insert into ootest (ID, TYPES, SHARES) values ('3', 'aa', 1); insert into ootest (ID, TYPES, SHARES) values ('4', 'bb', 1); insert into ootest (ID, TYPES, SHARES) values ('5', 'bb', 2); select t.id,t.types,t.shares, rowid from ootest t ;
|
eg：
| ID | TYPES | SHARES |
| ————- |:————-:| ——-:|
| 1 | aa | 1 |
| 2 | aa | 1 |
| 3 | aa | 1 |
| 4 | bb | 1 |
| 5 | bb | 2 |
存储过程实现 (Version 1.0)
初步想法需要搞个临时表，先筛选数据进去，再遍历修改，之后再查询临时表返回数据即可。为了简单实现这里就不用临时表了，直接查询，然后循环处理数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| /* --- -- -- 当过程中含有输出参数时，调用时必须通过BEGIN END块，不能通过EXEC或CALL调用。如： DECLARE retcode NUMBER(7,2); retnote varchar(4000); BEGIN queryPercentOne(retcode,retnote,'aa'); DBMS_OUTPUT.PUT_LINE(retcode); DBMS_OUTPUT.PUT_LINE(retnote); END; --- */
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
| create or replace procedure queryPercentOne(o_code out int, o_msg out varchar2,v_types in varchar2) is v_count number := 0; -- 总记录数 v_total number := 0; v_last number := 0; v_last_per number :=0; v_cid NVARCHAR2(16); v_ctypes NVARCHAR2(16); v_cshares number; v_rownum number :=0; v_cpercent number(3,2) :=0.0; v_percent number(3,2) :=0.0; v_crownum number :=1; CURSOR cur IS SELECT t.* FROM ootest t where t.types= v_types; begin begin select count(*) into v_count from ootest t where t.types= v_types; select sum(t.shares) into v_total from ootest t where t.types= v_types; EXCEPTION WHEN NO_DATA_FOUND THEN v_count :=0; v_total :=0; end ; open cur; LOOP fetch cur into v_cid,v_ctypes,v_cshares ; exit when cur%notfound; v_cpercent := v_cshares/v_total; if v_crownum = v_count then v_cshares := v_total; v_cpercent := (1 - v_percent); else v_percent := v_percent + (v_cshares/v_total); end if ; dbms_output.put_line(v_crownum||' . '||v_cid||' . '||v_ctypes||' . '||v_cshares||' .'|| to_char(v_cpercent,'0.99')); v_crownum := v_crownum+1; end LOOP; o_code :=-1; o_msg :='nothing'; end queryPercentOne;
|
测试存储过程
1 2 3 4 5 6
| begin -- Call the procedure querypercentone(o_code => :o_code, o_msg => :o_msg, v_types => :v_types); end;
|
测试 aa
1 . 1 . aa . 1 . 0.33
2 . 2 . aa . 1 . 0.33
3 . 3 . aa . 3 . 0.34
测试 bb
1 . 4 . bb . 1 . 0.33
2 . 5 . bb . 3 . 0.67
复杂SQL也可以做到 (Version2.0)
使用下列oracle自带函数
ratio_to_report
主要完成对百分比的计算，语法为
ratio_to_report(exp) over()
也就是根据over窗口函数的作用区间，求出作用区间中的单个值在整个区间的总值的比重
…
decode
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
……
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| select d.id, d.shares, d.ratio, d.cnt, d.total, decode(d.cnt, 1, d.tail_rt + d.rate, d.rate) rate, d.tail_rt from (select c.id, c.shares, c.ratio, -- 份额 c.total, round(c.ratio, 4) rate, --占比 1 - sum(round(c.ratio, 4)) over() tail_rt, --尾差 row_number() over(partition by c.types order by c.id) cnt from (select t.id, t.shares, t.types, b.total, ratio_to_report((t.shares)) OVER(partition by t.types) ratio from ootest t left join((select t.types, sum(t.shares) total from ootest t group by t.types) b) on t.types = b.types where t.types = 'bb') c) d;
|
