FULL/LEFT JOIN关联含Lag字段表失败，求库存预测滚动计算方案

问题核心 #

你的JOIN失效原因有两个：

• Fcst表的日期（Sep/Oct 2024）和SOH表的日期（Aug 2024）无重叠，直接关联拿不到SOH数据
• SOH表没有Lag字段，无法直接和Fcst的Lag分组关联

实现步骤 #

要得到期望的结果，需要先生成每个Lag对应的全量日期集合，再分别关联Fcst和SOH数据，最后计算滚动余额：

1. 提取所有需要的日期（包含SOH的Aug 2024和Fcst的Sep/Oct 2024）
2. 用CROSS JOIN将每个Lag和所有日期组合，确保每个Lag都覆盖所有月份
3. LEFT JOIN Fcst表获取对应Lag+Plant+Item+Date的Fcst值，无数据则填0
4. LEFT JOIN SOH表获取对应Plant+Item+Date的SOH值，无数据则填0
5. 用窗口函数按Lag、Plant、Item分组，按日期排序计算滚动累计余额

完整SQL代码 #

WITH all_dates AS (
    -- 提取所有需要的日期：SOH的日期 + Fcst的日期
    SELECT DISTINCT Date FROM SOH
    UNION
    SELECT DISTINCT Date FROM FCST
),
lag_date_combinations AS (
    -- 生成每个Lag和所有日期的组合，同时带上Plant和Item（这里取唯一的Plant+Item组合）
    SELECT 
        f.Lag,
        f.Plant,
        f.Item,
        d.Date
    FROM (SELECT DISTINCT Lag, Plant, Item FROM FCST) f
    CROSS JOIN all_dates d
)
SELECT
    ldc.Lag,
    ldc.Plant,
    ldc.Item,
    ldc.Date,
    COALESCE(f.Fcst, 0) AS Fcst,
    COALESCE(s.SOH, 0) AS SOH,
    -- 计算滚动累计余额：从第一个日期开始累加(SOH - Fcst)
    SUM(COALESCE(s.SOH, 0) - COALESCE(f.Fcst, 0)) OVER (
        PARTITION BY ldc.Lag, ldc.Plant, ldc.Item
        ORDER BY TO_DATE(ldc.Date, 'Mon YYYY') ASC
    ) AS Balance
FROM lag_date_combinations ldc
LEFT JOIN FCST f
    ON ldc.Lag = f.Lag
    AND ldc.Plant = f.Plant
    AND ldc.Item = f.Item
    AND ldc.Date = f.Date
LEFT JOIN SOH s
    ON ldc.Plant = s.Plant
    AND ldc.Item = s.Item
    AND ldc.Date = s.Date
ORDER BY ldc.Lag, TO_DATE(ldc.Date, 'Mon YYYY') ASC;

代码说明 #

• all_dates CTE：收集所有需要的月份，确保Aug 2024被包含进来
• lag_date_combinations CTE：通过CROSS JOIN让每个Lag都拥有所有日期，解决SOH无Lag字段的问题
• 两次LEFT JOIN分别关联Fcst和SOH数据，用COALESCE填充空值为0
• 窗口函数中用TO_DATE将文本日期转换为可排序的日期类型，确保月份顺序正确

执行结果 #

会完全匹配你期望的输出：

内容的提问来源于stack exchange，提问作者Edlynn's Mumny