拉链表与SCD2-优快云博客

文章目录

1 原文
2 小结

1 原文

解决缓慢变化维—拉链表
https://blog.youkuaiyun.com/panfelix/article/details/107062349
SCD2维度更新，如何紧急将hive脚本改成 Oracle脚本
https://blog.youkuaiyun.com/u014337370/article/details/109542827

2 小结

1,缓慢变化维的缓慢是相对与事实表而言,维度表的变化速度比事实表缓慢

2,随时间变化的维度表称为缓慢变化维

3 缓慢变化维

比如一张表四个字段有ID,姓名,年龄,修改时间四字段,
在原有表的基础上加上额外两列,
------------1 生效时间(dw_start_date)
------------2 失效时间(dw_end_date)
第一个表示某条数据生命周期的起始时间,即生效日期
第二表示某条数据生命周期的结束时间,即失效日期
-----------存在当dw_end_date为9999-12-31,表示这条数据最新

4 增量抽取当日新增数据和当日修改数据同步到ods层

5 编写sql重新计算dw_end_date

6 拉链表实现步骤,使用当天最新的数据union all 历史数据

7 查询拉链表

查询拉链表
1、获取2019-12-20日的历史快照数据
select *
from demo.dw_product_2
where dw_start_date <= '2019-12-20'
  and dw_end_date > '2019-12-20'
order by goods_id;

2、获取最新的商品快照数据
select *
from demo.dw_product_2
where dw_end_date = '9999-12-31'
order by goods_id;

8 其他缓慢变化维例子_1

--准备数据
create table dim_emp
(
    emp_sk     number,       --代理键
    emp_id     varchar2(50),--员工编号
    emp_name   varchar2(50),--员工姓名
    empno      varchar2(50), --部门
    emp_gw     varchar2(50),--岗位
    begin_date date,         --生效日期
    end_date   date,         --失效日期
    row_status number        --行有效状态
);

create table emp_table
(
    emp_id      varchar2(50),--员工编号
    emp_name    varchar2(50),--员工姓名
    empno       varchar2(50), --部门
    emp_gw      varchar2(50), --岗位
    last_update date --更新日期
);

SELECT *
FROM dim_emp;
SELECT *
FROM emp_table;


insert into dim_emp
values ('1', '001', '张三', '财务部', '财务经理', date'2020-02-01', date'9999-12-31', 1);
insert into dim_emp
values ('2', '002', '王五', '销售部', '销售员', date'2020-02-01', date'9999-12-31', 1);
insert into dim_emp
values ('3', '003', '赵四', '人力部', '人力经理', date'2020-02-01', date'9999-12-31', 1);
insert into dim_emp
values ('4', '004', '李青', '生产部', '车间主任', date'2020-02-01', date'9999-12-31', 1);
commit;

insert into emp_table
values ('001', '张三', '财务部', '财务经理', date'2020-03-01'); --不变
insert into emp_table
values ('002', '王五五', '销售部', '销售员', date'2020-03-01'); --姓名改变
insert into emp_table
values ('003', '赵四', '管理部', '总经理', date'2020-03-01'); -- 部门和岗位改变
insert into emp_table
values ('004', '李青', '生产部', '车间主任', date'2020-03-01'); --不变
insert into emp_table
values ('005', '钱七', '物流部', '快递员', date'2020-03-01'); --新增
commit;


--SCD2更新逻辑
select row_number() over (order by t1.emp_id) + t3.max_sk as emp_sk -- 生成代理键
     , t1.emp_id
     , t1.emp_name
     , t1.empno
     , t1.emp_gw
     , case
           when t1.last_update is null then date'2020-03-01' --正常情况应该是系统时间-1天
           else last_update end                           as begin_date
     , date'9999-12-31'                                      end_date
     , 1                                                  as row_status
from emp_table t1 --源表
         left join -- t1 左连接 t2
    dim_emp t2 --维度表
                   on t1.emp_id = t2.emp_id
         cross join
         (select max(emp_sk) as max_sk from dim_emp) t3 --获取维表最大代理键
where t2.emp_sk is null
   or t2.emp_name != t1.emp_name
   or t2.empno != t1.empno
   or t2.emp_gw != t1.emp_gw
--维表代理键为空或SCD2的字段不相等
--获取到源表相对于维表，新增或变化的数据
union all
select t4.emp_sk
     , t4.emp_id
     , t4.emp_name
     , t4.empno
     , t4.emp_gw
     , t4.begin_date
     , case
           when t4.emp_name != t5.emp_name --SCD2字段不相等则修改失效时间
               or t4.empno != t5.empno
               or t4.emp_gw != t5.emp_gw
               then date'2020-03-01'
           else end_date end as end_date
     , case
           when t4.emp_name != t5.emp_name --SCD2字段不相等则修改失效时间
               or t4.empno != t5.empno
               or t4.emp_gw != t5.emp_gw
               then 0
           else 1 end        as row_status
from dim_emp t4 --维表
         left join --t4 左连接 t5
    emp_table t5--源表
                   on t4.emp_id = t5.emp_id

9 其他缓慢变化维例子_2

INSERT OVERWRITE TABLE dw.dim_table --维表
SELECT
      ROW_NUMBER() OVER (t1.emp_code,t1.job_code) + max_sk AS sk_emp_info  --生成外键
    , t1.emp_name  --SDC2字段
    , t1.emp_code
    , t1.job_name ----SDC2字段
    , t1.jobt_code
    , CASE
        WHEN t1.last_update_dt IS NOT NULL
            THEN t1.last_update_dt
        ELSE DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd') --当前的前一天
      END AS valid_start_dt --生效时间
    , '99991231' AS valid_end_dt --失效时间
    , 'Current' AS row_status --行状态
--3、从【源表】获取相对于【维度表】变化/增加的数据：步骤1 [left join] 步骤2
FROM
---1、获取源数据
(
    SELECT
         emp_name  --SDC2字段
        ,emp__code --持久键
        ,job_name  --SDC2字段
        ,job_code --持久键
        ,last_update_dt
    FROM
        ods.ods_table
) t1
---2、获取维度数据
LEFT OUTER JOIN
(
    SELECT
         sk_emp_info  --代理键
        ,emp_name  --SDC2字段
        ,emp__code --持久键
        ,job_name ----SDC2字段
        ,job_code --持久键
    FROM
       dw.dim_table
) t2
--持久键 连接
ON t1.emp__code = t2.emp__code
    AND t1.job_code = t2.job_code
--获取维度表最大的外键，以便为新增数据生成外键
CROSS JOIN
(
    SELECT
        COALESCE(MAX(sk_emp_info),0) AS max_sk
)dim_sk_tmp
WHERE t2.sk_emp_info is null
    OR t1.emp_name != t2.emp_name
    OR t1.job_name != t2.job_name

UNION ALL

-- 4、从【维度表】获取"相对于【源表】有变化的数据"：步骤2 [left outer join] 步骤1
SELECT
      t3.sk_emp_info
    , t3.emp_name
    , t3.emp_code
    , t3.job_name
    , t3.job_code
    , t3.valid_start_dt
    , CASE
        WHEN t3.emp_name != t4.emp_name
            OR  t3.job_name != t4.job_name
                THEN t4.last_update_dt
        ELSE t3.valid_end_dt  --将维表SCD2字段有变化的某行的有效截止时间valid_end_dt 修改成源表的更新时间
      END AS valid_end_dt
    , CASE
        WHEN t3.emp_name != t4.emp_name
            OR  t3.job_name != t4.job_name
                THEN 'Expired' --将维表SCD2字段有变化的某行的状态row_status标记为“失效”--Expired
        ELSE t3.row_status
      END AS row_status
FROM
--2、获取维度数据
(
    SELECT
         sk_emp_info  --代理键
        ,emp_name  --SDC2字段
        ,emp__code --持久键
        ,job_name ----SDC2字段
        ,job_code --持久键
    FROM
       dw.dim_table
) t3
--1、获取源数据
LEFT OUTER JOIN
(
    SELECT
         emp_name  --SDC2字段
        ,emp__code --持久键
        ,job_name  --SDC2字段
        ,job_code --持久键
        ,last_update_dt
    FROM
        ods.ods_table
) t4
ON t3.emp_code = t4.emp_code
   AND t3.job_code = t4.job_code