SQL Server语言 - 在计算字段的总和中使用 LAG

Question

我需要根据前一行不同列中的字段值计算运行总计。

一个简化的例子是

例如

Day Investment(A)  Payments(B)   Multiplier(C) Profit(D)  TotalFund(E)
0   100            0                0             0         100
1   100            0                0.1           10        110
2   100            0                0.5           55        165
3   90            10               -0.2          -33        122
4   70            20                0.5           61        163
5   80           -10                0.1           16.3      189.3

所以我的表格将包含"日"、"投资 (A(、付款 (B( 和乘数 (C( "列。盈利(D(的计算方法是前一天的总资金(E(乘以当天的乘数(C(总基金(E(的计算方法是前一天的总基金(E(加上当日的利润(D(减去当天的付款(B(

我不知道如何在 SUM 中使用 LAG 来做到这一点

Answer 1

我认为你不能用LAG来计算它。这是使用recursive CTE的解决方案

with myTable as (
    select * from
        (values 
            (0,100,0,0)
            ,(1,100,0,0.1)
            ,(2,100,0,0.5)
            ,(3,90,10,-0.2)
            ,(4,70,20,0.5)
            ,(5,80,-10,0.1)
        ) t ([Day], [Investment(A)], [Payments(B)], [Multiplier(C)])
)
, rcte as (
    select
        *, [Profit(D)] = cast(0 as decimal(20,4)), [TotalFund(E)] = cast(100 as decimal(20,4))
    from
        myTable
    where [Day] = 0
    union all
    select
        b.[Day], b.[Investment(A)], b.[Payments(B)], b.[Multiplier(C)], cast(a.[TotalFund(E)] * b.[Multiplier(C)] as decimal(20,4))
        , cast(a.[TotalFund(E)] + a.[TotalFund(E)] * b.[Multiplier(C)] - b.[Payments(B)] as decimal(20,4))
    from
        rcte a
        join myTable b on a.[Day] + 1 = b.[Day]
)
select * from rcte
option (maxrecursion 0)