oracle 下实现累计求和

阳光穿透心脏的1/2处 2022-06-07 11:25 724阅读 0赞

row\_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).

与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．

row\_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．

rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

dense\_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row\_number是没有重复值的 ．  
lag（arg1,arg2,arg3):

arg1是从其他行返回的表达式  
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。  
arg3是在arg2表示的数目超出了分组的范围时返回的值。

在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

row\_number() OVER (PARTITION BY COL1 ORDER BY COL2) 和sum(qnty) over(partition by parent\_id order by part\_id) 可实现累加，其余类似。