hive-维表关联，维表层级id不确定情况

 

问题描述：事实表存储的类目id不确定是几级id；一共有三级类目，需要把类目信息维护进事实表，比如记录的是二级类目id，需要把一级类目数据维护进事实表。

一开始没有想到怎么搞，还是想了一会儿想了这么个处理方法：

ods_classify原始业务库表，id就是各级的类目id，pid就是上一层级的类目id，dim_classify是事先处理好的一二三级类目表

select 	t1.id
		,t4.third_id
        ,t4.third_title
        ,case 	when t4.sencond_id is not null then t4.sencond_id
         else 	t3.sencond_id end as sencond_id
        ,case 	when t4.sencond_title is not null then t4.sencond_title
         else 	t3.sencond_title end as sencond_title
        ,case 	when t4.first_id is not null then t4.first_id
        		when t3.first_id is not null then t3.first_id
         else 	t2.first_id end as first_id
        ,case 	when t4.first_title is not null then t4.first_title
        		when t3.first_title is not null then t3.first_title
         else 	t2.first_title end as first_title
from 	(
			select 	id
			from 	ods_classify
		) t1 left
join 	(
            SELECT  first_id
                    ,first_title
            FROM    dim_classify
            group by first_id
                     ,first_title
        ) t2
on 		t1.id = t2.first_id left
join 	(
			SELECT  sencond_id
                    ,sencond_title
                    ,first_id
                    ,first_title
            FROM    dim_classify
            group by sencond_id
                     ,sencond_title
                     ,first_id
                     ,first_title
		) t3
on 		t1.id = t3.sencond_id left
join 	(
			SELECT  third_id
                    ,third_title
                    ,sencond_id
                    ,sencond_title
                    ,first_id
                    ,first_title
            FROM    dim_classify
            group by third_id
                     ,third_title
                     ,sencond_id
                     ,sencond_title
                     ,first_id
                     ,first_title
		) t4
on 		t1.id = t4.third_id
;