Snowflake中是否存在Oracle Model Clause的等效实现？

Snowflake不支持Oracle的MODEL子句，你可以通过**递归CTE（Common Table Expression）**来重写你的库存迭代计算逻辑，以下是对应Snowflake SQL的实现：

完整代码 #

WITH ranked_data AS (
    SELECT 
        ITEM,
        PLANT,
        DAYSOUT,
        TRANSACTIONDATE,
        "SCHEDULEDPICKTIME",
        "ACTUAL PROMISED PICK DATE",
        PRODUCTIONDATE,
        "PRODUCTIONPLANNUMBER",
        "WORK ORDER NUMBER",
        "INTERCOSALESORDERNUMBER",
        "INTERCOPURCHASEORDERNUMBER",
        "SALESORDERNUMBER",
        "SOLD TO NO",
        "SHIP TO NO",
        "LOAD PLAN NUMBER",
        "LAST STATUS",
        "PURCHASEORDERNUMBER",
        QLEADTIME,
        TRANSACTIONTYPE,
        "INV QTY AVAILABLE",
        "INV QTY INTRANSIT",
        "INTERCO QTY ON PO",
        "INV QTY IN QUARANTINE",
        "PAST DUE INV QTY IN QUARANTINE",
        "INV QTY ON HOLD",
        "QTY ON WO",
        "QTY ON WR",
        "PAST DUE QTY ON WO",
        "QTY ARRIVING VIA INTERCO SHIPMENT",
        "QTY ON SALES ORDER",
        "PAST DUE QTY ON SALES ORDER",
        "QTY ON INTERCO SALES ORDER",
        INTERCOFLAG,
        "AVG WKLY SHIPMENTS OVER 6WK",
        "AVG WKLY SHIPMENTS OVER 1QTR",
        "AVG WKLY SHIPMENTS OVER 6MTH",
        "AVG WKLY SHIPMENTS 1YR",
        "ORIGINAL ORDER",
        "ORIGINAL PRODUCTION REQUEST",
        SUPPLY,
        DEMAND,
        ROW_NUMBER() OVER (PARTITION BY ITEM, PLANT ORDER BY DAYSOUT) AS rn
    FROM INPUTS
),
recursive_inventory AS (
    -- 初始化分区内第一行的计算值
    SELECT 
        *,
        GREATEST(0, COALESCE(LAG(SUPPLY) OVER (PARTITION BY ITEM, PLANT ORDER BY DAYSOUT), 0) + 
                   COALESCE(LAG(DEMAND) OVER (PARTITION BY ITEM, PLANT ORDER BY DAYSOUT), 0) + 
                   COALESCE(LAG(0) OVER (PARTITION BY ITEM, PLANT ORDER BY DAYSOUT), 0)) AS BEGINNINGINVENTORY,
        GREATEST(0, SUPPLY + DEMAND + 0) AS ENDINGINVENTORY,
        LEAST(0, DEMAND + GREATEST(0, COALESCE(LAG(SUPPLY) OVER (PARTITION BY ITEM, PLANT ORDER BY DAYSOUT), 0) + 
                                      COALESCE(LAG(DEMAND) OVER (PARTITION BY ITEM, PLANT ORDER BY DAYSOUT), 0) + 
                                      COALESCE(LAG(0) OVER (PARTITION BY ITEM, PLANT ORDER BY DAYSOUT), 0))) AS PREDICTEDSHORT
    FROM ranked_data
    WHERE rn = 1

    UNION ALL

    -- 递归迭代后续行，基于前一行结果计算当前值
    SELECT 
        rd.*,
        GREATEST(0, COALESCE(ri.SUPPLY, 0) + COALESCE(ri.DEMAND, 0) + COALESCE(ri.BEGINNINGINVENTORY, 0)) AS BEGINNINGINVENTORY,
        GREATEST(0, rd.SUPPLY + rd.DEMAND + COALESCE(ri.ENDINGINVENTORY, 0)) AS ENDINGINVENTORY,
        LEAST(0, rd.DEMAND + COALESCE(GREATEST(0, COALESCE(ri.SUPPLY, 0) + COALESCE(ri.DEMAND, 0) + COALESCE(ri.BEGINNINGINVENTORY, 0)), 0)) AS PREDICTEDSHORT
    FROM ranked_data rd
    JOIN recursive_inventory ri 
        ON rd.ITEM = ri.ITEM 
        AND rd.PLANT = ri.PLANT 
        AND rd.rn = ri.rn + 1
)
-- 输出最终结果，保持原排序
SELECT 
    ITEM,
    PLANT,
    DAYSOUT,
    TRANSACTIONDATE,
    "SCHEDULEDPICKTIME",
    "ACTUAL PROMISED PICK DATE",
    PRODUCTIONDATE,
    "PRODUCTIONPLANNUMBER",
    "WORK ORDER NUMBER",
    "INTERCOSALESORDERNUMBER",
    "INTERCOPURCHASEORDERNUMBER",
    "SALESORDERNUMBER",
    "SOLD TO NO",
    "SHIP TO NO",
    "LOAD PLAN NUMBER",
    "LAST STATUS",
    "PURCHASEORDERNUMBER",
    QLEADTIME,
    TRANSACTIONTYPE,
    "INV QTY AVAILABLE",
    "INV QTY INTRANSIT",
    "INTERCO QTY ON PO",
    "INV QTY IN QUARANTINE",
    "PAST DUE INV QTY IN QUARANTINE",
    "INV QTY ON HOLD",
    "QTY ON WO",
    "QTY ON WR",
    "PAST DUE QTY ON WO",
    "QTY ARRIVING VIA INTERCO SHIPMENT",
    "QTY ON SALES ORDER",
    "PAST DUE QTY ON SALES ORDER",
    "QTY ON INTERCO SALES ORDER",
    INTERCOFLAG,
    "AVG WKLY SHIPMENTS OVER 6WK",
    "AVG WKLY SHIPMENTS OVER 1QTR",
    "AVG WKLY SHIPMENTS OVER 6MTH",
    "AVG WKLY SHIPMENTS 1YR",
    "ORIGINAL ORDER",
    "ORIGINAL PRODUCTION REQUEST",
    SUPPLY,
    DEMAND,
    BEGINNINGINVENTORY,
    ENDINGINVENTORY,
    PREDICTEDSHORT
FROM recursive_inventory
ORDER BY ITEM, PLANT, DAYSOUT;

逻辑对应说明 #

1. 分区处理：原Oracle的PARTITION BY (ITEM, PLANT)通过递归CTE中PARTITION BY ITEM, PLANT生成行号，以及递归时的连接条件实现分组计算。
2. 维度排序：原Oracle的DIMENSION BY (DAYSOUT)对应按DAYSOUT排序生成行号，确保迭代顺序与原逻辑一致。
3. 迭代规则：
   • BEGINNINGINVENTORY：基于前一行的SUPPLY、DEMAND和BEGINNINGINVENTORY计算，取非负值。
   • ENDINGINVENTORY：基于当前行的SUPPLY、DEMAND和前一行的ENDINGINVENTORY计算，取非负值。
   • PREDICTEDSHORT：基于当前行的DEMAND和BEGINNINGINVENTORY计算，取非正值。
4. 空值处理：用Snowflake的COALESCE替代Oracle的NVL，功能完全一致。

注意事项 #

• 确保每个(ITEM, PLANT)分区内的DAYSOUT是唯一且有序的，否则行号生成会导致计算逻辑错误。
• 如果分区内的行数超过Snowflake默认递归深度（1000），需要先执行ALTER SESSION SET MAX_RECURSION_DEPTH = 1000000;调整递归深度限制。

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