如何创建KQL函数计算两个时间点间的工作时长？

需求说明 #

• 基于周一至周五工作制、9:00-17:00班次时间，计算两个datetime之间的工作时长，需排除公共假期
• 无内置函数可用，需自定义实现

原表类型函数及报错 #

最初编写表类型函数，测试时触发错误：Tabular expression is not expected in the current context

原函数代码 #

let workingTime = (startTime:datetime, endTime:datetime, workdayStart:datetime, workdayEnd:datetime) 
{ 
// 计算起始和结束时间之间的完整工作日数量
// 定义公共假期
let holidays = datatable(Holidays: datetime)
    [
    datetime(2022-04-15),datetime(2023-04-07),datetime(2023-05-01),datetime(2023-12-25)];
let middle_days = range Date from datetime_add('day', 1, startofday(startTime)) to datetime_add('day', -1, startofday(endTime)) step 1d;
let middle_work_days = toscalar(middle_days
    | where dayofweek(Date) / 1d between (1..5)
    | where Date !in (holidays)
    | summarize count());
let table = 
    union (print workdayStart=workdayStart, workdayEnd=workdayEnd, startTime=startTime, endTime=endTime, middle_work_days=middle_work_days);
table
| extend workday_start_dt = make_datetime(0001, 1, 1, hourofday(workdayStart), datetime_part('minute', workdayStart), 0)
| extend workday_end_dt = make_datetime(0001, 1, 1, hourofday(workdayEnd), datetime_part('minute', workdayEnd), 0)
| extend start_time_dt = make_datetime(0001, 1, 1, hourofday(startTime), datetime_part('minute', startTime), 0)
| extend end_time_dt = make_datetime(0001, 1, 1, hourofday(endTime), datetime_part('minute', endTime), 0)
| extend hoursinday = datetime_diff('hour', workday_end_dt, workday_start_dt)
| extend working_duration =  toreal(middle_work_days) * hoursinday
| extend workday_start_hour = datetime_part('hour', workdayStart)
| extend workday_start_min = datetime_part('minute', workdayStart)
| extend workday_end_hour = datetime_part('hour', workdayEnd)
| extend workday_end_min = datetime_part('minute', workdayEnd)
| extend hours_on_last_day = 
    iff(
    ((dayofweek(endTime) / 1d between (1..5)) and 
    startofday(endTime) !in (holidays) and 
    end_time_dt >= workday_start_dt),
    iff(
    end_time_dt >= workday_end_dt,
    toreal(hoursinday),
    // 结束日期已过的总时长
    toreal(datetime_diff('second', endTime, startofday(endTime))) / 60 / 60
    -
    // 午夜到上班时间的时长
    toreal(datetime_diff('second', make_datetime(datetime_part('year', endTime), datetime_part('month', endTime), datetime_part('day', endTime), workday_start_hour, workday_start_min), startofday(endTime)) / 60 / 60
    )),
    toreal(0)
    )
| extend hours_on_first_day = 
    iff(
    ((dayofweek(startTime) / 1d between (1..5)) and 
    startofday(startTime) !in (holidays) and 
    start_time_dt < workday_end_dt),
    iff(
    start_time_dt < workday_start_dt,
    toreal(hoursinday),
    toreal(datetime_diff('second', make_datetime(datetime_part('year', startTime), datetime_part('month', startTime), datetime_part('day', startTime), workday_end_hour, workday_end_min), startTime)) / 60 / 60
    ),
    toreal(0)    
    )
| project   total_working_hours = (working_duration + hours_on_first_day + hours_on_last_day)
};

测试代码及错误 #

// 示例起始和结束时间
let startTime_var = datetime_utc_to_local(datetime("2023-05-05 13:54:00"), 'Europe/London');
let endTime_var = datetime_utc_to_local(datetime("2023-06-09 08:46"), 'Europe/London');
// 本地时间的班次起止示例
let workdayStart_var = datetime("09:00");
let workdayEnd_var = datetime("17:00");
// 基于示例数据创建测试表
let sampletable = union(print startTime=startTime_var, endTime=endTime_var, workdayStart=workdayStart_var, workdayEnd=workdayEnd_var);
sampletable
| extend workingTime(startTime_var, endTime_var, workdayStart, workdayEnd_var)

错误信息：Tabular expression is not expected in the current context

修改后的标量函数及痛点 #

将函数改为标量类型后可部分运行，但middle_work_days变量依赖toscalar()，存在限制，需要找到不使用toscalar()的计算方式

修改后的标量函数代码 #

let workingTime = (startTime:datetime, endTime:datetime, workdayStart:datetime, workdayEnd:datetime)
{ 
// 计算起始和结束时间之间的完整工作日数量
// 定义公共假期
let holidays = datatable(Holidays: datetime)
    [
    datetime(2022-04-15),datetime(2023-04-07),datetime(2023-05-01),datetime(2023-12-25)];
let middle_days = range Date from datetime_add('day', 1, startofday(startTime)) to datetime_add('day', -1, startofday(endTime)) step 1d;
let middle_work_days = toscalar(middle_days
    | where dayofweek(Date) / 1d between (1..5)
    | where Date !in (holidays)
    | summarize count());
let workday_start_dt = make_datetime(0001, 1, 1, hourofday(workdayStart), datetime_part('minute', workdayStart), 0);
let workday_end_dt = make_datetime(0001, 1, 1, hourofday(workdayEnd), datetime_part('minute', workdayEnd), 0);
let start_time_dt = make_datetime(0001, 1, 1, hourofday(startTime), datetime_part('minute', startTime), 0);
let end_time_dt = make_datetime(0001, 1, 1, hourofday(endTime), datetime_part('minute', endTime), 0);
let hoursinday = datetime_diff('hour', workday_end_dt, workday_start_dt);
let working_duration = toreal(middle_work_days) * hoursinday;
let workday_start_hour = datetime_part('hour', workdayStart);
let workday_start_min = datetime_part('minute', workdayStart);
let workday_end_hour = datetime_part('hour', workdayEnd);
let workday_end_min = datetime_part('minute', workdayEnd);
let hours_on_last_day = 
    iff(
    ((dayofweek(endTime) / 1d between (1..5)) and 
    startofday(endTime) !in (holidays) and 
    end_time_dt >= workday_start_dt),
    iff(
    end_time_dt >= workday_end_dt,
    toreal(hoursinday),
    // 结束日期已过的总时长
    toreal(datetime_diff('second', endTime, startofday(endTime))) / 60 / 60
    -
    // 午夜到上班时间的时长
    toreal(datetime_diff('second', make_datetime(datetime_part('year', endTime), datetime_part('month', endTime), datetime_part('day', endTime), workday_start_hour, workday_start_min), startofday(endTime)) / 60 / 60
    )),
    toreal(0)
    );
let hours_on_first_day = 
    iff(
    ((dayofweek(startTime) / 1d between (1..5)) and 
    startofday(startTime) !in (holidays) and 
    start_time_dt < workday_end_dt),
    iff(
    start_time_dt < workday_start_dt,
    toreal(hoursinday),
    toreal(datetime_diff('second', make_datetime(datetime_part('year', startTime), datetime_part('month', startTime), datetime_part('day', startTime), workday_end_hour, workday_end_min), startTime)) / 60 / 60
    ),
    toreal(0)    
    )
;
working_duration + hours_on_first_day + hours_on_last_day
};

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