在oracle开发过程中经常会遇到累加、减、乘、除的问题。下面对这个做个小的总结
---创建测试表
CREATE TABLE TEST( PARENT_ID NUMBER, PART_ID NUMBER, QUALITY NUMBER); INSERT INTO TEST VALUES(1,1,2); INSERT INTO TEST VALUES(1,2,3); INSERT INTO TEST VALUES(1,3,2); INSERT INTO TEST VALUES(1,4,5); INSERT INTO TEST VALUES(2,2,3); INSERT INTO TEST VALUES(2,3,5); INSERT INTO TEST VALUES(2,4,7);
一、实现累加
SELECT T.*, SUM(T.QUALITY) OVER (PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RUNNING_QUALITY FROM TEST T
二、实现累减
SELECT T.PARENT_ID, T.PART_ID, T.QUALITY, SUM(DECODE(T.RN,1,T.QUALITY,-T.QUALITY)) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RUNNING_PROD FROM ( SELECT T.*, ROW_NUMBER() OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RN FROM TEST T) T
三、实现累乘
SELECT T.*, ROUND(EXP(SUM(LN(T.QUALITY)) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID)),0) RUNNING_PROD FROM TEST T
四、实现累除
SELECT T.PARENT_ID, T.PART_ID, T.QUALITY, EXP(SUM(DECODE(RN,1,LN(T.QUALITY),-LN(T.QUALITY))) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID)) RUNNING_PROD FROM ( SELECT T.*, ROW_NUMBER() OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RN FROM TEST T)T
累乘和累除的缺陷是不能对负数进行运算 因为ln(负数)没有意义