如何计算同Workflow ID下各步骤时间差并求平均耗时？

现有如下结构的表，需要基于updated_at字段计算每个步骤的平均耗时：

当前查询会跨Workflow计算时间差（比如workflow_id=25的最后一步和workflow_id=105的第一步），导致步骤耗时计算错误。正确的计算逻辑是：

• data_capture步骤耗时：当前行updated_at - 当前行created_at
• client_signature步骤耗时：当前行updated_at - 同Workflow的data_capture步骤的updated_at
• pm_signature步骤耗时：当前行updated_at - 同Workflow的client_signature步骤的updated_at
• 每个Workflow独立执行上述计算

以下是尝试的代码：

WITH tb1 AS (
SELECT
workflow_id, 
workflow_type,
step_name,
step_status,
step_created_at,
step_updated_at,
TIMESTAMPDIFF(SECOND, updated_time, step_updated_at) / 3600 elapsed_time
FROM (
    SELECT *,
        LAG(step_updated_at) OVER (ORDER BY step_updated_at) updated_time
    FROM view_client_workflow_status
    ) s
)
SELECT
step_name,
AVG(elapsed_time)
FROM tb1
WHERE workflow_type = 'client_onboarding_fcc_ip'
GROUP BY 1

核心是用PARTITION BY workflow_id将每个工作流的数据隔离，同时指定步骤的执行顺序，确保LAG函数只取同工作流内的上一步时间。

正确SQL代码 #

WITH workflow_steps AS (
    SELECT
        workflow_id,
        workflow_type,
        step_name,
        created_at,
        updated_at,
        -- 给每个工作流内的步骤按执行顺序编号
        CASE step_name
            WHEN 'data_capture' THEN 1
            WHEN 'client_signature' THEN 2
            WHEN 'pm_signature' THEN 3
        END AS step_order,
        -- 取同工作流内上一步的updated_at，第一步（data_capture）为NULL
        LAG(updated_at) OVER (PARTITION BY workflow_id ORDER BY step_order) AS prev_step_updated_at
    FROM view_client_workflow_status
),
step_elapsed AS (
    SELECT
        workflow_id,
        workflow_type,
        step_name,
        -- 计算每个步骤的耗时：第一步用created_at，其他用上一步的updated_at
        TIMESTAMPDIFF(SECOND, 
            COALESCE(prev_step_updated_at, created_at), 
            updated_at
        ) / 3600 AS elapsed_time_hours
    FROM workflow_steps
)
SELECT
    step_name,
    AVG(elapsed_time_hours) AS avg_elapsed_time_hours
FROM step_elapsed
WHERE workflow_type = 'client_onboarding_fcc_ip'
GROUP BY step_name
ORDER BY CASE step_name
            WHEN 'data_capture' THEN 1
            WHEN 'client_signature' THEN 2
            WHEN 'pm_signature' THEN 3
        END;

代码说明 #

1. workflow_steps CTE：
   • 用CASE给步骤指定固定执行顺序，避免因时间排序出现误差
   • LAG(updated_at) OVER (PARTITION BY workflow_id ORDER BY step_order)：仅在同一个workflow_id内，按步骤顺序取上一步的更新时间
2. step_elapsed CTE：
   • 用COALESCE处理第一步的情况：当prev_step_updated_at为NULL时，使用created_at计算耗时
   • 将秒数转换为小时数，和原代码保持一致
3. 最后按step_name分组，计算每个步骤的平均耗时，并按步骤顺序排序

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