使用Pandas按组动态填充缺失行（基于历史记录生成重复行）

需求说明 #

按RecordID分组，填充相邻ChangeDate之间的所有缺失日期行，缺失行的Stage字段沿用前一条记录的Stage值，最后一个Stage对应该分组下的最大日期。

输入数据 #

| RecordID | ChangeDate | Stage     |
|----------|------------|-----------|
| 17764    | 31-08-2021 | New       |
| 17764    | 02-09-2021 | inprogress|
| 17764    | 05-09-2021 | won       |
| 70382    | 04-01-2022 | new       |
| 70382    | 06-01-2022 | hold      |
| 70382    | 07-01-2022 | lost      |

预期输出 #

| RecordID | ChangeDate | Stage     |
|----------|------------|-----------|
| 17764    | 31-08-2021 | New       |
| 17764    | 01-09-2021 | New       |
| 17764    | 02-09-2021 | inprogress|
| 17764    | 03-09-2021 | inprogress|
| 17764    | 04-09-2021 | inprogress|
| 17764    | 05-09-2021 | won       |
| 70382    | 04-01-2022 | new       |
| 70382    | 05-01-2022 | new       |
| 70382    | 06-01-2022 | hold      |
| 70382    | 07-01-2022 | lost      |

解决方案 #

PostgreSQL 实现

使用递归CTE生成缺失日期，同时保留对应Stage值：

WITH ranked_data AS (
    SELECT 
        RecordID,
        TO_DATE(ChangeDate, 'DD-MM-YYYY') AS ChangeDate,
        Stage,
        LEAD(TO_DATE(ChangeDate, 'DD-MM-YYYY')) OVER (PARTITION BY RecordID ORDER BY TO_DATE(ChangeDate, 'DD-MM-YYYY')) AS next_date
    FROM your_table_name
),
recursive_dates AS (
    SELECT 
        RecordID,
        ChangeDate,
        Stage,
        next_date
    FROM ranked_data
    UNION ALL
    SELECT 
        rd.RecordID,
        rd.ChangeDate + INTERVAL '1 day' AS ChangeDate,
        rd.Stage,
        rd.next_date
    FROM recursive_dates rd
    WHERE rd.ChangeDate + INTERVAL '1 day' < rd.next_date
)
SELECT 
    RecordID,
    TO_CHAR(ChangeDate, 'DD-MM-YYYY') AS ChangeDate,
    Stage
FROM recursive_dates
ORDER BY RecordID, ChangeDate;

MySQL 8.0+ 实现

调整日期函数适配MySQL语法：

WITH ranked_data AS (
    SELECT 
        RecordID,
        STR_TO_DATE(ChangeDate, '%d-%m-%Y') AS ChangeDate,
        Stage,
        LEAD(STR_TO_DATE(ChangeDate, '%d-%m-%Y')) OVER (PARTITION BY RecordID ORDER BY STR_TO_DATE(ChangeDate, '%d-%m-%Y')) AS next_date
    FROM your_table_name
),
recursive_dates AS (
    SELECT 
        RecordID,
        ChangeDate,
        Stage,
        next_date
    FROM ranked_data
    UNION ALL
    SELECT 
        rd.RecordID,
        DATE_ADD(rd.ChangeDate, INTERVAL 1 DAY) AS ChangeDate,
        rd.Stage,
        rd.next_date
    FROM recursive_dates rd
    WHERE DATE_ADD(rd.ChangeDate, INTERVAL 1 DAY) < rd.next_date
)
SELECT 
    RecordID,
    DATE_FORMAT(ChangeDate, '%d-%m-%Y') AS ChangeDate,
    Stage
FROM recursive_dates
ORDER BY RecordID, ChangeDate;

代码说明 #

1. ranked_data：将字符串日期转换为日期类型，用LEAD()窗口函数获取同分组下的下一条记录日期，作为当前阶段的结束边界。
2. recursive_dates：递归生成当前日期到下一个日期之间的所有缺失日期，每个日期沿用当前的Stage值。
3. 最终将日期转回原字符串格式，按RecordID和日期排序输出。

注意事项 #

• 替换your_table_name为实际表名。
• 不同数据库的日期转换函数略有差异，需根据使用的数据库调整（比如SQL Server用CONVERT）。

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