clickhouse常用查询汇总（持续更新）

请直接看目录，查看自己感兴趣的条目

查看某个表的字段大小

SELECT
name,
formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table = 'table_name'
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC

其中：

name表示列名。
formatReadableSize函数用于将字节数转换为可读的文件大小格式。
sum(data_compressed_bytes)和sum(data_uncompressed_bytes)分别计算每列的压缩后与未压缩数据总大小。
ratio显示平均压缩比率（未压缩/压缩）并保留两位小数。
查询针对表table_name。
结果按压缩数据量降序排列。

查看某个表的总大小

SELECT
formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table = 'log'

查看某个库的总大小

SELECT
formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE database = 'dbname'

compressed_size|uncompressed_size|ratio|
---------------+-----------------+-----+
55.07 GiB      |493.46 GiB       | 8.96|

查看每天的数据分区的总大小

select
partition,
formatReadableSize(sum(bytes_on_disk)) as disk_size,
formatReadableSize(sum(data_compresse