拉链表加工示例-oracle版

--1.创建示例表
CREATE TABLE PERSON_INFO
(
  USERID    NUMBER NOT NULL,
  USER_NAME VARCHAR2(32),
  TEL       VARCHAR2(20)
);
COMMENT ON TABLE PERSON_INFO
  IS '人员信息表(每日或全量)';
COMMENT ON COLUMN PERSON_INFO.USERID
  IS '人员ID';
COMMENT ON COLUMN PERSON_INFO.USER_NAME
  IS '姓名';
COMMENT ON COLUMN PERSON_INFO.TEL
  IS '电话';
ALTER TABLE PERSON_INFO
  ADD CONSTRAINT PK_PERSON_INFO PRIMARY KEY (USERID)
  USING INDEX ;

CREATE TABLE PERSON_INFO_HIS
( ETL_START_DATE CHAR(8) NOT NULL,
  ETL_END_DATE   CHAR(8) NOT NULL,
  ETL_FLAG       VARCHAR2(2) NOT NULL,
  USERID         NUMBER NOT NULL,
  USER_NAME      VARCHAR2(32),
  TEL            VARCHAR2(20)
);
COMMENT ON TABLE PERSON_INFO_HIS
  IS '人员信息表(历史拉链表)';
COMMENT ON COLUMN PERSON_INFO_HIS.ETL_START_DATE
  IS '数据生效日期';
COMMENT ON COLUMN PERSON_INFO_HIS.ETL_END_DATE
  IS '数据失效日期';
COMMENT ON COLUMN PERSON_INFO_HIS.ETL_FLAG
  IS '数据变化标识(0:初始化,1:新增,2:修改,03:初始化数据删除,13:新增数据删除,23:修改数据删除)';
COMMENT ON COLUMN PERSON_INFO_HIS.USERID
  IS '人员ID';
COMMENT ON COLUMN PERSON_INFO_HIS.USER_NAME
  IS '姓名';
COMMENT ON COLUMN PERSON_INFO_HIS.TEL
  IS '电话';
ALTER TABLE PERSON_INFO_HIS
  ADD CONSTRAINT PK_PERSON_INFO_HIS PRIMARY KEY (ETL_START_DATE, ETL_END_DATE, USERID)
  USING INDEX ;
  
CREATE TABLE TMP_PERSON_INFO_HIS
( ETL_START_DATE CHAR(8) NOT NULL,
  ETL_END_DATE   CHAR(8) NOT NULL,
  ETL_FLAG       VARCHAR2(2) NOT NULL,
  USERID         NUMBER NOT NULL,
  USER_NAME      VARCHAR2(32),
  TEL            VARCHAR2(20)
);
COMMENT ON TABLE TMP_PERSON_INFO_HIS
  IS '人员信息表——临时表';
COMMENT ON COLUMN TMP_PERSON_INFO_HIS.ETL_START_DATE
  IS '数据生效日期';
COMMENT ON COLUMN TMP_PERSON_INFO_HIS.ETL_END_DATE
  IS '数据失效日期';
COMMENT ON COLUMN TMP_PERSON_INFO_HIS.ETL_FLAG
  IS '数据变化标识(0:初始化,1:新增,2:修改,03:初始化数据删除,13:新增数据删除,23:修改数据删除)';
COMMENT ON COLUMN TMP_PERSON_INFO_HIS.USERID
  IS '人员ID';
COMMENT ON COLUMN TMP_PERSON_INFO_HIS.USER_NAME
  IS '姓名';
COMMENT ON COLUMN TMP_PERSON_INFO_HIS.TEL
  IS '电话';
--2.添加测试数据
INSERT INTO PERSON_INFO VALUES(1,'张三','123');
INSERT INTO PERSON_INFO VALUES(2,'李四','123');
INSERT INTO PERSON_INFO VALUES(3,'王五','123');
INSERT INTO PERSON_INFO VALUES(4,'赵六','123');
COMMIT;
--3.拉链表初始化
INSERT INTO PERSON_INFO_HIS 
SELECT '20200101', '99991231', 0, A.USERID, A.USER_NAME, A.TEL
  FROM PERSON_INFO A;
COMMIT;
--4.模拟 20210101 数据变化,然后执行步骤5，核对结果
UPDATE PERSON_INFO SET USER_NAME='张三U' WHERE USERID=1;
COMMIT;
DELETE PERSON_INFO WHERE USERID=3;
COMMIT;
INSERT INTO PERSON_INFO VALUES(5,'田七','888999');
COMMIT;
--4.1模拟 20210101 删除后的数据 20220101 再新增回来,然后执行步骤5，核对结果
INSERT INTO PERSON_INFO VALUES(3,'王五','123');
COMMIT;
--5.开始每日拉链表的加工
--5.1.1删除跑批日期及之后的数据(兼容数据重跑)
DELETE PERSON_INFO_HIS WHERE ETL_START_DATE >= '20210101';
COMMIT;
--5.1.2开链(兼容数据重跑)
UPDATE PERSON_INFO_HIS TAG SET TAG.ETL_END_DATE = '99991231',TAG.ETL_FLAG = SUBSTR(TAG.ETL_FLAG,1,1) --还原数据上一次的状态
  WHERE TAG.ETL_END_DATE >= '20210101';
COMMIT;
--5.2清空临时表
TRUNCATE TABLE TMP_PERSON_INFO_HIS;
--5.3加工临时表
INSERT INTO TMP_PERSON_INFO_HIS
  SELECT *
    FROM (SELECT '20210101' ETL_START_DATE,
                 '99991231' ETL_END_DATE,
                 CASE
                   WHEN T1.USERID IS NULL THEN
                    T2.ETL_FLAG || '3' --数据被删除了
                   WHEN T2.USERID IS NULL THEN
                    '1' --是新增数据
                   ELSE
                   --对比除主键外的字段有没有变化
                    DECODE(DBMS_LOB.COMPARE(T1.USER_NAME || T1.TEL,
                                            T2.USER_NAME || T2.TEL),
                           0,
                           '9', --没有变化的数据
                           '2') --被修改的数据
                 END ETL_FLAG,
                 NVL(T1.USERID, T2.USERID) USERID,
                 T1.USER_NAME,
                 T1.TEL
            FROM PERSON_INFO T1
            FULL JOIN (SELECT *
                        FROM PERSON_INFO_HIS
                       WHERE ETL_END_DATE = '99991231') T2
              ON T2.USERID = T1.USERID)
   WHERE ETL_FLAG IN('1','2','03','13','23');
COMMIT;
--5.4加工拉链表
--第一步：恢复数据变化标志及封链修改和删除的数据
UPDATE PERSON_INFO_HIS TAG
   SET TAG.ETL_END_DATE = '20210101',
       TAG.ETL_FLAG     = NVL((SELECT TMP.ETL_FLAG
                                FROM TMP_PERSON_INFO_HIS TMP
                               WHERE TMP.USERID = TAG.USERID
                                 AND TMP.ETL_FLAG IN ('03', '13', '23')
                                 AND TAG.ETL_END_DATE = '99991231'),
                              TAG.ETL_FLAG)
 WHERE TAG.ETL_END_DATE = '99991231'
   AND TAG.USERID IN
       (SELECT T.USERID
          FROM TMP_PERSON_INFO_HIS T
         WHERE T.ETL_FLAG IN ('2', '03', '13', '23'));
COMMIT;
--第二步：插入变化及新增的数据
INSERT INTO PERSON_INFO_HIS
  SELECT T.ETL_START_DATE,
         T.ETL_END_DATE,
         T.ETL_FLAG,
         T.USERID,
         T.USER_NAME,
         T.TEL
    FROM TMP_PERSON_INFO_HIS T
   WHERE T.ETL_FLAG IN ('1', '2');
COMMIT;