数据分析师基础

Oracle新手入门数据分析师必知——拉链表（EMP表样例）

一、什么是拉链表？
答：所谓拉链表就是反应一条数据历史变化过程的表。拉链表的程序中涉及两张表，一张是用于取数据的源头表（源表），另一张就是拉链表（这张表里的数据体现了一个数据变化过程）。

二、拉链表的作用、应用是什么？
查看某一个时间点或者时间段的历史快照信息，比如：查看某一个订单在历史某一个时间点的状态，查看某一个用户在过去某一段时间内，更新过几次等等。

三、拉链表的优点、好处？
既能满足反应数据的历史状态，又可以最大程度的节省存储。

四、拉链表的实现过程？
1.1 造一个数据来源表，
1.2 造一个拉链表（相当于目标表）
**2 ** 来源表的数据同步到拉链表
** 2.1 ** 当来源表的数据发生了变化(新增或者修改)
** 2.2 ** 需要再次将发生了变化 (新增或者修改) 的数据同步到拉链表，此时同一个业务主键会有2条或者多条数据（比如7369的员工发生了变化，需要将7369的数据同步到拉链表中，拉链表里就会有2条 7369 的数据）
**3–**修改拉链表中重复数据的失效时间（将原来那条数据的失效时间修改为当前时间）

五、上实操，以EMP表为例

实现拉链表的过程
---1.1--造源表
CREATE TABLE EMP_LALIAN_SOURCE 
AS
SELECT T.*
FROM EMP T;

SELECT * FROM EMP_LALIAN_SOURCE;  --- 源表 

--1.2--创建一张拉链表  
create table EMP_LALIAN_TAG
(
  EMPNO    NUMBER(4)
  ,ENAME    VARCHAR2(10)
  ,JOB      VARCHAR2(9)
  ,MGR      NUMBER(4)
  ,HIREDATE DATE
  ,SAL      NUMBER(7,2)
  ,COMM     NUMBER(7,2)
  ,DEPTNO   NUMBER(2)
  ,START_DATE  DATE
  ,END_DATE    DATE
  ,ACTIVE_FLAG  VARCHAR2(10)
);

-----2--来源表的数据同步到拉链表， --初始化源数据
INSERT INTO EMP_LALIAN_TAG 
SELECT T.EMPNO,
       T.ENAME,
       T.JOB,
       T.MGR,
       T.HIREDATE,
       T.SAL,
       T.COMM,
       T.DEPTNO
       ,T.HIREDATE---入职日期这一天，就是这条数据的开始生效时间
       ,TO_DATE('47121231','YYYYMMDD')
       ,'Y'
FROM EMP_LALIAN_SOURCE T;

COMMIT;

SELECT * FROM EMP_LALIAN_TAG; --检查同步到拉链表中的初始化源数据

---2.1 当来源表的数据发生了变化
UPDATE EMP_LALIAN_SOURCE T SET DEPTNO = 40  WHERE DEPTNO = 20;
UPDATE EMP_LALIAN_SOURCE T SET SAL = 2850  WHERE EMPNO= 7782;
INSERT INTO EMP_LALIAN_SOURCE VALUES(123,'AAA','CLERK',7698,TRUNC(SYSDATE,'DD'),2380,100,20);

COMMIT;

SELECT * FROM EMP_LALIAN_SOURCE; --检查发生变化后的源表中数据

--2.2 将源表中发生了变化的数据同步到拉链表。此时同一个业务主键会有2条数据
--2.2 将新增，或者有改变的最新数据写入到拉链表  
INSERT INTO EMP_LALIAN_TAG
SELECT T.EMPNO,
       T.ENAME,
       T.JOB,
       T.MGR,
       T.HIREDATE,
       T.SAL,
       T.COMM,
       T.DEPTNO
       ,TRUNC(SYSDATE,'DD') AS START_DATE ---以当前系统日期为开始日期
       ,TO_DATE('47121231','YYYYMMDD') AS END_DATE
       ,'Y'  ACTIVE_FLAG
FROM  EMP_LALIAN_SOURCE T 
WHERE NOT EXISTS (	---更新后源表中不存在于拉链表中的数据（**更新的数据**）
				 SELECT 1
                 FROM EMP_LALIAN_TAG T1
                 WHERE T1.END_DATE = TO_DATE('4712-12-31','YYYY-MM-DD')
                 AND T.EMPNO = T1.EMPNO
                 AND T1.ENAME = T.ENAME
                 AND T.JOB = T1.JOB 
                 AND T.HIREDATE = T1.HIREDATE
                 AND NVL(T.MGR,0) = NVL(T1.MGR,0) 
                 AND T.SAL = T1.SAL 
                 AND NVL(T.COMM,'1') = NVL(T1.COMM,'1') 
                 AND T.DEPTNO = T1.DEPTNO 
                 ); 
COMMIT;

SELECT * FROM EMP_LALIAN_TAG; ---检查同步更新后的拉链表中数据

---3 修改拉链表中重复数据的失效时间
UPDATE EMP_LALIAN_TAG T 
SET T.END_DATE = TRUNC(SYSDATE,'DD') 
    ,T.ACTIVE_FLAG = 'N' ---修改结束日期为当前系统日期并标为失效
WHERE  T.END_DATE = TO_DATE('4712-12-31','YYYY-MM-DD')
AND EXISTS (	--找出变化了的数据
		   SELECT 1
           FROM EMP_LALIAN_SOURCE T2
           WHERE T.EMPNO = T2.EMPNO 
              AND (
              		T.ENAME <> T2.ENAME 
              		OR T.JOB <> T2.JOB 
              		OR NVL(T.MGR,0) <> NVL(T2.MGR,0) 
              		OR T.SAL <> T2.SAL 
                    OR NVL(T.COMM,'1') <> NVL(T2.COMM,'1') 
                    OR T.DEPTNO <> T2.DEPTNO 
                    OR T.HIREDATE <> T2.HIREDATE
                   ) 
           );

COMMIT;

---4 验证数据是否发生了改变
SELECT * FROM EMP_LALIAN_TAG;
SELECT * FROM EMP_LALIAN_SOURCE;  --- 源表  

附加一个拉链表练习，看自己掌握了没

题目：
用以下代码中的学生表 Student 作为拉链表的源表，
创建一个 拉链表 Student_TAG。

/*******学生 student表******/
create table student(
sno varchar2(10) ,
sname varchar2(20),
sage number(2),
ssex varchar2(5),
birthday date
);

/*******初始化学生表的数据******/
insert into student values ('s001','张芃',FLOOR(months_between(SYSDATE,date '2000-3-5')/12),'男',date '2000-3-5');
insert into student values ('s002','李蕤',FLOOR(months_between(SYSDATE,date '2001-2-3')/12),'男',date '2001-2-3');
insert into student values ('s003','吴筱莜',FLOOR(months_between(SYSDATE,date '2002-5-8')/12),'男',date '2002-5-8');
insert into student values ('s004','琴沁',FLOOR(months_between(SYSDATE,date '2000-6-15')/12),'女',date '2000-6-15');
insert into student values ('s005','王郦珩',FLOOR(months_between(SYSDATE,date '2000-8-12')/12),'女',date '2000-8-12');
insert into student values ('s006','李薄骞',FLOOR(months_between(SYSDATE,date '2001-9-20')/12),'男',date '2001-9-20');
insert into student values ('s007','刘昱音',FLOOR(months_between(SYSDATE,date '2002-10-5')/12),'男',date '2002-10-5');
insert into student values ('s008','萧戎翰',FLOOR(months_between(SYSDATE,date '2003-6-1')/12),'女',date '2003-6-1');
insert into student values ('s009','陈萧晓妍',FLOOR(months_between(SYSDATE,date '2001-1-15')/12),'女',date '2001-1-15');
insert into student values ('s010','陈媺珈',FLOOR(months_between(SYSDATE,date '2001-1-9')/12),'女',date '2001-1-9');
commit;

注：练习题目还是比较简单的，想要答案的同学可以评论区留言，一起进步

**

六、拉链表总结

按照下面的文字，回忆一下自己写的代码，复盘一下怎么去实现拉链表

**
–拉链表的初始化 就是 将源表的数据 写到 拉链表
实现拉链表的过程
1–造一个数据来源表，一个拉链表（相当于目标表）
2–来源表的数据同步到拉链表
2.1 当来源表的数据发生了变化
2.2 将发生了变化的数据同步到拉链表
3–修改拉链表中重复数据的失效时间（将原来那条数据的失效时间修改为当前时间）并加有效标识

七、打赏环节
觉得这篇博文思路清晰、结构明朗，对你的学习有帮助的话，鼓励小马一帮辣条叭_爱你们~~~