通过TSQL使用LIFO方法计算结果

Question

我想用TSQL通过后进先出法进行计算。

使用后进先出法将要求您通过出售最后交易来计算利润/损失。

工作原理示例：

1. 交易于3月1日达成，我们以每只5美元的价格购买10只股票
2. 交易于3月2日达成，我们以每只6美元的价格买进15只股票
3. 交易于3月3日达成，我们每只4美元买5只股票
4. 交易于3月4日达成，我们卖出17只股票，每只7美元

到第四笔交易时，我们已经从3月3日起以每股4美元的价格卖出了5只股票，从3月2日起以每只6美元的价格售出了12只股票。

因此，现在我们留下以下内容：3月1日交易的10只股票，每只5美元3月2日交易的3只股票，每只6美元（17-5-15=-3）。

还剩13只股票，平均价格为（10*5+3*6）/13=5.23076923

以下是测试数据生成脚本：

use TestTask
go
IF OBJECT_ID('testtable','U')IS NOT NULL
        DROP TABLE testtable
go
create table testtable
(
stockid int not null,
dealid int identity (1,1) not null,
dealtype char(1) not null,
stockdate datetime not null,
stockamount int not null,
priceperstock int not null
)
insert into testtable(stockid,dealtype,stockdate,stockamount,priceperstock)
VALUES
    (111,'B','01.03.2016',10,5),
    (111,'B','02.03.2016',15,6),
    (111,'B','03.03.2016',5,4),
    (111,'S','04.03.2016',17,7)

我想计算财务状况和许多其他参数，这需要我知道还有多少合适价格的股票。到目前为止，我已经做到了这一点：

select
stockid,
dealid,
dealtype,
stockdate,
priceperstock,
case dealtype
    when 'B' then stockamount
    when 'S' then -stockamount
end as stockamount,
sum(
        case dealtype
            when 'B' then stockamount
            when 'S' then -stockamount
        end
    ) over (partition by 
        stockid order by dealid ROWS UNBOUNDED PRECEDING)
         as poistion
from testtable

输出：

stockid dealid  dealtype       stockdate           priceperstock    stockamount    poistion
    111       1       B       2016-01-03 00:00:00.000      5             10             10
    111       2       B       2016-02-03 00:00:00.000      6             15             25
    111       3       B       2016-03-03 00:00:00.000      4             5              30
    111       4       S       2016-04-03 00:00:00.000      7            -17             13

期望输出：

stockid dealid  dealtype       stockdate           priceperstock    stockamount    poistion    stocksleft
    111       1       B       2016-01-03 00:00:00.000      5             10             10      10
    111       2       B       2016-02-03 00:00:00.000      6             15             25      3
    111       3       B       2016-03-03 00:00:00.000      4             5              30      0
    111       4       S       2016-04-03 00:00:00.000      7            -17             13      0

最好的方式是什么？

Answer 1

由于您的示例非常狭窄，因此很难组合出一个防弹解决方案。但这应该会让你开始走上正确的轨道，或者至少是一条轨道。它使用一种反向运行的总和，然后从库存量中减去。使用您的数据集进行小更改：

create table #testtable
(
stockid int not null,
dealid int identity (1,1) not null,
dealtype char(1) not null,
stockdate datetime not null,
stockamount int not null,
priceperstock int not null
)
 insert into #testtable(stockid,dealtype,stockdate,stockamount,priceperstock)
    VALUES
        (111,'B','01.03.2016',10,5),
        (111,'B','02.03.2016',15,6),
        (111,'B','03.03.2016',5,4),
        (111,'S','04.03.2016',-17,7) --signed int
----Add this to see another level 
 --insert into #testtable(stockid,dealtype,stockdate,stockamount,priceperstock)
 --   VALUES
 --       (111,'S','05.03.2016',-12,5)
    ;WITH CTE
    AS (
        SELECT stockid
            , dealid
            , dealtype
            , stockdate
            , priceperstock
            , stockamount
            , sum(stockamount) OVER (
                ORDER BY dealid DESC
                ) AS runningtotal
            , sum(stockamount) OVER (
            ORDER BY dealid) AS position
        FROM #testtable
        )
    SELECT stockid
        , dealid
        , dealtype
        , stockdate
        , priceperstock
        , stockamount
        --, runningtotal
        , position
        , CASE 
            WHEN dealtype = 'S' 
                THEN 0
            WHEN stockamount > runningtotal AND runningtotal < 0
                THEN 0
            WHEN stockamount > runningtotal AND runningtotal >= 0
                THEN runningtotal
            WHEN stockamount < runningtotal
                THEN stockamount
            END AS StockRemaining
    FROM cte
    ORDER BY dealid

Answer 2

我怀疑你可能会想交错买卖，所以虽然我认为另一个答案是一个很好的起点，但它不能完全处理整个场景。

基本上，我认为你必须用某种迭代机制来处理这个问题。我试着用递归来做，但不幸的是，分析函数不能很好地使用这种方法。所以我又回到了临时工桌上，一边循环。

create table #R (
    lvl int not null, stockId int not null, dealId int not null,
    stockDate datetime not null, stockAmount int not null, pricePerStock int not null,
    stockRemaining int not null, amountDeducted int not null
);
insert into #R (
    lvl, stockId, dealId, stockDate, stockAmount,
    pricePerStock, stockRemaining, amountDeducted
)
select 0, stockId, dealId, stockDate, stockAmount, pricePerStock, stockAmount, 0
from <T> where dealtype = 'B' /* <--- your table is <T> */
declare @lvl int = 0;
declare @rowCount int = 1;
while @rowCount > 0
begin
    set @lvl = @lvl + 1;
    with sells as (
        select stockId, dealId as saleId,
            row_number() over (order by dealId) as sellNum, stockAmount as sellAmount
        from <T> where dealType = 'S'
    )
    update #R
    set stockRemaining = (
        select stockRemaining
        from (
            select dealId,
                case
                    when r.stockRemaining + s.sellAmount
                          < sum(stockRemaining) over (order by dealId desc)
                        then r.stockRemaining
                    when sum(stockRemaining) over (order by dealId desc)
                          < s.sellAmount
                        then 0
                    else sum(stockRemaining) over (order by dealId desc)
                          - s.sellAmount
                end as stockremaining
            from sells s inner join #R r
                on r.stockId = s.stockId and r.dealId < s.saleId
            where s.stockId = #R.stockId and s.sellNum = @lvl
        ) data
        where dealId = #R.dealId
    )
    where dealId < (select saleId from sells where sellNum = @lvl);
    set @rowCount = @@rowCount;
end

我已经把它剪下来以便张贴。在这里看到它的实际操作，并提供更多的输出，以便更好地遵循逻辑：http://rextester.com/WPLKLJ95730