如何使用KQL计算各设备时间周期的重叠天数

给定包含DeviceID、StartDate、EndDate字段的Devices数据表，示例数据如下：

let Devices = datatable(DeviceID:string, StartDate:datetime, EndDate:datetime)
[
    "a1", datetime(2024-01-01), datetime(2024-05-03),
    "a1", datetime(2024-02-12), datetime(2024-02-18),
    "b1", datetime(2024-06-13), datetime(2024-07-07),
    "b1", datetime(2024-07-08), datetime(2024-07-08),
    "c1", datetime(2024-08-23), datetime(2024-10-10),
    "c1", datetime(2024-09-01), datetime(2024-10-07)
];

需编写KQL查询语句，统计每个DeviceID对应的时间周期（StartDate至EndDate）的总重叠天数，判断是否存在重叠及重叠时长。

以下是实现需求的KQL查询语句：

let Devices = datatable(DeviceID:string, StartDate:datetime, EndDate:datetime)
[
    "a1", datetime(2024-01-01), datetime(2024-05-03),
    "a1", datetime(2024-02-12), datetime(2024-02-18),
    "b1", datetime(2024-06-13), datetime(2024-07-07),
    "b1", datetime(2024-07-08), datetime(2024-07-08),
    "c1", datetime(2024-08-23), datetime(2024-10-10),
    "c1", datetime(2024-09-01), datetime(2024-10-07)
];
// 拆分时间区间为单日记录，便于统计每日覆盖次数
let DailyRecords = Devices
| mv-expand Day = range(bin(StartDate, 1d), bin(EndDate, 1d), 1d)
| project DeviceID, Day;
// 筛选出被多个区间覆盖的日期（即重叠日期）
let OverlapDays = DailyRecords
| summarize CoverageCount = count() by DeviceID, Day
| where CoverageCount > 1
| project DeviceID, Day;
// 统计每个设备的重叠情况，补充无重叠设备的记录
OverlapDays
| summarize 
    TotalOverlapDays = dcount(Day),
    HasOverlap = iff(dcount(Day) > 0, "是", "否")
by DeviceID
| union (
    Devices 
    | distinct DeviceID 
    | where not(DeviceID in (OverlapDays | distinct DeviceID)) 
    | project DeviceID, TotalOverlapDays = 0, HasOverlap = "否"
)
| order by DeviceID

• DailyRecords：把每个设备的时间区间拆分成单日记录，为后续统计每日被覆盖的次数做准备。
• OverlapDays：筛选出被多个时间区间覆盖的日期，这些就是重叠的日期。
• 最终统计：计算每个设备的总重叠天数，判断是否存在重叠，同时补充无重叠设备的记录，确保所有设备都被纳入统计。

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