SQL查询返回空结果：业务请求IT时长统计需求排查

需求说明 #

• 统计业务单元开发请求的平均完成时长，IT时长从MANAGER_APPROVAL阶段结束后开始计算
• 需排除ANALYSIS_APPROVAL阶段的耗时
• 报表按请求关闭月份分组，展示两项指标：「月度平均天数」「年初至该月的年度累计平均天数」

原SQL错误分析 #

原SQL逻辑完全不符合需求，导致空结果：

1. 仅筛选MANAGER_APPROVAL阶段数据，用LEAD只能拿到该阶段的下一个阶段开始时间，无法计算从审批结束到请求完成的完整时长
2. 用MANAGER_APPROVAL的开始时间作为分组的月份，而非请求实际关闭的月份（应取COMPLETED阶段的完成时间）
3. 年度累计平均的计算逻辑错误，直接将月度平均乘以12不符合“年初到当前月累计平均”的要求

修正后的SQL #

WITH stage_durations AS (
    -- 转换时间格式，计算每个阶段的耗时，标记是否为需排除的阶段
    SELECT
        request_id,
        stage,
        TO_TIMESTAMP(start, 'MM/DD/YY HH24:MI') AS stage_start,
        TO_TIMESTAMP(finish, 'MM/DD/YY HH24:MI') AS stage_finish,
        -- 计算当前阶段的耗时（秒）
        EXTRACT(EPOCH FROM (TO_TIMESTAMP(finish, 'MM/DD/YY HH24:MI') - TO_TIMESTAMP(start, 'MM/DD/YY HH24:MI'))) AS duration_sec,
        -- 标记是否为需要排除的阶段
        CASE WHEN stage = 'ANALYSIS_APPROVAL' THEN 1 ELSE 0 END AS is_excluded
    FROM mytable
    WHERE finish IS NOT NULL -- 排除未完成的阶段
),
request_total_duration AS (
    -- 按请求分组，计算MANAGER_APPROVAL结束后的总有效时长，以及请求关闭月份
    SELECT
        sd.request_id,
        -- 总有效时长：从MANAGER_APPROVAL结束到COMPLETED的总时长，减去所有ANALYSIS_APPROVAL的时长
        (EXTRACT(EPOCH FROM (MAX(CASE WHEN stage = 'COMPLETED' THEN stage_finish END) - MAX(CASE WHEN stage = 'MANAGER_APPROVAL' THEN stage_finish END))) 
         - SUM(CASE WHEN is_excluded = 1 THEN duration_sec ELSE 0 END)) / 86400 AS total_effective_days,
        -- 取COMPLETED阶段的月份作为关闭月份
        DATE_TRUNC('month', MAX(CASE WHEN stage = 'COMPLETED' THEN stage_finish END)) AS close_month
    FROM stage_durations sd
    GROUP BY sd.request_id
    HAVING MAX(CASE WHEN stage = 'COMPLETED' THEN stage_finish END) IS NOT NULL -- 仅统计已完成的请求
),
monthly_avg AS (
    -- 计算每个月的平均时长
    SELECT
        TO_CHAR(close_month, 'Mon YYYY') AS "Month",
        ROUND(AVG(total_effective_days)::NUMERIC, 2) AS "Average IT Duration (Monthly)",
        close_month AS sort_month -- 用于排序和窗口函数计算
    FROM request_total_duration
    GROUP BY close_month
)
-- 计算年度累计平均
SELECT
    "Month",
    "Average IT Duration (Monthly)",
    ROUND(AVG("Average IT Duration (Monthly)") OVER (PARTITION BY EXTRACT(YEAR FROM sort_month) ORDER BY sort_month)::NUMERIC, 2) AS "Average IT Duration (Yearly Cumulative)"
FROM monthly_avg
ORDER BY sort_month;

代码说明 #

1. stage_durations：统一转换时间格式，计算每个阶段的耗时，并标记需要排除的ANALYSIS_APPROVAL阶段
2. request_total_duration：按请求聚合，计算从MANAGER_APPROVAL结束到COMPLETED的总时长，减去所有ANALYSIS_APPROVAL阶段的耗时，得到有效时长；同时提取请求关闭月份
3. monthly_avg：按关闭月份分组，计算月度平均有效时长
4. 最后用窗口函数AVG() OVER()计算年初到当前月的累计平均时长，实现年度累计统计

样本数据预期结果 #

对于样本中的请求5052047：

• 总有效时长≈35.34天
• 关闭月份为Feb 2019
• 报表输出：

内容的提问来源于stack exchange，提问作者Bahadır Çolak