如何在KQL中基于UnitId与时间范围正确实现左外连接？

你的查询核心问题是使用了HTML转义字符>=和<=，而非KQL支持的原生比较运算符>=和<=，这会导致语法错误，无法正确执行。以下是几种正确的实现方式：

方式1：修复运算符后的左外连接 #

let trips = datatable(unitId:int, StartDate:datetime, EndDate:datetime)[
  1, '2020-01-01 01:00:00', '2020-01-06 04:00:00',
  1, '2020-01-07 04:01:00', '2020-01-09 02:00:00'];

let alerts = datatable(unitId:int, DateTime:datetime, description:string)[
  1, '2020-01-02 01:00:00', 'Speeding',
  1, '2020-01-16 01:00:00', 'CrashDetected',
  2, '2020-01-02 01:00:00', 'Speeding',
  2, '2020-01-16 01:00:00', 'CrashDetected'];

alerts
| join kind=leftouter trips on unitId and $left.DateTime >= $right.StartDate and $left.DateTime <= $right.EndDate

方式2：使用between简化时间范围判断 #

between运算符可以让时间范围的逻辑更简洁易读：

let trips = datatable(unitId:int, StartDate:datetime, EndDate:datetime)[
  1, '2020-01-01 01:00:00', '2020-01-06 04:00:00',
  1, '2020-01-07 04:01:00', '2020-01-09 02:00:00'];

let alerts = datatable(unitId:int, DateTime:datetime, description:string)[
  1, '2020-01-02 01:00:00', 'Speeding',
  1, '2020-01-16 01:00:00', 'CrashDetected',
  2, '2020-01-02 01:00:00', 'Speeding',
  2, '2020-01-16 01:00:00', 'CrashDetected'];

alerts
| join kind=leftouter trips on unitId and $left.DateTime between ($right.StartDate .. $right.EndDate)

方式3：使用apply实现更清晰的关联逻辑 #

如果需要为每条告警精准匹配符合条件的行程（尤其适合多匹配场景），可以用leftouter apply：

let trips = datatable(unitId:int, StartDate:datetime, EndDate:datetime)[
  1, '2020-01-01 01:00:00', '2020-01-06 04:00:00',
  1, '2020-01-07 04:01:00', '2020-01-09 02:00:00'];

let alerts = datatable(unitId:int, DateTime:datetime, description:string)[
  1, '2020-01-02 01:00:00', 'Speeding',
  1, '2020-01-16 01:00:00', 'CrashDetected',
  2, '2020-01-02 01:00:00', 'Speeding',
  2, '2020-01-16 01:00:00', 'CrashDetected'];

alerts
| leftouter apply (
    trips
    | where unitId == $left.unitId
    | where $left.DateTime between (StartDate .. EndDate)
)
| project unitId, DateTime, description, StartDate, EndDate

说明 #

• 替换转义字符后，左外连接会保留所有告警记录，匹配到行程的显示对应起止时间，未匹配的显示null。
• 若单个告警可能匹配多个行程，join和apply都会生成多行对应组合；如果只需单个匹配结果（比如最新的行程），可在apply子查询中添加sort by StartDate desc | take 1来筛选。

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