基于dbt与Power BI建模可追回工时及加班工时的技术咨询

业务逻辑回顾 #

• 员工每周最低计发40工时
• 无往期欠付工时（owed hours）时，单周超40小时部分按加班工时（OT）计发
• 单周工作不足40小时，差额计入欠付工时
• 单周最多可追回12小时欠付工时
• 欠付工时4周内未追回则失效，不再纳入计算

核心目标 #

生成当前周可追回工时报表，展示可追回时长、失效时长、剩余欠付工时，同时准确计算加班工时。

一、dbt建模方案 #

1. 模型分层设计 #

采用dbt经典三层架构，确保逻辑清晰、可维护：

• 源数据层（staging）：清洗原始工时数据，校验employee_id、week_id、hours_worked_this_week有效性，将week_id转换为周起始/结束日期（方便后续周差计算）。
• 中间层（intermediate）：通过递归CTE追踪每笔欠付工时的生命周期（产生、追回、失效），处理跨周结转逻辑。
• 事实层（marts）：生成最终报表模型，包含每周OT、欠付、可追回、失效工时等核心指标。

2. 关键逻辑实现（递归CTE） #

针对跨周追踪、动态计算的核心需求，按员工分组逐周递归计算：

WITH employee_weekly_base AS (
    SELECT
        employee_id,
        week_id,
        hours_worked_this_week,
        GREATEST(hours_worked_this_week - 40, 0) AS weekly_surplus, -- 本周工时盈余（超40小时部分）
        GREATEST(40 - hours_worked_this_week, 0) AS weekly_deficit -- 本周工时缺口（不足40小时部分）
    FROM {{ ref('stg_employee_hours') }}
),

-- 递归追踪欠付工时全生命周期
owed_tracking_recursive AS (
    -- 初始周：处理每个员工第一周数据
    SELECT
        employee_id,
        week_id,
        hours_worked_this_week,
        weekly_surplus,
        weekly_deficit,
        weekly_deficit AS current_owed, -- 初始欠付为本周缺口
        CASE WHEN weekly_deficit = 0 THEN weekly_surplus ELSE 0 END AS ot_paid, -- 无欠付时盈余全算OT
        -- 存储每笔欠付明细（产生周、时长、失效周）
        ARRAY[STRUCT(owed_week := week_id, hours := weekly_deficit, expired_week := week_id + 4)] AS owed_details
    FROM employee_weekly_base emp
    WHERE week_id = (SELECT MIN(week_id) FROM employee_weekly_base WHERE employee_id = emp.employee_id)

    UNION ALL

    -- 递归处理后续周
    SELECT
        curr.employee_id,
        curr.week_id,
        curr.hours_worked_this_week,
        curr.weekly_surplus,
        curr.weekly_deficit,
        -- 计算当前剩余欠付：先扣失效欠付，再扣本周追回，加本周新缺口
        GREATEST(
            -- 扣除本周失效的欠付
            (prev.current_owed - COALESCE((SELECT SUM(hours) FROM UNNEST(prev.owed_details) d WHERE d.expired_week <= curr.week_id), 0))
            -- 扣除本周可追回工时（最多12小时，不超过剩余欠付和本周盈余）
            - LEAST(12, curr.weekly_surplus, (prev.current_owed - COALESCE((SELECT SUM(hours) FROM UNNEST(prev.owed_details) d WHERE d.expired_week <= curr.week_id), 0)))
            -- 加上本周新工时缺口
            + curr.weekly_deficit,
            0
        ) AS current_owed,
        -- 计算本周OT：盈余减去追回部分后的剩余
        GREATEST(
            curr.weekly_surplus
            - LEAST(12, curr.weekly_surplus, (prev.current_owed - COALESCE((SELECT SUM(hours) FROM UNNEST(prev.owed_details) d WHERE d.expired_week <= curr.week_id), 0))),
            0
        ) AS ot_paid,
        -- 更新欠付明细：移除失效记录，添加本周新缺口，剩余欠付按产生顺序保留
        ARRAY(
            -- 保留未失效的欠付记录
            SELECT STRUCT(owed_week := d.owed_week, hours := d.hours, expired_week := d.expired_week)
            FROM UNNEST(prev.owed_details) d
            WHERE d.expired_week > curr.week_id
            -- 添加本周新产生的欠付
            UNION ALL
            SELECT STRUCT(owed_week := curr.week_id, hours := curr.weekly_deficit, expired_week := curr.week_id + 4)
            WHERE curr.weekly_deficit > 0
        ) AS owed_details
    FROM employee_weekly_base curr
    JOIN owed_tracking_recursive prev
        ON curr.employee_id = prev.employee_id
        AND curr.week_id = prev.week_id + 1
),

-- 最终报表模型
final_employee_hours_report AS (
    SELECT
        employee_id,
        week_id,
        hours_worked_this_week,
        ot_paid,
        current_owed AS owed_hours,
        -- 本周失效工时
        COALESCE((SELECT SUM(hours) FROM UNNEST(owed_details) d WHERE d.expired_week = week_id), 0) AS expired_hours,
        -- 本周实际追回工时
        LEAST(12, weekly_surplus, (current_owed + ot_paid - weekly_surplus)) AS recovered_hours
    FROM owed_tracking_recursive
)

SELECT * FROM final_employee_hours_report
ORDER BY employee_id, week_id

3. dbt最佳实践 #

• 增量模型：数据量大时配置增量模型，仅处理新增周数据，提升运行效率。
• 测试规则：添加dbt测试确保数据准确性，例如：
  • owed_hours不能为负
  • recovered_hours不超过12小时
  • ot_paid不能为负
• 文档注释：在模型中添加{{ doc() }}注释，说明业务逻辑和指标定义，方便团队协作。

二、Power BI可视化适配方案 #

1. 数据接入 #

直接连接dbt生成的final_employee_hours_report模型（支持Snowflake、BigQuery、PostgreSQL等主流数据源），或通过dbt导出为视图/表供Power BI读取。

2. 语义模型构建 #

• 日期维度表：创建包含week_id、周起始日期、周结束日期的日期表，用于时间范围筛选和关联。
• 核心度量值：

  -- 当前周总可追回工时
  当前周可追回工时 = CALCULATE(SUM(final_employee_hours_report[recovered_hours]), DATEADD('日期表'[周起始日期], 0, WEEK))
  
  -- 当前周总失效工时
  当前周失效工时 = CALCULATE(SUM(final_employee_hours_report[expired_hours]), DATEADD('日期表'[周起始日期], 0, WEEK))
  
  -- 当前周剩余欠付工时
  当前周剩余欠付工时 = CALCULATE(SUM(final_employee_hours_report[owed_hours]), DATEADD('日期表'[周起始日期], 0, WEEK))
  
  -- 累计欠付工时趋势
  累计欠付工时 = CALCULATE(SUM(final_employee_hours_report[owed_hours]), FILTER(ALL('日期表'), '日期表'[week_id] <= MAX('日期表'[week_id])))
  

3. 可视化设计 #

• 员工明细报表：用矩阵展示每个员工每周的hours_worked_this_week、ot_paid、owed_hours、recovered_hours、expired_hours，支持按员工、周筛选。
• 汇总仪表盘：
  • 卡片组件展示当前周总可追回、总失效、总欠付工时
  • 折线图展示累计欠付工时趋势变化
  • 柱状图对比不同员工每周追回工时
• 交互筛选：添加employee_id、week_id筛选器，支持快速定位特定员工或时间段数据。

4. 可扩展性优化 #

• 增量刷新：配置Power BI增量刷新，仅加载最近N周数据，提升报表加载速度。
• 语义模型复用：核心度量值统一存储在语义模型中，后续新增业务逻辑只需调整度量值，无需修改底层数据模型。

核心问题解决说明 #

1. 跨周追踪欠付工时及失效状态：dbt通过递归CTE记录每笔欠付的产生周和失效周，Power BI通过日期表关联，可直观查看欠付全生命周期。
2. 动态计算可追回与失效工时：dbt递归步骤优先扣除失效欠付，再计算本周可追回工时（上限12小时）；Power BI用度量值动态计算当前周实时指标。
3. 准确计算加班工时：dbt中先从本周盈余扣除追回欠付的部分，剩余盈余才计入OT，确保有欠付时优先追回再计发加班工资。

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