ClickHouse多列分组求和需求：按country与group分别聚合invalid字段

原始查询与结果 #

我编写的ClickHouse查询语句：

select id, c as country, 
        if(reason in [11, 100], 'food',  dictGetString('data.lables', 'threat_group', tuple(toInt16(reason)))) as group,
        toInt32(sumIf(count, dictGetString('data.lables',   'group', tuple(toInt16(reason))) IN
        ('Invalid a', 'Invalid b')
      AND reason NOT IN (200, 300) 
      AND url_path NOT iLIKE '%hello%'
    )) as invalid 
from data.table 
where id in(5957) 
group by id, group,country 

执行后得到的结果：

id   country    group       invalid
100     US      Not Known   30
100     GB      Known       2
100     GB      Undeclared  3
100     UA                  0
100     US      Known       20
100     GB                  2
100     TR      Undeclared   0
100     UA      Undeclared   3

需求 #

针对invalid字段，分别按country和group进行分组聚合，得到如下格式的结果：

id     country      group      invalid
100     -           Not Known  38
100     -           Known      22
100     -           Undeclared  3
100     US          -          50
100     GB          -           7
100     UA          -           3

实现方案 #

通过UNION ALL合并两种分组逻辑的结果，同时过滤无效数据：

-- 按group聚合，country统一显示为'-'
SELECT 
    id,
    '-' AS country,
    group,
    sum(invalid) AS invalid
FROM (
    SELECT 
        id,
        country,
        ifEmpty(group, 'Unknown') AS group,
        invalid
    FROM (
        -- 原始查询语句
        select id, c as country, 
                if(reason in [11, 100], 'food',  dictGetString('data.lables', 'threat_group', tuple(toInt16(reason)))) as group,
                toInt32(sumIf(count, dictGetString('data.lables',   'group', tuple(toInt16(reason))) IN
                ('Invalid a', 'Invalid b')
              AND reason NOT IN (200, 300) 
              AND url_path NOT iLIKE '%hello%'
            )) as invalid 
        from data.table 
        where id in(100) -- 注意原始查询id条件与结果不一致，此处按结果调整
        group by id, group,country 
    ) t
    WHERE invalid > 0 -- 过滤invalid为0的行
) t1
GROUP BY id, group

UNION ALL

-- 按country聚合，group统一显示为'-'
SELECT 
    id,
    country,
    '-' AS group,
    sum(invalid) AS invalid
FROM (
    SELECT 
        id,
        country,
        invalid
    FROM (
        -- 原始查询语句
        select id, c as country, 
                if(reason in [11, 100], 'food',  dictGetString('data.lables', 'threat_group', tuple(toInt16(reason)))) as group,
                toInt32(sumIf(count, dictGetString('data.lables',   'group', tuple(toInt16(reason))) IN
                ('Invalid a', 'Invalid b')
              AND reason NOT IN (200, 300) 
              AND url_path NOT iLIKE '%hello%'
            )) as invalid 
        from data.table 
        where id in(100)
        group by id, group,country 
    ) t
    WHERE invalid > 0
) t2
GROUP BY id, country
ORDER BY id, country, group;

关键说明 #

• 用UNION ALL将两组聚合结果合并：一组聚焦group维度，另一组聚焦country维度，分别用'-'填充未聚合的字段。
• 过滤invalid > 0的行，匹配期望结果中无0值的要求。
• 用ifEmpty(group, 'Unknown')处理空group值，避免聚合时出现空条目，若需保留空值可移除该函数。
• 注意原始查询中id条件与实际结果不一致，需根据真实数据调整where id in(...)的参数。

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