SQL Server中基于线性插值填充Annual_Mileage空值的技术需求

直接用SQL Server的LAG()和LEAD()窗口函数，结合CASE和COALESCE，就能按你的规则填充空值，而且严格基于每个客户的年度数据独立计算，避免跨客户的异常。

SELECT
    Year,
    Customer,
    Crashes,
    COALESCE(
        Annual_Mileage,
        CASE
            -- 前后都有有效值，取平均值
            WHEN LAG(Annual_Mileage) OVER (PARTITION BY Customer ORDER BY Year) IS NOT NULL
                 AND LEAD(Annual_Mileage) OVER (PARTITION BY Customer ORDER BY Year) IS NOT NULL
            THEN (LAG(Annual_Mileage) OVER (PARTITION BY Customer ORDER BY Year) + LEAD(Annual_Mileage) OVER (PARTITION BY Customer ORDER BY Year)) / 2.0
            -- 只有前一行有有效值，取前值
            WHEN LAG(Annual_Mileage) OVER (PARTITION BY Customer ORDER BY Year) IS NOT NULL
            THEN LAG(Annual_Mileage) OVER (PARTITION BY Customer ORDER BY Year)
            -- 只有后一行有有效值，取后值
            WHEN LEAD(Annual_Mileage) OVER (PARTITION BY Customer ORDER BY Year) IS NOT NULL
            THEN LEAD(Annual_Mileage) OVER (PARTITION BY Customer ORDER BY Year)
            -- 极端情况：该客户所有Annual_Mileage都是空值（根据你描述Crashes非空，但这里留个兜底）
            ELSE NULL
        END
    ) AS Filled_Annual_Mileage
FROM
    YourDataSourceName; -- 替换成你的表名或数据集名称

关键说明： #

• PARTITION BY Customer：确保每个客户的里程数据独立计算，不会和其他客户的行混淆，这应该是你之前用row_number()时出问题的核心原因——没按客户分组。
• ORDER BY Year：保证按时间顺序取前后年份的里程值，符合业务逻辑。
• LAG()/LEAD()：分别获取当前行的上一行、下一行的Annual_Mileage值，精准定位前后有效值。
• COALESCE：优先保留原始非空值，只有当原始值为空时才进入CASE逻辑处理。

注意事项： #

如果你的数据集是之前FULL JOIN后的结果，要确保FULL JOIN没有引入多余的重复行或无效行，否则会干扰窗口函数的计算。可以先对FULL JOIN的结果做去重或过滤，再套上面的逻辑。

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