MS SQL Server
  1. MS SQL Server
  3. TSQL Rlocation des calculs de mise à jour "Quirky" par Windowing et CTE
Intereting Posts
Conversion de la database SQL Server en database C # locale Obtenir les modifications effectuées par ExecuteNonQuery () Ajout / préservation du zéro principal lors de la mise à jour d'une colonne requête sql, caractère générique, MS SQL SERVER 2005 select l'logging supérieur avec la valeur de la colonne de partage SQL Server La connection à SQL Server à l'aide de Netbeans 7.0 est suspendue Node js knex et erreur mssql – 'TypeError: _this.driver.Connection n'est pas un constructor' Lier Oracle à SQL Server SSDT impossible de find des procédures stockées à partir de SQL Server Service Broker Ajout de SQL <? Xml version = "1.0" encoding = "UTF-8"?> À la requête Avertissement de longueur de key d'index cluster sur varchar (120)? Chiffrer le text en PHP et décrypter en MS SQL Combinaison de plusieurs instructions SELECT complexes afin qu'elles apparaissent sur une ligne La procédure stockée SSIS utilise la table temporaire 2008 et 2014 T-SQL – Existe-t-il un moyen de générer dynamicment des colonnes en fonction du nombre de lignes pour une valeur spécifiée dans une table?

TSQL Rlocation des calculs de mise à jour "Quirky" par Windowing et CTE

J'essaie de find une alternative à l'utilisation de la "mise à jour Quirky" en utilisant le fenêtrage et CTE pour un calcul assez complexe de "performance en cours d'exécution". Le calcul rapide pour l'exécution de la performance est ((1 + en cours d'exécution) * (1 + Daily)) – 1. Cette exécution doit être mise à jour pour la ligne en cours, puis utilisée dans le calcul des lignes suivantes. Il se réinitialise à 0 lorsque l'AssetID change. La seule alternative que j'ai trouvée est en utilisant exp (sum (log)) (montré dans l'exemple de code) et bien que les nombres soient corrects dans le monde réel, il est trop lent à utiliser, où Quirky Update est presque instantané. Dans mon exemple j'ai le fenêtrage remplaçant le calcul quotidien, mais parce que le courant a besoin de mettre à jour la ligne courante et cela a reflété dans le calcul suivant de rangées, je ne pourrais pas penser à employer le fenêtrage et le CTE pour manipuler le calcul courant. Donc, ma tentative est avec Variables dans l'instruction update similaire à Quirky Update et ils sont faux. Peut-être y a-t-il un moyen similaire à celui que je calcule tous les jours? Merci d'avance, Robb 

if OBJECT_ID('tempdb..#DMVRunningPerformance') is not null drop table #DMVRunningPerformance create table #DMVRunningPerformance ( LongPosition_bt bit null, AssetID_in int null, Symbol_vc varchar(255), DayID_in int, Status_vc varchar(255) null, MV float, DailyPerf float null, RunningPerf float null) CREATE CLUSTERED INDEX [indexdown] ON #DMVRunningPerformance ( [LongPosition_bt] ASC, [AssetID_in] ASC, [DayID_in] DESC ) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] insert into #DMVRunningPerformance values (1,100,'IBM',75006,NULL,201048.8987,NULL,NULL) insert into #DMVRunningPerformance values (1,100,'IBM',75005,NULL,200841.5658,NULL,NULL) insert into #DMVRunningPerformance values (1,100,'IBM',75004,NULL,200321.7043,NULL,NULL) insert into #DMVRunningPerformance values (1,100,'IBM',75003,NULL,201120.0467,NULL,NULL) insert into #DMVRunningPerformance values (1,100,'IBM',75002,NULL,201779.8805,NULL,NULL) insert into #DMVRunningPerformance values (1,100,'IBM',75001,NULL,201651.3917,NULL,NULL) insert into #DMVRunningPerformance values (1,100,'IBM',75000,NULL,201101.0320,NULL,NULL) insert into #DMVRunningPerformance values (1,200,'MSFT',75006,NULL,805048.8987,NULL,NULL) insert into #DMVRunningPerformance values (1,200,'MSFT',75005,NULL,801841.5658,NULL,NULL) insert into #DMVRunningPerformance values (1,200,'MSFT',75004,NULL,804321.7043,NULL,NULL) insert into #DMVRunningPerformance values (1,200,'MSFT',75003,NULL,809120.0467,NULL,NULL) insert into #DMVRunningPerformance values (1,200,'MSFT',75002,NULL,801779.8805,NULL,NULL) insert into #DMVRunningPerformance values (1,200,'MSFT',75001,NULL,801151.3917,NULL,NULL) insert into #DMVRunningPerformance values (1,200,'MSFT',75000,NULL,801901.0320,NULL,NULL) ------------------- BEGIN Quirky Update ------------------------------------- declare @RunningPerformance as float=0 declare @AssetID_in int=-1, @DayID_in int=0, @LongPosition_bt bit=0, @Status_vc varchar(255)=NULL, @NewGroup_bt bit=1, @DailyPerformance float, @NDMarketValue float, @PDMarketValue float=0, @NDSymbol_vc varchar(255)='XXXXXXXXXX', @PDSymbol_vc varchar(255)='XXXXXXXXXX', @NDDayID_in int, @PDDayID_in int, @NDAssetID_in int=-1, @PDAssetID_in int=-1 update #DMVRunningPerformance set @LongPosition_bt=LongPosition_bt, @NewGroup_bt=CASE WHEN @AssetID_in<>AssetID_in or @LongPosition_bt<>LongPosition_bt THEN 1 ELSE 0 END, @DailyPerformance=CASE WHEN @NewGroup_bt=1 THEN 0 ELSE (@NDMarketValue-MV)/MV END, @RunningPerformance=CASE WHEN @NewGroup_bt=1 THEN 0 ELSE ((1 + @RunningPerformance) * (1 + @DailyPerformance)) - 1 END ,DailyPerf=@DailyPerformance ,RunningPerf=@RunningPerformance ,@AssetID_in=AssetID_in ,@NDAssetID_in=AssetID_in ,@NDSymbol_vc=Symbol_vc ,@NDDayID_in=DayID_in ,@NDMarketValue=MV FROM #DMVRunningPerformance OPTION (MAXDOP 1) select 'this data is correct' select * from #DMVRunningPerformance ------------------- END Quirky Update ------------------------------------- -- reset performance update #DMVRunningPerformance set DailyPerf=null, RunningPerf=NULL ------------------- BEGIN windowing and CTE ------------------------------------- -- update daily perf with CTE ;WITH dailyCTE as ( select LongPosition_bt,AssetID_in,Symbol_vc,DayID_in,PreviousAssetID, MV, DailyPerformance = CASE WHEN YesterdayMV IS NULL THEN 0 ELSE (YesterdayMV - MV) / MV END FROM ( select p.LongPosition_bt,p.AssetID_in,p.Symbol_vc,p.DayID_in, p.MV, lag(p.MV,1) over (Partition by LongPosition_bt,AssetID_in order by DayID_in desc) as YesterdayMV, lag(p.AssetID_in,1) over (Partition by LongPosition_bt,AssetID_in order by DayID_in desc) as PreviousAssetID from #DMVRunningPerformance p ) p1 ) Update #DMVRunningPerformance set DailyPerf=dailyCTE.DailyPerformance, @NewGroup_bt=CASE WHEN PreviousAssetID is null THEN 1 ELSE 0 END, @RunningPerformance=CASE WHEN PreviousAssetID is null THEN DailyPerformance ELSE ((1 + @RunningPerformance) * (1 + DailyPerformance)) - 1 END, RunningPerf=@RunningPerformance from dailyCTE join #DMVRunningPerformance d on dailyCTE.LongPosition_bt=d.LongPosition_bt and dailyCTE.AssetID_in=d.AssetID_in and dailyCTE.DayID_in=d.DayID_in select 'this data daily is correct, running is incorrect' select * from #DMVRunningPerformance -- alternate method for running perf -- works but for realworld data takes orders of magnitude longer than quirky update if 1=2 select dr.AssetID_in,dr.LongPosition_bt,dr.DayID_in,dr.MV,dr.DailyPerf-1 as DailyPerformance, RunningPerformance_fl = exp(( select sum(log(DailyPerf)) from #DMVRunningPerformance where AssetID_in=dr.AssetID_in and LongPosition_bt=dr.LongPosition_bt and DayID_in >= DR.DayID_in)) - 1 from #DMVRunningPerformance dr order by dr.assetid_in,DR.DayID_in desc ------------------- END windowing and CTE ------------------------------------- drop table #DMVRunningPerformance

C'est assez facile en utilisant un CTE récursif. L'implémentation ci-dessous est une version rapide et sale, vous pouvez donc supprimer certaines des colonnes supplémentaires / redondantes. (DailyPerf2 et RunningPerf2 sont les nouveaux résultats.) 

 ;with orderedRows as ( select *, row_number() over (partition by AssetId_in order by DayId_in desc) as rn from #DMVRunningPerformance ), grouped as ( select *, cast(0.0 as float) as DailyPerf2, cast(0.0 as float) as RunningPerf2 from orderedRows where rn = 1 union all select r.* , cast((prevR.MV - r.MV) / r.MV as float) , cast(((1 + prevR.RunningPerf2) * (1 + (prevR.MV - r.MV) / r.MV)) - 1 as float) from orderedRows r inner join grouped prevR on r.AssetId_in = prevR.AssetID_in and r.rn = prevR.rn + 1 ) select * from grouped order by AssetID_in, DayID_in desc