Oracle PL/SQL中基于父记录值计算子记录start_date

针对同一item下，start_date为空的行需要基于上一层级的有效start_date和当前行的numofdays递推计算的需求，以下提供两种可行方案：

方案一：使用MODEL子句（推荐，逻辑直观） #

MODEL子句适合处理这种递推计算场景，直接按层级顺序，用前一行的start_date（无论原始还是计算得到的）加上当前行的numofdays填充空值：

WITH base_data AS (
    -- 替换为你的实际表或查询
    select 1 LineNumber, 123 item, 1 lvl, 2 numofdays, sysdate start_date from dual
    union all
    select 2 , 123 , 2, 2, sysdate + 10 from dual
    union all
    select 3 , 123 , 3, 1, null from dual
    union all
    select 4 , 123 , 4, 3, null from dual
    union all
    select 5 , 123 , 5, 2, null from dual
    union all
    select 6 , 345 , 1, 1, sysdate+2 from dual
    union all
    select 7 , 345 , 2, 2, null from dual
    union all
    select 8 , 345 , 3, 1, null from dual
)
SELECT LineNumber, item, lvl, numofdays, start_date
FROM base_data
MODEL
    PARTITION BY (item)  -- 按item分组处理
    ORDER BY lvl         -- 按层级顺序递推
    DIMENSION BY (lvl)
    MEASURES (LineNumber, numofdays, start_date)
    RULES (
        -- 空值行用上一层级的start_date加当前numofdays填充
        start_date[lvl] = CASE 
            WHEN start_date[lvl] IS NOT NULL THEN start_date[lvl]
            ELSE start_date[CV(lvl)-1] + numofdays[CV(lvl)]
        END
    )
ORDER BY item, lvl;

逻辑说明： #

• PARTITION BY (item)：确保仅在同一个item范围内计算
• ORDER BY lvl：按层级顺序处理，保证递推的正确性
• RULES逻辑：非空start_date保留原值，空值行取上一层级的start_date加上当前行的numofdays

方案二：使用窗口函数组合 #

如果数据库版本不支持MODEL子句，可通过窗口函数标记基准行，结合累计和计算：

WITH base_data AS (
    -- 替换为你的实际表或查询
    select 1 LineNumber, 123 item, 1 lvl, 2 numofdays, sysdate start_date from dual
    union all
    select 2 , 123 , 2, 2, sysdate + 10 from dual
    union all
    select 3 , 123 , 3, 1, null from dual
    union all
    select 4 , 123 , 4, 3, null from dual
    union all
    select 5 , 123 , 5, 2, null from dual
    union all
    select 6 , 345 , 1, 1, sysdate+2 from dual
    union all
    select 7 , 345 , 2, 2, null from dual
    union all
    select 8 , 345 , 3, 1, null from dual
),
grouped_data AS (
    SELECT 
        *,
        -- 标记当前行最近的非空start_date所在层级
        MAX(CASE WHEN start_date IS NOT NULL THEN lvl END) OVER (PARTITION BY item ORDER BY lvl ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS base_lvl
    FROM base_data
)
SELECT 
    LineNumber,
    item,
    lvl,
    numofdays,
    CASE 
        WHEN start_date IS NOT NULL THEN start_date
        ELSE (
            -- 获取基准行的start_date
            SELECT start_date FROM grouped_data g2 WHERE g2.item = g1.item AND g2.lvl = g1.base_lvl
        ) + (
            -- 计算从基准行下一行到当前行的numofdays累计和
            SUM(numofdays) OVER (PARTITION BY item, base_lvl ORDER BY lvl) - 
            (SELECT numofdays FROM grouped_data g2 WHERE g2.item = g1.item AND g2.lvl = g1.base_lvl)
        )
    END AS start_date
FROM grouped_data g1
ORDER BY item, lvl;

逻辑说明： #

1. grouped_data中用窗口函数标记每个行对应的最近非空start_date所在层级（base_lvl）
2. 空值行的start_date = 基准行的start_date + （基准行到当前行的numofdays总和 - 基准行自身的numofdays）

两种方案均可满足需求，MODEL子句的代码更简洁高效，建议优先使用。

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