flink sql支持Common Table Expression (CTE)吗？

[casel.chen]

flink sql支持Common Table Expression (CTE)吗？是不是可以通过 create temporary view xxx 来实现？CTE和temporary view的区别是什么？
例如


with toronto_ppl as (
    SELECT DISTINCT name
    FROM population
    WHERE country = "Canada"
      AND city = "Toronto"
)
   , avg_female_salary as (
        SELECT AVG(salary) as avgSalary
        FROM salaries
        WHERE gender = "Female"
    )
SELECT name
     , salary
FROM People
WHERE name in (SELECT DISTINCT FROM toronto_ppl)
  AND salary >= (SELECT avgSalary FROM avg_female_salary)

[Jingsong Li]

支持。

如果只是在单个sql中复用expression，和temporary view基本一样，区别不大。

在某些优化路径上不同，一般没有实质影响。

Best,
Jingsong

On Fri, May 21, 2021 at 11:32 PM casel.chen <[hidden email]> wrote:

> flink sql支持Common Table Expression (CTE)吗？是不是可以通过 create temporary view
> xxx 来实现？CTE和temporary view的区别是什么？
> 例如
>
>
> with toronto_ppl as (
>     SELECT DISTINCT name
>     FROM population
>     WHERE country = "Canada"
>       AND city = "Toronto"
> )
>    , avg_female_salary as (
>         SELECT AVG(salary) as avgSalary
>         FROM salaries
>         WHERE gender = "Female"
>     )
> SELECT name
>      , salary
> FROM People
> WHERE name in (SELECT DISTINCT FROM toronto_ppl)
>   AND salary >= (SELECT avgSalary FROM avg_female_salary)

--
Best, Jingsong Lee