image.png
开创新朝
节点的边框颜色或线段颜色有变的视为开创了新王朝，图示绿色所示的都是新王朝。
值得注意的是长剪头的7个类似五代十国，要视为一个整体（取长兄作为朝代名）
image.png
数据的iamroot计算出了上图的绿色点（新朝开创者）
-- 跟父亲的type、class不同或没有父亲的为本朝的root
create table find_father_root as
select a.*,case when (a.po_type<>b.po_type or a.tiny_class <> b.tiny_class or b.style_id is null) then a.style_id end as iamroot
from ods.sku_path1030 a
left join ods.sku_path1030 b on a.repeat_style_id=b.style_id --b是父表
where a.style_path like '%117201600%'
order by a.style_path
填充空白（找祖宗，本朝的）
create table find_father_myroot as
select a.style_id,a.repeat_style_id,a.po_type,a.style_path,coalesce(a.iamroot,max(b.iamroot)) my_root
from find_father_root a
left join find_father_root b on instr(a.style_path,b.iamroot)>0
group by a.style_id,a.repeat_style_id,a.po_type,a.style_path,a.iamroot
image.png
朝代合并
potype为fast且父辈的root相同的予以合并
image.png
select a.style_id,a.style_path,b.my_root froot,a.my_root
, min(a.my_root) over (partition by b.my_root) new_root
from find_father_myroot a
left join find_father_myroot b on a.repeat_style_id=b.style_id
order by a.style_path
最终结果
image.png
image.png
小调整：非fast的不要合并
select a.style_id,a.style_path,b.my_root froot,a.my_root
,case when a.po_type='Fast-track Repeat' then min(a.my_root) over (partition by a.po_type,c.my_root) else a.my_root end as new_root
from find_father_myroot a
left join find_father_myroot b on a.my_root=b.style_id --找上个朝代
left join find_father_myroot c on b.repeat_style_id=c.style_id --找上个朝代的开国
order by a.style_path
image.png
dag图
-- 画graph----------------------------------------------
create table ods.sku_path_dot as --drop table ods.sku_path_dot
select repeat_style_id ,style_id ,po_type,tiny_class,style_path,root_style_id,
dense_rank() over(partition by root_style_id order by po_type ) po_type_rk,
dense_rank() over(partition by root_style_id order by tiny_class ) tiny_class_rk
from ods.sku_path1030
-- 点
select distinct concat('"',style_id,'"',
case when tiny_class_rk=1 then '[color=red]'
when tiny_class_rk=2 then '[color=yellow]'
when tiny_class_rk=3 then '[color=green]'
when tiny_class_rk=4 then '[color=blue]'
end) as graphviz
from sku_path_dot where style_path like '%117201600%' --and repeat_style_id is not null
-- 边
union all
select concat('"',repeat_style_id,'" -> "' ,style_id,'"',case when po_type='Fast-track Repeat' then '[color=pink]' else '' end) as graphviz
from sku_path_dot where style_path like '%117201600%' and repeat_style_id is not null
union all
select '}'
http://www.webgraphviz.com/
登录网址在线出图：
复制下面2行，再加上上面代码跑出来的结果（本想放sql一起，顺序会乱）。
digraph G {
rankdir=LR
#上面sql结果
