递归查询子机构对应的分行机构

背景：

数据仓库设计宽表的时候，通常会是最细粒度的，之后的指标汇总都从宽表出发。银行系统或其他大型公司，全国各地都有分支机构。这就导致宽表的组织粒度可能只是到最底层的分支机构，我们统计报表，通常是高层级领导查看，不会关心最低级机构的明细业务情况。通常就会出现将报表统计到省级分行或者公司总部机构粒度的需求。

这个时候就可以参考如下方法，当然前提是我们的数据仓库将组织机构表处理成了我们想要的这种具有层级概念的组织机构表。

这里插一句话，其实地区码值表也可以做成这种有层级关系的表结构，但是通常我们很少见到这样设计的，具体原因可能是地区国家标准可能会随时变更，后续不好处理。

机构层级有8层（1-8）表结构如下

SELECT  org_cd,--机构编号
        org_name,--机构名称
        org_level_cd,--机构层级
        prt_org_cd--上级机构编号
    FROM 
        organization

Hive 版本支持递归CTE（3.1.0及以后的版本）对应的递归sql：

WITH RECURSIVE OrgHierarchy AS (
    -- 基础查询，找到所有2级机构
    SELECT 
        org_cd AS level_2_org_cd,  -- 2级机构编号
        org_name AS level_2_org_name,  -- 2级机构名称
        org_cd,
        org_name,
        org_level_cd,
        prt_org_cd
    FROM 
        organization
    WHERE 
        org_level_cd = 2

    UNION ALL

    -- 递归查询，找到3到8级的机构，并追踪它们所属的2级机构
    SELECT 
        parent.level_2_org_cd,  -- 使用递归结果中的2级机构编号
        parent.level_2_org_name,  -- 使用递归结果中的2级机构名称
        child.org_cd,  -- 3到8级机构编号
        child.org_name,  -- 3到8级机构名称
        child.org_level_cd,
        child.prt_org_cd
    FROM 
        OrgHierarchy parent
    JOIN 
        organization child
    ON 
        parent.org_cd = child.prt_org_cd
    WHERE 
        child.org_level_cd BETWEEN 3 AND 8
)

SELECT 
    child.org_cd AS org_cd,  -- 3到8级机构编号
    child.org_name AS org_name,  -- 3到8级机构名称
    parent.level_2_org_cd AS level_2_org_cd,  -- 对应的2级机构编号
    parent.level_2_org_name AS level_2_org_name  -- 对应的2级机构名称
FROM 
    OrgHierarchy child
JOIN 
    OrgHierarchy parent
ON 
    child.prt_org_cd = parent.org_cd
WHERE 
    child.org_level_cd BETWEEN 3 AND 8;