如何实现类似aj的功能，如果右表最近日期有多行记录，则需要这些记录都返回

如下表1和表2，表2的数据日期为每个月初。以表1为准，找到表2中target为S-INFO-WINSCODE，date为TRADE-DT之前最近的所有记录，表2中每一个target有多个source，把最新日期的数据记录都join到到表1形成一个新表

TRADE_DT   S_INFO_WINDCODE S_DQ_ADJCLOSE     Ret              
---------- --------------- ----------------- -----------------
2021.02.04 A               4.487512307241559 0.940215857466683
2021.02.04 B               1.054471984971315 0.633344997884706
2021.02.04 C               6.044307881966234 0.909127941122279
2021.02.07 A               8.147617720533162 0.746302032610402
2021.02.07 B               0.449538344983012 0.780524794943631
2021.02.07 C               4.924322243314237 0.217726393137127
2021.02.10 A               2.242919851560146 0.706920271040872
2021.02.10 B               3.251840437296778 0.908186007523909
2021.02.10 C               4.774149649310857 0.362120353849605
2021.02.13 A               9.988255267962813 0.307931573363021
2021.02.13 B               6.008266587741673 0.466165128862485
2021.02.13 C               5.97954104654491  0.630704167298973
2021.02.16 A               6.667174478061498 0.003475363599136

date       Source Target Weight           
---------- ------ ------ -----------------
2021.01.01 dd     A      8.203675118274986
2021.01.01 rr     A      2.744536611717194
2021.01.01 rrd    A      6.195137766189874
2021.01.01 eee    B      8.794530706945806
2021.01.01 ggg    B      1.932729484979063
2021.01.01 yyy    B      9.329929980449378
2021.01.01 yy     C      8.95809858571738 
2021.01.01 jj     C      6.212745844386518
2021.01.01 www    C      5.744842903222889
2021.02.01 dd     A      4.061586682219059
2021.02.01 rr     A      3.668303932063282
2021.02.01 rrd    A      0.131341586820781
2021.02.01 eee    B      0.412905525881797
2021.02.01 ggg    B      0.312636836897582
2021.02.01 yyy    B      2.967845387756824
2021.02.01 yy     C      8.218941506929695
2021.02.01 jj     C      0.563267054967582
2021.02.01 www    C      8.038489881437271
2021.03.01 dd     A      8.143457320984452
2021.03.01 rr     A      4.875419030431658
2021.03.01 rrd    A      9.306446227710694
2021.03.01 eee    B      6.461667772382498
2021.03.01 ggg    B      3.237210570368916
2021.03.01 yyy    B      0.680446848273277
2021.03.01 yy     C      5.294937985017896
2021.03.01 jj     C      5.325408771168441
2021.03.01 www    C      6.225936512928456

0 条评论
分类：技术问答

默认排序时间排序

1 个回答

haaha 2022-11-04 17:06

模拟生成数据如下

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

如何实现类似aj的功能，如果右表最近日期有多行记录，则需要这些记录都返回

1 个回答

相似问题