在Snowflake中基于每日库存与Forecast计算Days of Supply（DOS）

问题核心 #

你当前的SQL通过计算所有后续需求的总和推导DOS，不符合以当日库存为基准，累加当日及之后的Forecast直到即将耗尽库存时统计天数的规则，需要调整为逐天累计判断的逻辑。

解决方案 #

根据你提到的表结构（表A存库存数据，表B存需求数据），先关联两表，再用窗口函数计算累计需求，最终统计有效供应天数。修正后的SQL如下：

-- 关联A、B表，统一获取所需字段
WITH combined_data AS (
    SELECT 
        a.LOCATION,
        a.MATERIAL,
        a.START_DATE,
        a.PROJECTED_ON_HAND AS PROJECTED_INVENTORY,
        b.TOTAL_DEMAND AS FORECAST
    FROM "A" a
    JOIN "B" b 
        ON a.LOCATION = b.LOCATION 
        AND a.MATERIAL = b.MATERIAL 
        AND a.START_DATE = b.START_DATE
),
-- 计算从当日开始的累计需求和天数偏移
daily_cumulative AS (
    SELECT 
        LOCATION,
        MATERIAL,
        START_DATE,
        PROJECTED_INVENTORY,
        FORECAST,
        -- 累计从当日到后续所有日期的需求
        SUM(FORECAST) OVER (
            PARTITION BY LOCATION, MATERIAL 
            ORDER BY START_DATE 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS CUMULATIVE_FORECAST,
        -- 标记从当前日期起的天数（当日为1，次日为2...）
        ROW_NUMBER() OVER (
            PARTITION BY LOCATION, MATERIAL 
            ORDER BY START_DATE
        ) AS DAY_OFFSET
    FROM combined_data
)
-- 计算最终DOS
SELECT 
    dc.LOCATION,
    dc.MATERIAL,
    dc.START_DATE,
    dc.PROJECTED_INVENTORY,
    dc.FORECAST,
    -- 找到累计需求未超过库存的最大天数，无符合条件则返回0
    COALESCE(
        (SELECT MAX(DAY_OFFSET) 
         FROM daily_cumulative dcc
         WHERE dcc.LOCATION = dc.LOCATION
           AND dcc.MATERIAL = dc.MATERIAL
           AND dcc.START_DATE >= dc.START_DATE
           AND dcc.CUMULATIVE_FORECAST <= dc.PROJECTED_INVENTORY),
        0
    ) AS DOS
FROM daily_cumulative dc
ORDER BY dc.LOCATION, dc.MATERIAL, dc.START_DATE;

逻辑说明 #

1. 关联表：将表A的库存数据和表B的需求数据按位置、物料、日期维度关联，统一字段命名方便后续计算。
2. 累计计算：用窗口函数SUM(FORECAST) OVER (...)计算从当前日期开始的累计需求，ROW_NUMBER()标记从当日起的天数偏移。
3. 统计DOS：对每个日期，子查询筛选出累计需求未超过当日库存的所有记录，取最大的天数偏移即为有效供应天数；若当日需求直接超过库存，则返回0。

示例数据适配 #

如果库存和需求在同一表（如你提供的示例表），只需修改combined_data部分为直接读取单表：

WITH combined_data AS (
    SELECT 
        LOCATION,
        MATERIAL,
        START_DATE,
        PROJECTED_INVENTORY,
        FORECAST
    FROM your_sample_table -- 替换为你的示例表名
),
-- 后续逻辑同上述SQL

执行后将得到预期结果：

内容的提问来源于stack exchange，提问作者Yu Ching Tsoi