模拟生成数据如下
TRADE_DT=sort(take(2021.02.01+3*(1..20),60)) S_INFO_WINDCODE=take(`A`B`C,60) S_DQ_ADJCLOSE=rand(10.,60) Ret=rand(1.,60) tb1=table(TRADE_DT,S_INFO_WINDCODE,S_DQ_ADJCLOSE,Ret) date=sort(take(date(2021.01M+0..2),3*3*3)) Source=take(`dd`rr`rrd`eee`ggg`yyy`yy`jj`www,3*3*3) Target=take(`A`A`A`B`B`B`C`C`C,3*3*3) Weight=rand(10.,3*3*3) tb2=table(date,Source,Target,Weight)
通过cj和peach实现两个表的连接
def f(tb1,tb2,idate,icode){ t1=select * from tb1 where TRADE_DT=idate and S_INFO_WINDCODE=icode t2=select * from tb2 where date<=idate and Target=icode context by Target having date=max(date) return cj(t1,t2) } tb=unionAll(peach(f{tb1,tb2,,},tb1.TRADE_DT,tb1.S_INFO_WINDCODE),0)
TRADE_DT S_INFO_WINDCODE S_DQ_ADJCLOSE Ret date Source Target Weight
---------- --------------- ----------------- ----------------- ---------- ------ ------ -----------------
2021.02.04 A 4.487512307241559 0.940215857466683 2021.02.01 dd A 4.061586682219059
2021.02.04 A 4.487512307241559 0.940215857466683 2021.02.01 rr A 3.668303932063282
2021.02.04 A 4.487512307241559 0.940215857466683 2021.02.01 rrd A 0.131341586820781
2021.02.04 B 1.054471984971315 0.633344997884706 2021.02.01 eee B 0.412905525881797
2021.02.04 B 1.054471984971315 0.633344997884706 2021.02.01 ggg B 0.312636836897582
2021.02.04 B 1.054471984971315 0.633344997884706 2021.02.01 yyy B 2.967845387756824
2021.02.04 C 6.044307881966234 0.909127941122279 2021.02.01 yy C 8.218941506929695
2021.02.04 C 6.044307881966234 0.909127941122279 2021.02.01 jj C 0.563267054967582
2021.02.04 C 6.044307881966234 0.909127941122279 2021.02.01 www C 8.038489881437271
2021.02.07 A 8.147617720533162 0.746302032610402 2021.02.01 dd A 4.061586682219059
2021.02.07 A 8.147617720533162 0.746302032610402 2021.02.01 rr A 3.668303932063282
2021.02.07 A 8.147617720533162 0.746302032610402 2021.02.01 rrd A 0.131341586820781
2021.02.07 B 0.449538344983012 0.780524794943631 2021.02.01 eee B 0.412905525881797
2021.02.07 B 0.449538344983012 0.780524794943631 2021.02.01 ggg B 0.312636836897582
2021.02.07 B 0.449538344983012 0.780524794943631 2021.02.01 yyy B 2.967845387756824
2021.02.07 C 4.924322243314237 0.217726393137127 2021.02.01 yy C 8.218941506929695
2021.02.07 C 4.924322243314237 0.217726393137127 2021.02.01 jj C 0.563267054967582
2021.02.07 C 4.924322243314237 0.217726393137127 2021.02.01 www C 8.038489881437271
2021.02.10 A 2.242919851560146 0.706920271040872 2021.02.01 dd A 4.061586682219059
2021.02.10 A 2.242919851560146 0.706920271040872 2021.02.01 rr A 3.668303932063282
2021.02.10 A 2.242919851560146 0.706920271040872 2021.02.01 rrd A 0.131341586820781
2021.02.10 B 3.251840437296778 0.908186007523909 2021.02.01 eee B 0.412905525881797
2021.02.10 B 3.251840437296778 0.908186007523909 2021.02.01 ggg B 0.312636836897582
2021.02.10 B 3.251840437296778 0.908186007523909 2021.02.01 yyy B 2.967845387756824
2021.02.10 C 4.774149649310857 0.362120353849605 2021.02.01 yy C 8.218941506929695
2021.02.10 C 4.774149649310857 0.362120353849605 2021.02.01 jj C 0.563267054967582
2021.02.10 C 4.774149649310857 0.362120353849605 2021.02.01 www C 8.038489881437271
2021.02.13 A 9.988255267962813 0.307931573363021 2021.02.01 dd A 4.061586682219059
2021.02.13 A 9.988255267962813 0.307931573363021 2021.02.01 rr A 3.668303932063282
2021.02.13 A 9.988255267962813 0.307931573363021 2021.02.01 rrd A 0.131341586820781