COMMONTABLEEXPRESSION的功能,命令格式及樣本 - MaxCompute

通用資料表運算式（COMMON TABLE EXPRESSION）簡稱CTE，是一個臨時命名的結果集，用於簡化SQL。MaxCompute支援標準SQL的CTE功能，可以有效提高SQL語句的可讀性與執行效率。本文為您介紹CTE的功能、命令格式及使用樣本。

功能介紹

CTE可以被認為是在單個DML語句的執行範圍內定義的臨時結果集。CTE類似於派生表，它不作為Object Storage Service，並且僅在查詢期間持續。開發過程中結合CTE，可以提高SQL語句可讀性，便於輕鬆維護複雜查詢。
CTE是一個STATEMENT層級的子句運算式，以WITH開頭，後跟運算式名稱。包括以下兩類：
- NON RECURSIVE CTE：非遞迴CTE，即CTE不使用遞迴，不迭代訪問自己。
- RECURSIVE CTE：遞迴CTE，表示CTE可以迭代訪問自身的情境，能夠實現SQL的遞迴查詢功能，通常用來遍曆分層資料。
支援MATERIALIZE CTE（物化CTE）功能：定義CTE時，可在SELECT語句中使用MATERIALIZE HINT將CTE的計算結果緩衝到一個暫存資料表中，後續訪問CTE時，可直接從緩衝中讀取結果，從而避免了多層CTE嵌套情境下出現記憶體超限問題，進而提升CTE語句效能。

NON RECURSIVE CTE（非遞迴CTE）

命令格式

WITH 
     <cte_name> [(col_name[,col_name]...)] AS
     (
        <cte_query>
     )
    [,<cte_name> [(col_name[,col_name]...)] AS
     (
     <cte_query2>
     )
    ,……]

參數說明

參數	是否必填	說明
cte_name	是	CTE的名稱，不能與當前`WITH`子句中的其他CTE的名稱相同。查詢中任何使用到cte_name標識符的地方，均指CTE。
col_name	否	CTE輸出資料行的列名稱。
cte_query	是	一個`SELECT`語句。`SELECT`的結果集用於填充CTE。

使用樣本

假設現有如下代碼：

INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL ) a
    JOIN (
        SELECT * FROM src2 WHERE value > 0 ) b
    ON a.key = b.key
) c
UNION ALL 
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL ) a
    LEFT OUTER JOIN (
        SELECT * FROM src3 WHERE value > 0 ) b
    ON a.key = b.key AND b.key IS NOT NULL 
) d;

頂層的UNION兩側各為一個JOIN，JOIN的左表是相同的查詢語句。通過寫子查詢的方式，只能重複這段代碼。

使用CTE的方式重寫以上語句，命令樣本如下：

WITH 
  a AS (SELECT * FROM src WHERE key IS NOT NULL),
  b AS (SELECT * FROM src2 WHERE value > 0),
  c AS (SELECT * FROM src3 WHERE value > 0),
  d AS (SELECT a.key, b.value FROM a JOIN b ON a.key=b.key),
  e AS (SELECT a.key, c.value FROM a LEFT OUTER JOIN c ON a.key=c.key AND c.key IS NOT NULL)
INSERT OVERWRITE TABLE srcp PARTITION (p='abc') 
SELECT * FROM d UNION ALL SELECT * FROM e;

重寫後，a對應的子查詢只需寫一次，便可在後面進行重用。您可以在CTE的WITH子句中指定多個子查詢，像使用變數一樣在整個語句中反覆重用。除重用外，不必反覆嵌套。

RECURSIVE CTE（遞迴CTE）

命令格式

WITH RECURSIVE <cte_name>[(col_name[,col_name]...)] AS
(
  <initial_part> UNION ALL <recursive_part>
)
SELECT ... FROM ...;

參數說明

參數	是否必填	說明
RECURSIVE	是	Recursive CTE子句必須以`WITH RECURSIVE`開頭。
cte_name	是	CTE的名稱，不能與當前`WITH`子句中的其他CTE的名稱相同。查詢中任何使用到cte_name標識符的地方，均指CTE。
col_name	否	CTE輸出資料行的列名稱。若不顯式地指定輸出資料行名，則系統也支援自動推斷。
initial_part	是	用於計算初始資料集，它不可以遞迴地引用cte_name指向CTE自身。
recursive_part	是	用於計算後續迭代的結果，可以通過遞迴地引用cte_name，定義如何使用上一個迭代結果來遞迴地計算下一個迭代結果。
UNION ALL	是	initial_part和recursive_part之間必須通過UNION ALL來串連。

使用限制

RECURSIVE CTE不能用於IN/EXISTS/SCALAR的SUB-QUERY。
RECURSIVE CTE的預設遞迴操作次數限制為10次，支援設定odps.sql.rcte.max.iterate.num來修改這個預設值，最大上限為100（若設定值超過100，仍按照100來處理）。
RECURSIVE CTE不支援在迭代計算過程中記錄中間結果，即發生失敗時則會重新從頭開始計算。使用時建議控制遞迴次數，若整個計算過程耗時很長，建議通過建立暫存資料表來把中間的計算結果落盤。
RECURSIVE CTE不支援在查詢加速（MCQA）執行。若在MCQA模式中使用RECURSIVE CTE，當設定了interactive_auto_rerun=true，任務可以回退到普通模式執行，否則任務會失敗。

使用樣本

樣本1：定義一個名稱為cte_name的RECURSIVE CTE。

-- 方式1：定義一個名稱為cte_name的遞迴CTE，包含名為a，b的兩個輸出資料行
WITH RECURSIVE cte_name(a, b) AS (       
  SELECT 1L, 1L                                 -- intial_part：即迭代0資料集
  UNION ALL                                      -- 將cte的initial_part和recursive_part之間使用UNION ALL串連到一起
  SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5) -- recursive_part：其中cte_name指向的是上一個迭代的計算結果
SELECT * FROM cte_name ORDER BY a LIMIT 100;    -- 輸出遞迴CTE結果，即將所有迭代的資料union all到一起

-- 方式2：定義一個名稱為cte_name的遞迴CTE，不顯示指定輸出資料行名稱
WITH RECURSIVE cte_name AS (       
  SELECT 1L AS a, 1L AS b                            
  UNION ALL                                      
  SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5) 
SELECT * FROM cte_name ORDER BY a LIMIT 100;

說明

在recursive_part中為了避免遞迴操作陷入無限迴圈，需要設定迭代終止的條件。樣本中使用WHERE a + 1 <= 5來作為迭代是否應該結束的判據，如果WHERE條件不滿足會導致本輪迭代產生的資料集為空白，則迭代終止。
當未顯式地指定輸出資料行名時，系統也支援進行自動推斷。例如方式2中，使用initial_part的輸出資料行名作為RECURSIVE CTE的輸出資料行名。

返回結果如下：

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 1          |
| 2          | 2          |
| 3          | 3          |
| 4          | 4          |
| 5          | 5          |
+------------+------------+

樣本2：RECURSIVE CTE不能被用於IN/EXISTS/SCALAR的SUB-QUERY中，如下query在編譯的時候會報錯。

WITH RECURSIVE cte_name(a, b) AS (
  SELECT 1L, 1L
  UNION ALL
  SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5)
SELECT x, x in (SELECT a FROM cte_name) FROM VALUES (1L), (2L) AS t(x);

返回結果如下：

-- 返回報錯，在第5行存在一個in sub-query，且在sub-query中引用了RECURSIVE CTE cte_name
FAILED: ODPS-0130071:[5,31] Semantic analysis exception - using Recursive-CTE cte_name in scalar/in/exists sub-query is not allowed, please check your query, the query text location is from [line 5, column 13] to [line 5, column 40]

樣本3：建立employees表，描述某個公司的員工和他的管理者的對應關係，並插入資料。
```
CREATE TABLE employees(name STRING, boss_name STRING);
INSERT INTO TABLE employees VALUES 
  ('zhang_3',   null),
  ('li_4',      'zhang_3'),
  ('wang_5',    'zhang_3'),
  ('zhao_6',    'li_4'),
  ('qian_7',    'wang_5');
```
定義一個名為company_hierarchy的RECURSIVE CTE，以擷取此表所描述的組織架構。該RECURSIVE CTE包含3個輸出欄位，分別是員工的名字、他的管理者的名字以及他在公司組織架構中的level。
```
WITH RECURSIVE company_hierarchy(name, boss_name, level) AS (
    SELECT name, boss_name, 0L FROM employees WHERE boss_name IS NULL 
    UNION ALL 
    SELECT e.name, e.boss_name, h.level+1 FROM employees e JOIN company_hierarchy h WHERE e.boss_name = h.name
  )
SELECT * FROM company_hierarchy ORDER BY level, boss_name, name LIMIT 1000;
```
- 第二行initial_part，對應著employees中boss_name是null的記錄，並且給這些記錄的level欄位賦值為0。
- 第四行recursive_part，將employees和company_hierarchy進行JOIN操作，JOIN的條件是e.boss_name = h.name，即從employees表中擷取這些記錄，這些記錄對應的員工的管理者是上一個迭代擷取的員工記錄。
返回結果如下：
```
+------+-----------+------------+
| name | boss_name | level      |
+------+-----------+---------------+
| zhang_3 | NULL      | 0          |
| li_4 | zhang_3   | 1          |
| wang_5 | zhang_3   | 1          |
| zhao_6 | li_4      | 2          |
| qian_7 | wang_5    | 2          |
+------+-----------+------------+
```
整體計算過程可分解如下：
- 迭代0：擷取初始資料集，通過條件boss_name IS NULL對employees中的記錄進行過濾，得到一條記錄。
```
'zhang_3',   null,  0
```
- 迭代1：執行如下query，其中company_hierarchy對應於上面迭代0的計算結果。
```
SELECT e.name, e.boss_name, h.level+1 FROM employees e JOIN company_hierarchy h  WHERE e.boss_name = h.name
```
  執行後擷取到了level = 1的兩條記錄，他們的管理者都是zhang_3。
```
'li_4',      'zhang_3', 1
'wang_5',    'zhang_3',  1
```
- 迭代2：執行的過程和迭代1類似，但是company_hierarchy對應於迭代1的結果資料集。執行後擷取了level = 2的兩條記錄，它們的管理者是li_4或者wang_5。
```
'zhao_6',    'li_4'，   2
'qian_7',    'wang_5'， 2
```
- 迭代3：因為employees表中沒有員工的管理者是zhao_6或者qian_7，因此返回空集，導致Recursive-CTE的遞迴計算結束。
樣本4：假設向樣本2的employees表中多插入一條記錄，則返回報錯。
```
INSERT INTO TABLE employees VALUES('qian_7', 'qian_7');
```
這條記錄聲明qian_7的主管是他自己，這時再運行前面定義的RECURSIVE CTE，就會出現死迴圈。注意，系統對最大迭代次數有一定限制（請參見使用限制），這個query最終會失敗並結束。
返回結果如下：
```
-- 返回報錯
FAILED: ODPS-0010000:System internal error - recursive-cte: company_hierarchy exceed max iterate number 10
```

MATERIALIZE CTE

背景介紹

對於NON RECURSIVE CTE，MaxCompute在產生執行計畫的時候，所有的CTE都會被展開。

樣本如下：

WITH
  v1 AS (SELECT SIN(1.0) AS a) 
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

返回結果如下：

+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

在執行層面相當於如下SQL，即函數sin(1.0)會被執行兩次。

SELECT a FROM (SELECT SIN(1.0) AS a) 
UNION ALL 
SELECT a FROM (SELECT SIN(1.0) AS a);

在有多層嵌套CTE的複雜情境下，若所有的CTE被展開成最基本的葉子節點，所產生的結果是一個巨大的文法樹。在產生執行計畫時可能因文法樹節點過多導致失敗，並可能引發記憶體超限。樣本如下：

WITH
  v1 AS (SELECT 1L AS a, 2L AS b, 3L AS c),
  v2 AS (SELECT * FROM v1 UNION ALL SELECT * FROM v1 UNION ALL SELECT * FROM v1),
  v3 AS (SELECT * FROM v2 UNION ALL SELECT * FROM v2 UNION ALL SELECT * FROM v2),
  v4 AS (SELECT * FROM v3 UNION ALL SELECT * FROM v3 UNION ALL SELECT * FROM v3),
  v5 AS (SELECT * FROM v4 UNION ALL SELECT * FROM v4 UNION ALL SELECT * FROM v4),
  v6 AS (SELECT * FROM v5 UNION ALL SELECT * FROM v5 UNION ALL SELECT * FROM v5),
  v7 AS (SELECT * FROM v6 UNION ALL SELECT * FROM v6 UNION ALL SELECT * FROM v6),
  v8 AS (SELECT * FROM v7 UNION ALL SELECT * FROM v7 UNION ALL SELECT * FROM v7),
  v9 AS (SELECT * FROM v8 UNION ALL SELECT * FROM v8 UNION ALL SELECT * FROM v8)
SELECT * FROM v9;

為瞭解決上述情境中的問題，MaxCompute提供了MATERIALIZE CTE能力，支援將CTE計算結果緩衝，以供WITH語句外層的SQL引用，而無需全部展開。這一機制能夠有效避免嵌套CTE展開而導致的記憶體超限問題，從而提升CTE的語句效能。

使用樣本

定義CTE時，可在SELECT語句中使用MATERIALIZE HINT提示 /*+ MATERIALIZE */ 將CTE的計算結果緩衝到一張暫存資料表中，後續引用時候，可以直接從緩衝中讀取結果，不需要重新計算。樣本如下：

WITH 
  v1 AS (SELECT /*+ MATERIALIZE */ SIN(1.0) AS a) 
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

-- 返回結果如下。
+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

當MATERIALIZE HINT生效時，在LogView的Job Details頁簽中可以看到，中間的結果落盤，對應提交了多個Fuxi Job。

fuxi

使用限制

MATERIALIZE HINT必須應用於NON RECURSIVE CTE頂級的SELECT語句中，遞迴CTE不需要使用MATERIALIZE HINT。
- 錯誤樣本：如下CTE中的頂級語句是UNION而不是SELECT，所以MATERIALIZE HINT不會生效。
```
WITH 
  v1 AS (SELECT /*+ MATERIALIZE */ SIN(1.0) AS a 
         UNION ALL
         SELECT /*+ MATERIALIZE */ SIN(1.0) AS a) 
SELECT a FROM v1 UNION ALL SELECT a FROM v1;
```
  在LogView中可以看到，對應僅提交了一個Fuxi Job，如下圖所示。
- 正確樣本：將上述錯誤樣本改寫為子查詢來解決。
```
WITH 
  v1 AS (SELECT /*+ MATERIALIZE */ * FROM 
           (SELECT SIN(1.0) AS a
            UNION ALL 
            SELECT SIN(1.0) AS a)
        ) 
SELECT a FROM v1 UNION ALL SELECT a FROM v1;
```
如果CTE中使用了非確定性函數，例如RAND函數或者是使用者自訂的非確定性Java/Python UDF，將CTE修改為MATERIALIZE CTE之後，緩衝機制可能會導致最終計算結果發生變化。
MATERIALIZE CTE不支援在開啟查詢加速（MCQA）模式中執行。若使用者在MCQA模式中使用MATERIALIZE CTE，當設定了interactive_auto_rerun=true，任務可以回退到普通模式執行。否則任務會失敗。