在SQLServer中使用帶有CTE的遞回

我有下表結構：（這只是一個樣本集，在我的最終輸出查詢中具有完全相同的列）

實際資料在索引中的行數要多得多，我必須在到達索引值之前洗掉幾個符號。這是一個為內部使用而構建的自定義索引。

我想計算點對指數值的貢獻，最后是指數值。

要計算pts每個符號的貢獻，公式為：

ptsC = yesterday_index * wt * px_change / yest_close

我沒有昨天指數的起始值。即 2021 年 11 月 17 日，應視為 1000

11 月 18 日的指數值將是1000 sum(ptsC) 這個值現在應該用于計算 22-11 月等每個交易品種的 ptsC...

我正在嘗試撰寫遞回 CTE，但不確定我哪里出錯了。

昨天的指數值應遞回確定，因此應計算 ptsC。最終輸出應該是：

其中總積分貢獻是ptsC當天所有的總和，新指數價值是yesterday Index Value Total Point Contribution。

下面是我生成第一個表的代碼：

declare @beginval as float=17671.65
set @beginval=1000
declare @indexname varchar(20)='NIFTY ENERGY'
declare @mindt as datetime
select @mindt=min(datetime) from indices_json where indexname=@indexname

;
with tbl as (
SELECT        IndexName, datetime, sum(Indexmcap_today) totalMcap_today,sum(Indexmcap_yst) totalmcap_yst
FROM            indices_json
WHERE        IndexName = @indexname
group by indexname,datetime
)
,tbl2 as
(
select j.indexname,j.datetime,symbol,Indexmcap_today/d.totalMcap_today*100 calc_wt_today,Indexmcap_yst/d.totalmcap_yst*100 calc_wt_yest,iislPtsChange,adjustedClosePrice,pointchange
from indices_json j inner join tbl d on d.datetime=j.datetime and d.IndexName=j.IndexName
)
,tbl3 as
(
select indexname,datetime,symbol,calc_wt_today,calc_wt_yest,iislPtsChange,adjustedClosePrice,pointchange
,case when datetime=@mindt then @beginval*calc_wt_yest*iislPtsChange/adjustedClosePrice/100 else null end ptsC
 from tbl2 
)
,tbl4 as
(
select indexname,datetime,sum(ptsC)   @beginval NewIndexVal,sum(pointchange) PTSCC 
from tbl3
group by indexname,datetime
)
,tbl5 as
(
select *,lag(datetime,1,null) over(order by datetime asc) yest_dt 
from tbl4 
)
,
tbl6 as 
(
select d.*,s.yest_dt 
from tbl2 d inner join tbl5 s on d.datetime=s.datetime
)
,tbl7 as
(
select d.IndexName,d.datetime,d.symbol,d.calc_wt_today,d.calc_wt_yest,d.iislPtsChange,d.adjustedClosePrice,d.pointchange,case when i.datetime is null then @beginval else i.NewIndexVal end yest_index 
from tbl6 d left join tbl4 i on d.yest_dt=i.datetime
)
select IndexName,convert(varchar(12),datetime,106)date,symbol,round(calc_wt_yest,4) wt,iislPtsChange px_change,adjustedClosePrice yest_close--,pointchange,yest_index
from tbl7 d where datetime <='2021-11-24'
order by datetime

提前致謝。

uj5u.com熱心網友回復：

我為此找到了一個解決方案：我計算了每個日期的每個成分的回報，然后將某個日期的這些回報相加，然后將所有日期的回報的所有總和相乘以得出最終值 - 下面是查詢相同。我在這里不需要遞回

declare @beginval as float=17671.65

declare @indexname varchar(20)='NIFTY 50'
declare @mindt as datetime
select @mindt=min(datetime) from indices_json where indexname=@indexname
declare @startdt as datetime = '2021-11-01'
;
with tbl as (
SELECT        IndexName, datetime, sum(Indexmcap_today) totalMcap_today,sum(Indexmcap_yst) totalmcap_yst
FROM            indices_json
WHERE        IndexName = @indexname-- and symbol!='AXISBANK'
group by indexname,datetime
)
,tbl2 as
(
select j.indexname,j.datetime,symbol,Indexmcap_today/d.totalMcap_today*100 calc_wt_today,Indexmcap_yst/d.totalmcap_yst*100 calc_wt_yest,iislPtsChange,adjustedClosePrice,pointchange
from indices_json j inner join tbl d on d.datetime=j.datetime and d.IndexName=j.IndexName
)
,tbl7 as
(
select d.IndexName,d.datetime,d.symbol,d.calc_wt_today,d.calc_wt_yest,d.iislPtsChange,d.adjustedClosePrice,d.pointchange, d.calc_wt_yest*d.iislPtsChange/d.adjustedClosePrice/100 ret
from tbl2 d 
)
,tbl8 as
(
select indexname,datetime,1 sum(ret) tot_ret from tbl7 group by indexname,datetime 
)

select indexname,datetime date
,round(exp(sum(log(sum(tot_ret))) over (partition by IndexName order by datetime)),6)*@beginval final_Ret
from tbl8 where datetime>=@startdt
 group by indexname,datetime order by date

標籤：sql-server 递归 公用表表达式

上一篇：在遞回呼叫中收集CompletableFuture結果

下一篇：為什么我的解無法通過Leetcode斷字題