使用SQL按组扩展并线性插值补全非连续index数值

需求说明 #

现有数据表包含：

• index：非连续整数类型的索引列
• 多列分组字段（示例中为group_col_1、group_col_2）
• value：需插值的数值列

需完成两个操作：

1. 按分组字段分组，补全每组内index的所有中间缺失值
2. 基于每组内相邻的已知value，对缺失值做线性插值计算

示例 #

输入表 #

期望输出表 #

解决方案（Python Pandas实现） #

直接运行以下代码即可实现需求：

import pandas as pd

# 构造示例数据（实际使用时可替换为读取你的数据源）
df = pd.DataFrame({
    'index': [1,3,5,2,4,7],
    'group_col_1': ['A','A','A','B','B','B'],
    'group_col_2': ['A','A','A','C','C','C'],
    'value': [1,5,11,0,8,2]
})

# 定义分组处理函数：补全索引+插值
def process_group(group):
    # 生成当前分组的连续index序列
    full_idx = pd.RangeIndex(group['index'].min(), group['index'].max()+1, name='index')
    # 对齐到完整索引，补全缺失行
    group_full = group.set_index('index').reindex(full_idx)
    # 填充分组字段（补全后分组字段会变为NaN）
    group_full[['group_col_1', 'group_col_2']] = group[['group_col_1', 'group_col_2']].iloc[0]
    # 对value列执行线性插值
    group_full['value'] = group_full['value'].interpolate(method='linear')
    # 重置索引恢复原结构
    return group_full.reset_index()

# 应用分组处理并合并结果
result = df.groupby(['group_col_1', 'group_col_2']).apply(process_group).reset_index(drop=True)

print(result)

代码逻辑说明 #

1. 分组拆分：通过groupby按分组字段切割数据
2. 生成连续索引：获取每组index的最小/最大值，生成完整的整数序列
3. 补全缺失行：用reindex将分组数据对齐到完整索引，自动补全缺失的index行
4. 填充分组字段：补全后分组字段会变为NaN，用分组内首个有效值填充
5. 线性插值：调用interpolate(method='linear')对缺失的value计算插值
6. 合并结果：将所有分组的处理结果合并为最终数据表

解决方案（SQL实现，以PostgreSQL为例） #

如果用SQL处理，可通过生成连续索引、关联原始数据后计算插值：

WITH group_ranges AS (
    -- 获取每个分组的index范围
    SELECT 
        group_col_1,
        group_col_2,
        MIN(index) AS min_idx,
        MAX(index) AS max_idx
    FROM your_table
    GROUP BY group_col_1, group_col_2
),
full_index AS (
    -- 为每个分组生成连续index序列
    SELECT 
        gr.group_col_1,
        gr.group_col_2,
        generate_series(gr.min_idx, gr.max_idx) AS index
    FROM group_ranges gr
),
neighbor_data AS (
    -- 关联原始数据，找到每个index前后最近的已知值
    SELECT 
        fi.group_col_1,
        fi.group_col_2,
        fi.index,
        -- 前一个非空value及对应index
        LAST_VALUE(t.value) OVER (
            PARTITION BY fi.group_col_1, fi.group_col_2
            ORDER BY fi.index
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
        ) AS prev_val,
        LAST_VALUE(t.index) OVER (
            PARTITION BY fi.group_col_1, fi.group_col_2
            ORDER BY fi.index
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
        ) AS prev_idx,
        -- 后一个非空value及对应index
        FIRST_VALUE(t.value) OVER (
            PARTITION BY fi.group_col_1, fi.group_col_2
            ORDER BY fi.index
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW
        ) AS next_val,
        FIRST_VALUE(t.index) OVER (
            PARTITION BY fi.group_col_1, fi.group_col_2
            ORDER BY fi.index
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW
        ) AS next_idx,
        t.value AS original_val
    FROM full_index fi
    LEFT JOIN your_table t 
        ON fi.group_col_1 = t.group_col_1 
        AND fi.group_col_2 = t.group_col_2 
        AND fi.index = t.index
)
-- 计算线性插值并输出结果
SELECT 
    group_col_1,
    group_col_2,
    index,
    CASE
        WHEN original_val IS NOT NULL THEN original_val
        ELSE prev_val + (next_val - prev_val) * (index - prev_idx)::FLOAT / (next_idx - prev_idx)::FLOAT
    END AS value
FROM neighbor_data
ORDER BY group_col_1, group_col_2, index;

SQL逻辑说明 #

1. group_ranges：提取每个分组的index最小/最大值
2. full_index：用generate_series生成每个分组的连续index序列
3. neighbor_data：通过窗口函数，为每个缺失index找到前后最近的已知value和对应index
4. 插值计算：对缺失值应用线性插值公式：prev_val + (next_val - prev_val) * (当前index - prev_idx)/(next_idx - prev_idx)

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