HIVE SQL 将父子/树形结构转换成层级结构

如下需求：

将左边父子结构转换成右边层级结构（转换到三个层级即可）。

难点解析：父子层级表难点主要在层级深度，父子结构解析需要自连接及id=parent_id逐步进行处理，第一次连接可以获取到两个层级，当第二次自连接即可获取到第三个层级

--1、当前为层级固定时
--2、层级不固定时 ...较为复杂
with area_info as
 (select 1 as id, 北京市     as name, 0 as parent_id union all
  select 2 as id, 山东省     as name, 0 as parent_id union all
  select 3 as id, 昌平区     as name, 1 as parent_id union all
  select 4 as id, 海淀区     as name, 1 as parent_id union all
  select 5 as id, 沙河镇     as name, 3 as parent_id union all
  select 6 as id, 马池口镇    as name, 3 as parent_id union all
  select 7 as id, 中关村     as name, 4 as parent_id union all
  select 8 as id, 上地       as name, 4 as parent_id union all
  select 9 as id, 烟台市     as name, 2 as parent_id union all
  select 10 as id, 即墨区    as name, 9 as parent_id union all
  select 11 as id, 牟平区    as name, 9 as parent_id
),
P1 AS
 (SELECT 
         p.ID       LEVEL_1_ID,
         p.NAME     LEVEL_1_NAME,
         s.ID        LEVEL_2_ID,
         s.NAME      LEVEL_2_NAME,
         NULL        LEVEL_3_ID,
         NULL        LEVEL_3_NAME,
         NULL        LEVEL_4_ID,
         NULL        LEVEL_4_NAME
    FROM area_info s --子
    JOIN area_info p --父
      ON s.parent_id = p.id)
      ,
P2 AS
 (
  SELECT 
         p.LEVEL_1_ID,
         p.LEVEL_1_NAME,
         p.LEVEL_2_ID,
         p.LEVEL_2_NAME,
         s.ID            LEVEL_3_ID,
         s.NAME          LEVEL_3_NAME,
         NULL            LEVEL_4_ID,
         NULL            LEVEL_4_NAME
    FROM area_info s
    JOIN P1 p
      ON (s.parent_id = p.LEVEL_2_ID)
/*  UNION ALL
  SELECT *
    FROM P1*/
    )
SELECT LEVEL_1_NAME,LEVEL_2_NAME,LEVEL_3_NAME FROM P2;