查询中可以使用 WITH 子句来创建公共表表达式（Common Table Expression， 简称CTE）。CTE 会产生一个临时结果集，在单个 SQL 语句执行期间存在，供查询引用。CTE 可以简化查询逻辑，也可以通过复用重复计算提高查询性能。

使用时，请使用实际的数据表中的字段替换示例中的占位符。

WITH...SELECT... #

• 以下查询等价

  WITH cte AS (
      SELECT a, SUM(b) AS c
      FROM t
      GROUP BY a
      ORDER BY c DESC
  )
  SELECT *
  FROM cte
  LIMIT 10;
  

  SELECT *
  FROM (
      SELECT a, SUM(b) AS c
      FROM t
      GROUP BY a
      ORDER BY c DESC
  ) cte
  LIMIT 10;
  

• 多次引用 WITH 语句

  WITH cte AS (
      SELECT a, SUM(b) AS c
      FROM t
      GROUP BY a
  )
  SELECT 
      t1.d,
      t1.a,
      t1.b,
      cte1.c AS total,
      CASE WHEN cte2.c > 10000 THEN 'X'
           ELSE 'Y'
      END AS type
  FROM t1
  JOIN cte cte1 ON t1.a = cte1.a
  JOIN cte cte2 ON t1.a = cte2.a
  WHERE t1.b > 100;
  

• WITH 语句定义多个子查询

  WITH
      cte1 AS (
          SELECT COUNT(*) AS cnt
          FROM t
          WHERE d = 'val'
      ),
      cte2 AS (
          SELECT COUNT(*) AS cnt
          FROM t
      )
  SELECT 
      c1.cnt AS cnt1,
      c2.cnt AS cnt2,
      (c1.cnt * 100.0 / c2.cnt) AS ratio
  FROM cte1 c1, cte2 c2;
  

• WITH 语句多个子查询互相引用

  WITH
      cte1 AS (
          SELECT a, SUM(b) AS c
          FROM t
          GROUP BY a
      ),
      cte2 AS (
          SELECT SUM(c) AS total
          FROM cte1
      ),
      cte3 AS (
          SELECT 
              c1.a,
              c1.c,
              (c1.c * 100.0 / c2.total) AS ratio
          FROM cte1 c1
          CROSS JOIN cte2 c2
      )
  SELECT *
  FROM cte3
  ORDER BY ratio DESC;
  

INSERT...WITH #

• 在 INSERT 中使用 WITH 语句

  INSERT INTO target (a, c)
  WITH cte AS (
      SELECT a, SUM(b) AS c
      FROM t
      GROUP BY a
  )
  SELECT a, c
  FROM cte
  WHERE c > 10000;
  

• 在 INSERT OVERWRITE 中使用 WITH 语句

  INSERT OVERWRITE summary
  WITH
      cte1 AS (
          SELECT 
              a,
              SUM(b)      AS total,
              COUNT(*)    AS cnt
          FROM t
          WHERE e >= DATE '2025-01-01'
          GROUP BY a
      ),
      cte2 AS (
          SELECT 
              a,
              total * 1.0 / cnt AS avg
          FROM cte1
      )
  SELECT
      s.a,
      s.total,
      s.cnt,
      a.avg
  FROM cte1 s
  JOIN cte2 a 
      ON s.a = a.a;
  

Settings 参数 #

您可以使用以下 settings 参数优化 CTE，功能详细说明请参见Auto CTE 优化 和 隐式 CTE 优化。

CTE 支持 Inline all 和 Shared 两种执行模式。
Inline all 执行方式直接将 CTE 在主查询中展开，每次使用时单独执行。这种执行模式更加简单，能利用更多的优化，但是重复扫描和计算会造成很多浪费。
Shared 执行方式复用结果，避免重复计算。然而，这种方式会阻止优化器进行优化：例如无法将父查询的正确的谓词下推，会导致 CTE 扫描大量不需要的行；阻止 join reorder，无法获得全局最优的 join reoder。此外 CTE 如果过大，会导致查询需要大量额外的内存或者临时存储，执行效率低下。
ByteHouse 实现了 CBO Auto CTE 优化，枚举一个查询中不同的 CTE 的 Inline / Shared 计划 ，最后通过 Cost 进行选择，为不同的 CTE 选择不同的执行方式（Partial Inline），寻找全局最优解。
ByteHouse 可以通过 settings cte_mode 设置不同的执行模式 :

• cte_mode='SHARED'：Shared 执行方式。
• cte_mode='INLINED'：Inline all 执行方式。
• cte_mode='AUTO'：Partial Inline 执行方式。

ByteHouse 的 Auto CTE 可以优化查询中的 WITH 语句、定义的 VIEW 和隐式 CTE 产生的 CTE。

使用示例如下：

WITH v as (SELECT a, b FROM t
WHERE c < 1000)
SELECT v1.* FROM v v1, v v2, v v3
WHERE v1.a = v2.a AND v2.a = v3.a
AND v3.b = 'val'
SETTINGS cte_mode = 'INLINED';

ByteHouse 可以开启 enable_shared_common_plan_node，自动发现查询中相同的部分，构建 CTE。

SELECT * FROM
(SELECT a, b FROM t WHERE c < 1000) v1
WHERE b = 'val1';
UNION ALL
SELECT * FROM
(SELECT a, b FROM t WHERE c < 1000) v2
WHERE b = 'val2'
SETTINGS enable_shared_common_plan_node = true;