作者官方网站:http://www.wxl568.cn
处理层级段
drop table if exists sf_bdp.hgm_tm_org1;
create table sf_bdp.hgm_tm_org1 as
select a.orgid
, a.depth
, case when a.depth<'7' then a.orgid
else a.orgsupid
end as p6_orgid
from ods_hrss.tm_org a
where a.inc_day='20180717';
drop table if exists sf_bdp.hgm_tm_org2;
create table sf_bdp.hgm_tm_org2 as
select a.orgid
, a.depth
, a.p6_orgid
, case when t.orgsupid is not null then t.orgsupid
else a.orgid
end as p5_orgid
from sf_bdp.hgm_tm_org1 a
left join ods_hrss.tm_org t
on t.orgid=a.p6_orgid
and t.inc_day='20180717'
and t.depth='6';
drop table if exists sf_bdp.hgm_tm_org3;
create table sf_bdp.hgm_tm_org3 as
select a.orgid
, a.depth
, a.p6_orgid
, a.p5_orgid
, case when t.orgsupid is not null then t.orgsupid
else a.orgid
end as p4_orgid
from sf_bdp.hgm_tm_org2 a
left join ods_hrss.tm_org t
on t.orgid=a.p5_orgid
and t.inc_day='20180717'
and t.depth='5';
drop table if exists sf_bdp.hgm_tm_org4;
create table sf_bdp.hgm_tm_org4 as
select a.orgid
, a.depth
, a.p6_orgid
, a.p5_orgid
, a.p4_orgid
, case when t.orgsupid is not null then t.orgsupid
else a.orgid
end as p3_orgid
from sf_bdp.hgm_tm_org3 a
left join ods_hrss.tm_org t
on t.orgid=a.p4_orgid
and t.inc_day='20180717'
and t.depth='4';
drop table if exists sf_bdp.hgm_tm_org5;
create table sf_bdp.hgm_tm_org5 as
select a.orgid
, a.depth
, a.p6_orgid
, a.p5_orgid
, a.p4_orgid
, a.p3_orgid
, case when t.orgsupid is not null then t.orgsupid
else a.orgid
end as p2_orgid
from sf_bdp.hgm_tm_org4 a
left join ods_hrss.tm_org t
on t.orgid=a.p3_orgid
and t.inc_day='20180717'
and t.depth='3';
drop table if exists sf_bdp.hgm_tm_org6;
create table sf_bdp.hgm_tm_org6 as
select a.orgid
, a.depth
, a.p6_orgid
, a.p5_orgid
, a.p4_orgid
, a.p3_orgid
, a.p2_orgid
, case when t.orgsupid is not null then t.orgsupid
else a.orgid
end as p1_orgid
from sf_bdp.hgm_tm_org5 a
left join ods_hrss.tm_org t
on t.orgid=a.p2_orgid
and t.inc_day='20180717'
and t.depth='2';