#头条创作挑战赛#

ClickHouse的with子句

ClickHouse支持with子句以增强语句的表达，例如如下查询：

node1 :) SELECT pow(pow(2,2),3)
┌─pow(pow(2, 2), 3)─┐
│                64 │
└───────────────────┘

我们可以通过使用with子句进行简化，提高可读性：

node1 :) WITH pow(2,2) AS a SELECT power(a,3)
┌─pow(a, 3)─┐
│        64 │
└───────────┘

with的使用支持如下四种用法：

一、定义变量

可以通过with定义变量，这些变量在后续的查询子句中可以直接访问。

例如：

node1 :) WITH 10 AS start
SELECT number
FROM system.numbers
WHERE number > start
LIMIT 5
┌─number─┐
│     11 │
│     12 │
│     13 │
│     14 │
│     15 │
└────────┘

二、调用函数

可以访问select子句中的列字段，并调用函数做进一步处理，处理之后的数据可以在select子句中继续使用。

例如：

node1 :) 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 │ 7.40 GiB   │
│ system   │ 197.27 MiB │
│ default  │ 0.00 B     │
└──────────┴────────────┘

三、定义子查询

可以使用with定义子查询，例如，借助子查询可以得出各database未压缩数据大小与数据总和大小的比例排名：

node1 :) 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─┐
│ datasets │   97.31767735000648 │
│ system   │   2.682322649993527 │
│ default  │                   0 │
└──────────┴─────────────────────┘

注意：在with中使用子查询时智能返回一行数据，如果结果集大于一行则报错。

四、在子查询中重复使用with

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

node1 :) 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 │    97.2911778785499 │                     97 │
│ system   │  2.7088221214500954 │                      3 │
│ default  │                   0 │                      0 │
└──────────┴─────────────────────┴────────────────────────┘