[Alpha] With Clause#

OpenMLDB 在 0.7.2 开始支持 WITH 子句。WITH 子句借鉴了 WITH Clause in BigQuery 的语法，但仅支持非递归形式。一个 WITH 子句包括一个或多个 common table expressions (CTEs). 在同一个查询语句内, CTE 的作用类似一张临时表, 可以被后续子句引用。

Syntax#

query:
  [ WITH {non_recursive_cte}[, ...] ]
  { select | ( query ) | set_operation }
  [ ORDER BY ordering_expression ]
  [ LIMIT count ]

non_recursive_cte:
  cte_name AS ( query )

Description#

WITH 子句实现了层次化的数据模型，在实现上等价于子查询，因此和子查询有相同的上线要求。

SELECT语句元素

离线模式

在线预览模式

在线请求模式

说明

WITH Clause

✓

✓

✓

CTE rules#

  1. 一个 CTE 语句，可以被 1.和该 CTE 同一层的 SelectStmt 2.该 CTE 之后的其它 CTEs 引用。

  2. 同一层的 WITH 子句下有多个 CTE, 每个 cte_name 必须唯一。

  3. 一个查询语句包含多层 WITH 子句，更里层的 CTE 覆盖外层 cte_name 相同的 CTE。

Example#

  1. CTE overrides table name

    WITH t1 as (select col1 + 1 as id, std_ts from t1)
select * from t1;

  2. nested WITH clause

    WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q3 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q1 AS (SELECT * FROM q1),  # q1 (in the query) resolves to my_query
        q4 AS (SELECT * FROM q1)   # q1 resolves to the WITH subquery on the previous line.
    SELECT * FROM q1)              # q1 resolves to the third inner WITH subquery.