Snowflake递归计算prob_reabo与prob_reabo_CUM结果异常排查

正确计算逻辑 #

• prob_reabo：(1 - 上一行prob_reabo_cum) * 当前行prob_reabo_cond
• prob_reabo_CUM：上一行prob_reabo_cum + 当前行prob_reabo

正确计算示例（样例数据） #

假设初始prob_reabo_cum为0，样例prob_reabo_cond序列为[0.1, 0.2, 0.3]，计算结果如下：

错误结果展示 #

运行现有SQL后出现异常负数，示例错误输出：

错误SQL代码 #

WITH RECURSIVE cte AS (
    SELECT 
        id,
        prob_reabo_cond,
        ROW_NUMBER() OVER (ORDER BY id) AS rn,
        prob_reabo_cond AS prob_reabo,
        prob_reabo_cond AS prob_reabo_CUM
    FROM your_table
    WHERE rn = 1
    UNION ALL
    SELECT 
        t.id,
        t.prob_reabo_cond,
        t.rn,
        (1 - c.prob_reabo_CUM) * t.prob_reabo_cond AS prob_reabo,
        c.prob_reabo_CUM + (1 - c.prob_reabo_CUM) * t.prob_reabo_cond AS prob_reabo_CUM
    FROM your_table t
    JOIN cte c ON t.rn = c.rn + 1
)
SELECT * FROM cte;

问题原因分析 #

1. 锚点查询窗口函数使用错误：锚点中直接对原表计算ROW_NUMBER()并过滤rn=1，窗口函数的计算逻辑无法在WHERE子句中直接生效，导致初始行选取异常（可能返回多行或错误行），破坏递归的起始逻辑。
2. 初始值初始化错误：锚点中错误将prob_reabo_CUM初始化为prob_reabo_cond，而正确的初始状态应该基于上一行prob_reabo_cum=0计算第一行结果，错误的初始值会导致后续累加逻辑偏离预期。
3. 递归关联行号不一致：UNION ALL部分直接从原表重新计算行号，导致与递归CTE中的行号不匹配，引发错误的行关联，可能出现重复累加，使得prob_reabo_CUM超过1，进而1 - prob_reabo_CUM变为负数，最终生成负数的prob_reabo。

修正后的SQL #

WITH ordered_data AS (
    -- 预排序并生成稳定行号
    SELECT 
        id,
        prob_reabo_cond,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM your_table
),
recursive_cte AS (
    -- 锚点：基于初始cum=0计算第一行
    SELECT 
        id,
        prob_reabo_cond,
        rn,
        (1 - 0) * prob_reabo_cond AS prob_reabo,
        0 + (1 - 0) * prob_reabo_cond AS prob_reabo_CUM
    FROM ordered_data
    WHERE rn = 1
    UNION ALL
    -- 递归关联预排序的行号，确保顺序正确
    SELECT 
        od.id,
        od.prob_reabo_cond,
        od.rn,
        (1 - rc.prob_reabo_CUM) * od.prob_reabo_cond AS prob_reabo,
        rc.prob_reabo_CUM + (1 - rc.prob_reabo_CUM) * od.prob_reabo_cond AS prob_reabo_CUM
    FROM ordered_data od
    JOIN recursive_cte rc ON od.rn = rc.rn + 1
)
SELECT * FROM recursive_cte;

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