如何在Kusto中将多次记录更新转换为日度周期快照

问题解决代码 #

你遇到的空行问题可以用Kusto的fill_forward()函数实现向前填充历史最新值，完整可运行的代码如下：

let visits = datatable(id:guid, timestamp:datetime, category:string, start:datetime, end:datetime, row:int)
[
    "b5ce180e-ce11-4936-b3f1-c817a261622e", datetime(2021-10-01T01:02:03), "SRU", datetime(2021-09-30T01:02:03), datetime(null), 1,
    "b5ce180e-ce11-4936-b3f1-c817a261622e", datetime(2021-10-02T02:02:03), "SRU", datetime(2021-09-30T01:02:03), datetime(null), 2,
    "b5ce180e-ce11-4936-b3f1-c817a261622e", datetime(2021-10-02T02:05:03), "SRU", datetime(2021-09-30T01:02:03), datetime(null), 3,
    "b5ce180e-ce11-4936-b3f1-c817a261622e", datetime(2021-10-04T04:05:03), "SRU", datetime(2021-09-30T01:02:03), datetime(null), 4,
    "b5ce180e-ce11-4936-b3f1-c817a261622e", datetime(2021-10-05T07:05:03), "SRU", datetime(2021-09-30T01:02:03), datetime(2021-10-01T07:00:00), 5
];
let binsize = 1d;
let min_date_time = toscalar(visits | summarize startofday(min(timestamp)));
let max_date_time = toscalar(visits | summarize endofday(max(timestamp)));
// 生成全量日期序列左连接每日最新记录
range hour from min_date_time to max_date_time step binsize
| join kind=leftouter (
    visits
    | summarize arg_max(timestamp, *) by id, bin(timestamp, binsize)
    | extend hour = bin(timestamp, binsize)
) on hour
| project-away hour1, timestamp
| project-rename timestamp = timestamp1
// 按id分组、日期排序后向前填充空值，多id场景也兼容
| order by id asc, hour asc 
| extend prev_id = prev(id)
| fill_forward(id, category, start, end, row) by prev_id
// 最终按天分组聚合得到目标结果
| extend day = startofday(hour)
| summarize 
    total = dcount(id),
    started = countif(isnotnull(start) and start <= day and (isnull(end) or end > day)),
    ended = countif(isnotnull(end) and end <= day)
 by day, category
| order by day asc

关键逻辑说明 #

• 空值填充：先按id和日期排序，用fill_forward()函数将前序非空的字段值自动填充到后续无更新的空行中，加prev_id的逻辑是避免不同id之间的值互相填充，兼容多id场景。
• 聚合计算：按天和分类分组后，total统计当前生效的id总数，started统计当天仍处于生效状态（开始时间早等于当天，结束时间为空或晚于当天）的记录数，ended统计当天已经结束的记录数，输出结果和你给出的预期格式完全匹配。

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