ClickHouse 中的公共表表达式 CTE

什么是公共表表达式(CTE) ？在本文中，学习如何在 ClickHouse 数据库中使用 CTE，并通过示例跟踪用例

在下列情况下使用 CTE 很方便:

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

额外的好处是提高了 SQL 查询的可读性。

CTE 与临时表和嵌套查询之间的区别是什么？

• 如果子查询是相关的，那么它的调用将针对选定内容中的每一行重复，从而显著增加执行此查询的成本。
• 用大量数据填充临时表会在磁盘上造成负载。
• 由于存储临时表的特殊性，使用临时表执行查询会增加执行时间。

语法

ClickHouse 既支持 WITH< 表达式 > AS < 标识符 > ，也支持 WITH< 标识符 > AS < 子查询表达式 > 语法。

• 使用 WITH 初始化 CTE。
• 为查询提供名称。
• 接着是 AS。
• 定义查询
• 如果需要多个 CTE，请用逗号分隔它们。

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

• 使用 WITH 初始化 CTE。
• 定义一个表达方式
• 接着是 AS。
• 为表达式提供名称。
• 如果需要多个 CTE，请用逗号分隔它们。

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

例子

建表:

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

插入:

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)

选择:

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)

结果:

** **