Kusto/KQL：起止日期间生成新行并填充最近日志取值

核心需求 #

• 按维度（Site、Class等，支持扩展任意多维度）分组后，按时间倒序排列日志记录
• 相邻两条记录之间的时间区间，统一使用时间更晚的那条记录的指标值填充，生成区间内所有月末时间点的行
• 填充的行截止到更早那条记录的日期之后，不跨区间混用指标值

实现代码 #

// 示例输入数据，实际使用替换为自己的表名即可
datatable (Site: string,Class: string, Rating: int ,datestamp: datetime)    
 [         
 'Location 1',  'A', 5, '2022-06-30T00:00:00Z',
 'Location 1',  'A', 10, '2021-01-30T00:00:00Z',
 'Location 2',  'A', 4, '2022-06-30T00:00:00Z',
 'Location 2',  'A', 3, '2021-02-28T00:00:00Z',
 ] 
| partition by Site, Class // 新增维度直接在后面追加字段名即可
(
    order by datestamp desc
    // 取同组内上一条（时间更早的相邻记录）的日期作为当前区间的左边界
    | extend prev_datestamp = prev(datestamp, 1, datetime(0))
    // 生成区间内所有月末时间点：左边界后第一个月末到当前记录日期，步长1个月
    | extend DateRange = range(
        endofmonth(prev_datestamp + 1d), 
        datestamp, 
        30d
    )
    // 展开生成的时间序列
    | mv-expand DateRange to typeof(datetime)
    // 统一修正为标准月末日期，自动处理2月、大小月天数不一致问题
    | extend datestamp = endofmonth(DateRange)
    // 清理中间计算字段
    | project-away prev_datestamp, DateRange
)
// 按维度+时间倒序输出，和预期结果排序一致
| order by Site, datestamp desc

方案说明 #

• 代码自动适配不同维度组的独立时间区间，不会出现跨Site/跨Class的数值混用问题
• 如果需要按周/日粒度填充，只需要修改range函数的步长、以及对应的日期修正函数即可
• 新增维度时不需要修改核心计算逻辑，仅需在partition by后追加对应的维度字段名

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