Azure Data Explorer：按用户随时间绘制Flag A启用/禁用趋势图

我有如下格式的用户数据，记录了用户随时间的Flag A启用/禁用状态，希望可视化该数据以了解使用趋势：

Timestamp           | flag_enabled | flag_disabled | user_id
2025-01-01 00:00:00 | a,b          | c             | 1
2025-01-02 00:00:00 | b            | a,c           | 1
2025-01-03 00:00:00 | a,b          | c             | 1
2025-01-01 00:00:00 | a,b          | c             | 2
2025-01-02 00:00:00 | b            | a,c           | 2

我需要生成时序图，按时间统计持续启用Flag A的用户数和最终禁用该标识的用户数，期望输出格式如下：

timestamp           |  CountFlagEnabled  | CountFlagDisabled
2025-01-01 00:00:00 |  2                 | 0
2025-01-02 00:00:00 |  0                 | 2
2025-01-03 00:00:00 |  1                 | 0

现有查询语句仅能统计全局总数量，无法实现按时间维度的分组统计：

let EnabledFlags = UserFlags
| where flag_enabled contains "a"
| summarize FirstEnabled=min(Timestamp), LastEnabled=max(Timestamp) by user_id;

let DisabledFlags = UserFlags
| where flag_disabled contains "a"
| summarize LastDisabled=max(Timestamp) by user_id;

let MergedFlags = EnabledFlags
| join kind=leftouter (DisabledFlags) on user_id
| project user_id, FirstEnabled, LastEnabled, LastDisabled;

// Count number of users that continued to keep 'Flag A' on
let UsersContinuedFlagA = MergedFlags
| where isnull(LastDisabled) or LastEnabled > LastDisabled
| summarize CountOfUsersContinued=count();

// Count number of users that disabled 'Flag A' after enabling it
let UsersDisabledFlagA = MergedFlags
| where isnotnull(LastDisabled) and LastEnabled <= LastDisabled
| summarize CountOfUsersDisabled=count(); 

要实现按时间维度的状态统计，需要先构建每个用户在各个时间点的Flag A状态，再按时间分组计算数量：

// 1. 提取所有涉及Flag A的事件，标记事件类型
let FlagAEvents = UserFlags
| extend 
    IsEnabled = flag_enabled contains "a",
    IsDisabled = flag_disabled contains "a"
| where IsEnabled or IsDisabled
| project 
    Timestamp,
    user_id,
    EventType = case(
        IsEnabled and IsDisabled, "Toggled",
        IsEnabled, "Enabled",
        IsDisabled, "Disabled",
        "Unknown"
    );

// 2. 生成所有时间戳与用户的全量组合，避免时间点缺失
let AllTimestamps = UserFlags | distinct Timestamp | sort by Timestamp asc;
let UserTimePairs = FlagAEvents
| distinct user_id
| join kind=cross (AllTimestamps) on $left.user_id != $right.Timestamp
| project user_id, Timestamp;

// 3. 合并事件与全量时间对，填充状态并计算每个用户的时间点状态
let UserStateByTime = UserTimePairs
| leftouter join kind=leftouter (FlagAEvents) on user_id, Timestamp
| sort by user_id, Timestamp asc
| fill forward EventType
// 处理用户初始状态：第一个时间点无事件则默认未启用
| partition by user_id (
    extend 
        FlagEnabled = case(isnull(EventType) and row_number() == 1, false, EventType in ("Enabled", "Toggled")),
        FlagDisabled = case(isnull(EventType) and row_number() == 1, false, EventType == "Disabled")
);

// 4. 按时间分组统计数量
UserStateByTime
| summarize 
    CountFlagEnabled = countif(FlagEnabled),
    CountFlagDisabled = countif(FlagDisabled)
by Timestamp
| sort by Timestamp asc

• 先从原始数据中筛选出所有涉及Flag A的启用/禁用事件，标记事件类型
• 生成所有存在的时间戳与用户的组合，确保每个时间点都覆盖所有用户的状态记录
• 使用fill forward填充用户状态，处理未发生事件的时间点，保证状态连续性
• 最后按时间分组统计启用和禁用Flag A的用户数量，输出结果可直接用于生成时序图

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