PostgreSQL跨表更新：按条件填充空列并限制2小时时间范围

原始数据表 #

Table_1 #

Table_2 #

注意事项 #

• second_nid与second_datetime为一组，third_nid与third_datetime为一组，两组列在两张表中要么同时有值，要么同时为空。

更新规则 #

基于bid关联，从Table_2更新Table_1的second_nid、second_datetime、third_nid、third_datetime四列，需满足以下条件：

1. 条件1：若Table_1中上述四列均为空，且Table_2的second_datetime、third_datetime在Table_1的first_datetime的2小时范围内，则更新这四列（例如bid=17）；
2. 条件2：若Table_1中second_nid、second_datetime已有值，且Table_2的third_datetime在Table_1的first_datetime的2小时范围内，则仅更新third_nid、third_datetime（例如bid=06）。

更新后预期的Table_1 #

表创建SQL语句 #

CREATE TABLE table_1 (
    bid character varying(2) NOT NULL,
    first_nid character varying(5) NOT NULL,
    first_datetime timestamp without time zone NOT NULL,
    second_nid character varying(5),
    second_datetime timestamp without time zone,
    third_nid character varying(5),
    third_datetime timestamp without time zone,
    PRIMARY KEY(bid, first_nid, first_datetime)
);

INSERT INTO table_1 VALUES
      ('06', '6022', '2023-04-10 14:51:53', '', NULL, '', NULL)
    , ('20', '6022', '2023-04-10 14:13:18', '6005', '2023-04-10 14:14:24', '6004', '2023-04-10 14:14:59')
    , ('27', '6022', '2023-04-15 09:29:00', '6005', '2023-04-15 09:30:28', '', NULL)
    , ('17', '6022', '2023-04-15 08:29:00', '', NULL, '', NULL)
    ;
    
CREATE TABLE table_2 (
    bid character varying(2) NOT NULL,
    second_nid character varying(5) NOT NULL,
    second_datetime timestamp without time zone NOT NULL,
    third_nid character varying(5),
    third_datetime timestamp without time zone,
    PRIMARY KEY(bid, second_nid, second_datetime)
);

INSERT INTO table_2 VALUES
      ('06', '6005', '2023-04-10 14:52:24', '', NULL)
    , ('06', '6005', '2023-04-10 17:52:24', '', NULL)
    , ('27', '6005', '2023-04-15 09:35:01', '6004', '2023-04-15 09:41:13')
    , ('17', '6005', '2023-04-15 08:45:01', '6004', '2023-04-15 09:10:13')
    , ('17', '6005', '2023-04-15 11:45:01', '6004', '2023-04-15 11:10:13')
    ;

解决方案：更新SQL语句 #

WITH valid_updates AS (
    SELECT 
        t1.bid,
        t1.first_nid,
        t1.first_datetime,
        t2.second_nid,
        t2.second_datetime,
        t2.third_nid,
        t2.third_datetime,
        CASE 
            WHEN t2.second_datetime BETWEEN t1.first_datetime AND t1.first_datetime + INTERVAL '2 hours' 
                 AND (t2.third_datetime IS NULL OR t2.third_datetime BETWEEN t1.first_datetime AND t1.first_datetime + INTERVAL '2 hours')
            THEN TRUE
            ELSE FALSE
        END AS is_valid
    FROM table_1 t1
    JOIN table_2 t2 ON t1.bid = t2.bid
)
UPDATE table_1 t1
SET 
    second_nid = CASE 
                    WHEN t1.second_nid IS NULL OR t1.second_nid = '' THEN vu.second_nid 
                    ELSE t1.second_nid 
                 END,
    second_datetime = CASE 
                        WHEN t1.second_datetime IS NULL THEN vu.second_datetime 
                        ELSE t1.second_datetime 
                     END,
    third_nid = CASE 
                    WHEN (t1.second_nid IS NOT NULL AND t1.second_nid != '' AND vu.is_valid) 
                         OR (t1.second_nid IS NULL OR t1.second_nid = '' AND vu.is_valid)
                    THEN vu.third_nid 
                    ELSE t1.third_nid 
                 END,
    third_datetime = CASE 
                        WHEN (t1.second_datetime IS NOT NULL AND vu.is_valid) 
                             OR (t1.second_datetime IS NULL AND vu.is_valid)
                        THEN vu.third_datetime 
                        ELSE t1.third_datetime 
                     END
FROM valid_updates vu
WHERE 
    t1.bid = vu.bid
    AND t1.first_nid = vu.first_nid
    AND t1.first_datetime = vu.first_datetime
    AND vu.is_valid
    AND vu.second_datetime = (SELECT MIN(second_datetime) FROM valid_updates WHERE bid = vu.bid AND is_valid = TRUE);

逻辑说明 #

1. 用CTE valid_updates筛选出Table_2中符合2小时时间范围的有效记录；
2. 更新时区分两种场景：
   • 若Table_1的second组为空，则同步更新second和third的所有列；
   • 若Table_1的second组已有值，则仅更新third组列；
3. 通过子查询确保每个bid只取最早的有效更新记录，避免同一bid多条符合条件记录导致的重复更新。

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