Clickhouse CTE 语句的支持（功能完整）

在clickhouse早期的版本上 不支持CTE语句，在一些版本上CTE语句支持度不完整。

至少从clickhouse 21.1版本开始对CTE语句的支持就日趋完善了，已经支持递归查询。

node01.example.com :) select version();

SELECT version()

Query id: b7e99b50-20d0-4695-a75e-5408230a9c1f

┌─version()─┐
│ 21.1.3.32 │
└───────────┘


-- 递归查询：
node01.example.com :) with a as (select number from system.numbers limit 10) select * from a order by number desc limit 3;


┌─number─┐
│      9 │
│      8 │
│      7 │
└────────┘

3 rows in set. Elapsed: 0.005 sec.




WITH
    a AS
    (
        SELECT number
        FROM system.numbers
        LIMIT 10
    ),
    b AS
    (
        SELECT number AS n
        FROM a
    )
SELECT sum(n)
FROM b

Query id: 9097463f-7725-4a2b-aae6-d4b8fb3d7c17

┌─sum(n)─┐
│     45 │
└────────┘

1 rows in set. Elapsed: 0.008 sec.

-- 多层递进查询：
WITH
T1 as (select number as c1 from numbers(10000) where number%2),
T2 as (select max(c1) sm from T1),
T3 as (select * from T1 where c1 = (select sm from T2))
select * from T3;

┌───c1─┐
│ 9999 │
└──────┘

1 rows in set. Elapsed: 0.010 sec. Processed 10.00 thousand rows, 80.00 KB (1.04 million rows/s., 8.30 MB/s.)






-- 多表join：

with 
x as (select number as n from numbers(10)),
y as (select number as n from numbers(5))
select * from x where n in (select n from y);


Query id: 0469d5eb-4f26-456c-9fa8-638a99d257a4

┌─n─┐
│ 0 │
│ 1 │
│ 2 │
│ 3 │
│ 4 │
└───┘

5 rows in set. Elapsed: 0.004 sec.


相关的join和多层递进的测试语句：

 with 
x as (select number as a from numbers(10)),
y as (select number as a from numbers(5))
select * from x where a in (select a from y);

with 
x as (select number as a from numbers(10)),
y as (select number as a from numbers(5))
select * from x left join y using a;

可以改写标准的SQL：
with　x as (select number as a from numbers(10)),　y as (select number as a from numbers(5))　select x.a,y.a from x left join y on x.a=y.a;

┌─a─┬─y.a─┐
│ 0 │   0 │
│ 1 │   1 │
│ 2 │   2 │
│ 3 │   3 │
│ 4 │   4 │
│ 5 │   0 │
│ 6 │   0 │
│ 7 │   0 │
│ 8 │   0 │
│ 9 │   0 │
└───┴─────┘

10 rows in set. Elapsed: 0.004 sec.




with 
x as (select number as a from numbers(10)),
y as (select number as a from numbers(5))
select * from x join y using a;

with 
x as (select number as a from numbers(10)),
y as (select number as a from numbers(5)),
z as (select toUInt64(1) b)
select * from x join y using a where a in (select * from z);

with 
x as (select number as a from numbers(10)),
y as (select number as a from numbers(5)),
z as (select * from x where a % 2),
w as (select * from y where a > 0)
select * from x join y using a where a in (select * from z);

with 
x as (select number as a from numbers(10)),
y as (select number as a from numbers(5)),
z as (select * from x where a % 2),
w as (select * from y where a > 0)
select max(a) from x join y using a where a in (select * from z)
having a > (select min(a) from w);

with 
x as (select number as a from numbers(10)),
y as (select number as a from numbers(5)),
z as (select * from x where a % 2),
w as (select * from y where a > 0)
select a from x join y using a where a in (select * from z)
having a <= (select max(a) from w);



结论：
clickhouse的SQL语句越来越参考标准SQL的语法，主要参考MySQL 8.0 ，postgresql来实现，此外还有部分apache hive的语法。

参考：

https://github.com/ClickHouse/ClickHouse/pull/16575