如何在Snowflake SQL中创建动态时间区间？预算消耗追踪

AI 小北

2026-8-6

Snowflake SQL实现多预算动态耗尽日期计算

需求：编写Snowflake SQL查询，识别单个账户关联的多笔预算的耗尽日期。起始日期为2022-12-01，需先计算该账户第一笔预算的累计支出，当累计值达到预算额时记录对应日期，并将此日期作为新的起始日期，继续计算第二笔预算的累计支出，以此类推。当前无法基于上一笔预算结果创建动态时间区间，需解决该问题。

我的尝试

WITH TRM_INTERVAL AS 
    (
    SELECT 
    Country,
    replace(Account_ID, '-', '') AS fixed_accountid,
    TO_DATE(Date) AS CONVERTED_DATE,
    TO_DATE(CAST(CONVERTED_DATE AS VARCHAR), 'YYYY-DD-MM') AS CONVERTED_DATE_FORMAT,
    TRM2,
    TRM1,
    TOTAL_ARS_IN_ACCOUNT,
    row_number()
    over (partition by Account_ID, Country order by CONVERTED_DATE_FORMAT) AS ROW_NUMBER,
    lag(TOTAL_ARS_IN_ACCOUNT)
    over (partition by Account_ID, Country order by CONVERTED_DATE_FORMAT) AS PREVIOUS_ARS_BUCKET,
    lead(CONVERTED_DATE_FORMAT)
    over (partition by Account_ID, Country order by CONVERTED_DATE_FORMAT) AS NEXT_DATE
    FROM CONECTOR
),

--DETAIL
DETAIL AS (

    SELECT TI.COUNTRY, TI.fixed_accountid, TI.PREVIOUS_ARS_BUCKET, TI.TOTAL_ARS_IN_ACCOUNT, TI.ROW_NUMBER, GO.EVENT_DATE, SUM(CASE WHEN GO.CURRENCY_ORIGINAL = 'ARS' THEN GO.FINAL_CAC * 160 END) AS ARS_SPEND,
    
    SUM(ARS_SPEND) OVER(PARTITION BY TI.fixed_accountid, TI.COUNTRY, TI.TOTAL_ARS_IN_ACCOUNT ORDER BY GO.EVENT_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATIVE_ARS_SPEND
    
    FROM TABLE1 AS GO
    JOIN CONECTOR AS TI
    ON GO.ACCOUNT_ID = TI.fixed_accountid AND GO.COUNTRY = TI.COUNTRY
    WHERE GO.EVENT_DATE >= '2022-12-1'
    GROUP BY 1,2,3,4,5,6

),


--ACUMULADO
LOGIC AS (
    
    SELECT *,
    CUMULATIVE_ARS_SPEND / TOTAL_ARS_IN_ACCOUNT AS CUMULATIVE_ARS_SPEND_PERCENTAGE,
    --COMO VALIDAR CON EL TI TOTAL ARS CORRESPONDIENTE
    CASE WHEN CUMULATIVE_ARS_SPEND_PERCENTAGE >=0.686 AND EVENT_DATE <> CURRENT_DATE() THEN 'FLAG' END AS FLAGS  
    
    
    FROM DETAIL

)

当前能计算账户累计支出，但无法创建动态时间区间，导致后续预算重复使用前期日期和支出数据。

实际结果

FECHA   BUDGET      SPEND_DAILY     CUMULATIVE   % ACUMM
01/12/2022  500000    100000        100000       20.00%
01/13/2022  500000    150000        250000       50.00%
01/14/2022  500000    200000        450000       90.00%
01/15/2022  500000    50000         500000       100.00%
01/12/2022  30000000  100000        100000         0.33%
01/13/2022  30000000  150000        250000         0.83%
01/14/2022  30000000  200000        450000         1.50%
01/15/2022  30000000  50000         500000         1.67%
01/12/2022  4000000   100000        100000        2.50%
01/13/2022  4000000   150000        250000        6.25%
01/14/2022  4000000   200000        450000        11.25%
01/15/2022  4000000   50000         500000        12.50%

预期结果

FECHA       BUDGET  GASTO DIARIO    GASTO ACUMM % ACUMM
01/12/2022  500000     100000            100000   20.00%
01/13/2022  500000     150000            250000   50.00%
01/14/2022  500000     200000            450000   90.00%
01/15/2022  500000     50000             500000   100.00%
01/16/2022  30000000    100000           100000     0.33%
01/17/2022  30000000    200000           300000     1.00%
01/18/2022  30000000    500000           800000     2.67%
01/19/2022  30000000    300000           1100000    3.67%
01/20/2022  30000000    150000           1250000    4.17%
01/21/2022  30000000    20000000         21250000   70.83%
01/22/2022  30000000    10000000         31250000   104.17%

解决方案

使用递归CTE（Common Table Expression）处理这种依赖上一步结果的动态时间区间问题，代码如下：

-- 1. 预处理每日支出：按账户、日期聚合每日支出
WITH daily_spend AS (
    SELECT
        replace(GO.ACCOUNT_ID, '-', '') AS fixed_accountid,
        GO.COUNTRY,
        GO.EVENT_DATE,
        SUM(CASE WHEN GO.CURRENCY_ORIGINAL = 'ARS' THEN GO.FINAL_CAC * 160 END) AS daily_ars_spend
    FROM TABLE1 AS GO
    WHERE GO.EVENT_DATE >= '2022-12-01'
    GROUP BY fixed_accountid, COUNTRY, EVENT_DATE
    ORDER BY fixed_accountid, COUNTRY, EVENT_DATE
),

-- 2. 整理账户预算：按顺序编号，确定每笔预算的使用顺序
account_budgets AS (
    SELECT
        replace(Account_ID, '-', '') AS fixed_accountid,
        Country,
        TOTAL_ARS_IN_ACCOUNT AS budget_amount,
        ROW_NUMBER() OVER (PARTITION BY Account_ID, Country ORDER BY TO_DATE(Date)) AS budget_seq
    FROM CONECTOR
),

-- 3. 递归CTE：逐笔计算预算耗尽日期和后续起始日期
recursive_budget_tracking AS (
    -- 递归起始点：第一笔预算，起始日期为2022-12-01
    SELECT
        ab.fixed_accountid,
        ab.Country,
        ab.budget_seq,
        ab.budget_amount,
        DATE('2022-12-01') AS period_start_date,
        NULL AS period_end_date,
        0 AS cumulative_spend
    FROM account_budgets ab
    WHERE ab.budget_seq = 1

    UNION ALL

    -- 递归步骤：基于上一笔预算的耗尽日期，启动下一笔预算
    SELECT
        rbt.fixed_accountid,
        rbt.Country,
        ab.budget_seq,
        ab.budget_amount,
        DATEADD(DAY, 1, rbt.period_end_date) AS period_start_date,
        NULL AS period_end_date,
        0 AS cumulative_spend
    FROM recursive_budget_tracking rbt
    JOIN account_budgets ab
        ON rbt.fixed_accountid = ab.fixed_accountid
        AND rbt.Country = ab.Country
        AND ab.budget_seq = rbt.budget_seq + 1
    WHERE rbt.period_end_date IS NOT NULL -- 仅当上一笔预算已耗尽时处理下一笔
),

-- 4. 关联每日支出，计算累计支出并标记耗尽日期
budget_spend_detail AS (
    SELECT
        rbt.fixed_accountid,
        rbt.Country,
        rbt.budget_seq,
        rbt.budget_amount,
        ds.EVENT_DATE AS fecha,
        ds.daily_ars_spend AS gasto_diario,
        -- 计算当前预算下的累计支出
        SUM(ds.daily_ars_spend) OVER (
            PARTITION BY rbt.fixed_accountid, rbt.Country, rbt.budget_seq
            ORDER BY ds.EVENT_DATE
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS gasto_acumm,
        -- 标记预算耗尽日期
        CASE WHEN SUM(ds.daily_ars_spend) OVER (
            PARTITION BY rbt.fixed_accountid, rbt.Country, rbt.budget_seq
            ORDER BY ds.EVENT_DATE
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) >= rbt.budget_amount THEN ds.EVENT_DATE ELSE NULL END AS budget_exhaust_date
    FROM recursive_budget_tracking rbt
    JOIN daily_spend ds
        ON rbt.fixed_accountid = ds.fixed_accountid
        AND rbt.Country = ds.Country
        AND ds.EVENT_DATE >= rbt.period_start_date
    -- 仅保留当前预算未耗尽前的日期，以及耗尽当天记录
    QUALIFY SUM(ds.daily_ars_spend) OVER (
        PARTITION BY rbt.fixed_accountid, rbt.Country, rbt.budget_seq
        ORDER BY ds.EVENT_DATE
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) <= rbt.budget_amount + ds.daily_ars_spend
),

-- 5. 更新递归CTE的耗尽日期，确保后续预算能正确启动
updated_recursive_tracking AS (
    SELECT
        fixed_accountid,
        Country,
        budget_seq,
        budget_amount,
        period_start_date,
        MAX(budget_exhaust_date) OVER (PARTITION BY fixed_accountid, Country, budget_seq) AS period_end_date
    FROM recursive_budget_tracking rbt
    LEFT JOIN budget_spend_detail bsd
        ON rbt.fixed_accountid = bsd.fixed_accountid
        AND rbt.Country = bsd.Country
        AND rbt.budget_seq = bsd.budget_seq
)

-- 最终输出格式化结果
SELECT
    fecha AS FECHA,
    budget_amount AS BUDGET,
    gasto_diario AS "GASTO DIARIO",
    gasto_acumm AS "GASTO ACUMM",
    ROUND((gasto_acumm / budget_amount) * 100, 2) AS "% ACUMM"
FROM budget_spend_detail
ORDER BY fixed_accountid, Country, budget_seq, fecha;

代码说明

daily_spend：聚合每日支出数据，确保每个账户每天仅一条支出记录
account_budgets：为每个账户的预算按时间顺序编号，明确预算使用优先级
recursive_budget_tracking：递归初始化每笔预算的起始日期，上一笔预算耗尽后自动触发下一笔
budget_spend_detail：关联每日支出计算累计值，标记预算耗尽日期
updated_recursive_tracking：更新递归CTE中的耗尽日期，保证后续预算能从正确日期开始计算

此方案通过递归CTE实现了动态时间区间的传递，完全符合"上一笔预算耗尽次日启动下一笔预算计算"的需求。

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

如何在Snowflake SQL中创建动态时间区间？预算消耗追踪

我的尝试 #

实际结果 #

预期结果 #

解决方案 #

代码说明 #

我的尝试

实际结果

预期结果

解决方案

代码说明