如何在KQL中按1天时间仓统计计数并补全缺失日期默认值

问题描述 #

现有KQL查询语句：

traces
| where timestamp between (['_startTime'] .. ['_endTime'])
| where message == "Operation Success"
| summarize count() by bin(timestamp, 1d)

该查询覆盖7天时间范围，但仅2天有匹配记录，当前结果只返回有数据的日期，希望汇总结果显示时间范围内的所有日期，缺失数据的日期计数为0。

当前查询结果：

期望查询结果：

解决方案 #

要补全缺失日期的0值，需要先生成覆盖整个时间范围的完整日期序列，再与原查询结果左连接，最后将空值替换为0。修改后的查询如下：

// 生成时间范围内的完整日期序列
let date_range = range timestamp from ['_startTime'] to ['_endTime'] step 1d;
// 原逻辑获取有数据的日期计数
let data = traces
    | where timestamp between (['_startTime'] .. ['_endTime'])
    | where message == "Operation Success"
    | summarize count = count() by bin(timestamp, 1d);
// 左连接补全所有日期，空计数替换为0
date_range
| join kind=leftouter data on timestamp
| project timestamp, count = coalesce(count, 0)

逻辑说明 #

1. 生成完整日期序列：用range运算符创建从起始时间到结束时间、步长1天的日期列表，确保时间范围内的每一天都被包含。
2. 提取有效数据计数：保留原查询的筛选和汇总逻辑，仅获取有匹配记录的日期及对应计数。
3. 左连接补全数据：将完整日期序列与有效计数结果左连接，保证所有日期都被保留，无匹配数据的日期对应的count字段会是null。
4. 空值替换为0：通过coalesce函数将null值替换为0，得到符合预期的结果。

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