如何用SQL计算调查首记录与最新非空记录的净变化值？

你的核心问题是要跳过中间的空值，找到每个列最新的非空值，再和同一参与者的首记录（rank=1）做差值计算。原来的LAG函数只能对比相邻行，没法定位到最后一个有效数据，所以我们可以通过分步提取基准值和最新有效值来实现。

解决方案SQL（兼容多数主流数据库） #

WITH first_vals AS (
    -- 提取每个参与者的首记录（rank=1）作为基准值
    SELECT p_id, val_a AS first_a, val_b AS first_b, val_c AS first_c
    FROM dataset
    WHERE rank = 1
),
latest_ranks AS (
    -- 找到每个参与者各列最后一个非空值对应的最大rank
    SELECT 
        p_id,
        MAX(CASE WHEN val_a IS NOT NULL THEN rank END) AS rank_a,
        MAX(CASE WHEN val_b IS NOT NULL THEN rank END) AS rank_b,
        MAX(CASE WHEN val_c IS NOT NULL THEN rank END) AS rank_c
    FROM dataset
    GROUP BY p_id
),
latest_vals AS (
    -- 根据上面的rank，获取对应的最新非空值
    SELECT 
        lr.p_id,
        (SELECT val_a FROM dataset d WHERE d.p_id = lr.p_id AND d.rank = lr.rank_a) AS latest_a,
        (SELECT val_b FROM dataset d WHERE d.p_id = lr.p_id AND d.rank = lr.rank_b) AS latest_b,
        (SELECT val_c FROM dataset d WHERE d.p_id = lr.p_id AND d.rank = lr.rank_c) AS latest_c
    FROM latest_ranks lr
)
-- 计算差值
SELECT 
    f.p_id,
    latest_a - first_a AS val_a,
    latest_b - first_b AS val_b,
    latest_c - first_c AS val_c
FROM first_vals f
JOIN latest_vals lv ON f.p_id = lv.p_id;

代码逻辑拆解 #

1. first_vals 公共表表达式：直接筛选每个参与者rank=1的记录，得到各列的基准值（首记录值）。
2. latest_ranks 公共表表达式：针对每个列，用MAX()函数找到该列非空行中的最大rank——这个rank就是最新非空记录的位置。
3. latest_vals 公共表表达式：通过子查询，根据上面得到的rank值，精准获取每个列的最新非空值。
4. 最终计算：将基准值和最新非空值做减法，得到你需要的净变化值。

针对示例数据的验证 #

对于p_id=2的情况：

• 基准值：first_a=1、first_b=2、first_c=3
• 最新非空值对应的rank：rank_a=4、rank_b=4、rank_c=3
• 最新非空值：latest_a=4、latest_b=3、latest_c=1
• 差值：4-1=3、3-2=1、1-3=-2，完全匹配你的期望输出。

额外说明 #

如果某列除了首记录外全是空值，那么latest_ranks中对应的rank会是NULL，最终差值也会是NULL。如果需要把这种情况处理为0，可以用COALESCE()函数包裹差值，比如：COALESCE(latest_a - first_a, 0) AS val_a。

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