ClickHouse中按A分组且按B/C等任意列匹配分组的实现技巧

问题场景 #

现有如下数据表：

┌─A─┬─B─┬─C─┐
│ 1 │ 1 │ 1 │
│ 1 │ 2 │ 1 │
│ 1 │ 3 │ 3 │
└───┴───┴───┘

需要实现的分组逻辑：按A分组的同时，若行与行之间在B、C（或更多列）中任意一列的值相同，则归为同一组。期望实现类似如下逻辑的查询：

select A, groupArray(B) as B, groupArray(C) as C
from test
group by A and (B or C)

预期查询结果：

┌─A─┬─B----─┬─C─----┐
│ 1 │ [1,2] │ [1,1] │  // A和C匹配对应前两行
│ 1 │ [3]   │ [3]   │  // A匹配但B和C均不匹配对应第三行
└───┴──----─┴──----─┘

建表与插入数据的脚本：

create table tests
(
    A UInt64,
    B UInt64,
    C UInt64
)
    engine = Memory;

insert into tests (A, B, C)
values (1, 1, 1),
       (1, 2, 1),
       (1, 3, 3);

实现方案 #

可以利用ClickHouse的connectedComponents函数识别连通分量，将满足条件的行归为同一组。核心思路是：同一A值的行中，只要在B或C列有相同值，就视为同一连通组，再按A和连通组ID分组聚合。

查询脚本如下：

WITH
    rowNumberInAllBlocks() AS row_id,
    -- 生成同一A下，B或C值相同的行关联关系
    arrayConcat(
        arrayMap(x -> (row_id, x), (SELECT groupArray(row_id) FROM tests WHERE A = t.A AND B = t.B)),
        arrayMap(x -> (row_id, x), (SELECT groupArray(row_id) FROM tests WHERE A = t.A AND C = t.C))
    ) AS edges,
    arrayJoin(edges) AS edge,
    connectedComponents(edge.1, edge.2) AS component_id
SELECT
    A,
    groupArray(B) AS B,
    groupArray(C) AS C
FROM tests t
GROUP BY A, component_id
ORDER BY A, component_id;

扩展说明 #

如果需要支持更多列（如D、E等），只需在arrayConcat中添加对应列的关联逻辑即可，示例如下：

arrayConcat(
    arrayMap(x -> (row_id, x), (SELECT groupArray(row_id) FROM tests WHERE A = t.A AND B = t.B)),
    arrayMap(x -> (row_id, x), (SELECT groupArray(row_id) FROM tests WHERE A = t.A AND C = t.C)),
    arrayMap(x -> (row_id, x), (SELECT groupArray(row_id) FROM tests WHERE A = t.A AND D = t.D))
    -- 继续添加更多列的关联规则
)

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