在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)
ln(X)=
ln
X
\ln X
lnX=
log
e
X
\log_eX
logeX
ln
(
M
∗
N
)
\ln (M*N)
ln(M∗N)=
ln
(
M
)
\ln (M)
ln(M)+
ln
(
N
)
\ln (N)
ln(N)
exp
(
l
n
(
5
)
)
=
5
\exp(ln(5))=5
exp(ln(5))=5
以下示例第一行和第二行(QUALITY 2*3):
e
x
p
(
s
u
m
(
l
n
(
exp(sum(ln(
exp(sum(ln(T.QUALITY$))) $
=
e
x
p
(
l
n
(
2
)
+
l
n
(
3
)
)
=exp(ln(2)+ln(3))
=exp(ln(2)+ln(3))
=
e
x
p
(
l
n
(
2
∗
3
)
)
=exp(ln(2*3))
=exp(ln(2∗3))
=
2
∗
3
=2*3
=2∗3
=
6
=6
=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(负数)没有意义