Azure Data Explorer：计算服务预订变更历史及每日统计

需求1：生成服务预订变更历史表 #

捕捉每个设备每次服务状态变更时的具体增减项，仅保留有实际变更的时间点。

实现代码 #

let devices=datatable(device:string, timestamp:datetime, bookedServices:dynamic) [
    "device-1", datetime(2024-06-11), dynamic(["service1", "service2"]),
    "device-2", datetime(2024-06-11), dynamic(["service1", "service2"]),
    "device-1", datetime(2024-06-13), dynamic(["service2"]),
    "device-3", datetime(2024-06-14), dynamic(["service3"]),
    "device-1", datetime(2024-06-16), dynamic([]),
    "device-1", datetime(2024-06-19), dynamic(["service1", "service2"])
];
// 按设备分组排序，获取上一次服务状态
devices
| partition by device (
    sort by timestamp asc
    | extend previousServices = prev(bookedServices)
    | extend previousServices = iif(isnull(previousServices), dynamic([]), previousServices)
)
// 计算新增/移除的服务
| extend addedServices = set_difference(bookedServices, previousServices)
| extend removedServices = set_difference(previousServices, bookedServices)
// 过滤无变更的记录
| where array_length(addedServices) > 0 or array_length(removedServices) > 0
// 整理变更记录格式
| mv-expand addedServices to typeof(string)
| mv-expand removedServices to typeof(string)
| extend changeType = case(
    not(isnull(addedServices)), "预订",
    not(isnull(removedServices)), "移除",
    ""
)
| extend serviceName = coalesce(addedServices, removedServices)
| project timestamp, device, serviceName, changeType
| sort by timestamp asc, device asc

说明 #

• 按设备单独排序，对比前后两次服务状态的差异
• 用set_difference计算新增和移除的服务项
• 仅保留存在服务增减的记录，最终输出变更时间、设备、服务名和变更类型

需求2：生成各服务每日的设备预订数统计表 #

生成全量服务的每日预订数，即使当天无变更也保留记录。

实现代码 #

let devices=datatable(device:string, timestamp:datetime, bookedServices:dynamic) [
    "device-1", datetime(2024-06-11), dynamic(["service1", "service2"]),
    "device-2", datetime(2024-06-11), dynamic(["service1", "service2"]),
    "device-1", datetime(2024-06-13), dynamic(["service2"]),
    "device-3", datetime(2024-06-14), dynamic(["service3"]),
    "device-1", datetime(2024-06-16), dynamic([]),
    "device-1", datetime(2024-06-19), dynamic(["service1", "service2"])
];
// 提取所有出现过的服务
let allServices = devices
| mv-expand bookedServices to typeof(string)
| distinct bookedServices;
// 生成完整日期范围（从最早到最晚记录的所有日期）
let dateRange = devices
| summarize minDate = min(timestamp), maxDate = max(timestamp)
| mv-expand date_range = range(bin(minDate, 1d), bin(maxDate, 1d), 1d) to typeof(datetime)
| project date = date_range;
// 计算每个设备每项服务的生效时间段
let deviceServicePeriods = devices
| partition by device (
    sort by timestamp asc
    | extend nextTimestamp = next(timestamp)
    | extend endDate = iif(isnull(nextTimestamp), max(dateRange.date) + 1d, nextTimestamp)
)
| mv-expand service = bookedServices to typeof(string)
| project device, service, startDate = timestamp, endDate;
// 关联日期与服务，统计每日预订数
dateRange
| join kind=cross allServices on $left.date >= $left.date
| join kind=leftouter deviceServicePeriods on $left.bookedServices == $right.service and $left.date >= $right.startDate and $left.date < $right.endDate
| summarize bookedCount = dcount(device) by date, service = bookedServices
| sort by date asc, service asc

说明 #

• 提取全量服务列表，确保统计覆盖所有出现过的服务
• 生成完整日期范围，保证每日都有统计记录
• 计算每个设备每项服务的生效时间段，关联日期后统计当日有效预订数，无预订的服务计数为0

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