Clickhouse CTE 支持

从clickhouse 20.3.30版本开始支持标准SQL的部分with语句的写法 即支持CTE(common table expressionn)

SELECT version()

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



-- 子查询

CDP01.example.com :) with a as (select number from system.numbers limit 10) select * from a;

WITH a AS
    (
        SELECT number
        FROM system.numbers
        LIMIT 10
    )
SELECT *
FROM a

┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘


结论: 目前的CTE功能还是简单的子查询模式,并不支持递归调用,还是有待完善的。

10 rows in set. Elapsed: 0.006 sec.

-- 求和

CDP01.example.com :) with a as (select number from system.numbers limit 10) select sum(number) sum_num from a;

WITH a AS
    (
        SELECT number
        FROM system.numbers
        LIMIT 10
    )
SELECT sum(number) AS sum_num
FROM a

┌─sum_num─┐
│      45 │
└─────────┘

1 rows in set. Elapsed: 0.006 sec.

-- 目前只支持单层嵌套查询,不支持多层递归
CDP01.example.com :) with a as (select number from system.numbers limit 10) ,b as(select number as n from a) select sum(n) from b;

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


Received exception from server (version 20.10.3):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Missing columns: 'number' while processing query: 'SELECT number AS n FROM a', required columns: 'number', source columns: 'i'.

0 rows in set. Elapsed: 0.005 sec.



--- 有参数:enable_global_with_statement 

CDP01.example.com :) set enable_global_with_statement =1;

SET enable_global_with_statement = 1

WITH 1 AS x
SELECT 1 + y
FROM
(
    SELECT x + 2 AS y
)
UNION ALL
SELECT x + y
FROM
(
    SELECT 1 AS y
)

┌─plus(1, y)─┐
│          4 │
└─────────────
┌─plus(1, y)─┐
│          2 │
└─────────────

参考:

https://clickhouse.tech/docs/en/whats-new/changelog/

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

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页