如何基于字符串列数组动态构建Kusto summarize语句？

针对你的需求，有两种常用方法可以实现基于keys数组动态生成summarize聚合语句，无需硬编码列名：

方法1：使用mv-apply和make_bag（推荐，无需动态查询执行） #

这种方法通过展开动态列的键值对，聚合后重新打包为bag再拆包，自动适配所有存在的键：

let deviceTelemetry = datatable (deviceId:guid, timestamp:datetime, value:dynamic)[
'fddf1cec-16db-4461-9057-3d08e46b6bcf','2020-05-15 17:01:35.7750000', dynamic({ "level":  60}),
'fddf1cec-16db-4461-9057-3d08e46b6bcf','2020-05-15 18:01:35.7750000', dynamic({ "level":  50}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 17:01:35.7750000', dynamic({ "level": 100, "flow": 350}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 18:01:35.7750000', dynamic({ "level":  90, "flow": 360}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 19:01:35.7750000', dynamic({ "level":  80, "flow": 370}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 20:01:35.7750000', dynamic({ "level":  70, "flow": 380}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 21:01:35.7750000', dynamic({ "pressure":  120}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 20:01:35.7750000', dynamic({ "pressure":  130}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 21:01:35.7750000', dynamic({ "pressure":  140}),
];

deviceTelemetry
// 展开动态列中的每个键值对
| mv-apply kv = value on (
    extend key = tostring(bag_keys(kv)[0]), metric_value = toreal(kv[key])
)
// 按时间桶、设备ID和键计算平均值
| summarize avg_metric = avg(metric_value) by timestamp=bin(timestamp, 1d), deviceId, key
// 将同一设备、时间桶的平均值重新打包为bag
| summarize metrics = make_bag(pack(key, avg_metric)) by timestamp, deviceId
// 拆包bag为列
| evaluate bag_unpack(metrics)

优点： #

• 无需预定义键列表，自动适配所有存在的键
• 不需要动态查询执行，性能更稳定
• 自动处理缺失键的情况（对应列值为null）

方法2：动态生成查询字符串并执行 #

如果需要严格基于预计算的keys数组生成聚合语句，可以通过构造动态查询字符串并使用execute执行：

let deviceTelemetry = datatable (deviceId:guid, timestamp:datetime, value:dynamic)[
'fddf1cec-16db-4461-9057-3d08e46b6bcf','2020-05-15 17:01:35.7750000', dynamic({ "level":  60}),
'fddf1cec-16db-4461-9057-3d08e46b6bcf','2020-05-15 18:01:35.7750000', dynamic({ "level":  50}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 17:01:35.7750000', dynamic({ "level": 100, "flow": 350}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 18:01:35.7750000', dynamic({ "level":  90, "flow": 360}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 19:01:35.7750000', dynamic({ "level":  80, "flow": 370}),
'aaaaaaaa-fed4-c23b-422b-e85e0877c092','2020-05-15 20:01:35.7750000', dynamic({ "level":  70, "flow": 380}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 21:01:35.7750000', dynamic({ "pressure":  120}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 20:01:35.7750000', dynamic({ "pressure":  130}),
'cb04ccff-48bc-4108-9d16-7d7db9152895','2020-05-15 21:01:35.7750000', dynamic({ "pressure":  140}),
];

// 预计算所有需要聚合的键
let keys_list = toscalar(deviceTelemetry
| summarize make_bag(value)
| extend keys = bag_keys(bag_value)
| project keys);

// 动态生成聚合语句片段
let aggregations = strcat_array(
    array_map(x => strcat(x, "=avg(toreal(", x, "))"), keys_list), 
    ", "
);

// 构造完整查询字符串
let dynamic_query = strcat(
    "deviceTelemetry ",
    "| evaluate bag_unpack(value) ",
    "| summarize ", aggregations, " ",
    "by timestamp=bin(timestamp, 1d), deviceId"
);

// 执行动态查询
execute dynamic_query

优点： #

• 严格基于预定义的键列表生成聚合
• 生成的查询结构与硬编码版本一致

注意事项： #

• 需要启用动态查询执行权限
• 键列表变化时会自动更新聚合语句

两种方法都能实现你的需求，推荐优先使用方法1，因为它更简洁且不需要动态查询执行的额外权限。

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