如何在ClickHouse中实现多层嵌套Terms聚合（优化复杂查询）

需求说明 #

需要针对访问日志实现类似Elasticsearch的多层嵌套Terms聚合：先按userid做一级聚合，再在每个userid的子集内按status做二级聚合；同时需要适配动态字段聚合规划，避免原查询动态生成时过于复杂的问题。

现有实现（存在复杂度问题） #

原查询通过两次独立聚合+JOIN实现需求，但动态生成时需要维护多层子查询和关联逻辑，冗余且易出错：

原查询语句 #

SELECT
    base_query_userid,
    base_query_userid_count,
    sub_term_aggr
FROM
(
    SELECT
        userid AS base_query_userid,
        count(userid) AS base_query_userid_count
    FROM esvsch AS base
    GROUP BY userid
    HAVING base_query_userid_count > 8
    ORDER BY userid DESC
    LIMIT 15
) AS base_query
LEFT JOIN
(
    SELECT
        userid AS sub_query_userid,
        groupArray(tupleArray) AS sub_term_aggr
    FROM
    (
        SELECT
            (('status', status), ('count', status_count)) AS tupleArray,
            userid,
            count(status) AS status_count
        FROM esvsch
        GROUP BY
            userid,
            status
        HAVING status_count > 1
        ORDER BY status ASC
    )
    GROUP BY sub_query_userid
    LIMIT 15
) AS sub_query ON sub_query.sub_query_userid = base_query.base_query_userid

表结构 #

CREATE TABLE esvsch
(
    `method` String,
    `uri` String,
    `session_id` String,
    `userid` Int32,
    `req_id` Int32,
    `_timestamp` DateTime64(3),
    `time_taken` Int32,
    `thread_id` Int32,
    `status` Int32,
    `ip` IPv4,
)
ENGINE = MergeTree
PRIMARY KEY (req_id,_timestamp)
ORDER BY (req_id, _timestamp)

优化方案：单扫描+窗口函数简化聚合逻辑 #

通过一次表扫描+窗口函数，同时完成一级聚合的总计数和二级聚合的明细统计，避免JOIN操作，动态生成时只需调整聚合字段列表即可：

优化后查询语句 #

SELECT
    userid AS base_query_userid,
    total_count AS base_query_userid_count,
    -- 按status升序排列打包成键值对格式，与原输出结构一致
    groupArray(tuple(tuple('status', status), tuple('count', status_count))) AS sub_term_aggr
FROM (
    SELECT
        userid,
        status,
        count(*) AS status_count,
        -- 窗口函数计算当前userid的总请求数（一级聚合结果）
        count(*) OVER (PARTITION BY userid) AS total_count
    FROM esvsch
    GROUP BY userid, status
    -- 先过滤二级聚合的最小计数
    HAVING status_count > 1
)
-- 过滤一级聚合的最小计数
WHERE total_count > 8
GROUP BY userid, total_count
ORDER BY userid DESC
LIMIT 15

方案优势 #

1. 性能更优：仅扫描一次表，避免原方案中两次扫描+JOIN的开销；
2. 动态适配性强：如需新增聚合维度（如增加按method三级聚合），只需在子查询的GROUP BY中添加字段，并调整groupArray的打包内容即可，无需重构整个查询结构；
3. 逻辑更简洁：所有聚合逻辑集中在一个子查询内，易于维护和动态生成。

验证结果 #

优化后查询输出结构与原查询完全一致，示例输出如下：

┌─base_query_userid─┬─base_query_userid_count─┬─sub_term_aggr─────────────────────────────────────────────────────────────────────────────────────────────────┐
│          83634308 │                       9 │ [(('status',200),('count',3)),(('status',400),('count',2)),(('status',500),('count',2))] │
│          73634208 │                       9 │ [(('status',200),('count',3)),(('status',301),('count',2)),(('status',404),('count',2))] │
│          63634108 │                      10 │ [(('status',200),('count',4)),(('status',301),('count',2)),(('status',404),('count',2))] │
└───────────────────┴─────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

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