请求详解Snowflake非物化递归视图：含用途及示例代码解析

一、非物化递归视图的概念 #

• 非物化递归视图：和物化视图不同，它不会预存计算结果，每次查询时才实时生成数据。通过RECURSIVE关键字定义，核心由两部分构成：
  • 锚点查询：提供递归的基础数据集（通常是层级的顶层节点）
  • 递归查询：引用视图自身，基于锚点结果循环迭代，直到没有新数据返回
• 用UNION ALL连接锚点与递归部分，实现层级数据的遍历。

二、非物化递归视图的用途 #

• 处理树形/层级结构数据：比如员工上下级、组织架构、目录文件系统、商品品类层级等带有父子关系的数据
• 适配数据动态变化：无需预先存储层级结果，数据更新（如员工调岗、新增人员）后，查询视图会自动返回最新的层级关系
• 简化复杂查询：避免编写多层嵌套JOIN，用递归逻辑一键遍历所有层级

三、示例代码逐行解释 #

1. 创建员工表 #

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);

• 定义名为employees的表，存储三个字段：员工职位、员工ID、直属经理ID；CREATE OR REPLACE表示若表已存在则覆盖重建。

2. 插入测试数据 #

INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

• 插入6条数据构建三层组织架构：
  • 总裁（ID1）为顶层，无直属经理（manager_ID为NULL）
  • 两位副总裁（ID10、20）直属总裁
  • 3名基层员工分别对应直属副总裁

3. 创建递归视图 #

CREATE or replace RECURSIVE VIEW employee_hierarchy_02 (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION all
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_02.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_02.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_02
        WHERE employee_hierarchy_02.employee_ID = employees.manager_ID
);

• 视图基础定义：创建（或替换）名为employee_hierarchy_02的递归视图，指定5个输出字段：员工职位、员工ID、经理ID、验证用经理ID、经理职位。
• 锚点查询（递归起点）：

  SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
    FROM employees
    WHERE title = 'President'
  

  从员工表筛选出总裁数据作为递归起点，因总裁无上级，"MGR_EMP_ID (SHOULD BE SAME)"设为NULL，"MGR TITLE"固定为'President'。
• 递归查询（迭代逻辑）：

  SELECT employees.title, 
         employees.employee_ID, 
         employees.manager_ID, 
         employee_hierarchy_02.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
         employee_hierarchy_02.title AS "MGR TITLE"
    FROM employees INNER JOIN employee_hierarchy_02
    WHERE employee_hierarchy_02.employee_ID = employees.manager_ID
  

  通过INNER JOIN关联当前视图结果与员工表，关联条件为视图中员工ID = 员工表记录的经理ID，即找到当前层级员工的直属下属；同时将当前层级员工的ID和职位作为下属的经理信息填充到对应字段，"MGR_EMP_ID (SHOULD BE SAME)"用于验证该值与manager_ID一致，确保关联逻辑正确。
• UNION ALL：合并锚点与递归查询结果，递归查询会重复执行，直到找不到新的下属数据（遍历到最基层员工后）才停止。

四、视图查询结果说明 #

查询视图会得到完整的层级关系：

可见MGR_EMP_ID (SHOULD BE SAME)与manager_ID完全匹配，递归逻辑验证通过。

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