Kusto中基于指定设备存在性的条件列求和问题

现有数据集 #

let data = datatable(Timestamp:datetime, Name:string, Status:string, Energy: real)
            [
             datetime(2022-02-18 10:00:00 AM), "Device_1", "uptime", 1.0,
             datetime(2022-02-18 10:01:00 AM), "Device_2", "downtime", 2.0,
             datetime(2022-02-18 10:02:00 AM), "Device_3", "uptime", 3.0,
             datetime(2022-02-18 10:03:00 AM), "Device_4", "downtime", 4.0, 
             datetime(2022-02-18 10:04:00 AM), "Device_5", "uptime", 5.0,
             datetime(2022-02-18 10:05:00 AM), "Device_6", "downtime", 6.0,
             datetime(2022-02-18 10:06:00 AM), "Device_7", "uptime", 7.0,
             datetime(2022-02-18 10:07:00 AM), "Device_8", "downtime", 8.0,
             datetime(2022-02-18 10:08:00 AM), "Device_9", "uptime", 9.0,
             datetime(2022-02-18 10:09:00 AM), "Device_10", "downtime", 10.0
            ];
data
| extend EnergyUptime = iff(Status == "uptime", toreal(Energy), toreal(0))

需求说明 #

当数据集中同时存在Device_1、Device_3、Device_5三个设备时，对所有EnergyUptime字段求和；若任一设备不存在，求和结果返回0。预期结果为25（1+3+5+7+9）。

正确查询代码 #

let data = datatable(Timestamp:datetime, Name:string, Status:string, Energy: real)
            [
             datetime(2022-02-18 10:00:00 AM), "Device_1", "uptime", 1.0,
             datetime(2022-02-18 10:01:00 AM), "Device_2", "downtime", 2.0,
             datetime(2022-02-18 10:02:00 AM), "Device_3", "uptime", 3.0,
             datetime(2022-02-18 10:03:00 AM), "Device_4", "downtime", 4.0, 
             datetime(2022-02-18 10:04:00 AM), "Device_5", "uptime", 5.0,
             datetime(2022-02-18 10:05:00 AM), "Device_6", "downtime", 6.0,
             datetime(2022-02-18 10:06:00 AM), "Device_7", "uptime", 7.0,
             datetime(2022-02-18 10:07:00 AM), "Device_8", "downtime", 8.0,
             datetime(2022-02-18 10:08:00 AM), "Device_9", "uptime", 9.0,
             datetime(2022-02-18 10:09:00 AM), "Device_10", "downtime", 10.0
            ];
let required_devices = dynamic(["Device_1", "Device_3", "Device_5"]);
let existing_devices = data | summarize dmake_set(Name);
let all_required_exist = existing_devices | extend all_present = set_has_all(existing_devices_Name, required_devices) | project all_present;
data
| extend EnergyUptime = iff(Status == "uptime", toreal(Energy), toreal(0))
| summarize total = sum(EnergyUptime)
| extend final_result = iff(todynamic(all_required_exist)[0], total, 0.0)
| project final_result

逻辑解释 #

1. 定义required_devices数组，存储必须存在的三个设备名称。
2. 通过summarize dmake_set(Name)获取数据集中所有存在的设备集合。
3. 使用set_has_all函数判断现有设备集合是否包含所有必填设备，得到布尔值all_present。
4. 先计算所有EnergyUptime的总和，再根据all_present的值决定返回总和还是0。

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