如何在PostgreSQL中用插值填充分组月度累计计数器缺失值？

需求说明 #

按ID分组，将月度累计计数器中的NULL值（示例中用“-”表示）替换为前后最近非空值之间的线性插值结果，实现均匀递增的填充效果。

原数据表 #

期望结果 #

解决方案 #

假设数据表名为monthly_counters，id为分组字段，month为月份字段（建议转为日期类型），counter为计数器字段（NULL表示缺失值），可通过以下SQL实现：

WITH grouped_data AS (
    SELECT
        id,
        month,
        counter,
        -- 获取当前行之前最近的非空计数器值
        LAST_VALUE(counter) FILTER (WHERE counter IS NOT NULL) OVER (
            PARTITION BY id
            ORDER BY month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS prev_non_null_counter,
        -- 获取当前行之后最近的非空计数器值
        FIRST_VALUE(counter) FILTER (WHERE counter IS NOT NULL) OVER (
            PARTITION BY id
            ORDER BY month
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_non_null_counter,
        -- 获取当前行之前最近非空值对应的月份
        LAST_VALUE(month) FILTER (WHERE counter IS NOT NULL) OVER (
            PARTITION BY id
            ORDER BY month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS prev_non_null_month,
        -- 获取当前行之后最近非空值对应的月份
        FIRST_VALUE(month) FILTER (WHERE counter IS NOT NULL) OVER (
            PARTITION BY id
            ORDER BY month
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_non_null_month
    FROM monthly_counters
    -- 若month是文本类型，先转为日期：比如 month || '-01'::date AS month
)
SELECT
    id,
    month,
    CASE
        WHEN counter IS NOT NULL THEN counter
        ELSE
            prev_non_null_counter + 
            (next_non_null_counter - prev_non_null_counter) * 
            -- 计算当前行在插值区间中的比例
            (DATE_PART('month', age(month, prev_non_null_month))::numeric / 
             DATE_PART('month', age(next_non_null_month, prev_non_null_month))::numeric)
    END AS counter
FROM grouped_data
ORDER BY id, month;

代码解释 #

1. CTE grouped_data：

   • 通过LAST_VALUE(...) FILTER (...)按ID分组、月份排序，筛选出当前行及之前最后一个非空的计数器值和对应月份。
   • 通过FIRST_VALUE(...) FILTER (...)筛选出当前行及之后第一个非空的计数器值和对应月份。
   • FILTER (WHERE counter IS NOT NULL)确保只处理非空值，避免NULL干扰计算。

2. 主查询插值逻辑：

   • 若原始计数器值非空，直接保留。
   • 若为NULL，用线性插值公式计算：前值 + (后值 - 前值) * 当前月份在区间中的占比，实现前后非空值之间的均匀递增填充。

注意事项 #

• 如果month是文本格式（如'2023-09'），需要先转换为日期类型，比如month || '-01'::date，才能使用age()函数计算月份间隔。
• 此方案仅处理前后均有非空值的缺失行，若存在分组开头/结尾的连续NULL，可根据需求补充逻辑（比如用第一个非空值填充开头，最后一个非空值填充结尾）。

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