遵循SCD方法更新维表中记录的过程是什么

Question

当表中有更新时，我想在维度表中查看 scd 类型二形式的历史记录，营销区域被认为更新为新值。

我想在表中有更新时在维度表中查看scd类型二形式的历史记录，营销区域被认为更新为新值。我们的主要目标是根据历史数据保存更新记录方法。

在 SCD-2 中更新维度表（记录数较少 - 几百条）的算法将是：

FOR ALL ROWs in the SOURCE table query LOOP 

IF ( a corresponding row exists in the DIM TABLE 

THEN 

IF data are different THEN 

1. UPDATE the ""CURRENT"" row of the DIM TABLE 

SET EFFECTIVE_TO = NOW 

ACTIVE_FLAG = 0 (ZERO) 

2. INSERT new version into DIM TABLE 

SET EFFECTIVE_TO = NOW+1 day ahead 

ACTIVE_FLAG = 1 (ONE)

END IF 

ELSE 

INSERT new row into DIM TABLE 

END IF

所以我希望存储新值以及设置为标志 0 的旧值。

Answer 1

下面是检查条件和制作 SCD-2 的块。也让我知道你到底在寻找什么。&nbsp;BEGIN&nbsp; &nbsp; &nbsp; LOOP cusrsor&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;SELECT COUNT(1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INTO V_COUNT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM DIM table_name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE <conditions here key columns and change fields here >&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF V_COUNT = 1 THEN -- update flag and insert new record&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;UPDATE stmt;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INSERT stmt;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ELSE -- New values to insert&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INSERT stmt;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;END IF;&nbsp; &nbsp;END LOOP;&nbsp; &nbsp; END;