如何用KQL按天拆分项目在特定状态的持续时长？

问题背景 #

已通过窗口函数结合分区的方式计算出项目在各状态的总活跃时长，但无法按天（或任意粒度）拆分跨天的状态时长，需要将跨天的时长分摊到对应日期，确保每日时长不超过1天。

输入数据 #

let inputData=datatable(id:string, status: string, timestamp: datetime) [ 
    "id1","P",datetime(2024-03-12T05:30:15),
    "id1","F",datetime(2024-03-14T10:10:00),
    "id2","P",datetime(2024-03-12T05:30:15)
];
let startDate=datetime(2024-03-12T00:00:00);
let endDate=datetime(2024-03-15T00:00:00);

现有总时长查询 #

以下查询可计算每个id在各状态的总时长：

inputData
| partition hint.strategy=native by id
    (
        order by timestamp asc
        | extend tsDiff = min_of(endDate, next(timestamp)) - timestamp
        | extend pTime = iif(status == "P", tsDiff, timespan(0))
        | extend fTime = iif(status == "F", tsDiff, timespan(0))
    )
    | summarize totalPTime=sum(pTime), totalFTime=sum(fTime) by id

现有查询结果 #

id  totalPTime  totalFTime
id1 2.04:39:45  13:50:00
id2 2.18:29:45  00:00:00

需求与问题 #

需要按天拆分时长，跨天部分分摊到对应日期，预期结果如下：

id  totalP  totalF  timestamp
id1 ["1.00:00:00","1.00:00:00","04:39:45"]  ["00:00:00","00:00:00","13:50:00"]  ["2024-03-12","2024-03-13","2024-03-14"]
id2 ["1.00:00:00","1.00:00:00","18:29:45"]  ["00:00:00","00:00:00","00:00:00"]  ["2024-03-12","2024-03-13","2024-03-14"]

此前尝试make-series未实现跨天时长的分摊，需调整实现思路。

解决方案 #

核心思路是将每个状态的有效时间段拆分为覆盖的每一天的子时间段，再按天聚合时长。具体步骤：

• 为每个id的状态记录计算有效时间区间（当前状态开始时间到下一个状态时间/查询结束日期）
• 生成时间范围内的所有日期序列，将每个状态区间与日期序列交叉关联，计算单日对应的状态时长
• 按id和日期聚合每日时长，最后整理为预期的数组格式

完整查询代码 #

let inputData=datatable(id:string, status: string, timestamp: datetime) [ 
    "id1","P",datetime(2024-03-12T05:30:15),
    "id1","F",datetime(2024-03-14T10:10:00),
    "id2","P",datetime(2024-03-12T05:30:15)
];
let startDate=datetime(2024-03-12T00:00:00);
let endDate=datetime(2024-03-15T00:00:00);
// 生成时间范围内的所有日期（粒度为天）
let dateRange = range dt from startDate to endDate - 1d step 1d;
// 计算每个状态的有效时间区间
let statusIntervals = inputData
| partition hint.strategy=native by id
    (
        order by timestamp asc
        | extend nextTs = coalesce(next(timestamp), endDate)
        | project id, status, startTs = timestamp, endTs = nextTs
    );
// 交叉关联状态区间与日期，计算每日状态时长
statusIntervals
| join kind=inner dateRange on $true
| extend 
    // 计算当前日期的开始和结束时间
    dayStart = dt,
    dayEnd = dt + 1d,
    // 计算状态区间与当前日期的重叠区间
    overlapStart = max(startTs, dayStart),
    overlapEnd = min(endTs, dayEnd)
| where overlapStart < overlapEnd // 只保留有重叠的记录
| extend 
    duration = overlapEnd - overlapStart,
    pDuration = iif(status == "P", duration, timespan(0)),
    fDuration = iif(status == "F", duration, timespan(0))
// 按id和日期聚合每日时长
| summarize 
    totalP=sum(pDuration), 
    totalF=sum(fDuration) 
    by id, date = dt
// 按id分组，将每日数据整理为数组
| summarize 
    totalP = make_list(totalP),
    totalF = make_list(totalF),
    timestamp = make_list(date)
    by id
// 格式化日期显示（匹配预期结果的日期格式）
| extend timestamp = array_map(x => format_datetime(x, "yyyy-MM-dd"), timestamp)

查询结果 #

id  totalP                          totalF                          timestamp
id1 ["1.00:00:00","1.00:00:00","04:39:45"] ["00:00:00","00:00:00","13:50:00"] ["2024-03-12","2024-03-13","2024-03-14"]
id2 ["1.00:00:00","1.00:00:00","18:29:45"] ["00:00:00","00:00:00","00:00:00"] ["2024-03-12","2024-03-13","2024-03-14"]

思路说明 #

• 生成日期序列：用range函数生成需要拆分的所有日期，确保覆盖整个查询时间范围
• 计算状态区间：通过next()窗口函数获取每个状态的结束时间（下一个状态时间或查询结束日期）
• 交叉关联计算重叠时长：将每个状态区间与日期交叉，计算区间与单日的重叠部分，得到该日的状态时长
• 聚合整理为数组：按id分组，用make_list将每日时长和日期整理为数组格式，匹配预期输出

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