2025年拉链表的展开算法_如何求展开式的系数

在做数据仓库项目的过程中，有时候可能也会根据历史拉链表，展开为每天全量表；相当于一个还原的过程，即构建拉链表的反过程。

1、建表及插入测试数据语句

--建表语句  

--生成EDW_T00_H表(历史拉链表)  

-- Create table  

create table EDW_T00_H  

(  

  ID         VARCHAR2(2) not null,  

  START_DATE DATE not null,  

  STATUS     VARCHAR2(2),  

  END_DATE   DATE  

);

-- Add comments to the table   

comment on table EDW_T00_H  

  is '历史拉链表';  

-- Add comments to the columns   

comment on column EDW_T00_H.ID  

  is 'ID';  

comment on column EDW_T00_H.START_DATE  

  is '开始日期';  

comment on column EDW_T00_H.STATUS  

  is '状态';  

comment on column EDW_T00_H.END_DATE  

  is '结束日期';

-- Create/Recreate primary, unique and foreign key constraints   

alter table EDW_T00_H  

  add constraint EDW_T00_ID primary key (ID, START_DATE);  

--生成EDW_T00_ALL表(历史拉链展开后的表)  

-- Create table  

create table EDW_T00_ALL  

(  

  ID        VARCHAR2(2) not null,  

  DATA_DATE DATE not null,  

  STATUS    VARCHAR2(2) not null  

);

-- Add comments to the table   

comment on table EDW_T00_ALL  

  is '历史拉链展开后的表';  

-- Add comments to the columns   

comment on column EDW_T00_ALL.ID  

  is 'ID';  

comment on column EDW_T00_ALL.DATA_DATE  

  is '数据日期';  

comment on column EDW_T00_ALL.STATUS  

  is '状态';

--插入历史表测试数据  

insert into edw_t00_H (ID, START_DATE, STATUS, END_DATE)  

values ('3', to_date('01-12-2007', 'dd-mm-yyyy'), 'N', to_date('01-12-2009', 'dd-mm-yyyy'));  

insert into edw_t00_H (ID, START_DATE, STATUS, END_DATE)  

values ('3', to_date('01-12-2009', 'dd-mm-yyyy'), 'Y', to_date('16-12-2010', 'dd-mm-yyyy'));  

insert into edw_t00_H (ID, START_DATE, STATUS, END_DATE)  

values ('1', to_date('01-12-2010', 'dd-mm-yyyy'), 'Y', to_date('16-12-2010', 'dd-mm-yyyy'));  

insert into edw_t00_H (ID, START_DATE, STATUS, END_DATE)  

values ('2', to_date('01-12-2010', 'dd-mm-yyyy'), 'N', to_date('12-12-2010', 'dd-mm-yyyy'));  

COMMIT;

2、插入系统维护日期表

主要用来取ROWNUM供第3步操作



--插入系统维护时间  

CREATE TABLE SYS_MATIAN_DATE(DATE_COL DATE);  

  

DECLARE  

  V_DATE     DATE := TO_DATE('20080101', 'YYYYMMDD');  

  V_DATE_MAX DATE;  

BEGIN  

  EXECUTE IMMEDIATE 'TRUNCATE TABLE SYS_MATIAN_DATE';  

  SELECT SYSDATE INTO V_DATE_MAX FROM DUAL;  

  WHILE V_DATE <= V_DATE_MAX LOOP  

    INSERT INTO SYS_MATIAN_DATE  

      SELECT V_DATE FROM DUAL;  

    V_DATE := V_DATE + 1;  

    COMMIT;  

  END LOOP;  

END;  

  

SELECT * FROM SYS_MATIAN_DATE;

3、实现拉链表展开的代码

DECLARE  

  CURSOR LSLL_ID IS  

    SELECT ID,  

           START_DATE,  

           STATUS,  

           DECODE(END_DATE,  

                  TO_DATE('30001231', 'YYYYMMDD'),  

                  SYSDATE,      --TO_DATE(P_ETLDATE, 'YYYYMMDD'),  

                  END_DATE) END_DATE  

      FROM EDW_T00_H;  

  V_ID         VARCHAR2(1000);  

  V_START_DATE DATE;  

  V_END_DATE   DATE;  

  V_DATA_DATE  DATE;  

  V_STATUS     VARCHAR2(2);  

BEGIN  

  BEGIN  

    EXECUTE IMMEDIATE 'TRUNCATE TABLE EDW_T00_ALL';  

    OPEN LSLL_ID;  

    LOOP  

      FETCH LSLL_ID  

        INTO V_ID, V_START_DATE, V_STATUS, V_END_DATE;  

      EXIT WHEN LSLL_ID%NOTFOUND;  

      INSERT INTO EDW_T00_ALL  

        (ID, DATA_DATE, STATUS)  

        SELECT V_ID, V_START_DATE + ROWNUM - 1 AS DATA_DATE, V_STATUS  

          FROM SYS_MATIAN_DATE  

         WHERE V_START_DATE + ROWNUM - 1 < V_END_DATE;  

      COMMIT;  

    END LOOP;  

    CLOSE LSLL_ID;  

  END;  

END;

4、总结说明

第3步中，游标的START_DATE、END_DATE要根据实际情况，灵活变通；代码的EXECUTE IMMEDIATE 'TRUNCATE TABLE EDW_T00_ALL';需要不需要，根据实际情况来定；第2步中生成数据日期维护表，主要用来第3步



SELECT V_ID, V_START_DATE + ROWNUM - 1 AS DATA_DATE, V_STATUS



          FROM SYS_MATIAN_DATE



         WHERE V_START_DATE + ROWNUM - 1 < V_END_DATE;



取数用，目的是保证SYS_MATIAN_DATE表有足够的记录，以使对于每一条历史拉链表中的记录都能取得到；本文给出了我们进行拉链表展开时的一个思路。

2025年拉链表的展开算法_如何求展开式的系数

相关推荐