Snowflake动态计算供需缺口：基于上周值的实现方案问询

针对你需要动态处理任意周数的供需缺口计算需求，推荐使用**递归CTE（Common Table Expression）**实现，替代静态周度CTE。递归CTE可以自动逐周迭代计算，无需手动编写每一周的逻辑，完美适配动态周数场景。

实现步骤 #

1. 测试表创建（示例） #

首先创建原始数据表格（可替换为你的实际表结构）：

CREATE OR REPLACE TABLE supply_demand_raw (
    week_number INT,
    account_id VARCHAR,
    allocation INT,
    supply NUMERIC,
    demand NUMERIC
);

-- 插入测试示例数据
INSERT INTO supply_demand_raw VALUES
(1, 'A1', 1, 100, 80),
(1, 'A2', 1, 150, 120),
(2, 'A1', 1, 90, 70),
(2, 'A2', 0, 140, 130),
(3, 'A1', 0, 80, 90),
(3, 'A2', 0, 130, 140);

2. 递归CTE动态计算逻辑 #

以下代码严格遵循你给出的业务规则和计算公式，自动处理所有周数：

WITH RECURSIVE weekly_calculations AS (
    -- 锚点成员：初始化第1周数据，pv_ncsp和pv_cut设为0
    SELECT
        week_number,
        account_id,
        allocation,
        supply,
        demand,
        0::NUMERIC AS pv_ncsp,
        0::NUMERIC AS pv_cut,
        supply + 0 AS final_supply,
        demand + ABS(0) AS final_demand,
        IFF(final_supply - final_demand > 0, final_supply - final_demand, 0) AS ncsp,
        IFF(final_supply - final_demand < 0, final_supply - final_demand, 0) AS cuts
    FROM supply_demand_raw
    WHERE week_number = 1

    UNION ALL

    -- 递归成员：逐周计算后续数据
    SELECT
        curr.week_number,
        curr.account_id,
        curr.allocation,
        curr.supply,
        curr.demand,
        -- 按规则计算pv_ncsp
        CASE
            -- 规则2：allocation=1时，取上周对应账户的ncsp
            WHEN curr.allocation = 1 THEN prev.ncsp
            -- 规则3：allocation为0（从1变更或持续为0）时，取上周所有账户ncsp汇总值
            ELSE (SELECT SUM(ncsp) FROM weekly_calculations WHERE week_number = curr.week_number - 1)
        END AS pv_ncsp,
        -- 按规则计算pv_cut
        CASE
            -- 规则2：allocation=1时，取上周对应账户cuts的绝对值
            WHEN curr.allocation = 1 THEN ABS(prev.cuts)
            -- allocation为0时，pv_cut设为0（若业务有其他规则可调整）
            ELSE 0
        END AS pv_cut,
        -- 计算最终供需
        curr.supply + pv_ncsp AS final_supply,
        curr.demand + ABS(pv_cut) AS final_demand,
        -- 计算ncsp和cuts
        IFF(final_supply - final_demand > 0, final_supply - final_demand, 0) AS ncsp,
        IFF(final_supply - final_demand < 0, final_supply - final_demand, 0) AS cuts
    FROM supply_demand_raw curr
    JOIN weekly_calculations prev
        ON curr.account_id = prev.account_id
        AND curr.week_number = prev.week_number + 1
)
-- 输出最终结果，按周和账户排序
SELECT * FROM weekly_calculations ORDER BY week_number, account_id;

关键说明 #

• 动态适配周数：递归CTE会自动遍历所有连续的周数，无需手动添加每一周的静态CTE
• 规则严格落地：
  • 第1周强制初始化pv_ncsp和pv_cut为0
  • 针对allocation=1的场景，直接关联上周同账户的历史计算值
  • 针对allocation=0的场景，通过子查询获取上周所有账户的ncsp汇总值，满足规则要求
• 扩展性：如果周数不连续，可先通过窗口函数生成连续周序列，再关联原始数据进行计算

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