如何在KQL(Kusto)中实现基于最近日期的两表关联查询

问题说明 #

现有两张数据表需要基于最接近的可用日期完成关联，因为最终逻辑需要用KQL实现，不能使用Delete/Insert类的修改操作，具体样例数据和预期结果如下。

样例数据（SQL建表参考） #

drop table if exists #table1
drop table if exists #table2
create table #table1 ([date] date, val int)
create table #table2 ([date] date, discount int)

insert into #table1 ([date], val) values ('1/26/2010', 10)
insert into #table1 ([date], val) values ('1/25/2010', 9)
insert into #table1 ([date], val) values ('1/24/2010', 8)
insert into #table1 ([date], val) values ('1/24/2010', 9)
insert into #table1 ([date], val) values ('1/23/2010', 7)
insert into #table1 ([date], val) values ('1/22/2010', 10)
insert into #table1 ([date], val) values ('1/19/2010', 11)

insert into #table2 ([date], discount) values ('1/26/2010', 2)
insert into #table2 ([date], discount) values ('1/23/2010', 1)
insert into #table2 ([date], discount) values ('1/20/2010', 0)

预期结果 #

KQL 实现代码 #

// 模拟table1数据，实际使用时替换为你的真实表名
let table1 = datatable(date:datetime, val:int)
[
    datetime(2010-01-26), 10,
    datetime(2010-01-25), 9,
    datetime(2010-01-24), 8,
    datetime(2010-01-24), 9,
    datetime(2010-01-23), 7,
    datetime(2010-01-22), 10,
    datetime(2010-01-19), 11
];
// 模拟table2数据，实际使用时替换为你的真实表名
let table2 = datatable(date:datetime, discount:int)
[
    datetime(2010-01-26), 2,
    datetime(2010-01-23), 1,
    datetime(2010-01-20), 0
];
table1
// 给table1每一行加唯一标识，避免重复行被合并
| extend row_id = new_guid()
// 关联table2，数据量大时可在此处加时间范围过滤减少计算量，比如限定只关联前后7天内的记录
| join kind=inner (table2 | project date2=date, discount) on 1==1
// 计算两个日期的差值绝对值
| extend date_diff = abs(datetime_diff('day', date, date2))
// 按table1的行分组，取日期差最小的table2匹配行
| summarize arg_min(date_diff, *) by row_id
// 输出需要的字段，可按需调整
| project table1_date = date, val, table2_date = date2, discount
| order by table1_date desc

方案说明 #

• 全部逻辑符合KQL语法规范，无任何数据修改类操作，可直接在Kusto集群运行。
• 数据量较大时，建议在join步骤前添加时间窗口过滤条件，限定关联范围，可大幅降低计算开销。
• 若业务要求优先取早于table1日期的最近值，可调整排序逻辑，按日期差升序、日期升序取第一条即可。

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