Snowflake中如何调用同列已计算值实现剩余库存计算？

要实现这种依赖上一行计算结果的迭代逻辑，普通窗口函数（如LAG、累计求和）无法直接满足需求——因为它们只能基于原始数据计算，无法引用同列已计算出的新值。此时可以用递归CTE来实现，这是处理这类迭代计算的标准方案。

实现步骤与示例代码 #

假设你的原始数据存储在表inventory_data中，以下是完整的计算逻辑：

WITH ranked_data AS (
    -- 先给数据按周排序并添加行号，方便递归遍历
    SELECT 
        week_ending,
        sales_fcast,
        incoming_stock,
        ROW_NUMBER() OVER (ORDER BY week_ending) AS row_num
    FROM inventory_data
),
recursive_inventory AS (
    -- 锚点：首周数据，剩余库存初始值为14
    SELECT 
        week_ending,
        sales_fcast,
        incoming_stock,
        14 AS remaining_stock,
        row_num
    FROM ranked_data
    WHERE row_num = 1
    
    UNION ALL
    
    -- 递归部分：逐行计算剩余库存，依赖上一行的计算结果
    SELECT 
        rd.week_ending,
        rd.sales_fcast,
        rd.incoming_stock,
        -- 核心逻辑：本周剩余库存 = max(上周剩余库存 - 预测销量 + 入库库存, 0)
        GREATEST(ri.remaining_stock - rd.sales_fcast + rd.incoming_stock, 0) AS remaining_stock,
        rd.row_num
    FROM ranked_data rd
    JOIN recursive_inventory ri ON rd.row_num = ri.row_num + 1
)
-- 输出最终结果
SELECT 
    week_ending,
    sales_fcast,
    incoming_stock,
    remaining_stock
FROM recursive_inventory
ORDER BY week_ending;

代码说明 #

1. ranked_data CTE：给原始数据按week_ending排序并添加行号，确保递归时能按顺序遍历每一周的数据。
2. 递归CTE的锚点部分：指定首周的剩余库存初始值为14，作为计算的起点。
3. 递归部分：通过自连接ranked_data和recursive_inventory，每次取上一行的remaining_stock，按照业务逻辑计算当前行的值，并用GREATEST函数确保结果不低于0。

验证结果 #

运行上述代码后，会得到与你提供的示例完全一致的remaining_stock列值。

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