oracle sql语句实现累加、累减、累乘、累除代码,Oracle代码库,德仔网

代码语言

代码分类

【Oracle】 oracle sql语句实现累加、累减、累乘、累除

作者: / 发布于2018/5/9/ 1594

--创建测试表
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(负数)没有意义

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

同语言下

可能有用的

贡献的其它代码Label