本頁面由 Cloud Translation API 翻譯而成。

使用遞迴式 CTE

在 BigQuery 適用的 GoogleSQL 中，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。

如果發生遞迴疊代限制錯誤，請參閱本節的建議。

檢查是否有無限遞迴

為避免無限遞迴，請確認遞迴運算式在執行一定次數的疊代後，能夠產生空白結果。

檢查無限遞迴的一種方法，是將遞迴 CTE 轉換為 TEMP TABLE，並為第一個 100 迴迭使用 REPEAT 迴圈，如下所示：

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 的運算成本可能很高，因為它們是用於查詢階層資料和圖表資料。如果您並未查詢這兩種資料，請考慮使用其他方法，例如搭配非遞迴 CTE 使用 LOOP 陳述式。

將遞迴 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。詳情請參閱「迴圈」。

變更遞迴限制

如果您認為符合下列因素，請與客戶服務團隊聯絡，提高遞迴限制：

您有正當理由讓遞迴 CTE 執行超過 500 次疊代。
您可以接受執行時間大幅延長。

請注意，提高遞迴限制可能會帶來風險：

CTE 可能會失敗，並顯示其他錯誤訊息，例如記憶體超出或逾時。
如果您的專案使用即時定價模式，在您切換為以容量為準的定價模式前，CTE 仍可能會因帳單等級錯誤而失敗。
含有大量迭代作業的遞迴 CTE 會耗用大量資源。這可能會影響在相同保留區內執行的其他查詢，因為它們會競爭共用資源。

定價

如果您使用以量計價，BigQuery 會根據執行含有遞迴 CTE 的查詢時處理的位元組數來收取費用。

詳情請參閱「查詢大小計算方式」。

配額

如要瞭解遞迴 CTE 配額和限制，請參閱「配額與限制」。

使用遞迴式 CTE 透過集合功能整理內容 你可以依據偏好儲存及分類內容。