如何使用Kusto scan()算子确定会话的开始和结束时间
2026-7-29
Kusto会话分组：基于连续相同reason_code生成会话起止时间
我有一张存储连续遥测数据的表，仅包含Timestamp（表示事件开始时间）和reason_code字段。需要获取每个会话的开始与结束时间戳：
- 会话定义：
reason_code相同的连续记录集合 - 结束时间戳：下一个不同
reason_code事件的时间戳
源数据
| Timestamp | reason_code |
|---|---|
| 2023-02-28 01:07:01.9840000 | 4 |
| 2023-02-28 02:07:07.5330000 | 4 |
| 2023-02-28 03:07:13.9510000 | 4 |
| 2023-02-28 03:58:52.5190000 | 0 |
| 2023-02-28 04:58:57.9890000 | 0 |
| 2023-02-28 05:35:34.9820000 | 101 |
| 2023-02-28 05:35:44.9930000 | 1 |
| 2023-02-28 05:38:43.5760000 | 0 |
| 2023-02-28 05:55:24.1750000 | 0 |
| 2023-02-28 06:22:23.6230000 | 101 |
| 2023-02-28 06:22:33.6340000 | 4 |
| 2023-02-28 06:28:48.0060000 | 102 |
| 2023-02-28 06:51:06.2370000 | 101 |
| 2023-02-28 06:51:10.2420000 | 4 |
| 2023-02-28 06:55:28.4960000 | 4 |
| 2023-02-28 06:57:56.6280000 | 102 |
| 2023-02-28 07:38:30.8380000 | 101 |
期望输出
| start_ts | end_ts | reason_code | session |
|---|---|---|---|
| 2023-02-28 01:07:01.9840000 | 2023-02-28 03:58:52.5190000 | 4 | 1 |
| 2023-02-28 03:58:52.5190000 | 2023-02-28 05:35:34.9820000 | 0 | 2 |
| 2023-02-28 05:35:34.9820000 | 2023-02-28 05:35:44.9930000 | 101 | 3 |
| 2023-02-28 05:35:44.9930000 | 2023-02-28 05:38:43.5760000 | 1 | 4 |
| 2023-02-28 05:38:43.5760000 | 2023-02-28 06:22:23.6230000 | 0 | 5 |
| 2023-02-28 06:22:23.6230000 | 2023-02-28 06:22:33.6340000 | 101 | 6 |
| 2023-02-28 06:22:33.6340000 | 2023-02-28 06:28:48.0060000 | 4 | 7 |
| 2023-02-28 06:28:48.0060000 | 2023-02-28 06:51:06.2370000 | 102 | 8 |
| 2023-02-28 06:51:06.2370000 | 2023-02-28 06:51:10.2420000 | 101 | 9 |
| 2023-02-28 06:51:10.2420000 | 2023-02-28 06:57:56.6280000 | 4 | 10 |
| 2023-02-28 06:57:56.6280000 | 2023-02-28 07:38:30.8380000 | 102 | 11 |
| 2023-02-28 07:38:30.8380000 | 101 | 12 |
当前实现与优化需求
我认为scan()算子适合解决这个问题，但还没完全掌握其用法，当前实现代码如下：
T | scan with ( step s1 output=all: isempty(s1.reason_code) or s1.reason_code != reason_code; ) | extend EndTs=next(Timestamp) | project-rename StartTs=Timestamp
这个方案不够最优，希望找到**仅依赖scan()**的更简洁实现方式，求建议。
内容的提问来源于stack exchange，提问作者DocZerø
相关产品推荐
相关产品推荐
