优化含多层嵌套CASE的项目阶段预测SQL查询方案求助

核心规则 #

• 平均值优先级：优先使用项目所在城市的已完成项目平均值，城市无数据时 fallback 到所属Division的平均值；
• 预测逻辑：若前序阶段已完成，直接用前序实际完成日期加当前阶段平均值；若前序未完成，先预测前序阶段的完成时间，再以此为基础推导当前阶段的预测值。

基础数据示例 #

预期结果示例 #

当前问题 #

业务中存在9个项目阶段，现有CASE嵌套写法会导致代码指数级膨胀，维护难度极高，尝试函数和存储过程优化未成功，需简化逻辑。

解决方案 #

方案1：COALESCE简化平均值选择+逐步计算基准日期 #

通过COALESCE直接替代多层CASE选择有效平均值，然后逐步计算每个阶段的基准日期（实际完成日期或前序预测日期），避免嵌套。

WITH ProjectBaseData AS (
    SELECT 
        Id, Div, City,
        Stage1, Stage2, Stage3, Stage4,
        Stage2AvgDiv, Stage2AvgCity,
        Stage3AvgDiv, Stage3AvgCity,
        -- 计算每个阶段的有效平均值（优先城市，其次Division）
        COALESCE(Stage2AvgCity, Stage2AvgDiv) AS Stage2Avg,
        COALESCE(Stage3AvgCity, Stage3AvgDiv) AS Stage3Avg
        -- 依次添加Stage4到Stage9的Avg计算
    FROM YourTable
),
StageProjections AS (
    SELECT 
        *,
        -- Stage2预测：实际存在则用实际值，否则用Stage1加Stage2Avg
        COALESCE(Stage2, DATEADD(day, Stage2Avg, Stage1)) AS ProjectedStage2,
        -- Stage3预测：实际存在则用实际值，否则用Stage2的基准值（实际/预测）加Stage3Avg
        COALESCE(Stage3, DATEADD(day, Stage3Avg, COALESCE(Stage2, DATEADD(day, Stage2Avg, Stage1)))) AS ProjectedStage3
        -- Stage4到Stage9以此类推，每个阶段仅依赖前一个阶段的基准值
    FROM ProjectBaseData
)
SELECT 
    Id, Div, City,
    Stage1, Stage2, Stage3, Stage4,
    Stage2AvgDiv, Stage2AvgCity, ProjectedStage2,
    Stage3AvgDiv, Stage3AvgCity, ProjectedStage3
    -- 其他阶段字段
FROM StageProjections
ORDER BY Id;

方案2：递归CTE（适合大量阶段场景） #

将阶段数据转为行格式，通过递归CTE依次计算每个阶段的预测值，扩展阶段时仅需在元数据部分添加UNION ALL，无需修改递归逻辑。

WITH StageMetadata AS (
    -- 转换为行式的阶段元数据，包含实际日期和有效平均值
    SELECT Id, 1 AS StageNum, Stage1 AS ActualDate, NULL AS AvgDays FROM YourTable
    UNION ALL SELECT Id, 2, Stage2, COALESCE(Stage2AvgCity, Stage2AvgDiv) FROM YourTable
    UNION ALL SELECT Id, 3, Stage3, COALESCE(Stage3AvgCity, Stage3AvgDiv) FROM YourTable
    UNION ALL SELECT Id, 4, Stage4, COALESCE(Stage4AvgCity, Stage4AvgDiv) FROM YourTable
    -- 继续添加到Stage9
),
RecursivePredictions AS (
    -- 初始递归：计算Stage2的预测值
    SELECT 
        Id,
        StageNum,
        ActualDate,
        AvgDays,
        COALESCE(ActualDate, DATEADD(day, AvgDays, (SELECT ActualDate FROM StageMetadata sm WHERE sm.Id = rm.Id AND sm.StageNum = 1))) AS ProjectedDate
    FROM StageMetadata rm
    WHERE StageNum = 2
    UNION ALL
    -- 递归计算后续阶段
    SELECT 
        rm.Id,
        rm.StageNum,
        rm.ActualDate,
        rm.AvgDays,
        COALESCE(rm.ActualDate, DATEADD(day, rm.AvgDays, rp.ProjectedDate)) AS ProjectedDate
    FROM StageMetadata rm
    JOIN RecursivePredictions rp ON rm.Id = rp.Id AND rm.StageNum = rp.StageNum + 1
)
-- 行转列得到最终结果
SELECT 
    yt.Id, yt.Div, yt.City,
    yt.Stage1, yt.Stage2, yt.Stage3, yt.Stage4,
    yt.Stage2AvgDiv, yt.Stage2AvgCity, rp2.ProjectedDate AS ProjectedStage2,
    yt.Stage3AvgDiv, yt.Stage3AvgCity, rp3.ProjectedDate AS ProjectedStage3
    -- 继续关联Stage4到Stage9的预测结果
FROM YourTable yt
LEFT JOIN RecursivePredictions rp2 ON yt.Id = rp2.Id AND rp2.StageNum = 2
LEFT JOIN RecursivePredictions rp3 ON yt.Id = rp3.Id AND rp3.StageNum = 3
ORDER BY yt.Id;

方案优势 #

• COALESCE直接简化平均值选择逻辑，替代多层CASE嵌套；
• 逐步计算基准日期的方式，每个阶段仅依赖前序结果，代码线性增长，维护成本低；
• 递归CTE适合阶段数量多的场景，扩展时仅需修改元数据部分，递归逻辑无需变动。

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