--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;
拉链表加工示例-oracle版
于 2021-11-24 23:36:02 首次发布