缓慢变化维处理及事实表的字段的抽取

缓慢变化维，为了体现历史数据的变更。采用历史拉链的方式处理
（1）把新的维度union all上已有的维度，已有维度若关联得上当天的数据，并且end_date =‘9999-12-31’,则end_date更新为昨天
select …
from goods where create_time=‘2019-06-01’ —商品表的新增数据
union all
select …
if(b.id is null ,a.end_date, date_add(b.dt,-1)) end_date
from goods a
left join dim_goods_d b
on a.id = b.id and a.end_date=‘9999-12-31’
凡是历史拉链表均可以如上处理，不管是维表还是事实表。

维表处理之后，才开始抽取事实表的数据
select …,b.id as good_id
from fact_order a left join dim_goods_d b
on a.good_id = a.id and a.create_time<b.start_date and a.create_time<=b.end_date

hive中：
select * from(
select …,b.id as good_id
from fact_order a left join dim_goods_d b
on a.good_id = a.id)
where create_time<start_date and create_time<=end_date
（2）维表用代理键
将新的数据插入到维表
insert into dim_goods_d
select …, b.max_gid + row_number() over (order by a.id),create_time
goods a
cross join
(select max(gid) as max_gid from dim_goods_d) b
where a.create_time=‘2019-06-01’
事实表的处理

2019-06-01这天的数据，fact_order的gid应该是120，怎么写这个sql呢？
insert into fact_order
select t.,a.gid
from order t left join (
select a. from dim_goods_d a
join (
select id,max(gid) gid from dim_goods_d where dt<=‘2019-06-01’ group by id) on a.id=b.id and a.gid=b.gid
) d
on t.good_id=d.id
where t.create_time=‘2019-06-01’
最后结果：

（3）快照维度表

在维度的历史拉链表中，
insert overwrite into dim_goods_snapshot partition (dt=‘2019-06-01’)
select * from dim_goods_d where start_dt<=‘2019-06-01’ and ‘2019-06-01’<end_date

与事实表关联
select … b.id,…
from
fact_order a
join dim_goods_snapshot b
on a.good_id = b.id and b.dt=‘2019-06-01’
where a.create_time=‘2019-06-01’