SQL中计算整体留存成功人数：递归CTE还是其他方法？

现有字段 #

• Column_A：按时间顺序排列的调查步骤
• Column_B：该步骤的参与人数
• Column_C：该步骤的成功人数（保证 Column_B ≥ Column_C）

需求说明 #

需要计算Column_D：经过所有前置步骤后留存的成功参与者总数，数值只会保持不变或因参与者失败减少。Excel中实现逻辑为：

• 第一行：Column_D = Column_C（或Column_B，此时两者相等）
• 后续行：上一行Column_D - (当前行Column_B - 当前行Column_C)

示例数据 #

问题分析 #

你尝试的SQL错误在于用LAG([Column_C])代替了LAG([Column_D])，但普通窗口函数无法直接引用同一字段的动态计算结果（窗口函数基于原始数据集计算，不依赖生成列）。这里提供两种可行解决方案：

方案1：转换逻辑用窗口累加（推荐，性能更优） #

观察Column_D的计算逻辑可发现，它等价于初始成功人数（第一行的Column_C）减去从第一行到当前行的累计失败人数（失败人数=Column_B - Column_C）。利用窗口函数的累加特性直接实现：

WITH running_failures AS (
    SELECT 
        Column_A,
        Column_B,
        Column_C,
        -- 当前步骤的失败人数
        (Column_B - Column_C) AS step_failures,
        -- 累计到当前步骤的总失败人数
        SUM(Column_B - Column_C) OVER (ORDER BY Column_A ASC) AS total_failures,
        -- 获取初始成功人数（第一行的Column_C）
        FIRST_VALUE(Column_C) OVER (ORDER BY Column_A ASC) AS initial_success
    FROM dataset
)
SELECT 
    Column_A,
    Column_B,
    Column_C,
    initial_success - total_failures AS Column_D
FROM running_failures
ORDER BY Column_A;

方案2：递归CTE（完全匹配Excel逻辑） #

如果你的数据库支持递归CTE（如SQL Server、PostgreSQL、MySQL 8.0+等），可以完全复刻Excel的逐行计算逻辑：

WITH recursive cte AS (
    -- 锚点：获取第一行数据，初始Column_D等于Column_C
    SELECT 
        Column_A,
        Column_B,
        Column_C,
        Column_C AS Column_D
    FROM dataset
    WHERE Column_A = 0

    UNION ALL

    -- 递归：逐行计算，用上一行的Column_D减去当前行的失败人数
    SELECT 
        d.Column_A,
        d.Column_B,
        d.Column_C,
        c.Column_D - (d.Column_B - d.Column_C) AS Column_D
    FROM dataset d
    INNER JOIN cte c 
        ON d.Column_A = c.Column_A + 1
)
SELECT * FROM cte ORDER BY Column_A;

说明 #

• 方案1性能更优，适合大数据量场景，仅需单次扫描数据集；
• 方案2逻辑直观，完全对应Excel计算步骤，适合理解递归逻辑或数据库不支持复杂窗口函数的场景；
• 若Column_A不连续，递归CTE需调整关联条件（比如用LAG获取上一行的Column_A）。

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