Kusto中union+extend子查询报错：无法解析'traces'标量表达式

报错原因 #

原查询最后一步的extend算子写法错误：extend只能生成单个标量值，但你在这里嵌套了对traces表的完整查询；同时，此时查询上下文已经是前面过滤出的START-END时间窗口数据集，无法直接在这个层级引用原始的traces表做关联查询，导致系统无法识别traces这个标识符。

修正后的查询方案 #

方案一：先提取时间窗口，再关联统计 #

// 先提取所有有效的START-END时间窗口
let time_windows = union
    (traces | where message == 'start message' | project event = 'START', timestamp),
    (traces | where message == 'end message' | project event = 'END', timestamp)
| order by timestamp asc
| extend prev_event = prev(event), prev_timestamp = prev(timestamp)
| where event == 'END' and prev_event == 'START'
| project StartTime = prev_timestamp, EndTime = timestamp;

// 关联原始日志表，统计每个窗口内符合条件的消息数量
time_windows
| join kind=inner (
    traces
    | where message startswith 'message prefix'
    | project msg_timestamp = timestamp
) on $left.StartTime <= $right.msg_timestamp and $right.msg_timestamp <= $left.EndTime
| summarize count_messages = count() by StartTime, EndTime
// 补充计算时长相关字段
| extend Duration = EndTime - StartTime, DurationMinutes = toint(Duration / 600000000)
| project StartTime, EndTime, Duration, DurationMinutes, count_messages

方案二：用scan算子简化逻辑（推荐） #

scan可以直接在数据流中追踪START-END的状态，同时统计中间符合条件的消息，逻辑更紧凑：

traces
| where message in ('start message', 'end message') or message startswith 'message prefix'
| sort by timestamp asc
| scan declare (in_window:bool=false, start_time:datetime) with (
    step start: where message == 'start message' => in_window=true, start_time = timestamp;
    step count_msg: where in_window and message startswith 'message prefix' => ;
    step end: where in_window and message == 'end message' => 
        in_window=false,
        output start_time as StartTime, timestamp as EndTime, count_msg.count as count_messages;
)
| extend Duration = EndTime - StartTime, DurationMinutes = toint(Duration / 600000000)
| project StartTime, EndTime, Duration, DurationMinutes, count_messages

方案说明 #

• 方案一通过先定义时间窗口变量，再将窗口与原始日志表做范围关联，最后按窗口分组统计，彻底避免了extend中非法引用表的问题。
• 方案二利用scan的状态追踪能力，直接在单数据流中完成窗口识别和计数，性能更优，代码更简洁。

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