如何计算运行乘法 [英] How to calculate Running Multiplication
问题描述
我有两张桌子
WAC 表
ID wac_inc item
-- ----------------- ----
1 2.310000000000000 A
2 1.100000000000000 A
3 2.130000000000000 A
4 1.340000000000000 A
基线表
item baseline
---- ------------------
A 10.000000000000000
预期结果
ID wac_inc item Running_Mul
-- ----------------- ---- -----------
1 2.310000000000000 A 10.231 -- 10 * (1+(2.310000000000000/100))
2 1.100000000000000 A 10.343541 -- 10.231 * (1+(1.100000000000000/100))
3 2.130000000000000 A 10.563858 -- 10.343541 * (1+(2.130000000000000/100))
4 1.340000000000000 A 10.705413 -- 10.563858 * (1+(1.340000000000000/100))
找到running_mul的公式是
基线 * (1 + (wac_inc/100))
Baseline * (1 + (wac_inc/100))
这里对于每一行前一行 Running_Mul 值是 baseline 并且对于第一行 baseline 将来自 baseline 表.
here for every row previous row Running_Mul value is the baseline and for the first row baseline will be coming from baseline table.
希望我说清楚了.AFAIK 我们可以使用 CURSOR 来做到这一点，但我想尽可能地避免 RBAR.谁能建议我更好的方法.
Hope i made it clear. AFAIK we can do this using CURSOR but i want to avoid RBAR as much as possible.
Can anyone suggest me the better way of doing it.
推荐答案
尝试:
DECLARE @t TABLE
(
ID INT ,
wac DECIMAL(30, 10) ,
item CHAR(1)
)
DECLARE @b TABLE
(
item CHAR(1) ,
baseline DECIMAL(30, 10)
)
INSERT INTO @t
VALUES ( 1, 2.31, 'A' ),
( 2, 1.10, 'A' ),
( 3, 2.13, 'A' ),
( 4, 1.34, 'A' )
INSERT INTO @b
VALUES ( 'A', 10 );
WITH ordercte
AS ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY item ORDER BY ID ) AS rn
FROM @t
),
rec
AS ( SELECT t.item ,
t.ID ,
t.wac ,
t.rn ,
b.baseline * ( 1 + ( t.wac / 100 ) ) AS m
FROM ordercte t
JOIN @b b ON b.item = t.item
WHERE t.rn = 1
UNION ALL
SELECT t.item ,
t.ID ,
t.wac ,
t.rn ,
c.m * ( 1 + ( t.wac / 100 ) )
FROM ordercte t
JOIN rec c ON t.item = c.item
AND t.rn = c.rn + 1
)
SELECT id ,
wac ,
item ,
m
FROM rec
输出:
id wac item m
1 2.3100000000 A 10.231000
2 1.1000000000 A 10.343541
3 2.1300000000 A 10.563858
4 1.3400000000 A 10.705414
EDIT1
我试图实现 LOG EXP 技巧，但无法管理，除非@usr 引导我找到解决方案.所以所有功劳都归功于用户@usr:
I was trying to implement LOG EXP trick but could not manage unless @usr lead me to solution. So all credits to user @usr:
WITH ordercte
AS ( SELECT t.ID ,
t.wac ,
t.item ,
b.baseline ,
ROW_NUMBER() OVER ( PARTITION BY t.item ORDER BY ID ) AS rn
FROM @t t
JOIN @b b ON b.item = t.item
)
SELECT baseline
* EXP(SUM(LOG(( 1 + ( wac / 100 ) ))) OVER ( PARTITION BY item ORDER BY rn )) AS m
FROM ordercte
或者只是:
SELECT t.ID, t.wac, t.item, baseline
* EXP(SUM(LOG(( 1 + ( wac / 100 ) ))) OVER ( PARTITION BY t.item ORDER BY t.ID )) AS m
FROM @t t
JOIN @b b ON b.item = t.item
如果 ID 是您订购的字段.
if ID is the field you order by.
输出:
ID wac item m
1 2.3100000000 A 10.231
2 1.1000000000 A 10.343541
3 2.1300000000 A 10.5638584233
4 1.3400000000 A 10.7054141261722
EDIT2
对于 SQL 2008 使用:
For SQL 2008 use:
WITH cte
AS ( SELECT t.ID ,
t.wac ,
t.item ,
baseline ,
( SELECT SUM(LOG(( 1 + ( wac / 100 ) )))
FROM @t it
WHERE it.item = t.item AND it.ID <= t.ID
) AS e
FROM @t t
JOIN @b b ON b.item = t.item
)
SELECT ID, wac, item, baseline * EXP(e) AS m
FROM cte
EDIT3
这是 SQL Server 2008 的完整解决方案，使用 NULL 和负值进行拨号:
Here is complete solution for SQL Server 2008 with dialing with NULLs and negative values:
WITH cte
AS ( SELECT t.ID ,
t.wac ,
t.item ,
b.baseline ,
ca.e,
ca.n,
ca.m
FROM @t t
JOIN @b b ON b.item = t.item
CROSS APPLY(SELECT SUM(LOG(ABS(NULLIF( 1 + wac / 100 , 0)))) as e,
SUM(SIGN(CASE WHEN 1 + wac / 100 < 0 THEN 1 ELSE 0 END)) AS n,
MIN(ABS(1 + wac / 100)) AS m
FROM @t it
WHERE it.item = t.item AND it.ID <= t.ID
) ca
)
SELECT ID, wac, item, baseline *
CASE
WHEN m = 0 THEN 0
WHEN n % 2 = 1 THEN -1 * EXP(e)
ELSE EXP(e)
END as Result
FROM cte
这篇关于如何计算运行乘法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
