如何计算运行乘法

小编典典

如何计算运行乘法

sql

我有两张桌子

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

SQLFIDDLE

在这里，对于每一行，上一行的Running_Mul值是baseline，对于第一行，其值baseline将来自baseline table。

希望我说清楚。我们可以使用AFAIK来做到这一点，CURSOR但我想尽可能避免 RBAR。谁能建议我这样做的更好方法。

阅读 197

2021-03-10

共1个答案

小编典典

尝试：

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

编辑1

我试图实现LOG EXP技巧，但除非@usr将我引向解决方案，否则无法进行管理。因此，所有功劳归于用户@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是您订购的字段。

输出：

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

编辑2

对于SQL 2008，请使用：

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

编辑3

这是使用NULL和负值进行拨号的SQL Server 2008完整解决方案：

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

2021-03-10