[ SQL ] 数据仓库中两张拉链表间关联

背景：

　　1.因为业务系统中存在更新表数据的情况，且总数据量不大，数仓ODS层中采用了对事实表进行拉链的操作。

　　2.现在需要在ODS层中对多张拉链表进行关联，为了以后追数方便，需要先给出一个全量更新的脚本。

 

设计两个表结构，暂时称为“事实表（odstr）”和“维度表（odsdim）”：

drop table if exists odstr;
create table odstr(
num int,                   -- 序号
id int,                    -- 编号
code varchar(100),         -- 编码（维度表中码值）
create_dt varchar(100),    -- 创建时间（事实数据的入库时间）
modify_dt varchar(100),    -- 修改时间（同上）
start_dt varchar(100),     -- 数据生效时间（拉链开始时间）
end_dt varchar(100)        -- 数据失效时间（拉链结束时间）
);

drop table if exists odsdim;
create table odsdim(
num int,                  -- 序号
code varchar(100),        -- 编码
name varchar(100),        -- 编码的值
start_dt varchar(100),    -- 数据生效时间
end_dt varchar(100)       -- 数据失效时间
);

 

场景模拟数据：

　　1）码值先于事实数据发生，即在事实表中发生之前就已经存在很多维度的拉链数据，且有一条以上的拉链数据在事实数据插入前已经失效。

-- 事实表数据
insert into odstr values(1, 1001, 'A', '2021-01-01', '2021-10-05', '2021-10-05', '2021-10-17');    
insert into odstr values(2, 1001, 'A', '2021-01-01', '2021-10-18', '2021-10-18', '2021-10-20');

-- 维度表数据
insert into odsdim values(8, 'A', 'T110', '2021-10-01', '2021-10-02');  -- 不会被关联出
insert into odsdim values(1, 'A', 'T110', '2021-10-13', '2021-10-17');  -- 被事实表 1 关联出
insert into odsdim values(2, 'A', 'T120', '2021-10-18', '9999-12-31');  -- 被事实表 2 关联出

结果用编号展示应为：

　　　　　　odstr  <-> odsdim

　　　　  　　1　　　　1

　　　　  　　2　　　　2

 　　2）事实数据发生后，码值先后发生多次变化

-- 事实表数据
insert into odstr values(3, 1002, 'B', '2021-01-01', '2021-10-21', '2021-10-21', '9999-12-31');

-- 维度表数据
insert into odsdim values(9, 'B', 'T140', '2020-10-17', '2021-10-16'); -- 不会被关联出
insert into odsdim values(3, 'B', 'T140', '2021-10-17', '2021-10-25');
insert into odsdim values(4, 'B', 'T140', '2021-10-26', '2021-10-30');
insert into odsdim values(5, 'B', 'T140', '2021-10-31', '9999-12-31');

结果用编号展示应为：

　　　　　　odstr  <-> odsdim

　　　　  　　3　　　　3
　　　　  　　3　　　　4
　　　　  　　3　　　　5

 

SQL：

select a.num, b.num , b.name, a.code, b.code 
,case when a.start_dt <= b.end_dt and a.end_dt   >= b.end_dt  then 1 else 0 end as tj1  -- 条件1
,case when b.start_dt <= a.end_dt and b.end_dt > a.end_dt  then 1 else 0 end    as tj2  -- 条件2
from odstr a 
left join odsdim b 
on a.code = b.code 
and (
        (a.start_dt <= b.end_dt and a.end_dt   >= b.end_dt) 
     or 
         (b.start_dt <= a.start_dt and b.end_dt > a.start_dt )
    )

;