如何使用循环处理日期区间中自左连接的余额补全需求

问题背景 #

我有一张tbl_ledger_input表，需要将其中多列数据插入到tbl_ledger_branch表中。但balance列在部分日期可能为空（例如eff_date为4月2日时balance值为空），需填充前一日的balance值。目前已实现单日数据的处理逻辑，但需要扩展为处理一个日期范围内的所有日期。

已实现的单日处理代码 #

基础自左连接查询 #

select
a.ledger_code , a.ref_cur_id , a.ref_branch,a.balance,
b.ledger_code  ,b.ref_cur_id, b.ref_branch ,b.balance

from  (select * from tbl_ledger_input where eff_date = '06-APR-21' ) a  

left join  (select * from tbl_ledger_input where eff_date = '07-APR-21') b

on  a.ledger_code = b.ledger_code  and  

a.ref_cur_id = b.ref_cur_id  and a.ref_branch = b.ref_branch  

where b.ledger_code is null

order by  a.ledger_code , a.ref_cur_id , 
a.ref_branch,b.ledger_code  ,
b.ref_cur_id, b.ref_branch 

补充NVL逻辑的查询 #

select
a.ledger_code , a.ref_cur_id , a.ref_branch,a.balance,
NVL(b.ledger_code , a.ledger_code ) , 
NVL(b.ref_cur_id ,a.ref_cur_id ) , NVL(b.ref_branch ,a.ref_branch ) , 
NVL(b.balance , a.balance )

from  (select * from tbl_ledger_input where eff_date = '06-APR-21' ) a 

 left join  (select * from tbl_ledger_input where eff_date = '07-APR-21') b

on  a.ledger_code = b.ledger_code  and  a.ref_cur_id = b.ref_cur_id  and a.ref_branch = 
b.ref_branch  

where b.ledger_code is null

order by  a.ledger_code , a.ref_cur_id , a.ref_branch,b.ledger_code  ,
b.ref_cur_id, b.ref_branch ;

日期范围批量处理方案 #

不需要用循环，用窗口函数LAG()可以更高效地处理全日期范围的填充需求，同时生成可直接插入目标表的数据：

步骤1：生成全量带填充balance的数据集 #

WITH date_ranged_data AS (
    SELECT
        ledger_code,
        ref_cur_id,
        ref_branch,
        eff_date,
        -- 填充空值：取同分组前一日的balance值
        LAG(balance, 1) OVER (
            PARTITION BY ledger_code, ref_cur_id, ref_branch 
            ORDER BY eff_date
        ) AS filled_balance
    FROM tbl_ledger_input
    -- 筛选需要处理的日期范围，替换成你的起止日期
    WHERE eff_date BETWEEN '01-APR-21' AND '30-APR-21'
),
final_data AS (
    SELECT
        ledger_code,
        ref_cur_id,
        ref_branch,
        eff_date,
        -- 若当前balance不为空则用原值，否则用填充值
        NVL(balance, filled_balance) AS balance
    FROM date_ranged_data
)
-- 先验证数据，确认无误后替换为INSERT语句
SELECT * FROM final_data ORDER BY ledger_code, ref_cur_id, ref_branch, eff_date;

步骤2：插入到目标表 #

将上述查询的验证部分替换为INSERT语句，直接写入tbl_ledger_branch：

WITH date_ranged_data AS (
    SELECT
        ledger_code,
        ref_cur_id,
        ref_branch,
        eff_date,
        LAG(balance, 1) OVER (
            PARTITION BY ledger_code, ref_cur_id, ref_branch 
            ORDER BY eff_date
        ) AS filled_balance
    FROM tbl_ledger_input
    WHERE eff_date BETWEEN '01-APR-21' AND '30-APR-21'
),
final_data AS (
    SELECT
        ledger_code,
        ref_cur_id,
        ref_branch,
        eff_date,
        NVL(balance, filled_balance) AS balance
    FROM date_ranged_data
)
INSERT INTO tbl_ledger_branch (ledger_code, ref_cur_id, ref_branch, eff_date, balance)
SELECT ledger_code, ref_cur_id, ref_branch, eff_date, balance FROM final_data;

扩展说明 #

如果存在连续多天balance为空的情况，LAG()仅取前一日的值，若要递归填充所有连续空值，可改用LAST_VALUE()结合IGNORE NULLS（Oracle 12c+支持）：

LAST_VALUE(balance IGNORE NULLS) OVER (
    PARTITION BY ledger_code, ref_cur_id, ref_branch 
    ORDER BY eff_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS filled_balance

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