如何用Kusto(KQL)按用户和日期透视事件行并展示起止时间

数据集 #

datatable(CreatedDate:datetime, User:string, EventCode:string)
[
    datetime(4-22-2024 12:44:02.750 PM), "user1", "TS",
    datetime(4-23-2024 4:09:30.551 AM), "user1", "TD",
    datetime(4-23-2024 4:09:59.067 AM), "user1", "SP",
    datetime(4-23-2024 7:10:02.052 AM), "user1", "TD",
    datetime(4-23-2024 7:12:05.357 AM), "user1", "TC",
    datetime(4-25-2024 5:11:02.649 AM), "user1", "TD",
    datetime(4-25-2024 5:12:56.672 AM), "user1", "TC",
    datetime(4-23-2024 9:53:12.315 AM), "user2", "TS",
    datetime(4-25-2024 4:36:33.656 AM), "user2", "TD",
    datetime(4-25-2024 4:38:46.922 AM), "user2", "TC",
    datetime(4-22-2024 12:40:35.801 PM), "user3", "TS",
    datetime(4-23-2024 4:13:09.379 AM), "user3", "TD",
    datetime(4-23-2024 4:13:23.724 AM), "user3", "TS",
    datetime(4-23-2024 4:14:23.724 AM), "user3", "TC",
    datetime(4-25-2024 4:34:18.966 AM), "user3", "TD",
    datetime(4-25-2024 4:41:07.381 AM), "user3", "TC",
]
| order by User asc, CreatedDate asc

需求规则 #

• 按用户和日期分组，将每日事件合并为单行展示
• 事件标识规则：
  • TD：事件开始码
  • TC/SP：事件结束码
  • TS及其他码可忽略
• 中间事件不影响逻辑；若出现TD、TD、TC序列，仅保留最后一组TD到结束码的有效事件
• 输出需展示：当日总耗时、每个事件的结束码、起止时间、单事件耗时，支持单日最多10-15个事件的横向扩展，日期格式不限

查询实现 #

// 过滤有效事件，仅保留TD、TC、SP并排序
let filtered_events = datatable(CreatedDate:datetime, User:string, EventCode:string)
[
    datetime(4-22-2024 12:44:02.750 PM), "user1", "TS",
    datetime(4-23-2024 4:09:30.551 AM), "user1", "TD",
    datetime(4-23-2024 4:09:59.067 AM), "user1", "SP",
    datetime(4-23-2024 7:10:02.052 AM), "user1", "TD",
    datetime(4-23-2024 7:12:05.357 AM), "user1", "TC",
    datetime(4-25-2024 5:11:02.649 AM), "user1", "TD",
    datetime(4-25-2024 5:12:56.672 AM), "user1", "TC",
    datetime(4-23-2024 9:53:12.315 AM), "user2", "TS",
    datetime(4-25-2024 4:36:33.656 AM), "user2", "TD",
    datetime(4-25-2024 4:38:46.922 AM), "user2", "TC",
    datetime(4-22-2024 12:40:35.801 PM), "user3", "TS",
    datetime(4-23-2024 4:13:09.379 AM), "user3", "TD",
    datetime(4-23-2024 4:13:23.724 AM), "user3", "TS",
    datetime(4-23-2024 4:14:23.724 AM), "user3", "TC",
    datetime(4-25-2024 4:34:18.966 AM), "user3", "TD",
    datetime(4-25-2024 4:41:07.381 AM), "user3", "TC",
]
| where EventCode in ("TD", "TC", "SP")
| order by User, CreatedDate asc;

// 按用户和日期分区，配对有效事件（处理多TD场景）
let paired_events = filtered_events
| partition by User, bin(CreatedDate, 1d) into daily_events
{
    daily_events
    | extend is_start = EventCode == "TD", is_end = EventCode in ("TC", "SP")
    // 反向排序标记每个结束码对应的最近TD
    | order by CreatedDate desc
    | extend prev_start = row_window_session(CreatedDate, 0s, 0s, is_start)
    | where is_end or (is_start and next(is_end, 1) == true)
    | order by CreatedDate asc
    // 分组配对TD与结束码
    | extend event_group = row_number() / 2
    | where event_group != 0 // 过滤未配对的TD
    | summarize 
        EventEndCode = take_anyif(EventCode, is_end),
        StartTime = min(CreatedDate),
        EndTime = max(CreatedDate),
        Duration = max(CreatedDate) - min(CreatedDate)
        by User, bin(CreatedDate, 1d) as EventDate, event_group
};

// 横向扩展事件字段，计算当日总耗时
paired_events
| summarize 
    TotalDailyDuration = sum(Duration),
    // 生成15个事件的扩展字段
    Event1_EndCode = take_anyif(EventEndCode, event_group == 1),
    Event1_StartTime = take_anyif(StartTime, event_group == 1),
    Event1_EndTime = take_anyif(EndTime, event_group == 1),
    Event1_Duration = take_anyif(Duration, event_group == 1),
    Event2_EndCode = take_anyif(EventEndCode, event_group == 2),
    Event2_StartTime = take_anyif(StartTime, event_group == 2),
    Event2_EndTime = take_anyif(EndTime, event_group == 2),
    Event2_Duration = take_anyif(Duration, event_group == 2),
    Event3_EndCode = take_anyif(EventEndCode, event_group == 3),
    Event3_StartTime = take_anyif(StartTime, event_group == 3),
    Event3_EndTime = take_anyif(EndTime, event_group == 3),
    Event3_Duration = take_anyif(Duration, event_group == 3),
    Event4_EndCode = take_anyif(EventEndCode, event_group == 4),
    Event4_StartTime = take_anyif(StartTime, event_group == 4),
    Event4_EndTime = take_anyif(EndTime, event_group == 4),
    Event4_Duration = take_anyif(Duration, event_group == 4),
    Event5_EndCode = take_anyif(EventEndCode, event_group == 5),
    Event5_StartTime = take_anyif(StartTime, event_group == 5),
    Event5_EndTime = take_anyif(EndTime, event_group == 5),
    Event5_Duration = take_anyif(Duration, event_group == 5),
    Event6_EndCode = take_anyif(EventEndCode, event_group == 6),
    Event6_StartTime = take_anyif(StartTime, event_group == 6),
    Event6_EndTime = take_anyif(EndTime, event_group == 6),
    Event6_Duration = take_anyif(Duration, event_group == 6),
    Event7_EndCode = take_anyif(EventEndCode, event_group == 7),
    Event7_StartTime = take_anyif(StartTime, event_group == 7),
    Event7_EndTime = take_anyif(EndTime, event_group == 7),
    Event7_Duration = take_anyif(Duration, event_group == 7),
    Event8_EndCode = take_anyif(EventEndCode, event_group == 8),
    Event8_StartTime = take_anyif(StartTime, event_group == 8),
    Event8_EndTime = take_anyif(EndTime, event_group == 8),
    Event8_Duration = take_anyif(Duration, event_group == 8),
    Event9_EndCode = take_anyif(EventEndCode, event_group == 9),
    Event9_StartTime = take_anyif(StartTime, event_group == 9),
    Event9_EndTime = take_anyif(EndTime, event_group == 9),
    Event9_Duration = take_anyif(Duration, event_group == 9),
    Event10_EndCode = take_anyif(EventEndCode, event_group == 10),
    Event10_StartTime = take_anyif(StartTime, event_group == 10),
    Event10_EndTime = take_anyif(EndTime, event_group == 10),
    Event10_Duration = take_anyif(Duration, event_group == 10),
    Event11_EndCode = take_anyif(EventEndCode, event_group == 11),
    Event11_StartTime = take_anyif(StartTime, event_group == 11),
    Event11_EndTime = take_anyif(EndTime, event_group == 11),
    Event11_Duration = take_anyif(Duration, event_group == 11),
    Event12_EndCode = take_anyif(EventEndCode, event_group == 12),
    Event12_StartTime = take_anyif(StartTime, event_group == 12),
    Event12_EndTime = take_anyif(EndTime, event_group == 12),
    Event12_Duration = take_anyif(Duration, event_group == 12),
    Event13_EndCode = take_anyif(EventEndCode, event_group == 13),
    Event13_StartTime = take_anyif(StartTime, event_group == 13),
    Event13_EndTime = take_anyif(EndTime, event_group == 13),
    Event13_Duration = take_anyif(Duration, event_group == 13),
    Event14_EndCode = take_anyif(EventEndCode, event_group == 14),
    Event14_StartTime = take_anyif(StartTime, event_group == 14),
    Event14_EndTime = take_anyif(EndTime, event_group == 14),
    Event14_Duration = take_anyif(Duration, event_group == 14),
    Event15_EndCode = take_anyif(EventEndCode, event_group == 15),
    Event15_StartTime = take_anyif(StartTime, event_group == 15),
    Event15_EndTime = take_anyif(EndTime, event_group == 15),
    Event15_Duration = take_anyif(Duration, event_group == 15)
    by User, EventDate
| order by User, EventDate asc

结果说明 #

• 按用户和日期分组，每行代表一个用户的单日事件汇总
• TotalDailyDuration 为当日所有有效事件的耗时总和
• 每个事件字段（如Event1_*）对应一个有效TD-结束码配对的信息，包含结束码、起止时间和单事件耗时
• 自动过滤未配对的TD/结束码及TS等无关事件
• 针对TD、TD、TC这类序列，仅保留最后一组TD到TC的有效事件对

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