Snowflake递归CTE查询：为员工表添加ED_ID层级字段

需求说明 #

现有Snowflake中的emp员工表，包含loginName、loginID、managerid字段（默认假设表中有position字段用于标识岗位，如position = 'ED'代表ED岗位）。需为每个员工新增ED_ID字段：

• 若员工的上级层级中存在ED岗位，取最近的那个ED岗位对应的loginID
• 若整个层级中无ED岗位，ED_ID设为NULL

解决方案SQL #

WITH RECURSIVE emp_hierarchy AS (
    -- 初始层：加载员工基础数据，先判断自身是否为ED岗位
    SELECT 
        loginName,
        loginID,
        managerid,
        position,
        IFF(position = 'ED', loginID, NULL) AS ED_ID,
        1 AS hierarchy_level
    FROM emp
    UNION ALL
    -- 递归层：向上遍历经理层级，仅对未找到ED的员工继续追踪
    SELECT 
        e.loginName,
        e.loginID,
        m.managerid,
        m.position,
        -- 已找到ED则保留原值，未找到则检查当前经理是否为ED
        IFF(e.ED_ID IS NOT NULL, e.ED_ID, IFF(m.position = 'ED', m.loginID, NULL)) AS ED_ID,
        e.hierarchy_level + 1 AS hierarchy_level
    FROM emp_hierarchy e
    JOIN emp m ON e.managerid = m.loginID
    WHERE e.ED_ID IS NULL -- 终止已找到ED的员工递归
)
-- 筛选每个员工的最浅层级记录（即最近的ED岗位匹配结果）
SELECT 
    loginName,
    loginID,
    managerid,
    ED_ID
FROM emp_hierarchy
QUALIFY ROW_NUMBER() OVER (PARTITION BY loginID ORDER BY hierarchy_level) = 1
ORDER BY loginID;

关键逻辑解释 #

• 递归CTE设计：
  • 初始层先检查员工自身是否为ED，是则直接赋值ED_ID，否则留空，同时标记层级深度
  • 递归层仅处理未找到ED的员工，向上关联经理数据，若经理是ED则赋值ED_ID，否则继续向上遍历
• 最终筛选：通过QUALIFY和ROW_NUMBER()确保每个员工只保留层级最浅的记录，保证拿到的是最近的ED岗位
• 适配调整：如果岗位标识不在position字段（比如在loginName中），只需修改position = 'ED'对应的判断逻辑即可；若存在层级循环风险，可新增字段记录已遍历的loginID避免无限递归

内容的提问来源于stack exchange，提问作者Koushur