如何在ClickHouse中实现类ElasticSearch自定义范围直方图及优化？

1. ClickHouse是否有内置函数/更简便的实现方式？ #

ClickHouse 24.2版本没有完全等价于ES自定义直方图的一键聚合，但可以通过以下内置函数简化实现：

• intDivOrZero函数：替代原查询中floor((number - offset)/interval_val)*interval_val + offset的计算逻辑。针对整数场景，整数除法本身就是向下取整，用intDivOrZero(number - offset, interval_val)*interval_val + offset可得到完全一致的桶ID，且避免浮点运算，效率更高。
• 补全空桶的实现：如果需要像ES直方图那样自动补全无数据的空桶（原查询仅显示有数据的桶），可以结合range和arrayJoin生成所有预期桶，再通过左关联统计数据，确保所有区间都出现在结果中。

2. 原查询的优化方向及优化后语句 #

针对原查询，可从简化层级、优化计算逻辑、提升函数效率三个方向优化：

• 合并嵌套子查询，减少查询层级；
• 用intDivOrZero替代floor的浮点运算；
• 用直接字符串拼接concat替代arrayStringConcat，避免数组创建开销。

优化后的查询语句：

WITH
    0 AS offset,
    2 AS interval_val
SELECT
    bucket_id,
    concat(toString(bucket_id), '-', toString(bucket_id + interval_val - 1)) AS bucket_start_end_range,
    count(number) AS count
FROM (
    SELECT
        number,
        intDivOrZero(number - offset, interval_val) * interval_val + offset AS bucket_id
    FROM system.numbers
    LIMIT 10
)
GROUP BY bucket_id
ORDER BY bucket_id

如果需要补全无数据的空桶，可使用以下语句：

WITH
    0 AS offset,
    2 AS interval_val,
    (SELECT max(number) FROM system.numbers LIMIT 10) AS max_num
SELECT
    bucket,
    concat(toString(bucket), '-', toString(bucket + interval_val - 1)) AS bucket_start_end_range,
    coalesce(count, 0) AS count
FROM (
    SELECT range(offset, max_num + interval_val, interval_val) AS buckets
)
ARRAY JOIN buckets AS bucket
LEFT JOIN (
    SELECT
        intDivOrZero(number - offset, interval_val) * interval_val + offset AS bucket_id,
        count(number) AS count
    FROM system.numbers
    LIMIT 10
) ON bucket = bucket_id
ORDER BY bucket

内容的提问来源于stack exchange，提问作者srihari92