ORACLE sql语句实现累加、累减、累乘、累除

在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

---

三、实现累乘

在此使用到 ln()、sum()、exp()来计算

sum()：返回选择的数值和总和
ln()：返回x的自然对数. x必须是正数,并且大于0
exp()：计算e的x次幂. e为自然对数,约等于2.71828.

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

【分解】：

首先理解

$\ln (X)$=$\ln X$=${\log }_{e}X$
$\ln (M\ast N)$=$\ln (M)$+$\ln (N)$
$\exp (ln(5))=5$

以下示例第一行和第二行(QUALITY 2*3)：

$exp(sum(ln($T.QUALITY$))) $
$=exp(ln(2)+ln(3))$
$=exp(ln(2\ast 3))$
$=2\ast 3$
$=6$

---

四、实现累除

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