WITH

查询中可以使用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;