Oracle数据回填与前填问题：如何基于ids表补全t1缺失数据？

需求说明 #

有两张表t1和ids：t1包含按myid排序的myid与my_value字段，ids存储所有有效id列表。t1存在数据缺失，需按以下规则补全：

• 优先使用第一个可用数据填充（即t1中最早出现的有效值）
• 若ID大于t1中最大myid，则使用最新有效数据（即t1中最后一条记录的值）

表结构与测试数据 #

create table t1 
(myid int, 
 my_value varchar(10));

insert into t1 values(3, 'Value 3');
insert into t1 values(4, 'Value 4');
insert into t1 values(4, 'Value 5');

create table ids
(
  mids int
);

insert into ids values(1);
insert into ids values(2);
insert into ids values(3);
insert into ids values(4);
insert into ids values(5);
insert into ids values(6);

期望补全结果 #

当前尝试的Merge语句（无法得到正确结果） #

Merge INTO t1
  Using (SELECT ids.mids, t1.values FROM t1 Right JOIN ids ON t1.myid = ids.mids) y
  ON (t1.myid = y.mids)
  WHEN NOT MATCHED THEN
    INSERT (myid, my_value)
    VALUES (y.mids, y.values);

解决方案 #

原Merge语句仅做了简单右连接，缺失ID对应的my_value为NULL，未实现填充逻辑。以下是修正后的方案：

逻辑说明 #

1. 获取t1的两个关键值：
   • 第一个可用值：t1中最小myid对应的第一条记录值
   • 最新有效数据：t1中最后一条记录的值
2. 筛选出ids中存在但t1里没有的ID
3. 根据ID所在区间匹配填充值，再插入t1

完整SQL #

WITH t1_key_values AS (
    -- 获取t1最小myid及对应的第一个可用值
    SELECT MIN(myid) AS min_myid, FIRST_VALUE(my_value) OVER (ORDER BY myid) AS first_value
    FROM t1
),
t1_latest_value AS (
    -- 获取t1最新的有效数据，指定窗口范围确保取到最后一条
    SELECT LAST_VALUE(my_value) OVER (ORDER BY myid ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_value
    FROM t1
),
missing_ids AS (
    -- 找出需要补全的缺失ID
    SELECT mids
    FROM ids
    WHERE mids NOT IN (SELECT DISTINCT myid FROM t1)
),
insert_data AS (
    -- 生成需要插入的记录及对应填充值
    SELECT 
        mids AS myid,
        CASE
            WHEN mids < (SELECT min_myid FROM t1_key_values) THEN (SELECT first_value FROM t1_key_values)
            WHEN mids > (SELECT MAX(myid) FROM t1) THEN (SELECT latest_value FROM t1_latest_value)
            -- 处理中间缺失的ID：取小于当前ID的最大myid对应的最新值
            ELSE (
                SELECT TOP 1 my_value 
                FROM t1 
                WHERE myid < mids 
                ORDER BY myid DESC, (SELECT COUNT(*) FROM t1 t WHERE t.myid = t1.myid AND t.my_value <= t1.my_value) DESC
            )
        END AS my_value
    FROM missing_ids
)
MERGE INTO t1
USING insert_data y
ON t1.myid = y.myid
WHEN NOT MATCHED THEN
    INSERT (myid, my_value)
    VALUES (y.myid, y.my_value);

关键细节 #

• LAST_VALUE必须指定窗口范围，否则默认仅取当前行到起始行的数据，无法获取最后一条记录的值
• 中间缺失ID的处理逻辑，确保覆盖所有可能的缺失场景

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