Amazing functionality, congrats. We planned to include CTE in 2.0.7 version of IoTDB.
Best regards, ---------------------- Yuan Tian On Fri, Sep 5, 2025 at 2:58 PM Colin Shi <[email protected]> wrote: > Hi All, > > CTE functionality is completed and here is the PR ( > https://github.com/apache/iotdb/pull/16112). > > CTEs are particularly useful for breaking down intricate queries into > smaller, manageable parts, improving maintainability. > It allows you to define a subquery block within a SQL statement, which can > be referenced multiple times throughout the query. > Unlike temporary tables, CTEs are defined within the scope of a single > statement and do not persist beyond its execution, > making them a lightweight and efficient tool for query structuring. > > The CTE syntax is as below: > ------------- > WITH > * cte_name* [(*col_name* [, *col_name*] ...)] AS (*subquery*) > [, *cte_name* [(*col_name* [, *col_name*] ...)] AS (*subquery*)] > --------------- > Note: we do not support recursive CTE at this moment!! > > Here is an example to use CTE: > --------------- > with x as (select avg(salary) as avg_salary from employee) select id, name > from employee where salary > (select avg_salary from x); > --------------- > > Query results from CTEs aren't materialized. Each outer reference to the > named result set requires the defined query to be re-executed > (Inline). The next work is the enhancement to support CTE materialization. > > > On Wed, 13 Aug 2025 at 19:37, Yuan Tian <[email protected]> wrote: > > > Hi Colin, > > > > Glad to see that, we can discuss the feature definition like syntax for > CTE > > here. > > > > With CTE, I believe some of our complex SQL queries will become more > > concise and more modular. > > > > > > Best regards, > > ------------------------ > > Yuan Tian > > > > On Wed, Aug 13, 2025 at 7:12 PM Colin Shi <[email protected]> wrote: > > > > > Hi all, > > > > > > BTW, I'm working CTE feature recently and will update soon... > > > > > >
