# 6 关于数据仓库维度数据处理的方法探究系列——缓慢变化维处理——记录最新记录及上

3.3.3记录最新记录及上一次历史（Type 3 Dimension -- keep the current and previous values in the target

 CREATE TABLE t_dem_xxx ( ID VARCHAR(20) NOT NULL, Name1 VARCHAR(50), Name2 VARCHAR(50), PreName1 VARCHAR(50), PreName2 VARCHAR(50), CONSTRAINT PK_t_dem_xxx PRIMARY KEY (ID) ) go CREATE TABLE t_tmp_xxx ( ID VARCHAR(20) NOT NULL, Name1 VARCHAR(50), Name2 VARCHAR(50), CONSTRAINT PK_t_tmp_xxx PRIMARY KEY (ID) ) go CREATE PROCEDURE p_dem_xxx AS --维度抽取存储过程 BEGIN DECLARE @num NUMERIC(10,0) SELECT @num = COUNT(*) FROM t_dem_xxx --如果原表为空，构造缺省值 IF @num = 0 BEGIN INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-2','NULL值','' INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-1','缺失外键','' END --根据主键插入在维度表中找不到的基础数据 INSERT INTO t_dem_xxx ( ID , Name1 , Name2 ) SELECT a.ID,a.Name1,a.Name2 FROM t_tmp_xxx a LEFT OUTER JOIN t_dem_xxx b ON a.ID = b.ID WHERE b.ID IS NULL --根据主键更新在维度表中找到但是已经发生变化的的基础数据，重新设置数据 UPDATE t_dem_xxx SET Name1 = a.Name1, Name2 = a.Name2, PreName1 = b.Name1, PreName2 = b.Name2 FROM t_tmp_xxx a,t_dem_xxx b WHERE a.ID = b.ID AND (a.Name1<>b.Name1 or a.Name2<>b.Name2) END

 --第一次执行插入操作 INSERT INTO t_tmp_xxx VALUES ('1','AA','') INSERT INTO t_tmp_xxx VALUES ('2','BB','') EXEC p_dem_xxx SELECT * FROM t_dem_xxx SELECT * FROM t_tmp_xxx --更新数据后的变化 UPDATE t_tmp_xxx SET Name2 = 'A' WHERE ID = '1' EXEC p_dem_xxx SELECT * FROM t_dem_xxx SELECT * FROM t_tmp_xxx

• 博文量
412
• 访问量
1116706