azure - 如何从 AzureDataExplorer/Kusto 中的子查询引用外部查询以进行过滤+扩展？

Question

我有两个表，它们都包含某个实体的运行状况事件。表 1 包含间隔几分钟的频繁事件。表 2 的时间间隔可能从几分钟到几个月不等。我想将表 2 中某个实体的最新健康事件关联到表 1。

let Table1 = datatable (Table1Timestamp : datetime, Table1NameOfDataElement : string)
[
    datetime(2021-05-11 19:05:00), 'foo',
    datetime(2021-05-11 19:15:00), 'foo',
    datetime(2021-05-11 19:12:00), 'foo',
    datetime(2021-05-11 19:09:00), 'bar',
    datetime(2021-05-11 19:15:00), 'bar',
];
let Table2 = datatable (Table2Timestamp : datetime, Table2NameOfDataElement : string, Other: int)
[
    // Data is ragged, days before first entry but is current state of 'foo'
    datetime(2021-05-09 19:05:00), 'foo', 1,
    datetime(2021-05-09 19:05:00), 'bar', 2,
    datetime(2021-05-11 19:09:00), 'bar', 3,
];
Table1
| where Table1Timestamp between (datetime(2021-05-11 19:00:00) .. datetime(2021-05-11 20:00:00))
// determine state of entity in table 1 every 10 minutes
| summarize arg_max(Table1Timestamp, *) by Table1NameOfDataElement, TimeInterval = bin(Table1Timestamp, 10minutes)
// Find the row in table2 that is closest in time proximity to the row from table 1
// This naive approach will not compile
| extend MostRecentColumnInOtherTable = toscalar(Table2 
                               | where Table2Timestamp < **Table1Timestamp** 
                               | where Table2NameOfDataElement == **Table1NameOfDataElement** 
                               | top 1 by Table2Timestamp | project Other)

我不能这样做，因为 kql 不允许我在内部查询中引用外部查询中的列。我可以加入实体名称，但这只能解决一半问题，我需要查找表 1 中事件之前的最后一个条目。

一种简单的方法是加入 bin(Table2Timestamp, 10minutes) == bin(table1Timestamp, 10minutes) 但表 2 中的数据可能与表 1 中的相关行相距数月，表 2 仅记录发生时的状态变化。

表 1 的窗口很窄，通常包含数千行。 Table2 可以追溯到很多个月并包含数十万行。

如何快速查找 table2 中发生在 table1 行之前的最后一次更改？

Answer 1

找到了适合我的东西。只需对实体名称进行连接，然后与 arg_min 聚合以查找时间戳差异最小的行

let Table1 = datatable (Table1Timestamp : datetime, Table1NameOfDataElement : string)
[
    datetime(2021-05-11 19:05:00), 'foo',
    datetime(2021-05-11 19:15:00), 'foo',
    datetime(2021-05-11 19:12:00), 'foo',
    datetime(2021-05-11 19:09:00), 'bar',
    datetime(2021-05-11 19:15:00), 'bar',
];
let Table2 = datatable (Table2Timestamp : datetime, Table2NameOfDataElement : string, Other: int)
[
    // Data is ragged, days before first entry but is current state of 'foo'
    datetime(2021-05-09 19:05:00), 'foo', 1,
    datetime(2021-05-09 19:05:00), 'bar', 2,
    datetime(2021-05-11 19:09:00), 'bar', 3,
];
Table1
| where Table1Timestamp between (datetime(2021-05-11 19:00:00) .. datetime(2021-05-11 20:00:00))
// determine state of entity in table 1 every 10 minutes
| summarize arg_max(Table1Timestamp, *) by Table1NameOfDataElement, TimeInterval = bin(Table1Timestamp, 10minutes)
// Find the row in table2 that is closest in time proximity to the row from table 1
// This naive approach will not compile
| Lookup Table2 on $left.Table1NameOfDataElement == $right.Table1NameOfDataElement 
| where Table1Timestamp < Table2Timestamp
| summarize arg_min(TimeDiffFromTimestamp = datetime_diff('second', Table1Timestamp, Table2Timestamp), *) by bin(Table1TimeStamp), Table1NameOfDataElement