求助编写KQL查询：按时间窗口汇总错误时长与错误码

需求说明 #

• 输入数据表包含字段：StartTime、EndTime、ErrorDuration、ErrorCode
• 给定时间范围参数：

  let _startTime = datetime(2024-08-05T00:00:00Z);
  let _endTime = datetime(2024-08-08T00:00:00Z);
  

• 时间窗口参数：

  let _binTimestamp = 1h;
  

• 输出要求：
  1. 生成从_startTime到_endTime按_binTimestamp划分的所有时间窗口（含无错误窗口）
  2. 每个窗口需包含：窗口起始时间Timestamp、错误持续分钟数ErrorMinutes、去重后的错误码数组ErrorCodes

以下是满足需求的KQL查询：

// 定义时间范围和窗口参数
let _startTime = datetime(2024-08-05T00:00:00Z);
let _endTime = datetime(2024-08-08T00:00:00Z);
let _binTimestamp = 1h;

// 生成完整的时间窗口序列，确保无错误窗口不遗漏
let time_windows = range Timestamp from _startTime to _endTime step _binTimestamp;

// 处理原始错误数据，拆分到对应窗口并计算有效持续时间
let error_data = YourErrorTable
| where StartTime < _endTime and EndTime > _startTime  // 过滤与目标时间范围有交集的错误
| extend window_start = bin(StartTime, _binTimestamp)
| extend window_end = window_start + _binTimestamp
// 计算错误在当前窗口内的实际持续分钟数
| extend overlap_start = iif(StartTime < window_start, window_start, StartTime)
| extend overlap_end = iif(EndTime > window_end, window_end, EndTime)
| extend error_minutes_in_window = datetime_diff('minute', overlap_end, overlap_start)
// 按窗口分组汇总
| summarize 
    ErrorMinutes = sum(error_minutes_in_window),
    ErrorCodes = make_set(ErrorCode)
    by window_start;

// 关联窗口与错误数据，补全无错误窗口的默认值
time_windows
| left join kind=leftouter error_data on $left.Timestamp == $right.window_start
| project 
    Timestamp,
    ErrorMinutes = coalesce(ErrorMinutes, 0),
    ErrorCodes = coalesce(ErrorCodes, dynamic([]))
| sort by Timestamp asc;

关键逻辑说明 #

1. 生成完整时间窗口：用range函数创建连续的时间窗口序列，保证输出覆盖所有指定时间段。
2. 拆分错误到窗口：对跨窗口的错误计算其在每个窗口内的实际持续时长，避免重复统计。
3. 汇总错误信息：按窗口分组，用sum累加持续时间，make_set生成去重的错误码数组。
4. 补全无错误窗口：通过左连接关联窗口和错误数据，用coalesce为无错误窗口设置默认值（0分钟、空数组）。

预期输出示例 #

datatable(Timestamp: datetime, ErrorMinutes: int, ErrorCodes: dynamic)
[
    datetime(2024-08-05T00:00:00Z), 0, dynamic([]),
    datetime(2024-08-05T01:00:00Z), 0, dynamic([]),
    datetime(2024-08-05T02:00:00Z), 0, dynamic([]),
    datetime(2024-08-05T03:00:00Z), 0, dynamic([]),
    datetime(2024-08-05T04:00:00Z), 0, dynamic([]),
    datetime(2024-08-05T05:00:00Z), 60, dynamic([497, 402]),
    datetime(2024-08-05T06:00:00Z), 10, dynamic([497, 402]),
    datetime(2024-08-05T07:00:00Z), 0, dynamic([]),
    datetime(2024-08-05T08:00:00Z), 30, dynamic([402]),
    // 省略其余行...
    datetime(2024-08-07T23:00:00Z), 60, dynamic([467])
];

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