如何基于日期范围与tradename实现KDB+表非精确连接？

需求：在date和tradename列上连接两张表，规则如下：

• tradename需精确匹配
• date只需匹配目标日期前后2天的范围（即目标日期-2到目标日期+2之间的日期）

示例说明 #

比如有一条记录：

date        sym    tradename         bankname       
----------------------------------------------------
2022.02.12 881654 trade1             JPM       

只要tradename为trade1，且date落在2022.02.10到2022.02.14之间的记录，连接后会获得相同的uid。

表数据 #

表tb

q)tb
date       sym    tradename bankname
------------------------------------
2022.02.12 881654 trade1    JPM     
2022.04.11 881654 trade2    HSBC    
2022.01.23 881654 trade3    CNB     
2022.03.28 881654 trade4    CNB     
2022.04.13 881654 trade5    BARC  

表tb2

q)tb2
date       sym    tradename uid   
----------------------------------
2022.02.13 895126 trade1    146819
2023.05.20 896484 trade5    206552
2022.04.15 897237 trade5    233458
2024.07.22 892383 trade3    222416
2024.07.22 758030 trade3    222416
2022.01.21 123948 trade3    506945
2022.01.21 895574 trade3    506945
2023.05.07 895806 trade5    403922
2023.05.07 897812 trade1    49460 
2023.05.08 896294 trade2    244051
2022.02.13 893794 trade4    231607
2024.05.08 893911 trade1    142881

表生成代码 #

tb:([] date:(2022.02.12;2022.04.11;2022.01.23;2022.03.28;2022.04.13); sym:5#881654; tradename:`trade1`trade2`trade3`trade4`trade5;bankname:`JPM`HSBC`CNB`CNB`BARC)

tb2:([] date:2022.02.13 2023.05.20 2022.04.15 2024.07.22 2024.07.22 2022.01.21 2022.01.21 2023.05.07 2023.05.07 2023.05.08 2022.02.13 2024.05.08; sym:895126 896484 897237 892383 758030 123948 895574 895806 897812 896294 893794 893911; tradename:`trade1`trade5`trade5`trade3`trade3`trade3`trade3`trade5`trade1`trade2`trade4`trade1; uid:146819 206552 233458 222416 222416 506945 506945 403922 49460 244051 231607 142881)

当前使用wj函数的输出 #

q)wj[-2 2+\:tb.date;`tradename`date;tb;(`tradename`date xasc tb2;(first;`uid))]
date       sym    tradename bankname uid   
-------------------------------------------
2022.02.12 881654 trade1    JPM      146819
2022.04.11 881654 trade2    HSBC           
2022.01.23 881654 trade3    CNB      506945
2022.03.28 881654 trade4    CNB      231607
2022.04.13 881654 trade5    BARC     233458

期望输出 #

q)tbwUID
date       sym    tradename bankname uid   
-------------------------------------------
2022.02.12 881654 trade1    JPM      146819
2022.04.11 881654 trade2    HSBC           
2022.01.23 881654 trade3    CNB      506945
2022.03.28 881654 trade4    CNB     
2022.04.13 881654 trade5    BARC     233458

问题分析与解决方案 #

当前wj的问题在于分组键同时使用了tradename和date，导致日期范围匹配逻辑未正确生效：tb中trade4的日期是2022.03.28，tb2中trade4的日期是2022.02.13，明显不在±2天范围内，但wj错误匹配到了对应uid。

正确的做法是先按tradename等值关联，再过滤日期范围，最后聚合得到结果：

// 1. 按tradename等值连接两张表
joined: ej[`tradename; tb; tb2]
// 2. 过滤tb日期±2天内的记录
filtered: select from joined where date within date-2 to date+2
// 3. 按tb的主键分组，取第一个匹配的uid
matched: select first uid by date, tradename from filtered
// 4. 左连接回原表，补充未匹配的记录
tbwUID: tb lj matched

或者更简洁的写法：

tbwUID: tb lj (select first uid by date, tradename from ej[`tradename; tb; tb2] where date within date-2 to date+2)

执行后即可得到符合预期的结果，trade4行不会匹配到错误的uid。

内容的提问来源于stack exchange，提问作者threedom