如何在ClickHouse中生成包含子分组聚合结果的汇总数据？

当然可以做到！而且完全不用低效的Join或者重复查询——ClickHouse的嵌套聚合函数就能完美解决这个需求，还能保证只扫描一次原表，性能拉满。

给你一个完全匹配你需求的实现，直接就能用：

SELECT
    id,
    -- 计算id维度的总平均：用各client的v1总和除以总记录数，和直接从原表聚合结果一致
    sum(client_total_v1) / sum(client_count) AS AVG1,
    -- 计算id维度的总sum：直接累加各client的sum结果
    sum(client_sum_v2) AS SUM2,
    -- 把每个client的聚合结果打包成带字段名的数组，和你示例的格式一致
    groupArray(struct(client_avg_v1 AS AVG1, client_sum_v2 AS SUM2)) AS "Rows per Client"
FROM (
    -- 第一步：先按id+client做子分组聚合，拿到每个client的统计值
    SELECT
        id,
        client,
        AVG(v1) AS client_avg_v1,
        SUM(v1) AS client_total_v1,  -- 保留v1总和，用于外层计算id级平均
        COUNT(v1) AS client_count,   -- 保留记录数，用于外层计算id级平均
        SUM(v2) AS client_sum_v2
    FROM table
    WHERE `when` > today()
    GROUP BY id, client
)
GROUP BY id

逻辑解释 #

1. 内层子查询：先完成id+client维度的聚合，算出每个客户对应的v1平均、v1总和、记录数、v2总和，这一步把明细数据初步聚合，减少外层计算的压力。
2. 外层查询：再按id做二次聚合：
   • 用各client的v1总和除以总记录数，得到id级别的平均（和直接从原表按id聚合的结果完全一致，而且更高效，不用处理原始明细）；
   • 累加各client的v2总和，得到id级别的总sum；
   • 用groupArray函数把同一个id下的所有client聚合结果（包装成带字段名的结构体）收集成数组，就是你要的「Rows per Client」字段。

为什么比Join好？ #

这个方案只扫描一次原表，相比你之前用Join的方案（需要两次扫描表，分别计算id级和id+client级聚合），性能提升非常明显——尤其是你的真实表数据量大、字段多的时候，这种差异会被放大很多。

可选优化 #

如果你想在数组里保留client的名称，让结果更直观，可以修改结构体的定义：

groupArray(struct(client AS client_name, client_avg_v1 AS AVG1, client_sum_v2 AS SUM2)) AS "Rows per Client"

这样数组里的每个元素会变成{client_name: 'xxx', AVG1: 110, SUM2: 150}的形式，后续解析起来更清晰。

完全不用纠结Join的方案，这种嵌套聚合是ClickHouse专门针对这类分层聚合场景优化的写法，既简洁又高效，完全能满足你的需求。

备注：内容来源于stack exchange，提问作者user21994033