flink/hive sql 实现递归 查询人员组织架构

递归人员归属查询

• 解决的问题：flink sql无法使用递归查询的问题
• 场景

  • syc_loan_dpt(did, dname, dlevel, pdid)、syc_agent_usr(uid、uname、did)

  • 组织架构：元事业部/城市中心/分中心/中心分部/归属阶段/归属小组

  • 每个销售经归属，叶子节点可能为level in (6, 7, 8), 要求从原事业部开始到叶子节点

业务库（Oracle）

selecet
did, dname, dlevel, pdid
from syc_loan_dpt
start with dlevel='2'
connect by prior did=pdid
where did=(select did from syc_agent_usr where uid='xx')

Flink数仓查询归属层级（通过冗余组织层级，然后用户表关联其所属层级）

--叶子节点为6
with dpt as (
    select did, dname, dlevel, pdid from syc_loan_dpt
)
select
	 u.*, d.*
from syc_agent_usr u
left join (
    select
    	t4.did
        ,t1.dname	source	--原事业部
        ,t2.dname   city	--城市中心
        ,t3.dname	sub_city--城市分中心
        ,t4.dname	sub_dpt	--分部
    	,null       belong
		,null       group
    from dpt t1
    inner join t2 on t1.did=t2.pdid
    inner join t3 on t2.did=t3.pdid
    inner join t4 on t3.did=t4.pdid
    where t1.dlevel='3'

    union all 
    --叶子节点为7
    select
    	,t5.did
        ,t1.dname	source	--原事业部
        ,t2.dname   city 	--城市中心
        ,t3.dname	sub_city--城市分中心
        ,t4.dname	sub_dpt	--分部
        ,t5.dname	belong	--所属阶段
		,null       group
	from dpt t1
    inner join t2 on t1.did=t2.pdid
    inner join t3 on t2.did=t3.pdid
    inner join t4 on t3.did=t4.pdid
    inner join t5 on t4.did=t5.pdid
    where t1.dlevel='3'

    union all
    --叶子节点为8
    select
    	t6.did
        ,t1.dname	source	--原事业部
        ,t2.dname   city 	--城市中心
        ,t3.dname	sub_city--城市分中心
        ,t4.dname	sub_dpt	--分部
        ,t5.dname	belong	--所属阶段
        ,t6.dname	group	--所属小组
    from dpt t1
    inner join t2 on t1.did=t2.pdid
    inner join t3 on t2.did=t3.pdid
    inner join t4 on t3.did=t4.pdid
    inner join t5 on t4.did=t5.pdid
    inner join t6 on t5.did=t6.pdid
    where t1.dlevel='3'
) d on u.did=d.did