Snowflake/dbt环境下如何使用SQL计算拆分工时

Snowflake内置的窗口函数、CTE语法已经完全支持该逻辑，直接写dbt模型即可，性能比自定义存储过程更可控。

第一步：先完成异常打卡数据修正 #

先做预处理层，关联员工班次表修正异常下班时间：

with cleaned_attendance as (
    select 
        a.emp_id,
        a.task_id,
        a.time_in,
        -- 修正规则：下班时间为空/超过当日班次结束时间时，替换为班次结束时间
        case 
            when a.time_out is null or a.time_out > s.shift_end_time 
            then s.shift_end_time 
            else a.time_out 
        end as time_out
    from raw_attendance a
    -- 关联你的员工班次表，按员工+日期匹配
    join employee_shift s 
        on a.emp_id = s.emp_id
        and date(a.time_in) = s.shift_date
    -- 过滤掉打卡开始时间晚于班次结束的无效记录
    where a.time_in < s.shift_end_time
),

第二步：拆分时间点计算各区间并行任务数 #

这是核心逻辑，把所有打卡的开始/结束时间拆为独立时间节点，计算每个时间区间内的并行任务数量：

-- 提取所有时间边界点
time_points as (
    select emp_id, time_in as event_time, 1 as point_type from cleaned_attendance
    union all
    select emp_id, time_out as event_time, -1 as point_type from cleaned_attendance
),
-- 计算每个时间点的累计并行任务数
running_parallel as (
    select 
        emp_id,
        event_time,
        sum(point_type) over (partition by emp_id order by event_time) as parallel_count
    from time_points
),
-- 生成每个员工的连续时间区间，以及对应区间的并行数
time_intervals as (
    select 
        emp_id,
        event_time as interval_start,
        lead(event_time) over (partition by emp_id order by event_time) as interval_end,
        lag(parallel_count) over (partition by emp_id order by event_time) as parallel_tasks
    from running_parallel
    -- 过滤掉无效区间（结束时间为空或者和开始时间相同）
    qualify interval_start < interval_end and parallel_tasks > 0
),

第三步：关联原始打卡记录计算拆分后工时 #

把时间区间和原始打卡记录关联，计算每个任务在各区间的分摊时长，最后汇总即可：

task_interval_allocation as (
    select 
        c.emp_id,
        c.task_id,
        -- 计算当前任务在该区间内的有效时长，除以并行数得到分摊工时
        datediff('second', 
            greatest(c.time_in, t.interval_start), 
            least(c.time_out, t.interval_end)
        ) / 3600 / t.parallel_tasks as allocated_hours
    from cleaned_attendance c
    join time_intervals t 
        on c.emp_id = t.emp_id
        -- 任务时间和区间有重叠才关联
        and c.time_in < t.interval_end
        and c.time_out > t.interval_start
)
-- 最终汇总每个员工每个任务的总拆分工时
select 
    emp_id,
    task_id,
    round(sum(allocated_hours), 2) as total_hrs
from task_interval_allocation
group by emp_id, task_id

适配补充规则说明 #

• 可变并行数量：逻辑中的parallel_tasks为动态计算值，不管并行2个还是多个都可自动适配
• 同一员工单日多次打卡同一任务：原始打卡记录会分别关联区间计算，结果自动汇总
• 多名员工同时打卡同一任务：逻辑默认按员工维度拆分，最后按task_id二次汇总即可得到多员工总工时

dbt适配建议 #

你可以把上面的逻辑拆分为2个dbt模型：

• 中间层模型：int_attendance_cleaned 负责处理异常打卡时间修正
• 最终事实层模型：fct_task_allocated_hours 负责计算拆分后的工时，直接对接报表使用

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