可以使用 CTE 或 OVER 将此递归解决方案写入 T-SQL 查询吗?

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

假设您有一个名为 Table1 of Orders 的表，按时间顺序从内嵌 UDF 返回.请注意，OrderID 可能不同步，所以我故意在那里创建了一个异常(即我没有包含日期​​字段，但如果您更容易的话，我可以访问该列).

Lets imagine you have the following table called Table1 of Orders in chronological order returned from an In-line UDF. Please note that the OrderID may be out of sync so I have intentionally created an anomaly there (i.e. I have not included the Date field but I have access to the column if easier for you).

   OrderID  BuySell  FilledSize  ExecutionPrice  RunningTotal AverageBookCost  RealisedPnL
   339      Buy      2           24.5            NULL         NULL             NULL
   375      Sell     3           23.5            NULL         NULL             NULL
   396      Sell     3           20.5            NULL         NULL             NULL
   416      Sell     1           16.4            NULL         NULL             NULL
   405      Buy      4           18.2            NULL         NULL             NULL
   421      Sell     1           16.7            NULL         NULL             NULL
   432      Buy      3           18.6            NULL         NULL             NULL

我有一个函数，我想从上到下递归地应用它来计算 3 个 NULL 列，但是函数的输入将是前一次调用的输出.我创建的函数叫做 mfCalc_RunningTotalBookCostPnL，我在下面附上了这个

I have a function that I would like to apply recursively from the top to the bottom that will calculate the 3 NULL columns, however the imputs into the function will be the outputs from the previous call. The function I have created is called mfCalc_RunningTotalBookCostPnL and I have attached this below

CREATE FUNCTION [fMath].[mfCalc_RunningTotalBookCostPnL](
    @BuySell           VARCHAR(4),
    @FilledSize        DECIMAL(31,15),
    @ExecutionPrice    DECIMAL(31,15),
    @OldRunningTotal   DECIMAL(31,15),
    @OldBookCost       DECIMAL(31,15)
    )

RETURNS @ReturnTable TABLE(
    NewRunningTotal DECIMAL(31,15),
    NewBookCost DECIMAL(31,15),
    PreMultRealisedPnL  DECIMAL(31,15)
    )
AS
BEGIN
    DECLARE @SignedFilledSize   DECIMAL(31,15),
            @NewRunningTotal    DECIMAL(31,15),
            @NewBookCost        DECIMAL(31,15),
            @PreMultRealisedPnL DECIMAL(31,15)

    SET @SignedFilledSize = fMath.sfSignedSize(@BuySell, @FilledSize)
    SET @NewRunningTotal = @OldRunningTotal + @SignedFilledSize
    SET @PreMultRealisedPnL = 0
    IF SIGN(@SignedFilledSize) = SIGN(@OldRunningTotal)
        -- This Trade is adding to the existing position.
        SET @NewBookCost = (@SignedFilledSize * @ExecutionPrice +
            @OldRunningTotal * @OldBookCost) / (@NewRunningTotal)
    ELSE
    BEGIN
        -- This trade is reversing the existing position.
        -- This could be buying when short or selling when long.
        DECLARE @AbsClosedSize DECIMAL(31,15)
        SET @AbsClosedSize = fMath.sfMin(ABS(@SignedFilledSize), ABS(@OldRunningTotal));

        -- There must be Crystalising of PnL.
        SET @PreMultRealisedPnL = (@ExecutionPrice - @OldBookCost) * @AbsClosedSize * SIGN(-@SignedFilledSize)

        -- Work out the NewBookCost
        SET @NewBookCost = CASE
            WHEN ABS(@SignedFilledSize) < ABS(@OldRunningTotal) THEN @OldBookCost
            WHEN ABS(@SignedFilledSize) = ABS(@OldRunningTotal) THEN 0
            WHEN ABS(@SignedFilledSize) > ABS(@OldRunningTotal) THEN @ExecutionPrice
        END
    END

    -- Insert values into Return Table
    INSERT INTO @ReturnTable
        VALUES (@NewRunningTotal, @NewBookCost, @PreMultRealisedPnL)

    -- Return
    RETURN
END

所以我正在寻找的 t-SQL 命令(我不介意是否有人也可以创建外部应用)将生成以下结果/解决方案集:

So the t-SQL command I am looking for (I dont mind if someone can creates an Outer Apply too) would generate the following Result/Solution set:

OrderID BuySell FilledSize ExecutionPrice RunningTotal AverageBookCost RealisedPnL
339     Buy     2          24.5           2            24.5            0
375     Sell    3          23.5           -1           23.5            -2
396     Sell    3          20.5           -4           21.25           0
416     Sell    1          16.4           -5           20.28           0
405     Buy     4          18.2           -1           20.28           8.32
421     Sell    1          16.7           -2           18.49           0
432     Buy     3          18.6           1            18.6            -0.29

一些注意事项，上面的存储过程调用了一个简单的函数 fMath.sfSignedSize，它只是使 ('Sell',3) = -3.此外，为避免疑义，假设我的计算正确，我会看到按此顺序进行这些调用的解决方案！(请注意，我一开始假设 OldRunningTotal 和 OldBookCost 都为零):

A few notes, the above stored procedure calls a trivial function fMath.sfSignedSize which just makes ('Sell',3) = -3. Also, for the avoidance of doubt, I would see the solution making these calls in this order assuming I am correct in my calculations! (Note that I start off assuming the OldRunningTotal and OldBookCost are both zero):

SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Buy',2,24.5,0,0)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',3,23.5,2,24.5)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',3,20.5,-1,23.5)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',1,16.4,-4,21.25)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Buy',4,18.2,-5,20.28)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Sell',1,16.7,-1,20.28)
SELECT * FROM fMath.mfCalc_RunningTotalBookCostPnL('Buy',3,18.6,-2,18.49)

显然，[fMath].[mfCalc_RunningTotalBookCostPnL] 可能需要调整，以便它可以从 NULL 条目作为 OldRunningTotal 和 OldBookCost 开始，但这是微不足道的.应用递归性质的 SQL Set 理论有点难.

Obviously, the [fMath].[mfCalc_RunningTotalBookCostPnL] may need to be tweaked so that it can start off with NULL entries as the OldRunningTotal and OldBookCost but this is trivially done. The SQL Set theory of applying the resursive nature is a little harder.

非常感谢，伯蒂.

推荐答案

如果没有完全正常运行的 [fMath].[mfCalc_RunningTotalBookCostPnL] 来测试，这有点像在黑暗中刺伤.我在测试前第一次正确使用递归 CTE 的记录只有大约 50%，但即使不完美，如果我正确理解您的要求，也应该足以让您入门:

This is a bit of a stab in the dark without fully functioning [fMath].[mfCalc_RunningTotalBookCostPnL] to test with. My track record with getting recursive CTE's right the first time before testing is only about 50%, but even if not perfect it should be enough to get you started, if I understand your requirements correctly:

-- First, cache Table1 into #temp to improve recursive CTE performance
select
RowNum=ROW_NUMBER()OVER(ORDER BY OrderID)
, *
INTO #temp
FROM Table1;
GO

; WITH CTE (RowNum,OrderID, BuySell, FilledSize, ExecutionPrice, RunningTotal, AverageBookCost, RealisedPnL) AS (
    SELECT RowNum,OrderID, BuySell, FilledSize, ExecutionPrice, RunningTotal=0, AverageBookCost=0, RealisedPnL=0
    FROM #temp
    WHERE RowNum=1

    UNION ALL

    SELECT t.RowNum, t.OrderID, t.BuySell, t.FilledSize, t.ExecutionPrice
    , RunningTotal=c.NewRunningTotal, AverageBookCost=c.NewBookCost, RealisedPnL=c.PreMultRealisedPnL
    FROM #temp t
    INNER JOIN CTE ON CTE.RowNum+1 = t.RowNum
    CROSS APPLY [fMath].[mfCalc_RunningTotalBookCostPnL](t.BuySell, t.FilledSize, t.ExecutionPrice, CTE.RunningTotal, CTE.AverageBookCost) AS c
)
SELECT OrderID, BuySell, FilledSize, ExecutionPrice, RunningTotal, AverageBookCost, RealisedPnL
FROM CTE
/* Replace the above SELECT with the following after testing ok
UPDATE tab
SET RunningTotal=CTE.RunningTotal
, AverageBookCost=CTE.AverageBookCost
, RealisedPnL=CTE.RealisedPnL
FROM Table1 tab
INNER JOIN CTE on CTE.OrderID=tab.OrderID
*/
OPTION (MAXRECURSION 32767);
GO

-- clean up
DROP TABLE #temp
GO

另外一个免责声明 - 递归 CTE 适用于最大深度为 32767.如果这过于严格，您需要探索不同的方法，或对数据集进行某种窗口化.

One more disclaimer - recursive CTEs are good for a max depth of 32767. If this is too restrictive, you'll need to explore either a different method, or some sort of windowing on the data set.

相关文章