如何使用Azure Data Explorer/Kusto查询为管道运行数据新增计算列

核心逻辑说明 #

你需要的统计逻辑可以通过分组排序+相邻行取值实现，核心判断规则可以简化为：仅当最新一次管道运行状态为Fail时，计算与上一次运行的时间间隔，否则间隔字段留空，完全匹配你给出的3条规则。

完整查询代码 #

// 样例数据构造，实际使用时替换为你自己的表名即可
datatable(Customers:string, PipelineType:string, PipelineState:string, TimeStamp:datetime)
[
"CustomerA","PipelineA","Succes",datetime(2021-08-13 12:59:03.0073653),
"CustomerA","PipelineA","Fail",datetime(2021-08-13 09:59:03.0124853),
"CustomerA","PipelineB","Succes",datetime(2021-08-13 11:56:03.0151948),
"CustomerA","Pipeline B","Fail",datetime(2021-08-12 17:56:03.0019445),
"CustomerA","Pipeline C","Succes",datetime(2021-08-13 13:16:03.0015617),
"CustomerA","Pipeline C","Fail",datetime(2021-07-30 21:52:03.0157372),
"CustomerB","PipelineA","Succes",datetime(2021-08-13 12:59:03.0073331),
"CustomerB","PipelineA","Succes",datetime(2021-08-13 12:57:03.0099138),
"CustomerB","PipelineB","Fail",datetime(2021-07-30 03:33:03.0123262),
"CustomerB","Pipeline B","Succes",datetime(2021-08-13 13:16:03.0015297),
"CustomerB","Pipeline C","Fail",datetime(2021-08-13 12:57:03.0099499),
"CustomerB","Pipeline C","Succes",datetime(2021-08-13 13:16:03.0016348),
"CustomerC","PipelineA","Succes",datetime(2021-08-13 13:16:03.0016999),
"CustomerC","PipelineA","Succes",datetime(2021-08-13 12:59:03.0074113),
"CustomerC","PipelineB","Succes",datetime(2021-08-13 10:56:03.0075546),
"CustomerC","Pipeline B","Fail",datetime(2021-08-11 06:54:03.0118628),
"CustomerC","Pipeline C","Fail",datetime(2021-08-13 13:16:03.0016233),
"CustomerC","Pipeline C","Fail",datetime(2021-08-13 12:59:03.0072337),
]
// 修正管道名称的空格问题，避免同管道因为空格被识别为不同类型
| extend PipelineType = replace_string(PipelineType, " ", "")
// 按客户+管道分组，每组内按时间升序排序
| partition by (Customers, PipelineType)
{
    order by TimeStamp asc
    // 取同组上一行的状态和时间戳
    | extend PrevState = prev(PipelineState), PrevTime = prev(TimeStamp)
}
// 按规则计算时间间隔
| extend Interval_Hours = case(
    PipelineState == "Fail" and isnotempty(PrevTime),
    datetime_diff('hour', TimeStamp, PrevTime),
    double(null)
), Interval_Days = round(Interval_Hours/24,2)

结果说明 #

• 新增的Interval_Hours为两次运行间隔的小时数，Interval_Days为换算后的天数，仅当最新一次运行失败时会有值
• 针对样例数据，你可以看到CustomerC的PipelineC两次运行都失败，间隔为0.28小时（约17分钟），符合计算规则；CustomerB的PipelineC最近一次运行成功，对应间隔字段留空，符合规则要求。

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