KQL Union表结果集序列化后prev函数行匹配错误如何修复

核心原因 #

prev函数取值错误的本质是union后的排序规则没有保证同一小时的两天数据连续相邻、且前一天数据排在当天数据前。你在TableA和TableB内部做的排序会在union操作后失效，即使肉眼看起来行是成对排列的，序列化后的行序没有绑定小时维度，才会出现跨小时取前值的情况。

调整方案 #

只需要修改两处逻辑即可解决问题：

1. 对两个临时表新增小时维度标识列，统一按小时作为第一排序主键
2. 调整union后的排序规则，优先按小时排序，同小时内按时间先后排序，再执行序列化操作

修改后的完整代码 #

let start_time1=startofday(now() - 48h);
let start_time=startofday(now() - 24h);
let end_time=now();
let end_time1 = startofday(now()-24h);
let TableA = Source
| where TimeGenerated > start_time1 and TimeGenerated < end_time1
| where Computer == "ComputerName"
| where CounterName == "% Free Space"
| where InstanceName == "E:"
| summarize arg_min(TimeGenerated, CounterValue) by bin(TimeGenerated, 1h), InstanceName
// 新增小时槽标识，提取小时部分作为排序主键；新增日期标签区分数据归属
| extend HourSlot = format_datetime(TimeGenerated, 'HH'), DayTag = "PrevDay"
| project HourSlot, DayTag, TimeGenerated, InstanceName, CounterValue;
let TableB = Source
| where TimeGenerated > start_time and TimeGenerated < end_time
| where Computer == "ComputerName"
| where CounterName == "% Free Space"
| where InstanceName == "E:"
| summarize arg_min(TimeGenerated, CounterValue) by bin(TimeGenerated, 1h), InstanceName
| extend HourSlot = format_datetime(TimeGenerated, 'HH'), DayTag = "CurDay"
| project HourSlot, DayTag, TimeGenerated, InstanceName, CounterValue;
TableA
| union TableB
// 先按小时槽排序，同小时内按时间升序排列，保证前一天数据在前、当天数据在后
| order by HourSlot asc, TimeGenerated asc
| serialize
// 只对当天数据计算前值，过滤前一天数据的无效计算结果
| extend prevValue = iff(DayTag == "CurDay", prev(CounterValue, 1), double(null))
| extend diffvalue = CounterValue - prevValue
| extend trend = case(
    isnull(prevValue), "",
    CounterValue < prevValue, "Free Space Reduces",
    CounterValue > prevValue, "Free Space Increases",
    "No difference"
)
// 可根据需求调整最终展示顺序，比如按小时倒序排列
| order by HourSlot desc;

更稳定的替代方案（推荐） #

如果不需要保留两天数据的两行明细结构，可以直接用join算子按小时槽关联两天数据，完全避免行序依赖，准确率更高：

// TableA、TableB定义和上面保持一致，替换后续逻辑即可
TableA
| join kind=inner TableB on HourSlot
| extend diffvalue = CounterValue1 - CounterValue
| extend trend = case(
    CounterValue1 < CounterValue, "Free Space Reduces",
    CounterValue1 > CounterValue, "Free Space Increases",
    "No difference"
)
| project HourSlot, 前一天剩余空间=CounterValue, 当天剩余空间=CounterValue1, 差值=diffvalue, 趋势=trend;

内容的提问来源于stack exchange，提问作者Pavel Vítek