按ID补全缺失年份数据：新增空值行实现方案咨询

先把你的原始数据整理成表格：

下面分两种常用场景给出实现方案：

1. SQL 实现 #

核心思路是先为每个ID生成覆盖其所有年份范围的连续年份序列，再和原始表左连接补全缺失行。

步骤： #

• 第一步：获取每个ID的最小年份和最大年份，确定需要生成的年份区间。
• 第二步：用递归CTE生成每个ID对应的所有连续年份。
• 第三步：将生成的完整年份序列与原始表按id和year左连接，缺失的value会自动填充为NULL。

示例代码（以PostgreSQL/MySQL 8.0+为例）： #

WITH id_year_ranges AS (
    SELECT 
        id,
        MIN(year) AS min_year,
        MAX(year) AS max_year
    FROM your_table
    GROUP BY id
),
continuous_years AS (
    SELECT 
        id,
        min_year AS year
    FROM id_year_ranges
    UNION ALL
    SELECT 
        cy.id,
        cy.year + 1
    FROM continuous_years cy
    JOIN id_year_ranges yr ON cy.id = yr.id
    WHERE cy.year < yr.max_year
)
SELECT 
    cy.id,
    cy.year,
    t.value
FROM continuous_years cy
LEFT JOIN your_table t ON cy.id = t.id AND cy.year = t.year
ORDER BY cy.id, cy.year;

如果是低版本MySQL，可借助数字辅助表生成连续年份，逻辑和上述一致。

2. Python Pandas 实现 #

核心是通过重新索引的方式，为每个ID补全年份区间内的所有行。

步骤： #

• 第一步：按id分组，获取每个组的年份范围，生成连续年份序列。
• 第二步：构建包含所有id和对应年份的MultiIndex。
• 第三步：用这个完整索引对原始数据重新索引，缺失的value会填充为NaN（对应SQL的NULL）。

示例代码： #

import pandas as pd

# 原始数据
data = {
    'id': [1,1,1,2,2,2],
    'year': [2015,2016,2018,2010,2015,2020],
    'value': [200,3000,500,455,678,100]
}
df = pd.DataFrame(data)

# 生成每个ID的完整年份序列
full_index = []
for id_val, group in df.groupby('id'):
    min_year = group['year'].min()
    max_year = group['year'].max()
    years = pd.RangeIndex(min_year, max_year + 1)
    full_index.extend([(id_val, year) for year in years])

# 转换为MultiIndex并重新索引
full_index = pd.MultiIndex.from_tuples(full_index, names=['id', 'year'])
result_df = df.set_index(['id', 'year']).reindex(full_index).reset_index()

# 若需要将NaN转为Python原生None（适配数据库导出场景）
result_df['value'] = result_df['value'].where(pd.notnull(result_df['value']), None)

print(result_df)

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