主页 / coding / 问题 / 78511992
Accepted
lyrixx
Asked: 2024-05-21 20:47:48 +0800 CST

如何对 clickhouse 中的 groupArray 单元格中的值进行计数和分组

  • 772

我有以下代码：

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
    multiIf(statusCode >= 500, '5xx', statusCode >= 400, '4xx', statusCode >= 300, '3xx', statusCode >= 200, '2xx', 'errored'),
    groupArray(statusCode),
    count(1) as count
from statusCodes
group by 1;

在这里小提琴：https://fiddle.clickhouse.com/b3b3dfb9-1826-47a8-b201-831498c941f2

它给了我

1 2 3
4xx [404] 1
3xx [301, 301, 301, 301, 304] 5
2xx [200, 200, 200, 200, 200, 201] 6
5xx [500] 1
errored [0] 1

但我想要有类似的东西

1 2 3
2xx (200, 5), (201, 1) 6
3xx (301, 3), (304, 1) 5
4xx (404, 1) 1
5xx (500, 1) 1
errored (0, 1) 1

因此，总而言之，我想按主类别（2xx、3xx、4xx、5xx）对所有状态代码进行分组，并在行中按该类别的实际状态代码进行另一个分组。

clickhouse

1 个回答

  1. Best Answer

    这是使用两个聚合来完成此操作的方法，第一个通过错误组和错误代码，第二个仅通过错误：

    with cte as (
  select
    multiIf(statusCode >= 500, '5xx', statusCode >= 400, '4xx', statusCode >= 300, '3xx', statusCode >= 200, '2xx', 'errored') as error,
    statusCode,
    count(2) as cnt
  from status_code
  group by 1, 2
)
select error, groupArray(tuple(statusCode, cnt)) as arr, sum(cnt) as total_count
from cte
group by error
order by 1;

    结果 ：

    2xx     [(200,5),(201,1)]   6
3xx     [(301,4),(304,1)]   5
4xx     [(404,1)]           1
5xx     [(500,1)]           1
errored [(0,1)]             1

    演示在这里

    • 3

相关问题