oracle 下实现累计求和

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

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

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

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

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

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

在oracle开发过程中经常会遇到累加、减、乘、除的问题。@H_502_2@ 下面对这个做个小的总结

@H_502_2@ --创建测试表@H_502_2@ create table aspen(parent_id number,part_id number,qnty number);@H_502_2@ insert into aspen values(1,1,2);@H_502_2@ insert into aspen values(1,2,3);@H_502_2@ insert into aspen values(1,3,4,5);@H_502_2@ insert into aspen values(2,3);@H_502_2@ insert into aspen values(2,7);

@H_502_2@ --实现累加@H_502_2@ select parent_id,part_id,qnty,@H_502_2@ sum(qnty) over(partition by parent_id order by part_id) running_prod@H_502_2@ from aspen

PARENT_ID PART_ID QNTY RUNNING_PROD@H_502_2@ ---------- ---------- ---------- ------------@H_502_2@ 1 1 2 2@H_502_2@ 1 2 3 5@H_502_2@ 1 3 2 7@H_502_2@ 1 4 5 12@H_502_2@ 2 2 3 3@H_502_2@ 2 3 5 8@H_502_2@ 2 4 7 15

@H_502_2@ --实现累减@H_502_2@ select parent_id,@H_502_2@ sum(decode(rn,-qnty)) over(partition by parent_id order by part_id) running_prod@H_502_2@ from (select parent_id,@H_502_2@ row_number() over(partition by parent_id order by part_id) rn@H_502_2@ from aspen)

@H_502_2@ PARENT_ID PART_ID QNTY RUNNING_PROD@H_502_2@ ---------- ---------- ---------- ------------@H_502_2@ 1 1 2 2@H_502_2@ 1 2 3 -1@H_502_2@ 1 3 2 -3@H_502_2@ 1 4 5 -8@H_502_2@ 2 2 3 3@H_502_2@ 2 3 5 -2@H_502_2@ 2 4 7 -9

--实现累乘@H_502_2@ select parent_id,@H_502_2@ exp(sum(ln(qnty)) over(partition by parent_id order by part_id)) running_prod@H_502_2@ from aspen

@H_502_2@ PARENT_ID PART_ID RUNNING_PROD@H_502_2@ --------- ---------- ------------@H_502_2@ 1 1 2@H_502_2@ 1 2 6@H_502_2@ 1 3 12@H_502_2@ 1 4 60@H_502_2@ 2 2 3@H_502_2@ 2 3 15@H_502_2@ 2 4 105

--实现累除@H_502_2@ select parent_id,@H_502_2@ exp(sum(decode(rn,ln(qnty),-ln(qnty))) over(partition by parent_id order by part_id)) running_prod@H_502_2@ from (select parent_id,@H_502_2@ row_number() over(partition by parent_id order by part_id) rn@H_502_2@ from aspen )

@H_502_2@ PARENT_ID PART_ID RUNNING_PROD@H_502_2@ ---------- ---------- ------------@H_502_2@ 1 1 2@H_502_2@ 1 2 .666666667@H_502_2@ 1 3 .333333333@H_502_2@ 1 4 .066666667@H_502_2@ 2 2 3@H_502_2@ 2 3 .6@H_502_2@ 2 4 .085714286

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 和sum(qnty) over(partition by parent_id order by part_id)可实现累加，其余类似。

oracle 下实现累计求和

猜你在找的Oracle相关文章