如何让KQL的case分支无匹配时也显示默认统计行？

要实现每天都返回三个Duration桶的行，哪怕没有匹配数据，核心是先构建日期与桶的全量组合，再和你的聚合结果做左连接补0。具体实现如下：

// 替换成你的原始数据集及delay计算逻辑
let raw_data = your_table
| extend delay = ...; // 这里写你的delay计算代码
// 生成查询范围内的所有日期（按天）
let date_range = raw_data
| summarize min_date = min(bin(timestamp, 1d)), max_date = max(bin(timestamp, 1d))
| mvexpand timestamp = range(min_date, max_date, 1d)
| project timestamp;
// 定义所有需要的Duration桶
let duration_buckets = datatable(Duration:string)["< 30 days", "30-90 days", "90+ days"];
// 生成日期+桶的全量组合（笛卡尔积）
let all_combinations = date_range
| join kind=cross duration_buckets on $left.empty == $right.empty;
// 左连接聚合结果，补全0值
raw_data
| summarize Total = count() by Duration = case(delay < 30, "< 30 days", delay <= 90, "30-90 days", "90+ days"), bin(timestamp, 1d)
| join kind=leftouter all_combinations on Duration, timestamp
| project timestamp, Duration, Total = coalesce(Total, 0)
| sort by timestamp, Duration

关键逻辑说明： #

• date_range：从原始数据中提取日期边界，生成该区间内的每一天，确保没有遗漏日期
• duration_buckets：明确列出所有桶类型，避免case分支丢失上下文
• 交叉连接得到所有日期+桶的组合后，左连接你的聚合结果，用coalesce把无匹配的null替换为0

如果想每天一行、每个Duration桶作为单独列展示计数，用pivot可以直接实现，自动补0：

your_table
| extend delay = ...; // 你的delay计算逻辑
| summarize count_ = count() by Duration = case(delay < 30, "< 30 days", delay <= 90, "30-90 days", "90+ days"), bin(timestamp, 1d)
| pivot(Duration, sum(count_))
| sort by timestamp

补充：强制保留所有桶列 #

如果某个桶全程无数据，默认pivot不会生成对应列，可通过注入空数据强制保留三个桶：

let duration_buckets = datatable(Duration:string)["< 30 days", "30-90 days", "90+ days"];
your_table
| extend delay = ...; // 你的delay计算逻辑
| extend Duration = case(delay < 30, "< 30 days", delay <= 90, "30-90 days", "90+ days")
| union (duration_buckets | extend timestamp = datetime(null), count_=0)
| summarize count_ = count() by Duration, bin(timestamp, 1d)
| pivot(Duration, sum(count_))
| sort by timestamp

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