全部產品
Search
文件中心

:WITH

更新時間:Jul 06, 2024

查詢中可以使用WITH子句來建立通用運算式(Common Table Expression, 簡稱CTE)。CTE是一個臨時結果集,在單個SQL語句執行期間存在,供SELECT查詢引用。CTE可以扁平化巢狀查詢或者簡化子查詢,SELECT只需執行一遍子查詢,提高查詢效能。本文介紹如何在SELECT查詢中使用WITH子句。

注意事項

  • CTE之後可以接SQL語句或者其他的CTE(只能使用一個WITH),多個CTE中間用逗號(,)分隔,否則CTE將失效。

  • CTE語句中暫不支援分頁功能。

WITH使用方法

  • 以下兩個查詢等價

    SELECT a, b
    FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x;                   
    WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
    SELECT a, b FROM x;                   
  • WITH子句可用於多子查詢

    WITH
    t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
    t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
    SELECT t1.*, t2.*
    FROM t1 JOIN t2 ON t1.a = t2.a;                  
  • WITH子句中定義的關係可以互相串連

    WITH
    x AS (SELECT a FROM t),
    y AS (SELECT a AS b FROM x),
    z AS (SELECT b AS c FROM y)
    SELECT c FROM z;                   

CTE執行最佳化

3.1.9.3及以上核心版本的叢集支援使用CTE執行最佳化功能,該功能預設關閉,您可以通過設定CTE_EXECUTION_MODE配置項手動開啟該功能。功能開啟後,CTE子查詢被多次引用時,只執行一次,提升部分查詢的查詢效能。

重要

開啟CTE執行最佳化功能後可能會導致部分查詢的查詢效能會下降,若您發現查詢效能有明顯下降,建議您關閉CTE執行最佳化。

開啟CTE執行最佳化

  • 對指定查詢開啟CTE執行最佳化

    在指定查詢語句前添加Hint,僅對該查詢開啟CTE執行最佳化。內建資料集CTE執行最佳化,樣本如下:

    /*cte_execution_mode=shared*/ 
    WITH shared AS (SELECT L_ORDERKEY,L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
    SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;
  • 對所有查詢開啟CTE執行最佳化

    執行SET語句對所有查詢開啟CTE執行最佳化,樣本如下:

    SET adb_config cte_execution_mode=shared;

關閉CTE執行最佳化

  • 對指定查詢關閉CTE執行最佳化

    在指定查詢語句前添加Hint,僅對該查詢關閉CTE執行最佳化。內建資料集CTE執行最佳化,樣本如下:

    /*cte_execution_mode=inline*/ 
    WITH shared AS (SELECT L_ORDERKEY,L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
    SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;
  • 對所有查詢關閉CTE執行最佳化

    執行SET語句對所有查詢關閉CTE執行最佳化,樣本如下:

    SET adb_config cte_execution_mode=inline;