一.数据源:
id,city,parentId
1,北京市,0
2,山东省,0
3,昌平区,1
4,海淀区,1
5,沙河镇,3
6,马池口镇,3
7,中关村,4
8,上地,4
9,烟台市,2
10,青岛市,2
11,单平区,9
12,芝果区,9
13,即墨区,10
14,城阳,10
注意: 一个id 唯一对应一个ParentId
一个ParentId可以对应多个id
二.需要结果:
一级 二级 三级
0 1 3
1 北京市 昌平区 沙河镇
2 北京市 昌平区 马池口镇
4
3 北京市 海淀区 中关村
4 北京市 海淀区 上地
0 2 9
5 山东省 烟台市 单平区
6 山东省 烟台市 芝果区
10
7 山东省 青岛市 即墨区
8 山东省 青岛市 城阳
三.hive sql
-- 创建表
create table if not exists city(
id int,
name string,
parentId int
)
row format delimited fields terminated by ',';
-- 初始化数据
load data local inpath '/opt/data/city.txt' overwrite into table city;
-- 统计
select c.level1,c.level2,d.name level3
from
(
select a.name as level1,b.name as level2,
case
when b.name='昌平区' then 1
when b.name='海淀区' then 2
when b.name='烟台市' then 3
else 4 end as joinId
from
(select name,if(name='北京市',1,0) joinId from city
where parentId=0) a
join
(select name,if(name='昌平区' or name='海淀区',1,0) joinId from city
where parentId in(1,2)) b
on a.joinId=b.joinId
) c
join
(
select name,
case
when name='沙河镇' or name ='马池口镇' then 1
when name='中关村' or name ='上地' then 2
when name='单平区' or name ='芝果区' then 3
else 4 end as joinId
from city
where parentId in(3,4,9,10)
) d
on c.joinId=d.joinId;
# 更好的解决方式
with t as (select id,name,parentId from city)
select c.name level1,b.name level2,a.name level3 from t a
join t b on a.parentId=b.id
join t c on b.parentId=c.id;
level1 level2 level3
北京市 昌平区 沙河镇
北京市 昌平区 马池口镇
北京市 海淀区 中关村
北京市 海淀区 上地
山东省 烟台市 单平区
山东省 烟台市 芝果区
山东省 青岛市 即墨区
山东省 青岛市 城阳
Time taken: 9.515 seconds, Fetched: 8 row(s)
# 过程
with t as (select id,name,parentId from city)
select a.*,b.* from t a
join t b on a.parentId=b.id;
a.id a.name a.parentid b.id b.name b.parentid 关联 city.id city.name city.parentid
3 昌平区 1 1 北京市 0 1 北京市 0
4 海淀区 1 1 北京市 0 2 山东省 0
9 烟台市 2 2 山东省 0 3 昌平区 1
10 青岛市 2 2 山东省 0 4 海淀区 1
5 沙河镇 3 3 昌平区 1 5 沙河镇 3
6 马池口镇 3 3 昌平区 1 6 马池口镇 3
7 中关村 4 4 海淀区 1 7 中关村 4
8 上地 4 4 海淀区 1 8 上地 4
11 单平区 9 9 烟台市 2 9 烟台市 2
12 芝果区 9 9 烟台市 2 10 青岛市 2
13 即墨区 10 10 青岛市 2 11 单平区 9
14 城阳 10 10 青岛市 2 12 芝果区 9
Time taken: 16.461 seconds, Fetched: 12 row(s) 13 即墨区 10
with t as (select id,name,parentId from city)
select a.*,b.*,c.* from t a
join t b on a.parentId=b.id
join t c on b.parentId=c.id;
a.id a.name a.parentid b.id b.name b.parentid c.id c.name c.parentid
5 沙河镇 3 3 昌平区 1 1 北京市 0
6 马池口镇 3 3 昌平区 1 1 北京市 0
7 中关村 4 4 海淀区 1 1 北京市 0
8 上地 4 4 海淀区 1 1 北京市 0
11 单平区 9 9 烟台市 2 2 山东省 0
12 芝果区 9 9 烟台市 2 2 山东省 0
13 即墨区 10 10 青岛市 2 2 山东省 0
14 城阳 10 10 青岛市 2 2 山东省 0
# 自定义UDF出结果
select level1,level2,level3
from (
select
city(id,'city') level3,
city(cast(city(id,'code') as int),'city') level2,
city(cast(city(cast(city(id,'code') as int),'code') as int),'city') level1
from city
) a where level1 !='';
# 测试
# 根据id查父id
select id,city(id,'code') from city;
# 根据id查城市名
select city(id,'city') from city;
四. 输出结果
Total MapReduce CPU Time Spent: 0 msec
OK
c.level1 c.level2 level3
北京市 昌平区 沙河镇
北京市 昌平区 马池口镇
北京市 海淀区 中关村
北京市 海淀区 上地
山东省 烟台市 单平区
山东省 烟台市 芝果区
山东省 青岛市 即墨区
山东省 青岛市 城阳
Time taken: 37.589 seconds, Fetched: 8 row(s)
|