如何在KDB中基于最接近时间戳而非asof规则关联两张表

实现思路 #

要实现按最接近时间关联的逻辑，我们可以同时获取右表中不大于左表时间的最后一条记录和大于左表时间的第一条记录，对比两者与左表时间的间隔，选择间隔更小的记录即可。如果时间差相等，可根据需求选择优先取更早或更晚的记录，以下示例默认优先取更早的记录。

前置要求 #

和原生aj函数规则一致，右表必须提前按关联键（维度列+时间列）升序排序：

q: `sym`time xasc q;

通用函数实现 #

以下实现基于kdb原生aj的向量化操作，性能优于逐行遍历方案，适合处理大表数据，且完全兼容左表同一sym存在多条记录的场景：

closest_join:{[keys;left;right]
    t_col: last keys;
    val_cols: keys[right] except keys;
    if[0=count val_cols;'"右表没有需要关联的非键列"];
    // 1. 标准aj结果：取<=左表时间的最近记录
    aj_res: aj[keys;left;right];
    // 2. 反向时间aj结果：取>=左表时间的最近记录
    rev_right: update neg_t: neg t_col from right;
    rev_left: update neg_t: neg t_col from left;
    aj_rev_res: aj[(except[keys;t_col],`neg_t);rev_left;rev_right];
    // 3. 对比时间差选择更近的记录
    diff1: abs aj_res[t_col] - left[t_col];
    diff2: abs aj_rev_res[t_col] - left[t_col];
    res: left;
    res[val_cols]: ?[diff1<=diff2;aj_res[val_cols];aj_rev_res[val_cols]];
    res
}

效果验证 #

用你给出的测试表执行关联：

closest_join[`sym`time;t;q]

输出结果完全符合预期：

time     sym  qty px
---------------------
10:00:06 msft 100 102
10:00:03 ibm  200 100
10:00:04 ge   150

规则调整说明 #

如果遇到两条记录与左表时间差完全相等的场景，需要优先选择时间更晚的记录，只需把函数中判断条件diff1<=diff2修改为diff1<diff2即可。

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