SQL如何实现新增列统计项目各阶段已完成任务占总任务百分比

实现思路 #

你现有代码的核心问题是WHERE子句中包含T1."FINISH" = 'Y'过滤条件，会直接排除所有未完成的任务行，无法统计阶段总任务数。你需要把完成状态的过滤逻辑从WHERE移到聚合函数的条件判断中，用条件聚合同时统计完成和总任务的指标。

修改后SQL代码 #

SELECT 
    T2."Name", 
    MIN(T1."START") AS "Stage Start Date",
    T1."CLOSE" AS "Predicted Closing",
    -- 仅统计已完成任务的最晚结束日期，对齐原有逻辑
    MAX(CASE WHEN T1."FINISH" = 'Y' THEN T1."FINISHDATE" END) AS "Actual Finish Date",
    -- 仅累加已完成任务的占比，对齐原有逻辑
    SUM(CASE WHEN T1."FINISH" = 'Y' THEN T1."PERCENT" ELSE 0 END) AS "Total Completed Percent",
    -- 逾期计算仅用已完成任务的最晚结束日期，对齐原有逻辑
    DAYS_BETWEEN(T1."CLOSE", MAX(CASE WHEN T1."FINISH" = 'Y' THEN T1."FINISHDATE" END)) AS "Overdue",
    -- 新增：阶段总任务数
    COUNT(*) AS "Stage Total Tasks",
    -- 新增：阶段已完成任务数
    COUNT(CASE WHEN T1."FINISH" = 'Y' THEN 1 END) AS "Stage Completed Tasks",
    -- 新增：阶段任务完成率，处理除数为0的异常情况
    ROUND(
        COUNT(CASE WHEN T1."FINISH" = 'Y' THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0),
        2
    ) AS "Stage Completion Rate (%)"
FROM OPMG T0 
INNER JOIN PMG1 T1 ON T0."AbsEntry" = T1."AbsEntry"
INNER JOIN PMC2 T2 ON T1."StageID" = T2."StageID"
WHERE 
    T0."DocNum" = '[%DocNum]'
    AND T2."Name" NOT LIKE 'Client:%'
GROUP BY T2."Name", T1."CLOSE", T1."StageID"
ORDER BY T1."StageID"

关键逻辑说明 #

• 移除了WHERE中的T1."FINISH" = 'Y'过滤条件，保留所有任务行用于统计总任务数
• 原有和已完成任务相关的聚合逻辑，全部加上CASE WHEN T1."FINISH" = 'Y'条件判断，和原有计算逻辑保持一致
• 新增的阶段完成率默认保留2位小数，你可以根据需要调整ROUND函数的第二个参数修改精度
• 用NULLIF(COUNT(*), 0)避免阶段无任务时出现除以0的报错

内容的提问来源于stack exchange，提问作者André Schwarze