WITH | ClickHouse Documentation

ClickHouse 支持 CTE（Common Table Expression，公共表表达式），以增强查询语句的表达。例如下面的函数嵌套：

-- 原嵌套函数
SELECT pow(pow(2, 2), 3)

-- 更改为 CTE 形式
**WITH pow(2, 2) AS a** SELECT pow(a, 3)

CTE 通过 WITH 子句表示，目前支持以下 4 种用法。

1 .定义变量

可以定义变量，这些变量能够在后续的查询子句中被直接访问。例如下面示例中的常量 start，被直接用在紧接的 WHERE 子句中。

**WITH 10 AS start**
SELECT number FROM system.numbers 
WHERE number > **start** 
LIMIT 5

┌─number─┐
│     11 │
│     12 │
│     13 │
│     14 │
│     15 │
└────────┘

2.调用函数

可以访问 SELECT 子句中的列字段，并调用函数做进一步的加工处理。

例如在下面的示例中，对 data_uncompressed_bytes 使用聚合函数求和后，又紧接着在 SELECT 子句中对其进行了格式化处理：

**WITH SUM(data_uncompressed_bytes) AS bytes**
SELECT database , formatReadableSize(**bytes**) AS format FROM system.columns 
GROUP BY database 
ORDER BY bytes DESC

┌────database────┬─format─────┐
│ datasets       │ 12.12 GiB  │
│ default        │ 1.87 GiB   │
│ system         │ 1.10 MiB   │
│ dictionaries   │ 0.00 B     │
└────────────────┴────────────┘

3.定义子查询

可以定义子查询。例如在下面的示例中，借助子查询可以得出各 database 未压缩数据大小与数据总和大小的比例的排名。

W**ITH (
    SELECT SUM(data_uncompressed_bytes) FROM system.columns
) AS total_bytes**
SELECT database ,  (SUM(data_uncompressed_bytes) / **total_bytes**) * 100 AS database_disk_usage 
FROM system.columns 
GROUP BY database 
ORDER BY database_disk_usage DESC

┌─database───────┬──database_disk_usage───┐
│ datasets       │      85.15608638238845 │
│ default        │      13.15591656190217 │
│ system         │   0.007523354055850406 │
│ dictionaries   │                      0 │
└────────────────┴────────────────────────┘

在 WITH 中使用子查询时有一点需要特别注意，该查询语句只能返回一行数据，如果结果集的数据大于一行则会抛出异常。

4.在子查询中重复使用 WITH

在子查询中可以嵌套使用 WITH 子句，例如在下面的示例中，在计算出各 database 未压缩数据大小与数据总和的比例之后，又进行了取整函数的调用：

**WITH (
    round(database_disk_usage)
) AS database_disk_usage_v1**
SELECT database,database_disk_usage, **database_disk_usage_v1** 
FROM (
    --嵌套
    **WITH (
        SELECT SUM(data_uncompressed_bytes)  FROM system.columns
    ) AS total_bytes**
    SELECT database , (SUM(data_uncompressed_bytes) / **total_bytes**) * 100 AS database_disk_usage FROM system.columns 
    GROUP BY database 
    ORDER BY database_disk_usage DESC
)

┌────database────┬───database_disk_usage────┬─database_disk_usage_v1──────┐
│ datasets       │       85.15608638238845  │                     85      │
│ default        │       13.15591656190217  │                     13      │
│ system         │    0.007523354055850406  │                      0      │
└────────────────┴──────────────────────────┴─────────────────────────────┘