数据仓库@缓慢变化维(拉链算法)

数据仓库中的拉链算法：处理缓慢变化维

最新推荐文章于 2025-10-05 17:03:24 发布

原创最新推荐文章于 2025-10-05 17:03:24 发布 · 7k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#数据仓库 #缓慢变化维 #SCD

数据仓库专栏收录该内容

1 篇文章

订阅专栏

本文介绍了数据仓库中处理缓慢变化维的方法，特别是历史拉链算法，以减少数据冗余并保留历史变化。通过示例展示了如何在客户表中应用此算法，以及如何根据开始和结束时间取数，提供了MySQL的实现示例。

前言

维度表中的数据来源于操作型系统。在多维数据仓库或独立型数据集市中，数据直接来源于操作型系统。在企业信息化工厂中，来自于操作型系统的数据首先移到企业数据仓库中，然后进入多维数据集市。进入到维度表的信息，在操作型系统中仍然有可能发生改变。例如：客户的生日出现错误可能需要更新以纠正，客户的地址发生变化也需要更新等。

由于下游的星型模式使用代理键作为每个维度表的主键，因此不需要像原系统那样处理信息变化。操作型系统可以跟踪数据变化的历史情况，也可以简单的采用重写变化值的方式。无论如何，星型模式可以对不同方式的变化做出响应，这使得对整个业务过程的度量更有意义。

采用维度设计方案时，确定数据源的变化情况在维度表中如何表示非常重要。这一现象称为缓慢变化的维度，简称缓慢变化维（Slowly Changing Dimension）。该术语的名称反映了维度积累变化的实际情况，至少与积累数据行较为快速的事实表比较，维度变化相对缓慢。应对数据元素改变存在不同的响应方式。某些情况下，保留历史数据没有什么分析价值。某些情况下，保留历史数据将会起到至关重要的作用。——以上摘自数据仓库维度设计权威指南

说明

缓慢变化维其实在Kimball的维度数据仓库构建过程中是经常使用到的，所以掌握这样一个基本算法也是在做数据仓库开发工作中的必备技能，当然有时候我们也会听说拉链算法（俗称）其实都是指的是同一个东西

源业务系统中客户表

在源业务系统中的数据表是随时发生变化并且不会记录历史，只需要给客户呈现最新的数据即可

客户号 姓名地址电话
CIF10001 Jack China 17711111111
CIF10002 Rose China 17722222222

实现方式一：每日快照

可以看出客户Rose在20190103这天修改了手机号，那么我们只需要将源业务系统的数据表追加一个数据日期并进行每天全量快照就可以记录下数据每天的历史变化，一遍我们后续的维度分析。但是很明显这有一个缺点，就是数据重复非常严重，那么我们如果实现最少数据重复并记录数据的历史变化呢？继续看方式二

数据日期 客户号 姓名地址电话
20190101 CIF10001 Jack China 17711111111
20190101 CIF10002 Rose China
17722222222

20190102 CIF10001 Jack China 17711111111
20190102 CIF10002 Rose China
17722222222

20190103 CIF10001 Jack China 17711111111
20190103 CIF10002 Rose China 17733333333

实现方式二：历史拉链

同样是反映历史数据变化，我们的拉链方式明显可以看出数据量比方式一少了很多冗余，所以这种方式是推崇的。在客户Rose20190103这天做了手机号的一个变更我的拉链算法就会根据数据的变化进行记录，巧妙的运用了数据的生命周期进行管理。开始时间和结束时间这个时间区间内表明数据是有效的，从而减少了大量不变数据的数据冗余，也就是说我们这种算法是推荐用于不是大面积的数据变化，而是小范围的数据变更才更具有优势，因为是用时间换取了空间。从而也说明了方式一，是空间换取了时间。

开始日期 结束日期 客户号 姓名地址电话
20190101 99991231 CIF10001 Jack China 17711111111
20190101 20190102 CIF10002 Rose China
17722222222

20190103 99991231 CIF10002 Rose China
17733333333

取数逻辑实现

既然数据已经存好了，那么我们如何进行取数呢？方式一的取数逻辑已经很明显，只需要按照数据日期进行过滤即可，但是方式二呢？那么就让我们一起分析一下它的取数逻辑实现，我们现在就是要充分利用数据的生命周期来反映数据的历史变化。

获取20190101历史那天的数据那么我们的SQL可以写成：

Select * From User_Info Where '20190101' between Start_Date and End_Date;

获取20190102历史那天的数据那么我们的SQL可以写成：

Select * From User_Info Where '20190102' between Start_Date and End_Date;

获取20190103历史那天的数据那么我们的SQL可以写成：

Select * From User_Info Where '20190103' between Start_Date and End_Date;

获取最新的数据那么我们的SQL可以写成：

Select * From User_Info Where End_Date = ‘99991231’;

方式一 方式二
Where Data_Date = SomeDay Where SomeDay between Start_Date and End_Date

算法实现

其实，原理上面已经讲的很清楚了，针对不同的数据库具体的实现也是不太一样的，那么我这里给出一个MySQL的版本，供大家参考，Oracle的实现其实是比MySQL简单的

DELIMITER $$

DROP PROCEDURE IF EXISTS ETL.EDW_SCD_LOAD$$

CREATE PROCEDURE ETL.EDW_SCD_LOAD(
     IN P_DATA_DATE VARCHAR(50)
    ,IN P_IN_SCHEMA VARCHAR(50)
    ,IN P_IN_TABLE  VARCHAR(50)
    ,IN P_TO_SCHEMA VARCHAR(50)
    ,IN P_TO_TABLE  VARCHAR(50)
    ,OUT P_RESULT   INT
    )
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
BEGIN
    /*[DEFINER = { user | CURRENT_USER }]*/
/*
     Author    : XUEZHOUYI
     Name      : EDW_SCD_LOAD
     Functions : Slowly Changing Dimensions
     Purpose   : Slowly Changing Dimensions
     Revisions or Comments
     VER        DATE        AUTHOR           DESCRIPTION
    ---------  ----------  ---------------  ------------------------------------
    1.0        2017-08-01  XUEZHOUYI        1.CREATE THE PROCEDURE
    1.1        2017-08-28  XUEZHOUYI        1.ADD SCHEMAS
*/
    DECLARE V_PROC_NAME     VARCHAR(80)   DEFAULT 'ETL.EDW_SCD_LOAD.PRC';
    DECLARE V_START_TIME    CHAR(19)      DEFAULT NOW();
    DECLARE V_STEP_ID       INT           DEFAULT 0;
    /* ------------------------------------------------------------------------ */
    
    DECLARE V_SQL_STR      VARCHAR(20000) DEFAULT '';
    DECLARE V_COLUMN       VARCHAR(2000)  DEFAULT '';
    DECLARE V_JOIN_KEY     VARCHAR(50)    DEFAULT '';
    DECLARE V_COLUMNS      VARCHAR(2000)  DEFAULT '';
    DECLARE V_AAA_COLUMNS  VARCHAR(2000)  DEFAULT '';
    DECLARE V_JOIN_COLUMNS VARCHAR(2000)  DEFAULT '';
    
    /* DEFINE THE CURSOR */
    DECLARE IF_DONE INT DEFAULT FALSE;
    DECLARE MY_CURSOR1 CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = P_IN_SCHEMA AND TABLE_NAME = P_IN_TABLE;
    DECLARE MY_CURSOR2 CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = P_IN_SCHEMA AND TABLE_NAME = P_IN_TABLE AND COLUMN_KEY <> 'PRI';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET IF_DONE = TRUE;
    
    /* EXCEPTION HANDLER  */
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @V_RETURN_CODE = RETURNED_SQLSTATE ,@V_ERROR_MSG = MESSAGE_TEXT;
        CALL ETL.EDW_PROC_ERROR_LOG(P_DATA_DATE,V_START_TIME,NOW(),V_PROC_NAME,V_STEP_ID,@V_RETURN_CODE,@V_ERROR_MSG);
        SET P_RESULT = 1;
    END;
    
    SET P_RESULT = 0;
    /* EXCEPTION HANDLER  */
    
    SET V_STEP_ID = 1;
    /* GET PRIMARY KEY */
    SELECT COLUMN_NAME INTO V_JOIN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = P_IN_SCHEMA AND TABLE_NAME = P_IN_TABLE AND COLUMN_KEY = 'PRI';
    
    SET V_STEP_ID = 2;
    /* LOOP */
    OPEN MY_CURSOR1;
        MY_LOOP1:LOOP
            FETCH MY_CURSOR1 INTO V_COLUMN;
            IF IF_DONE THEN
                LEAVE MY_LOOP1;
            END IF;
            
            SET V_COLUMNS = CONCAT(V_COLUMNS,',',V_COLUMN);
            
        END LOOP MY_LOOP1;
    CLOSE MY_CURSOR1;
    
    SET V_STEP_ID = 3;
    SET IF_DONE = FALSE;
    OPEN MY_CURSOR1;
        MY_LOOP1:LOOP
            FETCH MY_CURSOR1 INTO V_COLUMN;
            IF IF_DONE THEN
                LEAVE MY_LOOP1;
            END IF;
            
            SET V_AAA_COLUMNS = CONCAT(V_AAA_COLUMNS,',AAA.',V_COLUMN);
            
        END LOOP MY_LOOP1;
    CLOSE MY_CURSOR1;
    
    SET V_STEP_ID = 4;
    SET IF_DONE = FALSE;
    OPEN MY_CURSOR2;
        MY_LOOP2:LOOP
            FETCH MY_CURSOR2 INTO V_COLUMN;
            IF IF_DONE THEN
                LEAVE MY_LOOP2;
            END IF;
            
            SET V_JOIN_COLUMNS = CONCAT(V_JOIN_COLUMNS,' OR AAA.',V_COLUMN,' <> BBB.',V_COLUMN);
            
        END LOOP MY_LOOP2;
    CLOSE MY_CURSOR2;
    
    SET V_STEP_ID = 5;
    /* SUPPORT FOR RERUN */
    SET @V_SQL_STR = CONCAT('
        UPDATE ',P_TO_SCHEMA,'.',P_TO_TABLE,' SET END_DATE = ',P_DATA_DATE,' WHERE END_DATE = DATE_FORMAT(DATE_SUB(STR_TO_DATE(',P_DATA_DATE,',''%Y%m%d''),INTERVAL 1 DAY),''%Y%m%d'')
    ');
    PREPARE V_SQL_STR FROM @V_SQL_STR;
    EXECUTE V_SQL_STR;
    COMMIT;
    
    SET @V_SQL_STR = CONCAT('
        DELETE FROM ',P_TO_SCHEMA,'.',P_TO_TABLE,' WHERE START_DATE = ',P_DATA_DATE,'
    ');
    PREPARE V_SQL_STR FROM @V_SQL_STR;
    EXECUTE V_SQL_STR;
    COMMIT;
    
    SET V_STEP_ID = 6;
    /* CLOSED THE RECORDS WERE NOT FOUND */
    SET @V_SQL_STR = CONCAT('
        UPDATE ',P_TO_SCHEMA,'.',P_TO_TABLE,' TGT INNER JOIN(
            SELECT
                AAA.',V_JOIN_KEY,'
            FROM
                ',P_TO_SCHEMA,'.',P_TO_TABLE,' AAA
            LEFT JOIN
                ',P_IN_SCHEMA,'.',P_IN_TABLE,' BBB
            ON
                AAA.',V_JOIN_KEY,' = BBB.',V_JOIN_KEY,'
            WHERE
                ',P_DATA_DATE,' BETWEEN AAA.START_DATE AND AAA.END_DATE
            AND BBB.',V_JOIN_KEY,' IS NULL
        ) SRC
        ON TGT.',V_JOIN_KEY,' = SRC.',V_JOIN_KEY,' AND ',P_DATA_DATE,' BETWEEN TGT.START_DATE AND TGT.END_DATE
        SET TGT.END_DATE = DATE_FORMAT(DATE_SUB(STR_TO_DATE(',P_DATA_DATE,',''%Y%m%d''),INTERVAL 1 DAY),''%Y%m%d'')
    ');
    PREPARE V_SQL_STR FROM @V_SQL_STR;
    EXECUTE V_SQL_STR;
    COMMIT;
    
    SET V_STEP_ID = 7;
    /* CLOSED THE RECORDS WERE OUT OF DATE */
    SET @V_SQL_STR = CONCAT('
        UPDATE ',P_TO_SCHEMA,'.',P_TO_TABLE,' TGT INNER JOIN(
            SELECT
                AAA.',V_JOIN_KEY,'
            FROM
                ',P_TO_SCHEMA,'.',P_TO_TABLE,' AAA
            INNER JOIN
                ',P_IN_SCHEMA,'.',P_IN_TABLE,' BBB
            ON
                AAA.',V_JOIN_KEY,' = BBB.',V_JOIN_KEY,'
            AND (1<>1',V_JOIN_COLUMNS,')
            WHERE
                ',P_DATA_DATE,' BETWEEN AAA.START_DATE AND AAA.END_DATE
        ) SRC
        ON TGT.',V_JOIN_KEY,' = SRC.',V_JOIN_KEY,' AND ',P_DATA_DATE,' BETWEEN TGT.START_DATE AND TGT.END_DATE
        SET TGT.END_DATE = DATE_FORMAT(DATE_SUB(STR_TO_DATE(',P_DATA_DATE,',''%Y%m%d''),INTERVAL 1 DAY),''%Y%m%d'')
    ');
    PREPARE V_SQL_STR FROM @V_SQL_STR;
    EXECUTE V_SQL_STR;
    COMMIT;
    
    SET V_STEP_ID = 8;
    /* INSERT THE NEW RECORDS */
    SET @V_SQL_STR = CONCAT('
        INSERT INTO ',P_TO_SCHEMA,'.',P_TO_TABLE,'(START_DATE,END_DATE',V_COLUMNS,')
        SELECT *
        FROM(
            SELECT 
                ',P_DATA_DATE,',99991231',V_AAA_COLUMNS,'
            FROM
                ',P_IN_SCHEMA,'.',P_IN_TABLE,' AAA
            LEFT JOIN
                ',P_TO_SCHEMA,'.',P_TO_TABLE,' BBB
            ON
                AAA.',V_JOIN_KEY,' = BBB.',V_JOIN_KEY,'
            AND ',P_DATA_DATE,' BETWEEN BBB.START_DATE AND BBB.END_DATE
            WHERE
                BBB.',V_JOIN_KEY,' IS NULL
        ) AS TMP
    ');
    PREPARE V_SQL_STR FROM @V_SQL_STR;
    EXECUTE V_SQL_STR;
    COMMIT;

END$$

DELIMITER ;

客户号	姓名	地址	电话
CIF10001	Jack	China	17711111111
CIF10002	Rose	China	17722222222

数据日期	客户号	姓名	地址	电话
20190101	CIF10001	Jack	China	17711111111
20190101	CIF10002	Rose	China	17722222222
20190102	CIF10001	Jack	China	17711111111
20190102	CIF10002	Rose	China	17722222222
20190103	CIF10001	Jack	China	17711111111
20190103	CIF10002	Rose	China	17733333333

开始日期	结束日期	客户号	姓名	地址	电话
20190101	99991231	CIF10001	Jack	China	17711111111
20190101	20190102	CIF10002	Rose	China	17722222222
20190103	99991231	CIF10002	Rose	China	17733333333

方式一	方式二
Where Data_Date = SomeDay	Where SomeDay between Start_Date and End_Date