如何在Kusto中按未知数量的维度列生成汇总结果？

我需要跨多个地理区域汇总任务执行结果，但无法预知env_cloud_location的所有可能取值（实际数量为5-12个）。当前使用的硬编码指定区域的查询如下：

datatable(env_cloud_location : string, result : string )[
    "East Asia", "Passed",
    "East Asia", "Failed",
    "East Asia", "Error",
    "East US", "Passed",
    "East US", "Error",
    "East US", "Passed",
    "North Europe", "Failed",
    "North Europe", "Error",
    "North Europe", "Error",
]
| summarize 
    eastAsia = countif(env_cloud_location  == "East Asia"),
    eastUS = countif(env_cloud_location  == "East US"),
    northEurope = countif(env_cloud_location == "North Europe")
    by result

期望输出格式为每个地理区域自动生成单独列，示例如下：

解决方案 #

使用Kusto的pivot运算符可以实现动态列生成，无需提前指定所有地理区域。修改后的查询如下：

datatable(env_cloud_location : string, result : string )[
    "East Asia", "Passed",
    "East Asia", "Failed",
    "East Asia", "Error",
    "East US", "Passed",
    "East US", "Error",
    "East US", "Passed",
    "North Europe", "Failed",
    "North Europe", "Error",
    "North Europe", "Error",
]
// 先统计每个结果类型与地理区域的任务数量
| summarize count() by result, env_cloud_location
// 按地理区域值动态生成列，空数据用0填充
| pivot env_cloud_location, sum(count_) on result with (default = 0)

说明 #

1. 第一步summarize count() by result, env_cloud_location先得到每个结果类型在对应区域的任务数
2. pivot运算符会自动遍历env_cloud_location的所有唯一值，为每个值生成独立列，default = 0确保无数据的单元格自动补0，完全匹配预期输出格式

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