拉链表的展开算法

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

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表有足够的记录，以使对于每一条历史拉链表中的记录都能取得到；本文给出了我们进行拉链表展开时的一个思路。