Kann diese rekursive Lösung in eine T-SQL-Abfrage mit CTE oder OVER geschrieben werden?

Stellen wir uns vor, Sie haben die folgende Tabelle namens Table1 von Orders in chronologischer Reihenfolge, die von einem Inline-UDF zurückgegeben wird. Bitte beachten Sie, dass die OrderID nicht synchron sein kann, so dass ich absichtlich eine Anomalie erstellt habe (dh ich habe das datesfeld nicht aufgenommen, aber ich habe Zugriff auf die Spalte, wenn es einfacher für Sie ist).

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 

Ich habe eine function, die ich gern rekursiv von oben nach unten anwenden möchte, um die 3 NULL Spalten zu berechnen, aber die Imputs in die function sind die Ausgänge aus dem vorherigen Aufruf. Die function, die ich erstellt habe, heißt mfCalc_RunningTotalBookCostPnL und ich habe diese unten beigefügt

 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 

Also der t-SQL-Befehl, den ich suche (ich denke nicht, wenn jemand auch eine äußere Anwendung erstellen kann) würde das folgende Ergebnis / Solution-Set erzeugen:

 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 

Ein paar Notizen, die oben gespeicherte Prozedur ruft eine triviale function fMath.sfSignedSize, die nur macht ('Sell', 3) = -3. Auch für die Vermeidung von Zweifeln würde ich die Lösung sehen, die diese Anrufe in dieser Reihenfolge macht, vorausgesetzt, ich bin in meinen Berechnungen richtig! (Beachten Sie, dass ich anfange zu unternehmen, dass die OldRunningTotal und OldBookCost beide null sind):

 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) 

Offensichtlich muss die [fMath] [mfCalc_RunningTotalBookCostPnL] möglicherweise angepasst werden, damit sie mit NULL-Einträgen als OldRunningTotal und OldBookCost starten kann, aber das ist trivial getan. Die SQL-Set-Theorie der Anwendung der resursiven Natur ist ein wenig härter.

Vielen Dank, Bertie.

Dies ist ein bisschen ein Stich in der Dunkelheit ohne voll funktionsfähig [fMath]. [MfCalc_RunningTotalBookCostPnL] zum Testen mit. Mein Track Record mit immer rekursiv CTE's Recht das erste Mal vor dem Testen ist nur etwa 50%, aber auch wenn nicht perfekt sollte es genug sein, um Ihnen den Start, wenn ich Ihre Anforderungen richtig zu verstehen:

 -- 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 

Ein weiterer Haftungsausschluss – rekursive CTEs sind gut für eine maximale Tiefe von 32767. Wenn dies zu restriktiv ist, müssen Sie entweder eine andere Methode oder eine Art windows auf dem datasatz zu erkunden.

Gesamtsumme ausführen UPDATE temp table vs CTE

 create table Test( OrderID int primary key, Qty int not null ); declare @i int = 1; while @i <= 5000 begin insert into Test(OrderID, Qty) values (@i * 2,rand() * 10); set @i = @i + 1; end; 

Rekursive Lösung dauert 9 Sekunden:

 with T AS ( select ROW_NUMBER() over(order by OrderID) as rn, * from test ) ,R(Rn, OrderId, Qty, RunningTotal) as ( select Rn, OrderID, Qty, Qty from t where rn = 1 union all select t.Rn, t.OrderId, t.Qty, p.RunningTotal + t.Qty from tt join rp on t.rn = p.rn + 1 ) select R.OrderId, R.Qty, R.RunningTotal from r option(maxrecursion 0); 

UPDATE-Tabelle dauert 0 Sekunden:

 create function TestRunningTotal() returns @ReturnTable table( OrderId int, Qty int, RunningTotal int ) as begin insert into @ReturnTable(OrderID, Qty, RunningTotal) select OrderID, Qty, 0 from Test order by OrderID; declare @RunningTotal int = 0; update @ReturnTable set RunningTotal = @RunningTotal, @RunningTotal = @RunningTotal + Qty; return; end; 

Diese beiden Ansätze könnten Ihnen zumindest einen Rahmen geben, um Ihre Anfrage zu erstellen.


BTW in SQL server, im Gegensatz zu MySQL, die Reihenfolge der variables Zuweisung spielt keine Rolle. Dies:

 update @ReturnTable set RunningTotal = @RunningTotal, @RunningTotal = @RunningTotal + Qty; 

Und die folgende:

 update @ReturnTable set @RunningTotal = @RunningTotal + Qty, RunningTotal = @RunningTotal; 

Sie führen beide die gleiche Weise aus, dh die variableszuordnungen passieren zuerst, unabhängig von der Position der variableszuordnung in der statement. Beide Abfragen haben dieselbe Ausgabe:

 OrderId Qty RunningTotal ----------- ----------- ------------ 2 4 4 4 8 12 6 4 16 8 5 21 10 3 24 12 8 32 14 2 34 16 9 43 18 1 44 20 2 46 22 0 46 24 2 48 26 6 54 

Auf deinem exakten Tisch verstehe ich nur den Kauf / Verkauf, du kannst es entweder um 1 und -1 multiplizieren, oder du kennst nur die Felder, zB:

 update @ReturnTable set @RunningTotal = @RunningTotal + CASE WHEN BuySell = 'Buy' THEN Qty ELSE -Qty END, RunningTotal = @RunningTotal; 

Wenn Sie ein Upgrade auf SQL server 2012 durchführen, ist hier die einfache Implementierung der laufenden Gesamtmenge:

 select OrderID, Qty, sum(Qty) over(order by OrderID) as RunningTotal from Test 

Auf dein genaues Problem:

 select OrderID, Qty, sum(CASE WHEN BuySell = 'Buy' THEN Qty ELSE -Qty END) over(order by OrderID) as RunningTotal from Test; 

AKTUALISIEREN

Wenn Sie sich mit einem schrulligen Update nicht wohl fühlen, können Sie eine Wächterklausel setzen, um zu überprüfen, ob die Reihenfolge der zu aktualisierenden Zeilen mit der ursprünglichen Reihenfolge übereinstimmt (unterstützt durch Identität (1,1)):

 create function TestRunningTotalGuarded() returns @ReturnTable table( OrderId int, Qty int, RunningTotal int not null, RN int identity(1,1) not null ) as begin insert into @ReturnTable(OrderID, Qty, RunningTotal) select OrderID, Qty, 0 from Test order by OrderID; declare @RunningTotal int = 0; declare @RN_check INT = 0; update @ReturnTable set @RN_check = @RN_check + 1, @RunningTotal = (case when RN = @RN_check then @RunningTotal + Qty else 1/0 end), RunningTotal = @RunningTotal; return; end; 

Wenn UPDATE wirklich Zeilen in unvorhersehbarer Reihenfolge (oder durch irgendeine Chance, die es sein wird) aktualisiert wird, ist der @RN_Check nicht gleich RN (Identitätsauftrag) mehr, der Code wird dann einen Split-by-Null-Fehler erheben. Mit guard-Klausel, unvorhersehbare Update-Reihenfolge wird schnell fehlschlagen ; Wenn dies dann passiert, wird es die time sein, eine Bug- Petition an Microsoft einzureichen, um das schrullige Update nicht so schrullig zu machen 🙂

Die Wächterklausel Hedge auf die inhärent zwingende Operation (variable Zuordnung) ist wirklich sequentiell.

Ich mache die laufenden Gesamtabfragen neu, um eine Partition (auf Kunden)

CTE-Ansatz:

 with T AS ( select ROW_NUMBER() over(partition by CustomerCode order by OrderID) as rn, * from test ) ,R(CustomerCode, Rn, OrderId, Qty, RunningTotal) as ( select CustomerCode, Rn, OrderID, Qty, Qty from t where rn = 1 union all select t.CustomerCode, t.Rn, t.OrderId, t.Qty, p.RunningTotal + t.Qty from tt join rp on p.CustomerCode = t.CustomerCode and t.rn = p.rn + 1 ) select R.CustomerCode, R.OrderId, R.Qty, R.RunningTotal from r order by R.CustomerCode, R.OrderId option(maxrecursion 0); 

Schrulliger Update-Ansatz:

 create function TestRunningTotalGuarded() returns @ReturnTable table( CustomerCode varchar(50), OrderId int, Qty int, RunningTotal int not null, RN int identity(1,1) not null ) as begin insert into @ReturnTable(CustomerCode, OrderID, Qty, RunningTotal) select CustomerCode, OrderID, Qty, 0 from Test order by CustomerCode, OrderID; declare @RunningTotal int; declare @RN_check INT = 0; declare @PrevCustomerCode varchar(50) = NULL; update @ReturnTable set @RN_check = @RN_check + 1, @RunningTotal = (case when RN = @RN_check then case when @PrevCustomerCode = CustomerCode then @RunningTotal + Qty else Qty end else 1/0 end), @PrevCustomerCode = CustomerCode, RunningTotal = @RunningTotal; return; end; 

Cursor-Ansatz (Code komprimiert, um die Scrollbars zu entfernen)

 create function TestRunningTotalCursor() returns @ReturnTable table(CustomerCode varchar(50), OrderId int, Qty int, RunningTotal int not null) as begin declare @c_CustomerCode varchar(50); declare @c_OrderID int; declare @c_qty int; declare @PrevCustomerCode varchar(50) = null; declare @RunningTotal int = 0; declare o_cur cursor for select CustomerCode, OrderID, Qty from Test order by CustomerCode, OrderID; open o_cur; fetch next from o_cur into @c_CustomerCode, @c_OrderID, @c_Qty; while @@FETCH_STATUS = 0 begin if @c_CustomerCode = @PrevCustomerCode begin set @RunningTotal = @RunningTotal + @c_qty; end else begin set @RunningTotal = @c_Qty; end; set @PrevCustomerCode = @c_CustomerCode; insert into @ReturnTable(CustomerCode, OrderId, Qty, RunningTotal) values(@c_CustomerCode, @c_OrderID, @c_Qty, @RunningTotal); fetch next from o_cur into @c_CustomerCode, @c_OrderID, @c_Qty; end; close o_cur; deallocate o_cur; return; end; 

Metriken auf 5.000 Zeilen:

 * Recursive CTE : 49 seconds * Quirky Update : 0 second * Cursor : 0 second 

Diese 0 Sekunden sind nicht sinnvoll. Nachdem ich die Zeilen auf 50.000 gestoßen habe, sind hier die Metriken:

 * Quirky Update : 1 second * Cursor : 3 second * Recursive CTE : An hour 

Caveat, fand ich heraus, dass schrullige Update ist wirklich schrullig, manchmal funktioniert es, irgendwann es nicht (angezeigt durch die Anwesenheit von divide-by-Null Fehler auf eine aus fünf Lauf der Abfrage).


Hier ist die DDL für data:

 create table Test( OrderID int primary key, CustomerCode varchar(50), Qty int not null ); declare @i int = 1; while @i <= 20 begin insert into Test(OrderID, CustomerCode, Qty) values ( @i * 2 ,case @i % 4 when 0 then 'JOHN' when 1 then 'PAUL' when 2 then 'GEORGE' when 3 then 'RINGO' end ,rand() * 10); set @i = @i + 1; end; 

AKTUALISIEREN

Anscheinend ist der reine CTE-Ansatz nicht gut. Muss einen Hybridansatz verwenden. Wenn die Zeilennummerierung auf eine aktuelle Tabelle abgestimmt ist, steigt die Geschwindigkeit

 select ROW_NUMBER() over(partition by CustomerCode order by OrderID) as rn, * into #xxx from test; with T AS ( select * from #xxx ) ,R(CustomerCode, Rn, OrderId, Qty, RunningTotal) as ( select CustomerCode, Rn, OrderID, Qty, Qty from t where rn = 1 union all select t.CustomerCode, t.Rn, t.OrderId, t.Qty, p.RunningTotal + t.Qty from tt join rp on p.CustomerCode = t.CustomerCode and t.rn = p.rn + 1 ) select R.CustomerCode, R.OrderId, R.Qty, R.RunningTotal from r order by R.CustomerCode, R.OrderId option(maxrecursion 0); drop table #xxx; 

Um zu rekapitulieren, hier sind die Metriken vor der Umwandlung der reinen CTE zu materialisierten Zeilennummerierung verwenden (Zeile nummeriert Ergebnisse ist in der tatsächlichen Tabelle, dh in der temporären Tabelle)

 * Quirky Update : 1 second * Cursor : 3 second * Recursive CTE(Pure) : An hour 

Nach dem materialisieren der Zeilennummerierung zur temporären Tabelle:

 * Quirky Update : 1 second * Cursor : 3 second * Recursive CTE(Hybrid) : 2 second (inclusive of row numbering table materialization) 

Hybrid rekursive CTE-Ansatz ist eigentlich schneller als Cursor-Ansatz.


Ein weiterer UPDATE

Nur indem man einen geclusterten Primärschlüssel auf die sequentielle Spalte setzt, werden die UPDATE-Aktualisierungszeilen auf ihre physikalische Reihenfolge gesetzt. Es gibt nicht mehr divide-by-zero (Guard-Klausel, um nicht-sequentielle Aktualisierung zu erkennen) auftritt. z.B

 alter function TestRunningTotalGuarded() returns @ReturnTable table(    CustomerCode varchar(50), OrderId int, Qty int, RunningTotal int not null, RN int identity(1,1) not null primary key clustered ) 

Ich habe versucht, die schrulligen Update (mit gruppierten Primärschlüssel an Ort und Stelle) 100 Mal, wenn es Ecke Fälle könnte, fand ich keine so weit. Ich habe keinen Splitter-durch-Null-Fehler gefunden. Lesen Sie die Schlussfolgerung am unteren Rand dieses Blog-Post: http://www.ienablemuch.com/2012/05/recursive-cte-is-evil-and-cursor-is.html

Und es ist immer noch schnell sogar mit gruppierten Primärschlüssel vorhanden.

Hier ist die Metrik für 100.000 Zeilen:

 Quirky Update : 3 seconds Hybrid Recursive CTE : 5 seconds Cursor : 6 seconds 

Schrulliges Update (das ist doch nicht so schrullig) ist immer noch schnell. Es ist schneller als hybride rekursive CTE.