父子订单混合在一个表是一种比较常见的模型,按道理说一个事实表最好不要混用不同粒度的行,但是现在存在这种模型,对于实时任务的关联成本比较高。本文主要记录一下对于这种合单模型的优化,以减少关联次数。
问题描述:A0是父订单,A1和A2是子订单,最终要把A0打横在A1和A2上。
--合单
===>主表:a(id)
A0
A1
A2
===>关系:relation(id1,id2)
A1 ,A0
A2 ,A0
由于父子单混合,所有的表里面都区分不出来主子单,只能先全部关联。a是主表,b和c都是从表,里面都包含了一部分主和从的信息,先全部打成宽表,再分别通过主子单号去筛选出主从信息。
--step1
create view tmp_v1 as
select *
from a
left join b on a.id = b.id
left join c on a.id = c.id
--step2
select ,t.id1
,t1.time as first_time --id1 info
,t.id2
,t2.time as second_time ---id2 info
from relation t
left tmp_v1 t1 on relation.id1 = tmp_v1.id
left tmp_v1 t2 on relation.id2 = tmp_v2.id
离线这样做并无不妥,但是实时也这么做的话,相当于tmp_v1被消费了两次,且要进行两次关联,这样的关联成本高,因为这是双流join。
模型优化:对于每一个子单,构造两条记录,再进行1次关联和1次聚合即可。
---合单模型优化
===>关系:relation(id1,id2)
A1 ,A0
A2 ,A0
===>构造关系:relation_2(id1,fk,pk)
A1 ,A0, A1@A0 --本身
A1 ,A1, A1@A1 --新增
A2 ,A0, A2@A0 --本身
A2 ,A2, A1@A2 --构造
--step0
create view relation_2 as
select id1,fk,concat(id,id2) as pk
from
(
select id1
,id2 as fk --本身
,concat(id,id2) as pk
from relation
union all
select id1
,id1 as fk --新增
,concat(id,id2) as pk
from relation
);
用构造的relation_2表的外键去关联,因为外键里面报告主子两条单号,便可以一次性关联完成。
--step1
create view tmp_v1 as
select *
from a
left join b on a.id = b.id
left join c on a.id = c.id
--step2
select id1
,last_value(if(flag=1,time,null)) as first_time
,last_value(if(flag=2,fk,null)) as id2
,last_value(if(flag=2,time,null)) as second_time
from
(
select a.id1
,a.fk
,a.pk
,b.time
,if(id1=fk,1,2) as flag
from relation_2 as a
left join tmp_v1 as b
on a.fk = b.id ----1次关联
)
group by id1 --1次聚合
====>(id1, fk, pk ,time)
A1 ,A0, A1@A0 ,time0
A1 ,A1, A1@A1 ,time1
A2 ,A0, A2@A0 ,time0
A2 ,A2, A1@A2 ,time2
通过构造一个关系表,便可以通过外键进行一次关联,就能将主子单的信息都带上;
再按照子单聚合,就能将主子单的信息打横。