在oracle开发过程中经常会遇到累加、减、乘、除的问题。下面对这个做个小的总结
--创建测试表create table aspen(parent_id number,part_id number,qnty number);insert into aspen values(1,1,2);insert into aspen values(1,2,3);insert into aspen values(1,3,2);insert into aspen values(1,4,5);insert into aspen values(2,2,3);insert into aspen values(2,3,5);insert into aspen values(2,4,7);
--实现累加select parent_id,part_id,qnty,sum(qnty) over(partition by parent_id order by part_id) running_prodfrom aspen
PARENT_ID PART_ID QNTY RUNNING_PROD---------- ---------- ---------- ------------ 1 1 2 2 1 2 3 5 1 3 2 7 1 4 5 12 2 2 3 3 2 3 5 8 2 4 7 15
--实现累减select parent_id,part_id,qnty, sum(decode(rn,1,qnty,-qnty)) over(partition by parent_id order by part_id) running_prodfrom (select parent_id,part_id,qnty, row_number() over(partition by parent_id order by part_id) rn from aspen)
PARENT_ID PART_ID QNTY RUNNING_PROD---------- ---------- ---------- ------------ 1 1 2 2 1 2 3 -1 1 3 2 -3 1 4 5 -8 2 2 3 3 2 3 5 -2 2 4 7 -9
--实现累乘select parent_id,part_id,exp(sum(ln(qnty)) over(partition by parent_id order by part_id)) running_prodfrom aspen
PARENT_ID PART_ID RUNNING_PROD--------- ---------- ------------ 1 1 2 1 2 6 1 3 12 1 4 60 2 2 3 2 3 15 2 4 105
--实现累除select parent_id,part_id,exp(sum(decode(rn,1,ln(qnty),-ln(qnty))) over(partition by parent_id order by part_id)) running_prodfrom (select parent_id,part_id,qnty, row_number() over(partition by parent_id order by part_id) rn from aspen )
PARENT_ID PART_ID RUNNING_PROD---------- ---------- ------------ 1 1 2 1 2 .666666667 1 3 .333333333 1 4 .066666667 2 2 3 2 3 .6 2 4 .085714286
累乘和累除的缺陷是不能对负数进行运算 因为ln(负数)没有意义
