1. 新建测试表
CREATE table org_info
(
org CHAR(4),
parent_org CHAR(4)
);
2. 插入测试数据
insert into org_info(org,parent_org) values('1100','0200');
insert into org_info(org,parent_org) values('0200','0300');
insert into org_info(org,parent_org) values('0300','0100');
insert into org_info(org,parent_org) values('3100','4100');
insert into org_info(org,parent_org) values('4100','0100');
3.离线批量加工递归问题转为非递归
背景:机构表经常查数据需要实现机构树的构建,确定哪些机构有权限访问本条数据,
注,上级机构可以访问下级机构数据,下级不能访问上级机构的数据。
对于递归深度比较浅的问题,可以直接使用left join去实现递归的拆解,效率比递归要快很多。
- 首先将父级的依赖转化到一个类似数组(递归行转化为列)
select org0.org
,case when org1.org is null then org0.parent_org
when org2.org is null then concat(org1.org,',',org1.parent_org)
else concat(org1.org,',',org2.org,',',org2.parent_org)
end as parent_org
from org_info org0
left join org_info org1
on org0.parent_org = org1.org
left join org_info org2
on org1.parent_org = org2.org
每一个机构号都对应有一个父级机构数组,只需查询父级数组就能确定权限问题
- 以上一步数据为基础,对数组进行拆分(列转行的问题)
知识点:
split()实现对数组的分拆
explode()单行数据拆解成多行,可用于map
lateral view 侧视图
lateral view explode() 单行拆解成多行构建一个侧视图与主表进行笛卡尔积关联
select
org
,parent_org_new
from
(
select org0.org
,case when org1.org is null then org0.parent_org
when org2.org is null then concat(org1.org,',',org1.parent_org)
else concat(org1.org,',',org2.org,',',org2.parent_org)
end as parent_org
from org_info org0
left join org_info org1
on org0.parent_org = org1.org
left join org_info org2
on org1.parent_org = org2.org
)a
lateral view explode(split(a.parent_org,',')) num as parent_org_new
代码效果
行转列的应用
以上数据新建一个org_list表存储,作为测试数据
知识点
1,concate_ws()对集合的数据,进行分隔形成字符串
2,collect_set()分组中的某列转为一个数组,去重后,返回
3,collect_list()分组中的某列转为一个数组,返回
测试表以步骤3第2步的数据作为数据源
select
org
,concat_ws(',',collect_set(parent_org_new)) as parent_org
from org_list
GROUP BY org
;
突破group by限制
实现某一列不是分组列,但却需要随机取出该列随机一条数据
参考Hive笔记之collect_list/collect_set(列转行)
select
org
,collect_set(parent_org_new)[0] as parent_org
from org_list
GROUP BY org