ClickHouse匹配两表数组元素并生成含去重结果的新字段

我有两张表test和test2，均包含path（字符串类型）和arrKW（字符串数组类型）字段。表结构及数据如下：

表test的创建语句与数据 #

create table test (path String, arrKW Array(String))Engine=Memory as
select * from values (('folder/puntonet',['kw1','kw2']),
('folder/puntonet-2.0',['kw2','kw3']),
('folder/puntonet-4',['kw2','kw4']),
('folder/puntonet-5',['kw5','kw4']));

对应数据：

表test2的创建语句与数据 #

create table test2 (path String, arrKW Array(String))Engine=Memory as
select * from values (('folder/otherpuntonet',['kw1','kw2']),
('folder/otherpuntonet-2.0',['kw2','kw77']),
('folder/otherpuntonet-4',['kw2','kw77']),
('folder/puntonet-5',['kw5','kw4']))

对应数据：

需求 #

匹配两表中至少共享一个数组元素的记录，为符合条件的记录生成新字段arrResult，该字段包含来自test2的去重path值。期望结果如下：

解决方案 #

通过数组交叉匹配+分组聚合的方式实现，利用ClickHouse的数组函数完成逻辑：

SELECT
    t1.path,
    t1.arrKW,
    groupArrayDistinct(t2.path) AS `arrResult (from table2)`
FROM (
    SELECT * FROM test
    UNION ALL
    SELECT * FROM test2
) t1
LEFT JOIN test2 t2 ON arrayIntersect(t1.arrKW, t2.arrKW) != [] AND t1.path != t2.path
GROUP BY t1.path, t1.arrKW
HAVING length(`arrResult (from table2)`) > 0
ORDER BY t1.path;

逻辑说明 #

1. 合并两表数据：用UNION ALL把test和test2的所有记录合并，确保所有需要匹配的条目都被纳入计算；
2. 关联匹配条件：通过arrayIntersect判断两个数组是否存在共享元素，同时排除自身匹配（t1.path != t2.path）；
3. 分组聚合去重：按t1.path和t1.arrKW分组，用groupArrayDistinct收集并去重来自test2的path值；
4. 过滤无效结果：用HAVING过滤掉没有匹配结果的记录，只保留符合需求的条目。

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