Oracle通用维、父子维相互转换
所谓通用维即维度层级1、2、3均作为字段展示为列,父子维即维度id+父级维度+维度层级字段
lvl_id1 | lvl_name1 | lvl_id2 | lvl_name2 | lvl_id3 | lvl_name3 |
id | parent_id | id_lvl |
oracle通用维转父子维
将顶级机构抽取插入:Insert into dim_org_3 (select distinct org_lvl1_id,org_lvl1_desc,null,null,as_of_date,null from dim_org_lvl where org_lvl1_id is not null)
将2级机构抽取插入:Insert into dim_org_3 (select distinctorg_lvl2_id,org_lvl2_desc,org_lvl1_id,org_lvl1_desc,as_of_date,null from dim_org_lvl where org_lvl2_id is not null)
将3级机构抽取插入:insert into dim_org_3 (select distinctorg_lvl3_id,org_lvl3_desc,org_lvl2_id,org_lvl2_desc,2,as_of_date,null from dim_org_lvl where org_lvl3_id is not null)
oracle父子维转通用维
SELECT A.ORG_ID AS ORG_LVL1_ID,
A.ORG_NAME AS ORG_LVL1_NAME,
B.ORG_ID AS ORG_LVL2_ID,
B.ORG_NAME AS ORG_LVL2_NAME,
C.ORG_ID AS ORG_LVL3_ID,
C.ORG_NAME AS ORG_LVL3_NAME,
D.ORG_ID AS ORG_LVL4_ID,
D.ORG_NAME AS ORG_LVL4_NAME,
E.ORG_ID AS ORG_LVL5_ID,
E.ORG_NAME AS ORG_LVL5_NAME,
F.ORG_ID AS ORG_LVL6_ID,
F.ORG_NAME AS ORG_LVL6_NAME,
NVL(F.ORG_NAME,
NVL(E.ORG_NAME,
NVL(D.ORG_NAME, NVL(C.ORG_NAME,NVL(B.ORG_NAME, A.ORG_NAME))))) AS ORG_NAME,
A.BBQ_||A.USERID_|| NVL(F.ORG_ID,
NVL(E.ORG_ID,
NVL(D.ORG_ID, NVL(C.ORG_ID,NVL(B.ORG_ID, A.ORG_ID))))) AS ORG_ID
FROM DIM_ORG A
LEFT JOIN DIM_ORG B ON B.UP_ORG_ID = A.ORG_ID
AND B.ORG_LEVEL ='1'
LEFT JOIN DIM_ORG C ON C.UP_ORG_ID = B.ORG_ID
AND C.ORG_LEVEL ='2'
LEFT JOIN DIM_ORG D ON D.UP_ORG_ID = C.ORG_ID
AND D.ORG_LEVEL ='3'
LEFT JOIN DIM_ORG E ON E.UP_ORG_ID = D.ORG_ID
AND E.ORG_LEVEL ='4'
LEFT JOIN DIM_ORG F ON F.UP_ORG_ID = E.ORG_ID
AND F.ORG_LEVEL ='5'
WHERE A.ORG_LEVEL = '0';