PostgreSQL基于匹配条件与时间范围实现表UPSERT操作问询

现有Table_1表数据 #

插入/更新规则 #

需逐条处理待插入数据，满足以下规则：

• 若bid与first_nid匹配，且待插入数据的first_datetime与表中对应记录的first_datetime相差在30分钟范围内，同时表中该记录的second_nid为NULL，则更新该记录的second_nid、second_datetime、third_nid、third_datetime（待插入数据有值时），否则忽略此条数据；
• 若bid与first_nid匹配，且待插入数据的first_datetime与表中对应记录的first_datetime相差在30分钟范围内，但表中该记录的second_nid不为NULL，则直接忽略此条数据；
• 若bid与first_nid匹配，但待插入数据的first_datetime与表中所有同bid+first_nid的记录的first_datetime相差均超过30分钟，则执行插入操作，新增一条记录。

待插入的原始数据 #

预期最终表数据 #

建表与初始数据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, 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', NULL, NULL, NULL, NULL)
    , ('17', '6022', '2023-04-15 08:29:00', '6005', '2023-04-15 08:31:00', '6004', '2023-04-15 08:32:00')
;

实现条件化更新与插入的SQL #

第一步：执行更新操作（处理规则1） #

UPDATE table_1 t
SET 
    second_nid = COALESCE(s.second_nid, t.second_nid),
    second_datetime = COALESCE(s.second_datetime, t.second_datetime),
    third_nid = COALESCE(s.third_nid, t.third_nid),
    third_datetime = COALESCE(s.third_datetime, t.third_datetime)
FROM (
    -- 待插入的原始数据集合
    VALUES
        ('06', '6022', '2023-04-10 15:55:53', NULL, NULL, NULL, NULL),
        ('27', '6022', '2023-04-15 09:35:00', '6005', '2023-04-15 09:36:28', '6004', '2023-04-15 09:37:28'),
        ('17', '6022', '2023-04-15 08:28:00', NULL, NULL, NULL, NULL)
) AS s(bid, first_nid, first_datetime, second_nid, second_datetime, third_nid, third_datetime)
WHERE 
    t.bid = s.bid
    AND t.first_nid = s.first_nid
    -- 判断时间差是否在30分钟内（转换为秒计算）
    AND ABS(EXTRACT(EPOCH FROM (t.first_datetime - s.first_datetime))) <= 30 * 60
    -- 仅更新表中second_nid为NULL的记录
    AND t.second_nid IS NULL;

第二步：执行插入操作（处理规则3，自动过滤规则2的情况） #

INSERT INTO table_1 (bid, first_nid, first_datetime, second_nid, second_datetime, third_nid, third_datetime)
SELECT 
    s.bid, s.first_nid, s.first_datetime, s.second_nid, s.second_datetime, s.third_nid, s.third_datetime
FROM (
    -- 待插入的原始数据集合
    VALUES
        ('06', '6022', '2023-04-10 15:55:53', NULL, NULL, NULL, NULL),
        ('27', '6022', '2023-04-15 09:35:00', '6005', '2023-04-15 09:36:28', '6004', '2023-04-15 09:37:28'),
        ('17', '6022', '2023-04-15 08:28:00', NULL, NULL, NULL, NULL)
) AS s(bid, first_nid, first_datetime, second_nid, second_datetime, third_nid, third_datetime)
-- 仅插入不存在同bid+first_nid且时间在30分钟内的记录
WHERE NOT EXISTS (
    SELECT 1
    FROM table_1 t
    WHERE 
        t.bid = s.bid
        AND t.first_nid = s.first_nid
        AND ABS(EXTRACT(EPOCH FROM (t.first_datetime - s.first_datetime))) <= 30 * 60
);

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