基于Snowflake可用库存计算库存可维持周数及累计值

现有数据表：

期望生成结果表：

逻辑说明：当Available_Stock为1000时，累计Value到第5周时超出1000，因此库存仅能维持4周。需在Snowflake中实现该计算：累计Value并判断是否超过可用库存，取首次超出前的周数及对应累计值。

通过窗口函数计算累计消耗值，再筛选出累计值未超过库存的最大周数，最终聚合得到目标结果：

WITH cumulative_consumption AS (
    SELECT
        ID,
        Week_No,
        Value,
        Available_Stock,
        SUM(Value) OVER (PARTITION BY ID ORDER BY Week_No) AS running_total
    FROM your_table_name
),
valid_weeks AS (
    SELECT
        ID,
        Available_Stock,
        MAX(Week_No) AS max_valid_week,
        MAX(running_total) AS total_consumed
    FROM cumulative_consumption
    WHERE running_total <= Available_Stock
    GROUP BY ID, Available_Stock
)
SELECT
    ID,
    total_consumed AS "SUM(VALUE)",
    CONCAT(max_valid_week, ' Weeks') AS Stock_Lasts_Weeks,
    Available_Stock
FROM valid_weeks;

代码解释：

• cumulative_consumption CTE：按ID分组、周数排序，用窗口函数计算每周的累计消耗值running_total。
• valid_weeks CTE：筛选出累计值未超过可用库存的记录，按ID和库存分组，提取库存能维持的最后一周数，以及对应的累计消耗总量。
• 最终查询：格式化输出字段，将周数拼接为X Weeks的形式。

该逻辑同时兼容累计值刚好等于库存的场景；若所有周的累计消耗都未超过库存，会自动返回全部周数的累计值和总周数。

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