使用CONNECT BY PRIOR计算未完成预测周期的人员起止数量

你的初始尝试主要存在两个核心问题：

• 递归方向搞反了：你是从当前月份往过去的月份递归，但我们需要从有已知起始/结束值的最早月份开始，向后续月份递推，这样才能正确传递上一期的结束人数作为当前期的起始人数。
• MERGE的赋值逻辑混淆：你试图用查询结果的结束值更新当前记录的起始值，但递归查询本身并没有正确计算出递推后的数值。

正确的解决方案 #

我们可以通过CONNECT BY递归生成每个周期的正确起始和结束人数，再用MERGE同步更新原表。以下是完整的可执行SQL：

MERGE INTO HEAD_COUNT h
USING (
    SELECT 
        period_start,
        -- 递归计算起始人数：上一期的结束人数，起点直接用已知值
        CASE 
            WHEN LEVEL = 1 THEN head_count_start 
            ELSE PRIOR head_count_end 
        END AS calc_head_count_start,
        -- 计算结束人数：起始人数 + 到岗数 - 离岗数，起点直接用已知值
        CASE 
            WHEN LEVEL = 1 THEN head_count_end 
            ELSE PRIOR head_count_end + arrival - exits 
        END AS calc_head_count_end
    FROM HEAD_COUNT
    -- 从最早的有有效初始值的记录开始递归（这里是2017年9月的记录）
    START WITH period_start = DATE '2017-09-01'
    -- 按月份递增方向递归：确保下一条记录是上一条的下一个月
    CONNECT BY ADD_MONTHS(PRIOR period_start, 1) = period_start
    -- 强制递归顺序，避免歧义
    ORDER SIBLINGS BY period_start
) src
ON (h.period_start = src.period_start)
WHEN MATCHED THEN UPDATE
    SET h.head_count_start = src.calc_head_count_start,
        h.head_count_end = src.calc_head_count_end;

关键逻辑拆解 #

1. 递归起点选择：我们以2017-09-01作为递归起点，因为这条记录的HEAD_COUNT_START和HEAD_COUNT_END是已知的非零值，是整个计算链的基准。
2. 递归方向控制：ADD_MONTHS(PRIOR period_start, 1) = period_start确保递归从旧月份向新月份推进，每一步都能继承上一期的结束人数。
3. 数值递推计算：
   • 起点记录直接使用已知的起始/结束值；
   • 后续记录的起始人数等于上一期的结束人数，结束人数通过起始人数 + 到岗数 - 离岗数公式计算。
4. MERGE同步更新：将递归计算出的正确数值覆盖原表中对应的字段。

验证结果 #

执行完上述SQL后，你可以通过以下查询验证计算结果：

SELECT period_start, head_count_start, head_count_end, arrival, exits
FROM HEAD_COUNT
ORDER BY period_start DESC;

比如2017年10月的起始人数会等于2017年9月的结束人数276，结束人数为276 + 4 - 30 = 250，和你插入的已知值完全匹配；2017年11月的起始人数为250，结束人数为250 + 0 - 64 = 186，后续所有月份的数值都会按规则正确递推。

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