查询分层数据

本主题介绍如何使用以下方法存储和查询分层数据：

  • JOINs

  • 递归 CTEs（公共表表达式）

  • CONNECT BY

另请参阅：

CONNECT BY、WITH 命令的递归 CTE 部分、使用 CTEs （公用表表达式）、表格 SQL UDFs (UDTFs)

本主题内容：

存储分层数据

对许多类型的数据而言，最佳的表示方式都是层次结构，例如树。

举例来说，员工通常按层次结构组织，公司总裁位于层次结构的顶端。

层次结构的另一个例子是“部件分解”。例如，汽车包含发动机；发动机包含燃油泵；燃油泵包含软管。

您可将分层数据存储在以下位置：

  • 表层次结构。

  • 具有表示层次结构的一列（或多列）的单个表（例如，指示每个员工的直接经理）。

下面将介绍这两种技术。

备注

本主题重点介绍存储为 结构化 数据形式的分层数据。分层数据也可存储为半结构化数据形式（例如，JSON 数据可存储为 ARRAY、OBJECT 或 VARIANT 数据类型）。关于半结构化数据的信息，请参阅：

跨多个表的分层数据

关系数据库通常使用不同的表来存储分层数据。例如，一个表包含“父”数据，另一个表包含“子”数据。如果事先了解整个层次结构，即可为层次结构中的每个层创建一个表。

以存储员工信息和经理信息的人力资源数据库为例。如果公司规模很小，可能只有两个级别，例如，一名经理和两名员工。

CREATE OR REPLACE TABLE managers  (title VARCHAR, employee_ID INTEGER);
Copy
CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
Copy
INSERT INTO managers (title, employee_ID) VALUES
    ('President', 1);
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('Vice President Engineering', 10, 1),
    ('Vice President HR', 20, 1);
Copy

单个表中的分层数据

在某些情况下，层次结构中的级别数量可能会发生变化。

例如，随着公司的发展壮大，最初只有两级层次结构（总裁及其他员工）的公司可能拥有更多级别。公司可能会扩大到包括总裁、副总裁和正式员工这几个级别。

如果级别数量未知，也就无法创建具有已知表数量的层次结构，在某些此类情况下，可将分层数据存储在一个表中。例如，可在单个表中包含所有员工，并且可以包含存储每个员工的 manager_ID 列，该列指向同一表中的另一位员工。例如：

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
Copy
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);
Copy

如果层次结构的所有级别存储的都是相同的数据，在本例中是员工 ID、职务等数据，则将整个数据层次结构存储在一个表中效果最好。如果不同级别的数据不适合相同的记录结构，那么将所有数据都存储在一个表中可能不切实际。

使用联接来查询分层数据

在两级层次结构（例如，经理和员工）中，可使用双向联接来查询数据：

SELECT 
        employees.title, 
        employees.employee_ID, 
        managers.employee_ID AS MANAGER_ID, 
        managers.title AS "MANAGER TITLE"
    FROM employees, managers
    WHERE employees.manager_ID = managers.employee_ID
    ORDER BY employees.title;
+----------------------------+-------------+------------+---------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE |
|----------------------------+-------------+------------+---------------|
| Vice President Engineering |          10 |          1 | President     |
| Vice President HR          |          20 |          1 | President     |
+----------------------------+-------------+------------+---------------+
Copy

在三级层次结构中，可以使用三向联接：

SELECT
     emps.title,
     emps.employee_ID,
     mgrs.employee_ID AS MANAGER_ID, 
     mgrs.title AS "MANAGER TITLE"
  FROM employees AS emps LEFT OUTER JOIN employees AS mgrs
    ON emps.manager_ID = mgrs.employee_ID
  ORDER BY mgrs.employee_ID NULLS FIRST, emps.employee_ID;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE              |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+
Copy

此概念可按需扩展到任意多个级别，只要您知道需要多少个级别即可。但是，如果级别数量发生变化，则查询需要更改。

使用 CONNECT BY 或递归的 CTEs 查询分层数据

对于事先不知道级别数量的分层数据，Snowflake 提供了两种查询方法：

  • 递归的 CTEs（公用表表达式）。

  • CONNECT BY 子句。

递归的 CTE 允许创建可以引用自身的 WITH 子句。这允许迭代访问层次结构的每个级别，并累计结果。

CONNECT BY 子句允许创建一种 JOIN 操作类型，一次处理层次结构的一个级别，并允许每个级别引用前一级别的数据。

有关更多详细信息，请参阅：

自联接、递归的 CTE 与 CONNECT BY 之间的差异

CONNECT BY 仅允许自联接。递归的 CTEs 更加灵活，允许将一个表联接到一个或多个其他表。

CONNECT BY 子句具有递归的 CTE 的大部分功能。但是，递归的 CTE 可以执行 CONNECT BY 做不到的一些事情。

例如，查看递归的 CTE 示例，可以看到其中一个查询对输出进行了缩进和排序，让每个“子”项都出现在相应的“父”项下方。排序是通过创建一个排序键来完成的，该排序键包含从顶部一直到当前级别的 IDs 链。在经理/员工的示例中，该链包含总裁的 ID，然后是副总裁的 ID，以此类推。这个排序键以类似于横向树的方式对行进行分组。CONNECT BY 语法不支持此操作，因为“START WITH”子句不允许代码指定额外的列（即超出表本身的列），例如 sort_key。请对比下面两个代码片段：

SELECT indent(LEVEL) || employee_ID, manager_ID, title
  FROM employees
    -- This sub-clause specifies the record at the top of the hierarchy,
    -- but does not allow additional derived fields, such as the sort key.
    START WITH TITLE = 'President'
    CONNECT BY ...

WITH RECURSIVE current_layer
   (employee_ID, manager_ID, sort_key) AS (
     -- This allows us to add columns, such as sort_key, that are not part
     -- of the employees table.
     SELECT employee_ID, manager_ID, employee_ID AS sort_key
     ...
     )
Copy

但您可以使用 SYS_CONNECT_BY_PATH 函数来实现与 CONNECT BY 子句类似的效果。

尽管 CONNECT BY 子句版本受限，因为 START WITH 子句不能在行中已有列的基础之上添加列（甚至不能基于行中已有的值派生列），但它也有一些优点：

  • 您可以访问每行的所有列，而不需要在列的列表中指定这些列。在递归的 CTE 中，递归子句无权访问未在 CTE 中显式指定的列。

  • 在递归的 CTE 中，必须在 CTE 中指定列，并且锚定子句和递归子句中的 select 的投影列表均必须与 CTE 中的列匹配。如果不同的投影子句中的列顺序不匹配，则可能会导致无限循环等问题。

  • CONNECT BY 语法支持方便的伪列，例如 LEVEL、CONNECT_BY_ROOT 和 CONNECT_BY_PATH

CONNECT BY 与递归的 CTE 之间有一个细微区别，在递归的 CONNECT BY 中，使用 PRIOR 关键字来指示应从上一个迭代中获取哪些列值，而在递归的 CTE 中，要使用表名和 CTE 名称来指示哪些值是从当前迭代中获取的，哪些值是从上一个迭代中获取的。（在递归的 CTE 中，您还可以在 CTE 列的列表中使用与源表或表的表达式中的列名不同的列名，以区分当前迭代与上一个迭代。）

非连续层次结构

本主题介绍了层次结构以及递归的 CTEs （公用表表达式）和 CONNECT BY 子句如何使用父子关系。在本主题的所有示例以及 CONNECT BY 文档和递归的 CTE 文档中的所有示例中，使用的层次结构都是连续的。没有一个示例有父项和孙项，它们之间也没有对应的子项。

例如，如果您对一辆汽车进行“部件分解”，在没有包含轮胎的车轮组件（以及包含在汽车中的车轮组件）的前提下，您无法获得一个汽车组件和一个轮胎组件。

但在某些情况下，数据可能不完整。例如，在员工/经理层次结构中，假设工程副总裁退休，而公司没有立即聘用接任者。如果删除了这位 VP 的员工记录，则其 VP 下级的员工将与层次结构的其余部分“切断”，导致员工表不再包含一个连续的层次结构。

如果使用递归的 CTEs 或 CONNECT BY 处理数据，则需要考虑表中的数据是否表示单独一个连续的树。可在包含多个树的单个表上使用递归的 CTEs 和 CONNECT BY，但一次只能查询一个树，并且该树必须是连续的。

语言: 中文