Как рассчитать текущее умножение

Ответ 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

Вывод:

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:

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

EDIT2

Для использования 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

EDIT3

Вот полное решение для SQL Server 2008 с набором номера с NULL и отрицательными значениями:

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

Вы можете преобразовать серию умножений в ряд дополнений со следующим математическим трюком:

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

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

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

Это испускает sum = 10, product = 24.

Потенциальными проблемами являются ошибки округления и нулевые факторы.

Теперь вы можете использовать один из обычных способов достижения заданного агрегата, например, функции окон. Это решенная проблема.

Ответ 3

Для полноты здесь представлено полное решение для SQL Server 2012, в котором используется трюк EXP(SUM(LOG(val))), предложенный @usr в другом ответе.

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;

набор результатов

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

Если SQL Server 2012 доступен, это окно SUM очень эффективно. Для предыдущих версий любое решение на основе набора приведет к сложности O(n*n), что означает, что курсор будет лучшим способом. Вот очень хорошая статья Аарона Бертрана, сравнивающая различные методы расчета текущих итогов: http://sqlperformance.com/2012/07/t-sql-queries/running-totals Или вопрос SO: Рассчитать общий общий/текущий баланс

Конечно, если ваша таблица мала, то решение на основе набора с O(n*n) сложностью может работать быстрее, чем O(n) с курсором из-за накладных расходов курсора, поэтому вам нужно проверить производительность с вашими реальными данными.

Ответ 4

вы можете сделать это легко с помощью рекурсивного 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

Вывод:

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

зарегистрировать демо

EDIT - добавление другого решения:

вы можете сделать это, взяв справку из копии исходной таблицы:

Предполагая, что ваша схема и данные:

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.Выберите копию из исходной таблицы (используйте temp table или table variable) и обновите первую запись из базовой таблицы:

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 @id int,@rm numeric(38,15)
select @id=min(id) from #tmp

select @rm=Running_Mul from #tmp where [email protected]

3. обновить копию:

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

и теперь вы можете проверить результат:

select * from #tmp
drop table #tmp

Результат:

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