OpenSearch：Common Table Expression簡介

CTE簡介

CTE 全稱 Common Table Expresssion，我們可以通過CTE定義一個臨時（query生存期）的命名結果集，在query接下來的查詢語句（不局限於SELECT，但是我們目前只支援了SELECT）中就可以直接引用這個CTE，從而達到簡化查詢的目的。一個標準CTE定義文法如下所示：

WITH expression_name[(column_name [,...])]
AS
   (CTE_definition)
SQL_statement;

1. 首先，我們需要指定這個CTE的name，以方便在後面的引用。

2. 然後需要在括弧中定義輸出資料行名，如果有多個輸出資料行名，則用逗號分隔開，這裡的列的數目和類型要和CTE定義子句中的輸出資料行數目和類型對齊。此處定義好的列名即為最後的輸出資料行名，如果這裡沒有定義輸出資料行名，則以CTE定義子句中實際輸出的列為準。

3. 隨後通過一個AS來定義CTE的資料產出子句，改CTE最後產出的資料將由這個子句產生。

4. OK，到這裡一個完整的CTE已經定義完成，接下來就可以在SQL語句中直接引用它啦。

使用樣本

接下來通過幾個具體的例子來展示如何使用CTE。

WITH T_CTE (i1_cte,i2_cte) AS (SELECT i1,i2 FROM t1)
SELECT * FROM T_CTE

以上樣本是一個最簡單的CTE，在SELECT語句中直接輸出了CTE定義的結果集。

WITH T_CTE (i1_cte,i2_cte) AS (SELECT i1,i2 FROM t1)
SELECT * FROM t2 JOIN T_CTE ON t2.i1 = T_CTE.i1_cte AND t2.i2 = T_CTE.i2_cte

也可以在JOIN中使用它：

WITH T_CTE (i1_cte,i2_cte) AS (SELECT i1,i2 FROM t1)
SELECT * FROM t2 WHERE EXISTS (SELECT * FROM T_CTE WHERE t2.i1 = i1_cte AND t2.i2 = i2_cte)

或者在自查詢中使用。

常見問題FAQ

Performence

雖然在上文中提到，CTE是定義了一個臨時結果集，但是真正的執行順序並非是先產出CTE的結果集再在select中去加工，實際上，CTE的定義語句會在產生執行計畫的階段被展開，從而和引用者select一同被最佳化。所以執行計畫並不會比不用CTE的時候覆雜，兩者具有同等的效能表現。