大数据ClickHouse进阶(八):ClickHouse的with子句
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 │
└──────────┴─────────────────────┴────────────────────────┘