oracle sql分析函数ratio_to_report按比例计算分摊数值并且去尾差
数据准备:
create table TSHARESTREAM_TEST
(
rank_id INTEGER,
c_saleserialno VARCHAR2(255),
c_originalno VARCHAR2(255),
f_saleshares NUMBER(16,2)
);
insert into tsharestream_test (RANK_ID, C_SALESERIALNO, C_ORIGINALNO, F_SALESHARES)
values (1510, '0050000001533', null, 1.00);
insert into tsharestream_test (RANK_ID, C_SALESERIALNO, C_ORIGINALNO, F_SALESHARES)
values (1510, '0050000001533', null, 1.00);
insert into tsharestream_test (RANK_ID, C_SALESERIALNO, C_ORIGINALNO, F_SALESHARES)
values (1510, '0050000001533', null, 3.00);
代码:
这里1000是写死的待分摊总额,按照tsharestream_test 表的f_saleshares字段比例分摊着1000,并将尾差加在最大比例的一笔上
select a.rank_id,a.c_saleserialno,a.f_saleshares,
case when (a.rn < a.cnt or a.rn