oracle 累乘计算,oracle sql语句实现累加、累减、累乘、累除

在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_prod

from 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_prod

from (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_prod

from 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_prod

from (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(负数)没有意义