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结果