基于匹配条件批量替换车辆记录START属性的技术实现需求

原始数据 #

清洗需求 #

当同一VEHICLE下存在replacement非空的记录，且存在同VEHICLE的replacement为空记录（其TO字段等于前者的FROM字段）时，需将所有同VEHICLE、replacement非空的记录的START字段替换为匹配到的replacement为空记录的START字段。

预期结果 #

注：VEHICLE=2的W-Z相关记录不受影响，因为无匹配的replacement非空记录的FROM字段等于它们的TO字段。

PL/SQL实现方案 #

以下是完成数据清洗的PL/SQL匿名块，核心逻辑是先匹配每个VEHICLE下的关联关系，再批量更新符合条件的记录：

DECLARE
    -- 游标：获取需要匹配的替换关系（VEHICLE、目标START值、关联的replacement值）
    CURSOR c_match IS
        SELECT DISTINCT
            t1.vehicle,
            t1.start AS new_start,
            t2.replacement
        FROM
            vehicle_data t1
            JOIN vehicle_data t2 ON t1.vehicle = t2.vehicle
        WHERE
            t1.replacement IS NULL
            AND t2.replacement IS NOT NULL
            AND t1.to = t2.from;
BEGIN
    -- 遍历匹配结果，批量更新对应记录
    FOR rec IN c_match LOOP
        UPDATE vehicle_data
        SET start = rec.new_start
        WHERE
            vehicle = rec.vehicle
            AND replacement = rec.replacement;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('数据清洗完成，已更新符合条件的记录');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('清洗失败，错误信息：' || SQLERRM);
END;
/

代码说明 #

1. 游标c_match：筛选所有符合匹配条件的关联关系——同一VEHICLE下，replacement为空的记录的TO等于replacement非空记录的FROM，同时获取对应的目标START值（空replacement记录的START）和关联的replacement值。
2. 批量更新：遍历游标中的每个匹配项，将同一VEHICLE下所有replacement等于该值的记录的START字段替换为目标START值。
3. 异常处理：确保操作失败时回滚事务，并输出错误信息。

测试数据准备 #

先创建测试表并插入数据：

-- 创建测试表
CREATE TABLE vehicle_data (
    vehicle NUMBER,
    "FROM" VARCHAR2(10),
    "TO" VARCHAR2(10),
    "START" VARCHAR2(10),
    replacement NUMBER
);

-- 插入测试数据
INSERT INTO vehicle_data
WITH a AS (
    SELECT 2 "vehicle", 'A' "FROM", 'B' "TO", 'A' "START", NULL "replacement" FROM dual UNION ALL
    SELECT 2 "vehicle", 'B' "FROM", 'C' "TO", 'A' "START", NULL "replacement" FROM dual UNION ALL
    SELECT 2 "vehicle", 'C' "FROM", 'D' "TO", 'A' "START", NULL "replacement" FROM dual UNION ALL
    SELECT 2 "vehicle", 'D' "FROM", 'E' "TO", 'A' "START", NULL "replacement" FROM dual UNION ALL
    SELECT 2 "vehicle", 'E' "FROM", 'F' "TO", 'E' "START", 123 "replacement" FROM dual UNION ALL
    SELECT 2 "vehicle", 'G' "FROM", 'H' "TO", 'E' "START", 123 "replacement" FROM dual UNION ALL
    SELECT 2 "vehicle", 'I' "FROM", 'J' "TO", 'E' "START", 123 "replacement" FROM dual UNION ALL
    SELECT 2 "vehicle", 'W' "FROM", 'X' "TO", 'W' "START", NULL "replacement" FROM dual UNION ALL
    SELECT 2 "vehicle", 'X' "FROM", 'Y' "TO", 'W' "START", NULL "replacement" FROM dual UNION ALL
    SELECT 2 "vehicle", 'Y' "FROM", 'Z' "TO", 'W' "START", NULL "replacement" FROM dual UNION ALL
    SELECT 3 "vehicle", 'Q1' "FROM", 'Q2' "TO", 'W' "START", NULL "replacement" FROM dual UNION ALL
    SELECT 3 "vehicle", 'Q2' "FROM", 'Q3' "TO", 'W' "START", NULL "replacement" FROM dual
)
SELECT * FROM a;

COMMIT;

执行PL/SQL匿名块后，查询表数据即可看到预期的清洗结果。

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