在Kusto中按自定义起止时间生成24小时数据并补全缺失状态

核心需求实现：获取含起止时间的24小时状态数据 #

要实现自定义起止时间范围的状态查询，且自动补全起止时间点的最近历史状态（无对应时间记录时），仅用prev()无法生成起止时间点的新记录，需主动构造起止点的状态记录并与原始数据合并，具体代码如下：

// 1. 定义自定义起止时间
let start_time = datetime(2024-01-01 00:00:00);
let end_time = start_time + 24h; // 24小时范围
// 2. 获取原始数据（扩大范围以确保能拿到起止点之前的最近记录）
let raw_data = StatusTable | where date between (start_time - 7d .. end_time);
// 3. 构造起始时间点的最近状态记录
let start_status = raw_data 
    | where date <= start_time 
    | top 1 by date desc 
    | project date = start_time, status;
// 4. 构造结束时间点的最近状态记录
let end_status = raw_data 
    | where date <= end_time 
    | top 1 by date desc 
    | project date = end_time, status;
// 5. 合并数据、去重并排序
raw_data
| where date between (start_time .. end_time)
| union start_status, end_status
| distinct date, status
| sort by date asc

代码说明 #

• 扩大raw_data的时间范围到start_time - 7d是为了确保能获取到起始时间点之前的最近历史记录，可根据实际数据情况调整这个时间窗口；
• start_status和end_status分别提取起止时间点之前的最后一条状态记录，并将时间替换为自定义的起止时间，实现无对应时间记录时的补全；
• 最后合并原始范围内的数据与构造的起止点记录，去重后按时间排序，得到包含起止时间状态的完整结果。

无数据场景处理：时间段无记录时显示最近历史 #

如果自定义时间段内完全没有数据，可通过判断结果集是否为空，自动返回全表的最近历史记录，代码如下：

// 1. 定义自定义起止时间
let start_time = datetime(2024-01-01 00:00:00);
let end_time = start_time + 24h;
// 2. 获取原始表数据
let raw_data = StatusTable;
// 3. 生成含起止点的时间段数据
let range_data = raw_data
    | where date between (start_time .. end_time)
    | union (raw_data | where date <= start_time | top 1 by date desc | project date = start_time, status)
    | union (raw_data | where date <= end_time | top 1 by date desc | project date = end_time, status)
    | distinct date, status;
// 4. 无数据时返回最近历史记录
range_data
| union (
    range_data 
    | count 
    | where count_ == 0 
    | project date = start_time, status = tostring(raw_data | top 1 by date desc | project status)
)
| sort by date asc

代码说明 #

• 通过count函数判断range_data是否为空；
• 若为空，则提取全表最新的一条状态记录，将时间设为自定义起始时间（也可改为结束时间，根据需求调整），确保返回结果不为空。

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