Snowflake计算列自引用问题：按规则更新OPEN_BAL与CLOSE_BAL

原始数据集 #

更新规则 #

当状态为F时，需按以下规则更新期初余额和期末余额：

• 当日期初余额等于前一日的期末余额
• 当日期末余额为期初余额 + 1
• 后续记录的余额需依赖前一条计算后的结果

示例说明：

• 第2天：期初余额保持158（正确），期末余额应改为159
• 第3天：期初余额应为159，期末余额应改为160
• 第4天：期初余额应为160，期末余额应改为161，以此类推

样本数据与预期输出 #

尝试的查询与问题 #

尝试使用LAG函数实现，但无法实现自引用（即无法让LAG引用计算后的余额值），导致结果不符合预期。以下是尝试的查询代码：

WITH RankedRecords AS (
    SELECT 
        YEAR(TRY_TO_DATE(TRIM(f.open_date ), 'yymmdd')) folio_yr,
        s.folio_mo, s.folio_no,
        f.tt_status status,
        IFNULL(try_cast(s.open_bal AS FLOAT), 0) as open_bal,
        IFNULL(try_cast(s.close_book AS FLOAT), 0) as close_bal,
        LAG(close_bal) OVER (ORDER BY folio_yr, fol_mo, fol_no) AS prev_close_bal
    from ops_fivetran_prd_raw.azure_sql_midstreamt_us_tms6data.Stock s
    join ops_fivetran_prd_raw.azure_sql_midstreamt_us_tms6data.FolioStatus f
        on s.folio_mo = f.fol_mo
        and s.folio_no = f.fol_no
        and s.term_id = f.term_id
        and upper(trim(s.term_code)) = upper(trim(f.term_code))
        where s.supplier_no = '0000000000' and s.term_id = '00000BN' and folio_yr = '2024' and s.folio_mo = '08' and s.prod_id = 'MOBRED'
),
UpdatedBalances AS (
    SELECT 
        folio_yr, folio_mo, folio_no,
        status,
        CASE 
            WHEN status = 'F' THEN prev_close_bal
            ELSE open_bal
        END AS expected_open_bal,
        CASE 
            WHEN status = 'F' THEN prev_close_bal + 1
            WHEN status = 'F' THEN open_bal + 1
            ELSE close_bal
        END AS expected_close_bal
    FROM RankedRecords
),
FinalBalances AS (
    SELECT 
        folio_yr, folio_mo, folio_no,
        status,
        expected_open_bal,
        expected_close_bal,
        LAG(expected_close_bal) OVER (ORDER BY folio_yr, folio_mo, folio_no) AS prev_expected_close_bal
    FROM UpdatedBalances
)
SELECT 
    folio_yr, folio_mo, folio_no,
    status,
    CASE 
        WHEN status = 'F' THEN COALESCE(prev_expected_close_bal, expected_open_bal)
        ELSE expected_open_bal
    END AS open_bal,
    expected_close_bal AS close_bal
FROM FinalBalances;

注：示例中的日期列对应实际数据中的账套年份、账套月份、账套编号（原字段名FOLIO_YR、FOLIO_MO、FOLIO_NO）

解决方案（在SELECT查询内实现） #

可以通过累积计算偏移量的方式实现，无需递归CTE，直接在SELECT中用窗口函数完成：

1. 先按账套年份、月份、编号对记录排序，生成行号
2. 找到最后一条状态为C的记录的期末余额作为基准值
3. 计算每条记录与基准记录的行号差，以此推导更新后的余额

以下是适配实际表结构的查询代码：

WITH BaseData AS (
    SELECT 
        YEAR(TRY_TO_DATE(TRIM(f.open_date ), 'yymmdd')) AS 账套年份,
        s.folio_mo AS 账套月份,
        s.folio_no AS 账套编号,
        f.tt_status AS 状态,
        IFNULL(TRY_CAST(s.open_bal AS FLOAT), 0) AS 原始期初余额,
        IFNULL(TRY_CAST(s.close_book AS FLOAT), 0) AS 原始期末余额,
        -- 生成排序后的行号
        ROW_NUMBER() OVER (ORDER BY YEAR(TRY_TO_DATE(TRIM(f.open_date ), 'yymmdd')), s.folio_mo, s.folio_no) AS 行号
    FROM ops_fivetran_prd_raw.azure_sql_midstreamt_us_tms6data.Stock s
    JOIN ops_fivetran_prd_raw.azure_sql_midstreamt_us_tms6data.FolioStatus f
        ON s.folio_mo = f.fol_mo
        AND s.folio_no = f.fol_no
        AND s.term_id = f.term_id
        AND UPPER(TRIM(s.term_code)) = UPPER(TRIM(f.term_code))
    WHERE s.supplier_no = '0000000000' 
      AND s.term_id = '00000BN' 
      AND YEAR(TRY_TO_DATE(TRIM(f.open_date ), 'yymmdd')) = '2024' 
      AND s.folio_mo = '08' 
      AND s.prod_id = 'MOBRED'
),
BenchmarkData AS (
    SELECT 
        *,
        -- 获取最后一条状态为C的记录的期末余额和行号
        LAST_VALUE(CASE WHEN 状态 = 'C' THEN 原始期末余额 END) OVER (ORDER BY 行号 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 基准余额,
        LAST_VALUE(CASE WHEN 状态 = 'C' THEN 行号 END) OVER (ORDER BY 行号 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 基准行号
    FROM BaseData
)
SELECT 
    账套年份,
    账套月份,
    账套编号,
    状态,
    CASE 
        WHEN 状态 = 'C' THEN 原始期初余额
        ELSE 基准余额 + (行号 - 基准行号) - 1
    END AS 更新后期初余额,
    CASE 
        WHEN 状态 = 'C' THEN 原始期末余额
        ELSE 基准余额 + (行号 - 基准行号)
    END AS 更新后期末余额
FROM BenchmarkData
ORDER BY 行号;

逻辑说明 #

• 基准余额：取最后一条状态为C的记录的期末余额（即示例中的158）
• 对于状态为F的记录：
  • 更新后期初余额 = 基准余额 + (当前行号 - 基准行号) - 1
  • 更新后期末余额 = 基准余额 + (当前行号 - 基准行号)
• 状态为C的记录保持原始余额不变

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