How to calculate Running Multiplication

问题:

I have two tables

WAC table

ID  wac_inc             item
--  -----------------   ----
1   2.310000000000000   A
2   1.100000000000000   A
3   2.130000000000000   A
4   1.340000000000000   A

Baseline Table

item    baseline
----    ------------------
A       10.000000000000000

Expected Result

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))

Formula to find running_mul is

Baseline * (1 + (wac_inc/100))

SQLFIDDLE

here for every row previous row Running_Mul value is the baseline and for the first row baseline will be coming from baseline table.

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.

回答1:

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

回答2:

You can transform a series of multiplications into a series of additions with the following math trick:

exp(log(a) + log(b)) = a * b

So MUL(a) is EXP(SUM(LOG(a))).

SELECT SUM(val) AS [Sum], EXP(SUM(LOG(val))) AS Product
FROM (VALUES 
    (1), (2), (3), (4)
) x(val)

This emits sum = 10, product = 24.

Potential problems are rounding errors and zero factors.

You can now use one of the usual ways to achieve a running aggregate such as windowing functions. That's a solved problem.

回答3:

For the sake of completeness here is a full solution for SQL Server 2012 that uses the EXP(SUM(LOG(val))) trick suggested by @usr in another answer.

WITH
CTE
AS
(
    SELECT
        0 AS ID
        ,item
        ,baseline AS wac_inc
        ,baseline AS m
    FROM baseline

    UNION ALL

    SELECT
        ID
        ,item
        ,wac_inc
        ,1 + wac_inc/100 AS m
    FROM wac
)
SELECT
    ID
    ,item
    ,wac_inc
    ,m
    ,EXP(SUM(LOG(m)) OVER (PARTITION BY item ORDER BY ID ROWS UNBOUNDED PRECEDING)) AS MulRows
FROM CTE;

result set

ID  item wac_inc            m                   MulRows
0   A   10.000000000000000  10.000000000000000  10
1   A   2.310000000000000   1.023100000000000   10.231
2   A   1.100000000000000   1.011000000000000   10.343541
3   A   2.130000000000000   1.021300000000000   10.5638584233
4   A   1.340000000000000   1.013400000000000   10.7054141261722

If SQL Server 2012 is available, this window SUM is very efficient. For previous versions any set-based solution would result in O(n*n) complexity, which means that cursor would be a better way. Here is a very good article by Aaron Bertrand comparing different methods of calculating running totals: http://sqlperformance.com/2012/07/t-sql-queries/running-totals Or SO question: Calculate running total / running balance

Of course, if your table is small, then set-based solution with O(n*n) complexity may run faster than O(n) solution with the cursor due to cursor overhead, so you need to check the performance with your real data.

回答4:

you could do it easily with a recursive cte:

with rec(id ,wi,i,r) as 
(
  select top (1) w.ID,w.wac_inc,w.item, b.baseline * (1 + (w.wac_inc/100))
  from wac w join baseline b on w.item=b.item
  union all
  select w.ID,w.wac_inc,w.item, r.r * (1 + (w.wac_inc/100))
  from wac w
  join rec r on (w.ID)-1 = r.id
 )
 select * from rec

Output:

1   2.31    A   10.231
2   1.1     A   10.343541
3   2.13    A   10.563858
4   1.34    A   10.705414

check in the demo

---

EDIT - Adding another solution:

you can do it by taking help from a copy of your original table :

Assuming your schema and data is:

create  table wac
  (ID int,wac_inc numeric(38,15),item char )

insert wac
values (1,2.31,'A'),
(2,1.1,'A'),
(3,2.13,'A'),
(4,1.34,'A')

1.take a copy from original table(use a temp table or a table variable) and update the first record from baseline table:

create table  #tmp (ID int,wac_inc numeric(38,15),item char, Running_Mul numeric(38,15))
insert into #tmp select id,wac_inc,item,null from wac

update #tmp set Running_Mul = (select top 1 baseline from baseline)*(1+(wac_inc/100))
where id = (select min(id) from #tmp)

2.declare these variables:

declare @id int,@rm numeric(38,15)
select @id=min(id) from #tmp

select @rm=Running_Mul from #tmp where id=@id

3.update the copy:

update #tmp
set @rm=Running_Mul= case 
                    when @id <> id then @rm*(1+(wac_inc/100))
                    else Running_Mul
                 end,
@id=id

and now you can check the result:

select * from #tmp
drop table #tmp

Result:

ID  wac_inc             item    Running_Mul
1   2.310000000000000   A   10.231000000000000
2   1.100000000000000   A   10.343541000000000
3   2.130000000000000   A   10.563858000000000
4   1.340000000000000   A   10.705414000000000