ClickHouse 中的公用表表达式Common Table Expression (CTE)

最新推荐文章于 2023-11-10 23:57:33 发布

复杂的程序猿

最新推荐文章于 2023-11-10 23:57:33 发布

阅读量212

点赞数

分类专栏：大数据文章标签：大数据

本文链接：https://blog.csdn.net/weixin_39116917/article/details/125797025

版权

大数据专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文介绍了在ClickHouse中如何使用公共表表达式（CTE）来优化查询，特别是在处理可多次复用的查询结果、递归查询以及提升SQL可读性时。CTE与临时表和子查询相比，具有更优的性能和内存效率。通过示例展示了如何创建和使用CTE，并插入和查询数据。通过CTE，可以简化复杂查询，提高查询效率。

摘要由CSDN通过智能技术生成

在以下情况下使用 CTE在以下情况下使用 CTE在以下情况下使用 CTE很方便：

当一个请求可以获取数据，并且它的大小适合内存空间时
需要多次使用此查询的结果
创建递归查询

一个好处是您的 SQL 查询的可读性得到了提高您的 SQL 查询的可读性得到了提高您的 SQL 查询的可读性得到了提高。

CTE 与临时表和嵌套查询有什么区别？

如果子查询是相关的如果子查询是相关的如果子查询是相关的，然后对选择中的每一行重复其调用，从而大大增加了执行此查询的成本。
用大量数据填充临时表会在磁盘上产生负载。
由于存储临时表的特殊性，使用它们执行查询会增加执行时间

句法

ClickHouse支持WITH <expression> AS <identifier> 以及WITH <identifier> AS <subquery expression>语法。

使用启动 CTE WITH。
为查询提供名称。
跟随AS。
定义查询。
如果需要多个 CTE，请用逗号分隔它们

WITH locations AS
(
SELECT location
FROM table
WHERE date > (today() - 10)
)
SELECT *
FROM locations

使用启动 CTE WITH。
定义一个表达式。
跟随AS。
为表达式提供一个名称。
如果需要多个 CTE，请用逗号分隔它们。

WITH ('USA', 'BRA') AS locations
SELECT 'ARG' IN (locations)

Example

Create:

CREATE TABLE SpareParts
(
    `id` UInt32,
    `partName` String,
    `partOrigin` String,
    `storeID` UInt32
)
ENGINE = MergeTree()
ORDER BY id

Insert:

INSERT INTO SpareParts VALUES (1, 'headlight', 'USA', 1)
INSERT INTO SpareParts VALUES (2, 'hood', 'JPN', 1)
INSERT INTO SpareParts VALUES (3, 'bumper', 'USA', 1)
INSERT INTO SpareParts VALUES (4, 'radiator', 'BRA', 3)
INSERT INTO SpareParts VALUES (5, 'wheel', 'BRA', 2)
INSERT INTO SpareParts VALUES (6, 'stabilizer', 'ARG', 3)
INSERT INTO SpareParts VALUES (7, 'absorber', 'TUR', 2)
INSERT INTO SpareParts VALUES (8, 'cable', 'MEX', 1)
INSERT INTO SpareParts VALUES (9, 'spring', 'MEX', 3)
INSERT INTO SpareParts VALUES (10, 'door', 'USA', 2)

Select:

WITH
    originsByStore AS
    (
        SELECT
            storeID,
            groupArray(partOrigin) AS origins
        FROM SpareParts
        GROUP BY storeID
    ),
    partsByStore AS
    (
        SELECT
            storeID,
            groupArray(partName) AS partNames
        FROM SpareParts
        GROUP BY storeID
    ),
    has(origins, 'USA') = 1 AS isUSA
SELECT
    storeID,
    origins,
    partNames,
    isUSA
FROM originsByStore AS t1
LEFT JOIN
(
    SELECT
        storeID,
        partNames
    FROM partsByStore
) AS t2 USING (storeID)

Result: