请求将Oracle SQL查询改写为Databricks兼容的SQL查询

没问题，我来帮你把这段Oracle SQL转换成Databricks SQL（基于Spark SQL）兼容的版本。核心的改动是替换Oracle特有的层级查询语法（CONNECT BY、CONNECT_BY_ROOT、SYS_CONNECT_BY_PATH），因为Databricks用递归CTE（WITH RECURSIVE）来处理树形结构遍历，下面是具体的改写过程和最终代码：

原Oracle查询的核心逻辑回顾 #

原查询从PR.DEPDGDGCY表出发，以DEP_O_TYP = 'ALYC_S'的节点为根，遍历层级结构（避免循环），获取根节点ID、每个节点的上级ID、层级路径，再关联其他业务表后去重、排序，最终输出ANLY_S_ID和OG_ID。

适配Databricks的改写版本 #

WITH RECURSIVE dep_hierarchy AS (
    -- 初始CTE：获取根节点（DEP_O_TYP = 'ALYC_S'）的基础信息
    SELECT
        DEP.DEP_O_ID AS STT_O_ID,
        DEP.DEP_O_TYP AS STA_O_TYP,
        DEP.DEP_U_ID,
        DEP.DEP_UP_TYP,
        1 AS L2CHILD,  -- 初始层级为1
        '/' || DEP.DEP_U_ID AS PRNT2CHILD_PATH  -- 初始路径
    FROM PR.DEPDGDGCY DEP
    WHERE DEP.DEP_UP_TYP <> 'ALYC_S'
      AND DEP.E_DT > CURRENT_DATE
      AND DEP.DEP_O_TYP = 'ALYC_S'  -- 起始节点条件

    UNION ALL

    -- 递归CTE：遍历子节点，传递根节点信息并构建路径
    SELECT
        parent.STT_O_ID,  -- 始终传递根节点的ID
        parent.STA_O_TYP,
        child.DEP_U_ID,
        child.DEP_UP_TYP,
        parent.L2CHILD + 1 AS L2CHILD,
        parent.PRNT2CHILD_PATH || '/' || child.DEP_U_ID AS PRNT2CHILD_PATH
    FROM dep_hierarchy parent
    JOIN PR.DEPDGDGCY child
        ON parent.DEP_U_ID = child.DEP_O_ID
        AND parent.DEP_UP_TYP = child.DEP_O_TYP
    WHERE child.DEP_UP_TYP <> 'ALYC_S'
      AND child.E_DT > CURRENT_DATE
      -- 避免循环：确保当前节点还没出现在路径里（替代Oracle的NOCYCLE）
      AND NOT parent.PRNT2CHILD_PATH LIKE '%/' || child.DEP_U_ID || '/%'
),
-- 去重处理：对应原查询里的多层DISTINCT
distinct_hierarchy AS (
    SELECT DISTINCT
        STT_O_ID,
        DEP_U_ID,
        STA_O_TYP,
        DEP_UP_TYP,
        L2CHILD,
        PRNT2CHILD_PATH
    FROM dep_hierarchy
)
SELECT DISTINCT
    Y.STT_O_ID AS ANLY_S_ID,
    Y.OG AS OG_ID
FROM (
    SELECT DISTINCT
        M.STT_O_ID,
        O.OB_OF_ANA_C_ID AS OG
    FROM distinct_hierarchy M
    LEFT JOIN PR.PLE_NUE_SRC R
        ON R.PLE_ID = M.DEP_U_ID
        AND R.E_DT > CURRENT_DATE
    LEFT JOIN PR.REV_SRC_IO RV
        ON RV.REV_S_ID = R.REV_S_ID
        AND RV.E_DT > CURRENT_DATE
    LEFT JOIN PR.REV_GOR O
        ON RV.REV_OIGR_ID = O.REV_OIGR_ID
    ORDER BY M.STT_O_ID, OG ASC
) Y
LEFT JOIN CRE.O_NMS ORGN
    ON Y.OG = ORGN.OG_ID
    AND ORGN.CU_N_IND = 'Y'
LEFT JOIN CRE.ORGAFDHTIO OG
    ON Y.OG = OG.OG_ID
LEFT JOIN PR.SM_KY SK
    ON Y.STT_O_ID = SK.SM_KY_O_ID
    AND SK.SMART_KEY_OBJ_TYP = 'ALYC_S'
    AND SK.E_DT > CURRENT_DATE
ORDER BY ANLY_S_ID;

关键改动说明 #

• 递归CTE替代CONNECT BY：用WITH RECURSIVE定义初始节点和递归逻辑，替代Oracle的CONNECT BY NOCYCLE和START WITH。
• 根节点传递：在递归过程中始终保留根节点的STT_O_ID，替代Oracle的CONNECT_BY_ROOT函数。
• 路径构建：通过字符串拼接||逐步构建层级路径，替代Oracle的SYS_CONNECT_BY_PATH。
• 循环避免：通过检查路径中是否已包含当前节点，替代Oracle的NOCYCLE关键字。
• 日期函数替换：用Databricks支持的CURRENT_DATE替代Oracle的SYSDATE（如果需要时间精度可以用CURRENT_TIMESTAMP）。

备注：内容来源于stack exchange，提问作者RatnakarRao M