How to calculate Running Multiplication
Try:
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
Output:
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
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
Or just:
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
if ID is the field you order by.
Output:
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
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
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
How to find Running Multiplication
Your method is pretty reasonable. Good catch on the nullif() in the sum(), by the way. Although the else clause is computed only after the then, components of the else are calculated during the aggregation -- so log(0) would return an error.
I think there are some simpler ways to calculate the sign, such as:
power(-1, sum(case when column1 < 0 then 1 else 0 end))
or:
(case when sum(case when column1 < 0 then 1 else 0 end) % 2 = 0 then 1 else -1 end)
However, which version is "simpler" is a matter of opinion.
Power bi: Calculate the running total and multiply by each months value
Here is one way to do it:
//amount_total measure calculates total amount
amount_total = SUMX(data, data[amount])
//value_total measure calculates total value
value_total = SUMX(data, data[value])
//amount_rt measure calculates running total multiplied by value
amount_rt =
VAR cur_date = MAX(data[date])
VAR rt = CALCULATE(
[amount_total],
FILTER(
ALL(data),
data[date] <= cur_date
)
)
RETURN rt * [value_total]
Result:
Running Multiplication in T-SQL
Following solution assumes there are always 3 rows per quarter (only the last quarter might be partial), single SELECT, no recursion :-)
WITH sumQuart AS
(
SELECT *,
CASE
WHEN ROW_NUMBER() -- for the 1st month in a quarter
OVER (PARTITION BY CCP, Years, Quart
ORDER BY months) = 1
-- return the sum of all GTS of this quarter
THEN SUM(GTS) OVER (PARTITION BY CCP, Years, Quart)
ELSE NULL -- other months
END AS sumGTS
FROM gts
)
,cte AS
(
SELECT
sq.*,
COALESCE(b.Baseline, -- 1st quarter
-- product of all previous quarters
CASE
WHEN MIN(ABS(sumGTS)) -- any zeros?
OVER (PARTITION BY sq.CCP ORDER BY sq.Years, sq.Quart, sq.Months
ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING) = 0
THEN 0
ELSE -- product
EXP(SUM(LOG(NULLIF(ABS(COALESCE(b.Baseline,1) * sumGTS),0)))
OVER (PARTITION BY sq.CCP ORDER BY sq.Years, sq.Quart, sq.Months
ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING)) -- product
-- odd number of negative values -> negative result
* CASE WHEN COUNT(CASE WHEN sumGTS < 0 THEN 1 END)
OVER (PARTITION BY sq.CCP ORDER BY sq.Years, sq.Quart, sq.Months
ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING) % 2 = 0 THEN 1 ELSE -1 END
END) AS newBaseline
FROM sumQuart AS sq
LEFT JOIN BASELINE AS b
ON B.CCP = sq.CCP
AND b.Quart = sq.Quart
AND b.Years = sq.Years
)
SELECT
CCP, months, Quart, Years, GTS,
round(newBaseline * GTS,2),
round(newBaseline,2)
FROM cte
See Fiddle
EDIT:
Added logic to handle values <= 0 Fiddle
Oracle Running Multiplication
The exp(sum(ln())) approach works as long as you add the analytics for the sum() part, not for the exp(). This would give you the product of the original values:
WITH t AS (
SELECT 1 AS id, DATE '2014-01-01' AS dat, 0.001 AS value FROM dual
UNION ALL SELECT 2, DATE '2014-01-02', 0.003 FROM dual
UNION ALL SELECT 3, DATE '2014-01-03', 0.002 FROM dual
)
SELECT id, dat, value, EXP(SUM(LN(value))
OVER (PARTITION BY null ORDER BY dat))
AS total
FROM t
ORDER BY dat;
ID DAT VALUE TOTAL
---------- --------- ---------- ----------
1 01-JAN-14 .001 .001
2 02-JAN-14 .003 .000003
3 03-JAN-14 .002 .000000006
And this would give you the product of the running total:
WITH t AS (
SELECT 1 AS id, DATE '2014-01-01' AS dat, 0.001 AS value FROM dual
UNION ALL SELECT 2, DATE '2014-01-02', 0.003 FROM dual
UNION ALL SELECT 3, DATE '2014-01-03', 0.002 FROM dual
),
u AS (
SELECT id, dat, value, SUM(value)
OVER (PARTITION BY null ORDER BY dat) AS total
FROM t
)
SELECT id, dat, value, total, EXP(SUM(LN(total))
OVER (PARTITION BY null ORDER BY dat)) AS product
FROM u
ORDER BY dat;
ID DAT VALUE TOTAL PRODUCT
---------- --------- ---------- ---------- ----------
1 01-JAN-14 .001 .001 .001
2 02-JAN-14 .003 .004 .000004
3 03-JAN-14 .002 .006 .000000024
Use your own table instead of the CTE obviously; and if you're trying to get the product/sum over multiple values with an ID when change it to partition by id. Using null is to make this work with your sample data.
Sum of previous rows and multiply with value from another column
While your post is lacking some vital information...I don't necessarily blame you because it's a difficult problem to both explain and to solve.
It looks like you're basically trying to come up with a compounding interest calculator. Except in this case, the rate changes every year.
To calculate the PRODUCT aggregate of your forecasts, I found this blog post:
https://blog.jooq.org/2018/09/21/how-to-write-a-multiplication-aggregate-function-in-sql/
It just required a very tiny bit of tweaking.
This is my answer:
DECLARE @StartingYear int = 2021,
@StartingBudget decimal(12, 2);
SELECT @StartingBudget = yt.budget
FROM #YourTable yt
WHERE yt.startyear = @StartingYear
SELECT yt.startyear, yt.division, yt.account, yt.budget, yt.forecast, YearBudget = yt.budget, PrevYearDiff = yt.budget
FROM #YourTable yt
WHERE yt.startyear = @StartingYear
UNION ALL
SELECT x.startyear, x.division, x.account, x.budget, x.forecast
, YearBudget = CONVERT(decimal(10,2), x.SumProdBudget)
, PrevYearDiff = CONVERT(decimal(10,2), x.SumProdBudget - LAG(x.SumProdBudget,1,@StartingBudget) OVER (ORDER BY x.startyear))
FROM (
SELECT yt.startyear, yt.division, yt.account, yt.budget, yt.forecast
, SumProdBudget = EXP(SUM(LOG(1+yt.forecast)) OVER (ORDER BY yt.startyear)) * @StartingBudget
FROM #YourTable yt
WHERE yt.startyear > @StartingYear
) x
Returns:
| startyear | division | account | budget | forecast | YearBudget | PrevYearDiff |
|-----------|----------|---------|------------|-------------|------------|--------------|
| 2021 | 40 | 4100 | 5122952.22 | 0.012306656 | 5122952.22 | 5122952.22 |
| 2022 | 40 | 4100 | 0.00 | 0.011424198 | 5181477.84 | 58525.62 |
| 2023 | 40 | 4100 | 0.00 | 0.010431491 | 5235528.38 | 54050.54 |
| 2024 | 40 | 4100 | 0.00 | 0.009311863 | 5284280.90 | 48752.52 |
| 2025 | 40 | 4100 | 0.00 | 0.008324122 | 5328267.90 | 43987.00 |
| 2026 | 40 | 4100 | 0.00 | 0.007763793 | 5369635.47 | 41367.57 |
The key to this whole thing is this line:
SELECT SumProdBudget = EXP(SUM(LOG(1+yt.forecast)) OVER (ORDER BY yt.startyear)) * @StartingBudget
This is saying to return a running product of all previous forecasts, then multiply that by the original budget. This will produce the budget for each year, technically based on the budget of the previous year.
Then once you have that, I stuck it in a sub-query to find the difference between the year and its previous year.
Sample Data:
IF OBJECT_ID('tempdb..#YourTable','U') IS NOT NULL DROP TABLE #YourTable; --SELECT * FROM #YourTable
CREATE TABLE #YourTable (
startyear int NOT NULL,
division int NOT NULL,
account int NOT NULL,
budget decimal(12, 2) NOT NULL,
forecast decimal(10, 9) NOT NULL,
);
INSERT INTO #YourTable (startyear, division, account, budget, forecast)
VALUES (2021, 40, 4100, 5122952.22, 0.012306656)
, (2022, 40, 4100, 0 , 0.011424198)
, (2023, 40, 4100, 0 , 0.010431491)
, (2024, 40, 4100, 0 , 0.009311863)
, (2025, 40, 4100, 0 , 0.008324122)
, (2026, 40, 4100, 0 , 0.007763793)
, (2027, 40, 4100, 0 , 0.007557735)
, (2028, 40, 4100, 0 , 0.007357883)
, (2029, 40, 4100, 0 , 0.007160051)
, (2030, 40, 4100, 0 , 0.006953345)
, (2031, 40, 4100, 0 , 0.006737952)
, (2032, 40, 4100, 0 , 0.006535297)
, (2033, 40, 4100, 0 , 0.006364179)
, (2034, 40, 4100, 0 , 0.006213237)
, (2035, 40, 4100, 0 , 0.006085724)
, (2036, 40, 4100, 0 , 0.005944279)
, (2037, 40, 4100, 0 , 0.005758285)
, (2038, 40, 4100, 0 , 0.005559474)
, (2039, 40, 4100, 0 , 0.005360105)
, (2040, 40, 4100, 0 , 0.005163794)
, (2041, 40, 4100, 0 , 0.004972228);
Calculate the cumulative sum of multiplying each element of one array by all the elements of a second array
In general multiplying two sliding windows is called a convolution, implemented in numpy. Your definition is subtly different at the end, however this can be fixed.
result = np.convolve(arr1, arr2)[:len(arr1)]
diff = len(arr1) - len(arr2)
for k in range(diff, len(arr1)):
# i = k - j
# 0 <= i < diff gives k - diff < j <= k
# 0 <= j < len(arr2)
lo = max(0, 1 + k - diff)
hi = min(1 + k, len(arr2))
result[k] = np.dot(arr1[k-hi+1:k-lo+1][::-1], arr2[lo:hi])
Related Topics
Use a Like Clause in Part of an Inner Join
SQL Recursion Without Recursion
Xml Query() Works, Value() Requires Singleton Found Xdt:Untypedatomic
Postgresql:How to Select Top N Percent(%) Entries from Each Group/Category
Preserve Parent-Child Relationships When Copying Hierarchical Data
T-SQL Select Get All Months Within a Range of Years
How to Use Array_Agg() for Varchar[]
Efficient Query to Split a Delimited Column into Separate Rows in Another Table
How to Determine Position of Row in SQL Result-Set
Select Records in on Table Based on Conditions from Another Table
Using Bcp Utility to Export SQL Queries to a Text File
How Does One Escape an Apostrophe in Db2 SQL
How to Use a SQL Select Statement with Access Vba
On Update Current_Timestamp and JPA