问题:有一类问题,比如像用户修改了订单状态,需要看每个状态的生效时间范围等等,这类因为维度变化,又需要反映历史变化的情况可以使用拉链表
思路:业务系统当用户操作的时候记录用户订单状态的操作时间,如下建表order_update,还有最终呈现效果的orer_his表,每这部分修改的数据按照新增和修改分类插入到order_his表中。
表结构如下:
create table order_his(
`id` string COMMENT '订单编号',
`order_status` string COMMENT '订单状态',
`user_id` string COMMENT '用户id',
`operate_time` string COMMENT '操作时间'
start_date 开始时间
end_date 生效结束时间
) COMMENT '拉链表'
create table order_update(
`id` string COMMENT '订单编号',
`order_status` string COMMENT '订单状态',
`user_id` string COMMENT '用户id',
`operate_time` string COMMENT '操作时间'
) COMMENT '订单更新表'
具体实现sql:
插入当天更新表里的最新记录,更新历史表里已有记录的结束时间
订单表中数据同一天有多次状态更新,应以每天的最后一个状态为当天的最终状态。比如一天之内订单状态创建,支付,完成都有,应拉取最终的状态进行拉练表更新
insert overwrite table order_his
select
id,
order_status ,
user_id,
operate_time ,
'当天' start_date,
'9999-99-99' end_date
from order_update where operate_time ='当天'
union all
select
oh.id,
oh.order_status ,
oh.user_id,
oh.operate_time,
oh.start_date,
if(oi.id is null ,oh.end_date, date_add(oi.dt,-1)) end_date
from order_his oh
left join
(select * from order_update where operate_time='当天') oi
on oh.id=oi.id and oh.end_date='9999-99-99'