可以使用CTE或OVER将这种递归解决方案写到T-SQL查询中吗？

admin

可以使用CTE或OVER将这种递归解决方案写到T-SQL查询中吗？

sql

假设您有一个从内联UDF返回的按时间顺序排列的名为Table1 of
Orders的表。请注意，OrderID可能不同步，所以我有意在此创建了异常（即，我没有包括Date字段，但如果您方便的话，我可以访问该列）。

   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，我在下面附加了此函数

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命令（我不介意是否有人也可以创建外部申请）将生成以下结果/解决方案集：

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均为零）：

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理论要难一些。

非常感谢，伯蒂。

阅读 141

2021-07-01

共1个答案

admin

在没有完全运行[fMath]。[mfCalc_RunningTotalBookCostPnL]正常运行的情况下，这有点暗中的刺伤。我在测试之前就第一次获得递归CTE的经验大约只有50％，但即使不完美，只要我正确理解了您的要求，它也足以让您入门：

-- 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是很好的。如果限制太严格，则需要探索另一种方法或对数据集进行某种形式的窗口化。

2021-07-01