MS SQL Server及R中客户年度里程NULL值的线性插值处理

问题背景 #

处理每位客户的年度里程数据，年份范围固定为2009-2022且连续无间隔，部分客户的记录覆盖2009至2020等连续年份，但Annual_Mlg字段存在随机NULL值：可能出现在年份范围开头、中间或结尾，连续NULL数量不定。需要为每一年填充有效里程值（0为有效值，表示当年无出行），不允许使用默认值，必须通过前值、后值或线性插值消除所有NULL，且已填充的NULL值需用于后续插值计算。

填充规则 #

• 若NULL被两个非NULL值夹在中间，执行线性插值；
• 范围开头的NULL替换为第一个非NULL值；
• 范围结尾的NULL替换为倒数第一个非NULL值；
• 已填充的NULL值需用于后续插值计算。

尝试过的SQL代码 #

SELECT 
Customer,
Year,
Final_Mlg = COALESCE(Annual_Mlg, (Prev_Mlg + Next_Mlg)/2, 
(lag(Final_Mlg) over (partition by Customer order by Year) + Next_Mlg)/2, Prev_Mlg, Next_Mlg,
lead(Final_Mlg) over (partition by Customer order by Year), 
lead(Final_Mlg,2) over (partition by Customer order by Year),
lead(Final_Mlg,3) over (partition by Customer order by Year),
lead(Final_Mlg,4) over (partition by Customer order by Year),
lead(Final_Mlg,5) over (partition by Customer order by Year),
lead(Final_Mlg,6) over (partition by Customer order by Year),
lead(Final_Mlg,7) over (partition by Customer order by Year),
lead(Final_Mlg,8) over (partition by Customer order by Year),
lead(Final_Mlg,9) over (partition by Customer order by Year),
lead(Final_Mlg,10) over (partition by Customer order by Year),
lead(Final_Mlg,11) over (partition by Customer order by Year),

lag(Final_Mlg) over (partition by Customer order by Year), 
lag(Final_Mlg,2) over (partition by Customer order by Year),
lag(Final_Mlg,3) over (partition by Customer order by Year),
lag(Final_Mlg,4) over (partition by Customer order by Year),
lag(Final_Mlg,5) over (partition by Customer order by Year),
lag(Final_Mlg,6) over (partition by Customer order by Year),
lag(Final_Mlg,7) over (partition by Customer order by Year),
lag(Final_Mlg,8) over (partition by Customer order by Year),
lag(Final_Mlg,9) over (partition by Customer order by Year),
lag(Final_Mlg,10) over (partition by Customer order by Year),
lag(Final_Mlg,11) over (partition by Customer order by Year)),

Annual_Mlg,
Prev_Mlg,
Next_Mlg

FROM #table2
ORDER BY 
Customer,
Year

示例场景及期望结果 #

CASE 1：年份范围开头存在连续NULL值 #

CASE 2：年份范围结尾存在连续NULL值 #

CASE 3：非NULL值之间存在NULL值 #

MS SQL Server 解决方案 #

使用递归CTE结合窗口函数，能覆盖所有场景并支持已填充值参与后续计算：

完整代码 #

-- 1. 生成所有客户的完整年份序列
WITH AllYears AS (
    SELECT Year = 2009 UNION ALL SELECT Year + 1 FROM AllYears WHERE Year < 2022
),
CustomerYears AS (
    SELECT DISTINCT c.Customer, y.Year
    FROM (SELECT DISTINCT Customer FROM #table2) c
    CROSS JOIN AllYears y
),
-- 2. 关联原始数据，标记前后非NULL值
BaseData AS (
    SELECT 
        cy.Customer,
        cy.Year,
        Original_Mlg = t.Annual_Mlg,
        -- 向前找最近的非NULL值
        PrevValid_Mlg = LAST_VALUE(t.Annual_Mlg) OVER (
            PARTITION BY cy.Customer 
            ORDER BY cy.Year 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) IGNORE NULLS,
        -- 向后找最近的非NULL值
        NextValid_Mlg = FIRST_VALUE(t.Annual_Mlg) OVER (
            PARTITION BY cy.Customer 
            ORDER BY cy.Year 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            ) IGNORE NULLS,
        -- 记录前后非NULL值的年份
        PrevValid_Year = LAST_VALUE(CASE WHEN t.Annual_Mlg IS NOT NULL THEN cy.Year END) OVER (
            PARTITION BY cy.Customer 
            ORDER BY cy.Year 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) IGNORE NULLS,
        NextValid_Year = FIRST_VALUE(CASE WHEN t.Annual_Mlg IS NOT NULL THEN cy.Year END) OVER (
            PARTITION BY cy.Customer 
            ORDER BY cy.Year 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            ) IGNORE NULLS
    FROM CustomerYears cy
    LEFT JOIN #table2 t ON cy.Customer = t.Customer AND cy.Year = t.Year
),
-- 3. 递归填充NULL值
RecursiveFill AS (
    SELECT 
        Customer,
        Year,
        Original_Mlg,
        Final_Mlg = COALESCE(Original_Mlg, PrevValid_Mlg) -- 先填充开头的NULL
    FROM BaseData
    WHERE Year = (SELECT MIN(Year) FROM BaseData WHERE Customer = BaseData.Customer)

    UNION ALL

    SELECT 
        bd.Customer,
        bd.Year,
        bd.Original_Mlg,
        Final_Mlg = CASE
            -- 原始值非空直接用
            WHEN bd.Original_Mlg IS NOT NULL THEN bd.Original_Mlg
            -- 中间NULL，线性插值
            WHEN bd.PrevValid_Year IS NOT NULL AND bd.NextValid_Year IS NOT NULL THEN
                bd.PrevValid_Mlg + (bd.NextValid_Mlg - bd.PrevValid_Mlg) * (bd.Year - bd.PrevValid_Year) / (bd.NextValid_Year - bd.PrevValid_Year)
            -- 结尾NULL，用最后一个有效值
            ELSE (SELECT Final_Mlg FROM RecursiveFill rf WHERE rf.Customer = bd.Customer AND rf.Year = bd.Year - 1)
        END
    FROM BaseData bd
    JOIN RecursiveFill rf ON bd.Customer = rf.Customer AND bd.Year = rf.Year + 1
)
SELECT 
    Customer,
    Year,
    Final_Mlg,
    Original_Mlg
FROM RecursiveFill
ORDER BY Customer, Year
OPTION (MAXRECURSION 100); -- 年份最多14年，递归深度足够

R语言解决方案 #

使用tidyverse处理数据分组，zoo包的na.approx实现线性插值，fill函数处理首尾NULL填充：

完整代码 #

library(tidyverse)
library(zoo)

# 假设原始数据框名为mileage_data
filled_data <- mileage_data %>%
    # 生成每个客户的完整年份序列
    group_by(Customer) %>%
    complete(Year = 2009:2022) %>%
    # 填充开头的NA为第一个非NA值
    fill(Annual_Mlg, .direction = "down") %>%
    # 填充结尾的NA为最后一个非NA值
    fill(Annual_Mlg, .direction = "up") %>%
    # 对中间剩余的NA执行线性插值
    mutate(Final_Mlg = na.approx(Annual_Mlg, Year)) %>%
    ungroup()

# 查看结果
print(filled_data)

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