如何用SQL实现基于上一行和前一列的PersonsAlive计算列

你的需求是按ZipCode分区，逐行计算PersonsAlive（首行固定为100000，后续行依赖上一行计算结果乘以当前行SurvivalProbablity），LAG函数无法完成这类递归累积计算，这里提供两种可行方案：

方法一：递归CTE（逻辑清晰，推荐） #

递归CTE天生适合处理这种逐行依赖前一行计算结果的场景，步骤如下：

1. 锚点成员：获取每个ZipCode的最小年龄（Age=0），初始化PersonsAlive为100000。
2. 递归成员：依次关联每个ZipCode的下一个年龄行，用上一行的PersonsAlive乘以当前行的SurvivalProbablity得到当前行的结果。
3. 整合递归结果与原表字段。

示例代码：

WITH RecursivePersons AS (
    -- 锚点：每个ZipCode的首行（Age=0），初始化PersonsAlive为100000
    SELECT 
        ZipCode,
        Age,
        [Population],
        Deaths,
        DeathRate,
        Death_Proportion,
        DeathProbablity,
        SurvivalProbablity,
        CAST(100000 AS DECIMAL(18,6)) AS PersonsAlive
    FROM #temp4
    WHERE Age = 0

    UNION ALL

    -- 递归：关联下一个年龄的行，计算PersonsAlive
    SELECT 
        t.ZipCode,
        t.Age,
        t.[Population],
        t.Deaths,
        t.DeathRate,
        t.Death_Proportion,
        t.DeathProbablity,
        t.SurvivalProbablity,
        CAST(rp.PersonsAlive * t.SurvivalProbablity AS DECIMAL(18,6)) AS PersonsAlive
    FROM #temp4 t
    INNER JOIN RecursivePersons rp 
        ON t.ZipCode = rp.ZipCode 
        AND t.Age = rp.Age + 1
)
SELECT * FROM RecursivePersons
ORDER BY ZipCode, Age;

注意：

• 确保Age是连续递增的（0-85），若存在不连续情况，可改用ROW_NUMBER()排序后关联。
• 使用DECIMAL类型避免浮点精度丢失。

方法二：累积乘积窗口函数 #

SQL没有原生的累积乘积函数，但可以通过对数转换+求和+指数转换模拟实现，公式为EXP(SUM(LOG(SurvivalProbablity)) OVER (...))。

逻辑：

• Age=0时，PersonsAlive直接设为100000。
• Age>0时，PersonsAlive = 100000 × 从Age=1到当前Age的SurvivalProbablity累积乘积。

示例代码：

SELECT 
    ZipCode,
    Age,
    [Population],
    Deaths,
    DeathRate,
    Death_Proportion,
    DeathProbablity,
    SurvivalProbablity,
    CASE 
        WHEN Age = 0 THEN CAST(100000 AS DECIMAL(18,6))
        ELSE CAST(100000 * EXP(SUM(LOG(SurvivalProbablity)) OVER (PARTITION BY ZipCode ORDER BY Age)) AS DECIMAL(18,6))
    END AS PersonsAlive
FROM #temp4
ORDER BY ZipCode, Age;

注意：

• 该方法要求SurvivalProbablity必须是正数（不能为0或负数），否则LOG函数会报错。
• 浮点运算可能存在微小精度误差，对精度要求极高时优先选递归CTE。

为什么你的LAG方法无效？ #

你用LAG(PersonsAliveTemp,1) * SurvivalProbablity无法得到正确结果的核心原因：

• LAG只能获取原始表中前一行的PersonsAliveTemp值，而非计算后的PersonsAlive结果。
• 当PersonsAliveTemp仅在首行有值时，后续行的LAG结果会是NULL，导致计算结果为空。

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