ClickHouse 中的公用表达式

在以下情况下使用 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 语法

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)

结果