使用递归 CTE

在 GoogleSQL for BigQuery 中，WITH 子句包含一个或多个通用表表达式 (CTE) 以及可以在查询表达式中引用的临时表。CTE 可以是非递归和/或递归的。RECURSIVE 关键字在 WITH 子句 (WITH RECURSIVE) 中启用递归。

递归 CTE 可以引用自身、前一个 CTE 或后一个 CTE。非递归 CTE 只能引用前面的 CTE，并且不能引用其自身。递归 CTE 会持续运行，直至找不到新结果，而非递归 CTE 只运行一次。由于这些特性，递归 CTE 通常用于查询分层数据和图表数据。

例如，假设有一个图表，其中每一行代表一个可以链接到其他节点的节点。如需查找从某个特定起始节点开始的所有可访问节点的传递性闭环，并且不知道最大跃点数，您需要在查询中使用递归 CTE (WITH RECURSIVE)。递归查询将从起始节点的基本案例开始，每个步骤都将计算可从到上一步为止的所有已知节点访问的未知新节点。查询会在无法再找到新节点时结束。

但是，递归 CTE 的计算开销可能很大，因此在使用它们之前，请查看本指南和 GoogleSQL 参考文档的 WITH 子句部分。

创建递归 CTE

如需在 GoogleSQL 中创建递归 CTE，请使用 WITH RECURSIVE 子句，如以下示例所示：

WITH RECURSIVE
  CTE_1 AS (
    (SELECT 1 AS iteration UNION ALL SELECT 1 AS iteration)
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3
  )
SELECT iteration FROM CTE_1
ORDER BY 1 ASC

上述示例生成以下结果：

/*-----------*
 | iteration |
 +-----------+
 | 1         |
 | 1         |
 | 2         |
 | 2         |
 | 3         |
 | 3         |
 *-----------*/

递归 CTE 包括基本部分、联合运算符和递归部分。基本部分运行递归联合运算的第一次迭代。递归部分运行其余迭代，并且必须包含对递归 CTE 的一个自引用。只有递归部分可以包含自引用。

在前面的示例中，递归 CTE 包含以下组成部分：

• 递归 CTE 名称：CTE_1
• 基本部分：SELECT 1 AS iteration
• 联合运算符：UNION ALL
• 递归部分：SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3

如需详细了解递归 CTE 语法、规则和示例，请参阅 GoogleSQL 参考文档中的 WITH 子句。

探索有向无环图 (DAG) 中的可达性

您可以使用递归查询来探索有向无环图 (DAG) 中的可达性。以下查询查找名为 GraphData 的图中可从节点 5 访问的所有节点：

WITH RECURSIVE
  GraphData AS (
    --    1          5
    --   / \        / \
    --  2 - 3      6   7
    --      |       \ /
    --      4        8
    SELECT 1 AS from_node, 2 AS to_node UNION ALL
    SELECT 1, 3 UNION ALL
    SELECT 2, 3 UNION ALL
    SELECT 3, 4 UNION ALL
    SELECT 5, 6 UNION ALL
    SELECT 5, 7 UNION ALL
    SELECT 6, 8 UNION ALL
    SELECT 7, 8
  ),
  R AS (
    (SELECT 5 AS node)
    UNION ALL
    (
      SELECT GraphData.to_node AS node
      FROM R
      INNER JOIN GraphData
        ON (R.node = GraphData.from_node)
    )
  )
SELECT DISTINCT node FROM R ORDER BY node;

上述示例生成以下结果：

/*------*
 | node |
 +------+
 | 5    |
 | 6    |
 | 7    |
 | 8    |
 *------*/

排查迭代限制错误

递归 CTE 可能导致无限递归，在未满足终止条件前，递归部分会持续执行。为了终止无限递归，系统会对每个递归 CTE 实施迭代次数限制。对于 BigQuery，迭代限制为 500 次迭代。递归 CTE 达到最大迭代次数后，CTE 执行会取消并报错。

存在此限制是因为递归 CTE 的计算开销可能很大，并且运行具有大量迭代的 CTE 会消耗大量系统资源，并且需要较长的时间才能完成。

达到迭代限制的查询通常缺少适当的终止条件，从而导致无限循环或在不当场景中使用递归 CTE。

如果您遇到递归迭代限制错误，请查看本部分中的建议。

检查无限递归

为了防止无限递归，请确保递归部分能够在执行一定次数的迭代后生成空结果。

检查无限递归的一种方法是，对于前 100 次迭代，将递归 CTE 转换为具有 REPEAT 循环的 TEMP TABLE，如下所示：

DECLARE current_iteration INT64 DEFAULT 0;

CREATE TEMP TABLE recursive_cte_name AS
SELECT base_expression, current_iteration AS iteration;

REPEAT
  SET current_iteration = current_iteration + 1;
  INSERT INTO recursive_cte_name
    SELECT recursive_expression, current_iteration
    FROM recursive_cte_name
    WHERE termination_condition_expression
      AND iteration = current_iteration - 1
      AND current_iteration < 100;
  UNTIL NOT EXISTS(SELECT * FROM recursive_cte_name WHERE iteration = current_iteration)
END REPEAT;

替换以下值：

• recursive_cte_name：要调试的递归 CTE。
• base_expression：递归 CTE 的基本部分。
• recursive_expression：递归 CTE 的递归部分。
• termination_condition_expression：递归 CTE 的终止表达式。

例如，我们来看看以下名为 TestCTE 的递归 CTE：

WITH RECURSIVE
  TestCTE AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 3 FROM TestCTE WHERE MOD(n, 6) != 0
  )

此示例使用以下值：

• recursive_cte_name：TestCTE
• base_expression：SELECT 1
• recursive_expression：n + 3
• termination_condition_expression：MOD(n, 6) != 0

以下代码将测试 TestCTE 是否有无限递归：

DECLARE current_iteration INT64 DEFAULT 0;

CREATE TEMP TABLE TestCTE AS
SELECT 1 AS n, current_iteration AS iteration;

REPEAT
SET current_iteration = current_iteration + 1;

INSERT INTO TestCTE
SELECT n + 3, current_iteration
FROM TestCTE
WHERE
  MOD(n, 6) != 0
  AND iteration = current_iteration - 1
  AND current_iteration < 10;

UNTIL
  NOT EXISTS(SELECT * FROM TestCTE WHERE iteration = current_iteration)
    END REPEAT;

-- Print the number of rows produced by each iteration

SELECT iteration, COUNT(1) AS num_rows
FROM TestCTE
GROUP BY iteration
ORDER BY iteration;

-- Examine the actual result produced for a specific iteration

SELECT * FROM TestCTE WHERE iteration = 2;

上述示例生成了以下结果，其中包括迭代 ID 和该迭代期间生成的行数。

/*-----------+----------*
 | iteration | num_rows |
 +-----------+----------+
 | 0         | 1        |
 | 1         | 1        |
 | 2         | 1        |
 | 3         | 1        |
 | 4         | 1        |
 | 5         | 1        |
 | 6         | 1        |
 | 7         | 1        |
 | 8         | 1        |
 | 9         | 1        |
 | 10        | 1        |
 *-----------+----------*/

以下是迭代 2 期间生成的实际结果：

/*----------+-----------*
 | n        | iteration |
 +----------+-----------+
 | 7        | 2         |
 *----------+-----------*/

如果行数始终大于零（在此示例中为真），则示例可能存在无限递归。

验证是否恰当使用递归 CTE

验证您在适当的场景中使用递归 CTE。递归 CTE 的计算开销可能很大，因为它们旨在查询分层数据和图表数据。如果您不查询这两种数据，请考虑使用替代方法，例如将 LOOP 语句与非递归 CTE 结合使用。

将一个递归 CTE 拆分为多个递归 CTE

如果您认为您的递归 CTE 需要的迭代次数超出允许的最大值，您可以将一个递归 CTE 拆分为多个递归 CTE。

您可以使用类似于以下内容的查询结构来拆分递归 CTE：

WITH RECURSIVE
  CTE_1 AS (
    SELECT base_expression
    UNION ALL
    SELECT recursive_expression FROM CTE_1 WHERE iteration < 500
  ),
  CTE_2 AS (
    SELECT * FROM CTE_1 WHERE iteration = 500
    UNION ALL
    SELECT recursive_expression FROM CTE_2 WHERE iteration < 500 * 2
  ),
  CTE_3 AS (
    SELECT * FROM CTE_2 WHERE iteration = 500 * 2
    UNION ALL
    SELECT recursive_expression FROM CTE_3 WHERE iteration < 500 * 3
  ),
  [, ...]
SELECT * FROM CTE_1
UNION ALL SELECT * FROM CTE_2 WHERE iteration > 500
UNION ALL SELECT * FROM CTE_3 WHERE iteration > 500 * 2
[...]

替换以下值：

• base_expression：当前 CTE 的基本部分表达式。
• recursive_expression：当前 CTE 的递归部分表达式。

例如，以下代码将一个 CTE 拆分为三个不同的 CTE：

WITH RECURSIVE
  CTE_1 AS (
    SELECT 1 AS iteration
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 10
  ),
  CTE_2 AS (
    SELECT * FROM CTE_1 WHERE iteration = 10
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_2 WHERE iteration < 10 * 2
  ),
  CTE_3 AS (
    SELECT * FROM CTE_2 WHERE iteration = 10 * 2
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_3 WHERE iteration < 10 * 3
  )
SELECT iteration FROM CTE_1
UNION ALL
SELECT iteration FROM CTE_2 WHERE iteration > 10
UNION ALL
SELECT iteration FROM CTE_3 WHERE iteration > 20
ORDER BY 1 ASC

在前面的示例中，500 次迭代被替换为 10 次迭代，以便更快地查看查询结果。查询会生成 30 行，但每个递归 CTE 仅迭代 10 次。输出如下所示：

/*-----------*
 | iteration |
 +-----------+
 | 2         |
 | ...       |
 | 30        |
 *-----------*/

您可以在更大的迭代上测试上一查询。

使用循环取代递归 CTE

为避免迭代限制，请考虑使用循环而不是递归 CTE。您可以使用多个循环语句（例如 LOOP、REPEAT 或 WHILE）之一创建循环。如需了解详情，请参阅循环。

更改递归限制

如果您满足以下条件，请与 Customer Care 联系以提高递归限制：

• 您因合理原因需要递归 CTE 运行超过 500 次迭代。
• 您可以接受时间更长的执行。

请注意，提高递归限制存在潜在风险：

• 您的 CTE 可能会失败，并显示不同的错误消息，例如内存超出或超时。
• 如果您的项目使用的是按需价格模式，您的 CTE 可能仍会失败并显示结算层级错误，直到您切换到基于容量的价格模式。
• 具有大量迭代的递归 CTE 会消耗大量资源。这可能会影响同一预留中运行的其他查询，因为它们会竞争共享资源。

价格

如果使用按需结算，BigQuery 会根据使用递归 CTE 执行查询期间处理的字节数收费。

如需了解详情，请参阅查询大小计算。

配额

如需了解递归 CTE 配额和限制，请参阅配额和限制。