如何在ClickHouse中对groupArray内的值进行分组统计

以下是实现你需求的SQL代码，通过两层分组完成先按状态码类别聚合、再在组内统计具体状态码数量的逻辑：

drop table if exists statusCodes;

create table statusCodes (statusCode UInt16) engine=Memory;

insert into statusCodes values (0);
insert into statusCodes values (200);
insert into statusCodes values (200);
insert into statusCodes values (200);
insert into statusCodes values (200);
insert into statusCodes values (200);
insert into statusCodes values (201);
insert into statusCodes values (301);
insert into statusCodes values (301);
insert into statusCodes values (301);
insert into statusCodes values (301);
insert into statusCodes values (304);
insert into statusCodes values (404);
insert into statusCodes values (500);

select
    category,
    arrayStringConcat(arrayMap(x -> concat('(', toString(x.1), ', ', toString(x.2), ')'), groupTuple(statusCode, code_count)), ', ') as detailed_stats,
    sum(code_count) as total_count
from (
    select
        multiIf(statusCode >= 500, '5xx', statusCode >= 400, '4xx', statusCode >= 300, '3xx', statusCode >= 200, '2xx', 'errored') as category,
        statusCode,
        count(1) as code_count
    from statusCodes
    group by category, statusCode
)
group by category
order by category;

逻辑说明 #

1. 内层子查询：先为每个状态码标记所属类别，再按「类别+具体状态码」分组，统计每个状态码在对应类别下的出现次数。
2. 外层查询：按类别再次分组，将每组内的状态码和对应次数打包成元组数组，通过arrayMap和concat格式化为(状态码, 次数)的字符串，最后用arrayStringConcat拼接成逗号分隔的明细；同时通过sum(code_count)计算每个类别的总数量。

执行结果 #

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