如何用前工作日余额填充缺失周末行并按客户日期范围补0

问题 #

现有数据表（字段：date、customer_id、balance）：

date        customer_id      balance
2022-07-01      1            100
2022-07-04      1            150
2022-07-05      1            200
     .          1             .
     .          1             .
2022-07-31      1            650
2022-07-07      2            200
2022-07-08      2            300
2022-07-11      2            400
     .          2             .
     .          2             .
2022-07-19      2            750

需求：

• 为每个客户填充缺失日期（周末/节假日）的balance值，填充规则为使用前一个工作日的balance
• 仅在客户自身的日期范围内执行填充：即每个客户的日期范围是其记录中的最小date到最大date，超出该范围的日期balance设为0

预期输出表：

date       customer_id      balance
 2022-07-01      1           100
 2022-07-02      1           100
 2022-07-03      1           100
 2022-07-04      1           150
 2022-07-05      1           200
      .          1            .
      .          1            .
 2022-07-31      1           650
 2022-07-01      2            0
 2022-07-02      2            0
      .          2            .
      .          2            .
 2022-07-07      2           200
 2022-07-08      2           300
 2022-07-09      2           300
 2022-07-10      2           300
 2022-07-11      2           400
      .          2            .
      .          2            .
 2022-07-19      2           750
 2022-07-20      2            0
      .          2            .
      .          2            .
 2022-07-31      2            0

已知常规日历表交叉连接方案无法适配，需提供可行实现方法。

解决方案 #

以下是基于SQL的通用实现方案，以PostgreSQL语法为例，可根据不同数据库调整细节：

-- 1. 生成业务所需的全量日期区间（这里是2022-07-01至2022-07-31）
WITH calendar AS (
    SELECT '2022-07-01'::DATE AS cal_date
    UNION ALL
    SELECT cal_date + INTERVAL '1 day'
    FROM calendar
    WHERE cal_date < '2022-07-31'
),
-- 2. 计算每个客户的有效日期范围
customer_date_ranges AS (
    SELECT 
        customer_id,
        MIN(date) AS min_date,
        MAX(date) AS max_date
    FROM original_table
    GROUP BY customer_id
),
-- 3. 生成每个客户对应所有日期的组合
customer_calendar AS (
    SELECT 
        c.cal_date,
        cr.customer_id,
        cr.min_date,
        cr.max_date
    FROM calendar c
    CROSS JOIN customer_date_ranges cr
),
-- 4. 关联原始数据，准备填充
joined_data AS (
    SELECT 
        cc.cal_date AS date,
        cc.customer_id,
        ot.balance,
        cc.min_date,
        cc.max_date
    FROM customer_calendar cc
    LEFT JOIN original_table ot 
        ON cc.cal_date = ot.date 
        AND cc.customer_id = ot.customer_id
)
-- 5. 最终填充逻辑：区间内用前值填充，区间外返回0
SELECT 
    date,
    customer_id,
    CASE 
        WHEN date < min_date OR date > max_date THEN 0
        ELSE COALESCE(
            LAST_VALUE(balance IGNORE NULLS) OVER (
                PARTITION BY customer_id 
                ORDER BY date 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ), 0
        )
    END AS balance
FROM joined_data
ORDER BY customer_id, date;

关键说明 #

• 若有现成的系统日历表，可直接替换递归生成的calendar CTE，提升性能
• LAST_VALUE(balance IGNORE NULLS)会自动跳过缺失日期的NULL值，取最近的非NULL余额填充，完美适配周末/节假日的缺失场景
• 外层CASE语句严格控制填充范围，超出客户自身日期区间的日期直接返回0

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