如何让Kusto查询显示计数为0的事件类别并转置为列格式？

问题说明 #

原查询仅返回有数据的事件类别（如eve_1），当eve_2无匹配数据时不会显示，需调整查询，确保每个时间点下eve_1、eve_2都存在，计数为0时显示0，最终转为以时间为行、事件类别为列的格式。

修改后的完整查询 #

// 1. 统计原始数据中各时间-事件类别的计数
let event_stats = Table 
| where DateTime > ago(7d) 
| where EID in (11, 22, 33)
| extend
    Evet_cat = case(EID in (11, 22), "eve_1", 
                    EID == 33, "eve_2",
                    "others"),
    Date_time = format_datetime(DateTime, 'yyyy-MM-dd HH')
| summarize count_ = count() by Date_time, Evet_cat
| where Evet_cat in ("eve_1", "eve_2"); // 过滤无关类别

// 2. 生成所有时间点与事件类别的全组合
let all_time_points = event_stats | distinct Date_time;
let all_event_cats = dynamic(["eve_1", "eve_2"]) | mv-expand Evet_cat to typeof(string);
let time_cat_pairs = all_time_points | join kind=cross all_event_cats on $left.empty = $right.empty;

// 3. 补全0值并转置为列格式
time_cat_pairs
| project Date_time, Evet_cat
| left join event_stats on Date_time, Evet_cat
| extend count_ = coalesce(count_, 0)
| evaluate pivot(Evet_cat, sum(count_))
| order by Date_time desc

关键步骤解释 #

• 原始统计过滤：在原始统计后过滤掉others类别，只保留需要的eve_1和eve_2，减少无效数据。
• 全组合构建：通过distinct提取所有出现过的时间点，结合固定事件类别列表做交叉连接，确保每个时间点都对应所有事件类别，避免遗漏无数据的类别。
• 0值补全：使用left join关联原始统计结果，无匹配的行计数会为null，通过coalesce将其替换为0。
• 列转置：evaluate pivot()将事件类别转为列，sum(count_)保证每个时间点每个类别的计数正确聚合。

中间输出示例 #

|   Date_time   | Evet_cat | count_ |
|---------------|----------|--------|
| 2023-02-24 13 | eve_1    |     10 |
| 2023-02-24 13 | eve_2    |      0 |
| 2023-02-24 12 | eve_1    |      5 |
| 2023-02-24 12 | eve_2    |      0 |

最终输出示例 #

|   Date_time   | eve_1    | eve_2  |
|---------------|----------|--------|
| 2023-02-24 13 |    10    |      0 |
| 2023-02-24 12 |     5    |      0 |

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