Clickhouse CTE 支持

最新推荐文章于 2024-02-02 23:45:18 发布

vkingnew

最新推荐文章于 2024-02-02 23:45:18 发布

阅读量901

点赞数 1

分类专栏： Clickhouse

本文链接：https://blog.csdn.net/vkingnew/article/details/109542048

版权

Clickhouse 专栏收录该内容

102 篇文章 70 订阅

订阅专栏

从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