多任务并行时零件工序空闲天数计算方案咨询（SQL/Alteryx/Tableau）

问题背景 #

我负责一个跟踪零件工序流转的项目，核心目标是计算零件未处于工序中的空闲天数。单任务场景下逻辑清晰：

• 工序总时长 = 工序结束日期 - 工序开始日期
• 任务总时长 = 单个任务结束日期 - 单个任务开始日期（多任务则求和）
• 空闲天数 = 工序总时长 - 所有任务时长之和

但多任务并行时，直接求和任务时长会导致重复统计重叠时间，上述公式失效。项目基于Tableau+SQL构建，需通过SQL/Alteryx/Tableau实现可行方案，目前正梳理思路并对接IT部门。

SQL 实现方案 #

核心是计算任务时间的实际覆盖总时长（去重重叠部分），再用工序总时长减去这个值得到空闲天数。

实现代码 #

WITH ranked_tasks AS (
    SELECT 
        part_id,
        process_id,
        task_start,
        task_end,
        -- 标记当前任务是否与上一个任务重叠
        CASE WHEN task_start <= LAG(task_end) OVER (PARTITION BY part_id, process_id ORDER BY task_start)
             THEN 0 ELSE 1 END AS is_new_interval
    FROM task_table
),
interval_groups AS (
    SELECT 
        part_id,
        process_id,
        task_start,
        task_end,
        -- 生成重叠任务的分组ID
        SUM(is_new_interval) OVER (PARTITION BY part_id, process_id ORDER BY task_start) AS interval_group
    FROM ranked_tasks
),
merged_intervals AS (
    SELECT 
        part_id,
        process_id,
        MIN(task_start) AS interval_start,
        MAX(task_end) AS interval_end
    FROM interval_groups
    GROUP BY part_id, process_id, interval_group
)
SELECT 
    p.part_id,
    p.process_id,
    (p.process_end - p.process_start) AS process_total_days,
    SUM(m.interval_end - m.interval_start) AS actual_task_days,
    (p.process_end - p.process_start) - SUM(m.interval_end - m.interval_start) AS idle_days
FROM process_table p
JOIN merged_intervals m ON p.part_id = m.part_id AND p.process_id = m.process_id
GROUP BY p.part_id, p.process_id, p.process_start, p.process_end;

逻辑说明 #

1. 用窗口函数标记与上一任务重叠的记录，区分新的时间区间
2. 对任务按分组ID合并，得到无重叠的连续任务区间
3. 计算合并后区间的总时长，避免重复统计并行任务的重叠时间

Alteryx 实现方案 #

利用Alteryx的可视化工具链处理时间区间合并：

• 导入工序表与任务表，通过part_id和process_id关联数据
• 按part_id、process_id、task_start对任务数据排序
• 使用「Multi-Row Formula」工具标记重叠任务的分组ID，再通过「Summarize」工具按分组合并，得到无重叠的interval_start和interval_end
• 用「Formula」工具计算：
  • 工序总时长 = process_end - process_start
  • 实际任务时长 = 合并后区间的时长之和
  • 空闲天数 = 工序总时长 - 实际任务时长
• 将结果输出到Tableau或数据库，供后续可视化使用

Tableau 实现方案 #

适合中小数据集，通过计算字段与表计算处理：

1. 按part_id、process_id、task_start对任务数据排序
2. 创建计算字段是否新区间：

IF [task_start] <= LOOKUP([task_end], -1) THEN 0 ELSE 1 END

3. 创建计算字段区间分组ID：

RUNNING_SUM([是否新区间])

4. 用LOD表达式计算合并后单组任务时长：

{INCLUDE [part_id], [process_id], [区间分组ID]: MAX([task_end]) - MIN([task_start])}

5. 最终计算空闲天数：

([process_end] - [process_start]) - SUM([合并后单组任务时长])

落地建议 #

1. 先确认数据粒度：确保零件、工序、任务的关联关系清晰，每个任务都有明确的part_id、process_id、task_start、task_end字段
2. 优先在ETL层（SQL/Alteryx）处理数据：大数据量下前端计算会影响性能，对接IT部门时优先确认递归CTE权限或Alteryx使用权限
3. 样本验证：用包含多并行任务的测试数据验证计算结果，确保重叠时间未被重复统计

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