Oracle 19c MERGE语句实现table1分组最新日期行同步至table2

业务需求 #

基于Oracle 19c环境实现跨表同步：

• 仅同步table1内每个分组下log_date最新的行到table2
• 后续table1中对应分组有新增、更新记录时，下次执行MERGE语句自动更新table2中匹配分组的对应行，无需额外处理

测试对象结构与示例数据 #

测试用表、序列与示例数据的初始化语句如下，可直接在环境执行：

CREATE TABLE table1 (
  tab1_id   NUMBER(2)
, log_date     DATE
, group_name  VARCHAR2(10)
, text2     VARCHAR2(10)
, text3     VARCHAR2(10)
, CONSTRAINT pk_tab1 PRIMARY KEY (tab1_id)
);

INSERT INTO table1 VALUES (1, '10-FEB-21', 'group1', 'textrow1', 'textrow1');
INSERT INTO table1 VALUES (2, '09-JAN-21', 'group1', 'textrow2', 'textrow2');
INSERT INTO table1 VALUES (3, '09-MAR-21', 'group1', 'textrow3', 'textrow3');

INSERT INTO table1 VALUES (4, '08-MAR-21', 'group2', 'textrow4', 'textrow4');
INSERT INTO table1 VALUES (5, '08-JAN-21', 'group2', 'textrow5', 'textrow5');
INSERT INTO table1 VALUES (6, '10-FEB-21', 'group2', 'textrow6', 'textrow6');

INSERT INTO table1 VALUES (7, '10-MAR-21', 'group3', 'textrow7', 'textrow7');
INSERT INTO table1 VALUES (8, '05-JAN-21', 'group3', 'textrow8', 'textrow8');
INSERT INTO table1 VALUES (9, '05-FEB-21', 'group3', 'textrow9', 'textrow9');
COMMIT;

CREATE TABLE table2 (
  tab2_id   NUMBER(2)
, tab1_id   NUMBER(2) UNIQUE NOT NULL
, log_date     DATE
, group_name     VARCHAR2(10)
, text2     VARCHAR2(10)
, text3     VARCHAR2(10)
, CONSTRAINT pk_tab2 PRIMARY KEY (tab2_id)
);

CREATE SEQUENCE  seq_table2_id  MINVALUE 1 MAXVALUE 99 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE;

预期同步结果 #

按规则同步完成后，table2的存储效果如下（可忽略序列自动生成的tab2_id取值差异）：

TAB2_ID    TAB1_ID LOG_DATE  GROUP_NAME TEXT2      TEXT3
 ---------- ---------- --------- ---------- ---------- ----------
       1          3 09-MAR-21 group1     textrow3   textrow3
       2          4 08-MAR-21 group2     textrow4   textrow4
       3          7 10-MAR-21 group3     textrow7   textrow7

已验证可用的MERGE实现语句 #

以下语句可直接满足同步需求：

MERGE INTO table2 t2
USING(
  WITH T1_TO_MERGE AS (
  SELECT
      row_number() OVER(
          PARTITION BY group_name
          ORDER BY log_date DESC
      ) row_num,
      tab1_id,
      log_date,
      group_name,
      text2,
      text3
  FROM
      table1
  )
  SELECT * FROM T1_TO_MERGE WHERE row_num = 1
     ) t1
      ON(t2.group_name = t1.group_name)
WHEN MATCHED THEN UPDATE
SET t2.tab1_id =  t1.tab1_id,
    t2.log_date = t1.log_date,
    t2.text2 = t1.text2,
    t2.text3 = t1.text3
WHEN NOT MATCHED THEN
INSERT(
    t2.tab2_id,
    t2.tab1_id,
    t2.log_date,
    t2.group_name,
    t2.text2,
    t2.text3)
VALUES
    (seq_table2_id.NEXTVAL,
    t1.tab1_id,
    t1.log_date,
    t1.group_name,
    t1.text2,
    t1.text3);
COMMIT;

核心逻辑说明：

• 子查询通过窗口函数按group_name分区，对log_date倒序排序，取每个分区第1行即为对应分组的最新记录
• MERGE以group_name作为匹配键，保证每个分组在table2中仅存一条有效记录
• 匹配到已有分组时全量更新业务字段；未匹配到新分组时，通过序列生成主键完成插入

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