[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.