按ID分组补全缺失日期并按规则填充Amount值的实现问题

需求说明 #

你需要对数据集按ID分组补全各组内的缺失日期，新增日期的Amount字段按以下规则填充：

1. 对应ID首次观测值所在月份中，首次观测日期之前的缺失日期，Amount填充为0；例：ID1首次观测在2020年12月29日，则2020年12月1日至28日的Amount均为0
2. 其余缺失日期的Amount填充为该日期前最近一次观测的Amount值
3. 各ID分组的日期范围无需预先指定

原始数据样例 #

期望输出样例 #

Oracle 实现方案 #

实现思路 #

• 自动计算每个ID的日期范围：首次观测日期所在月的第一天为起始，末次观测日期所在月的最后一天为结束，无需预先指定范围
• 递归生成每个ID对应范围内的全部连续日期
• 用窗口函数做缺失值前向填充，再按规则修正首次观测前的值为0

完整SQL代码 #

假设原始表名为transaction_data，日期字段存储格式为dd.mm.yyyy的字符串，代码如下：

WITH id_boundary AS (
    -- 计算每个ID的日期边界和首次观测日期
    SELECT
        ID,
        TRUNC(MIN(TO_DATE(Date, 'dd.mm.yyyy')), 'MM') AS start_date,
        LAST_DAY(MAX(TO_DATE(Date, 'dd.mm.yyyy'))) AS end_date,
        MIN(TO_DATE(Date, 'dd.mm.yyyy')) AS first_obs_date
    FROM transaction_data
    GROUP BY ID
),
full_date_series AS (
    -- 生成每个ID的连续日期序列
    SELECT
        b.ID,
        b.start_date + LEVEL - 1 AS calc_date,
        b.first_obs_date
    FROM id_boundary b
    CONNECT BY LEVEL <= b.end_date - b.start_date + 1
        AND PRIOR ID = b.ID
        AND PRIOR SYS_GUID() IS NOT NULL -- 解决多ID递归的循环报错问题
),
data_join AS (
    -- 关联原始交易数据
    SELECT
        f.ID,
        f.calc_date,
        f.first_obs_date,
        t.Amount
    FROM full_date_series f
    LEFT JOIN transaction_data t
        ON f.ID = t.ID
        AND f.calc_date = TO_DATE(t.Date, 'dd.mm.yyyy')
)
-- 最终按规则填充Amount
SELECT
    ID,
    TO_CHAR(calc_date, 'dd.mm.yyyy') AS Date,
    CASE
        WHEN calc_date < first_obs_date THEN 0
        ELSE LAST_VALUE(Amount IGNORE NULLS) OVER (PARTITION BY ID ORDER BY calc_date)
    END AS Amount
FROM data_join
ORDER BY ID, calc_date;

注意事项 #

如果原始表的日期字段已经是DATE类型，删除代码中的TO_DATE转换逻辑即可直接运行。

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