|
|
假设你有一个UDF按时间顺序排列的返回名称Table1 of9 |) ]) o2 U* x5 O' K; |$ I& x3 M- h
Orders手表。请注意，OrderID也许不同步，所以我打算在这里创造异常(也就是说，我不包括在内Date字段，但如果您方便的话，我可以访问该列）。6 B/ _. I$ w( n h( i
OrderID BuySell FilledSize ExecutionPrice RunningTotal AverageBookCost RealisedPnL Buy .5 5 NULL NULL NULL Sell 3 233333 .5 5 NULL NULL NULL Sell 200202000 .5 5 NULL NULL NULL 416 Sell .4 NULL NULL NULL 405 Buy 188188 .2 NULL NULL NULL 421 Sell .7 NULL NULL NULL 432 Buy 1888188 .6 NULL NULL NULL我有一个函数，希望从上到下递归应用，计算三个函数NULL列，但该函数的输入将是之前调用的输出。我创建的函数被称为mfCalc_RunningTotalBookCostPnL，我在下面附加了这个函数/ v& R# Y! e7 E* _' g
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)ASBEGIN 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 @ExecutionPrice END END -- Insert values into Return Table INSERT INTO @ReturnTable VALUES (@NewRunningTotal,@NewBookCost,@PreMultRealisedPnL) -- Return RETURNEND所以，我在寻找t-SQL命令(我不介意有人能否创建外部申请)将生成以下结果/解决方案集:1 ^0 o/ o6 W$ @
OrderID BuySell FilledSize ExecutionPrice RunningTotal AverageBookCost RealisedPnL339 Buy .5 244244 2444244 24 24 .5 555555Sell 3 23333 .5 5 -1 233233 .5 5 -2396 Sell 3 200200 .5 5 -4 .25 5 0416 Sell .4 -5 5 20.28 Buy 4 188818.2 -1 200202000 .28 8.32421 Sell .7 -2 18.49 Buy 3 1888188. 188188 1888188 .6 -0.29请注意，上述存储过程调用了一个简单的函数fMath.sfSignedSize，该函数仅使（’Sell’，3）=7 p1 _4 S( J3 L' P) [8 n3 `
-3.另外，为了避免怀疑，假设我的计算是正确的，我会看到解决方案按照这个顺序调用。！（请注意，我首先假设OldRunningTotal和OldBookCost均为零）：2 r e* i3 j/ A1 [- C) n
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
! E& Z5 |& |! u: t- I$ y8 OSet理论更难。
& W9 S# Z1 r) ]& c! N Z非常感谢，伯蒂。
8 T+ j. M2 @( L% x
4 u* d6 t8 I5 V3 x: R" E4 r. q: z7 z 解决方案: |
|
技术问答
242 人阅读
|
0 人回复
|
2023-09-12
