You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@iotdb.apache.org by Colin Shi <sh...@gmail.com> on 2025/08/13 11:11:19 UTC
CTE
Hi all,
BTW, I'm working CTE feature recently and will update soon...
Re: CTE
Posted by Jialin Qiao <qi...@apache.org>.
Good work!
Jialin Qiao
Yuan Tian <ja...@gmail.com> 于2025年9月5日周五 15:09写道：
>
> 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 <sh...@gmail.com> 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 <ja...@gmail.com> 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 <sh...@gmail.com> wrote:
> > >
> > > > Hi all,
> > > >
> > > > BTW, I'm working CTE feature recently and will update soon...
> > > >
> > >
> >
Re: CTE
Posted by Yuan Tian <ja...@gmail.com>.
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 <sh...@gmail.com> 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 <ja...@gmail.com> 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 <sh...@gmail.com> wrote:
> >
> > > Hi all,
> > >
> > > BTW, I'm working CTE feature recently and will update soon...
> > >
> >
>
Re: CTE
Posted by Colin Shi <sh...@gmail.com>.
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 <ja...@gmail.com> 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 <sh...@gmail.com> wrote:
>
> > Hi all,
> >
> > BTW, I'm working CTE feature recently and will update soon...
> >
>
Re: CTE
Posted by Yuan Tian <ja...@gmail.com>.
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 <sh...@gmail.com> wrote:
> Hi all,
>
> BTW, I'm working CTE feature recently and will update soon...
>