在ClickHouse中匹配表数组元素并在新字段返回结果

原始表结构与数据 #

假设目标表为your_table，结构定义如下：

CREATE TABLE your_table (
    path String,
    arrKW Array(String)
) ENGINE = MergeTree()
ORDER BY path;

表中原始数据：

解决方案SQL #

SELECT
    t1.path,
    t1.arrKW,
    arrayFilter(
        x -> x != t1.path,
        arrayDistinct(
            arrayFlatten(groupArray(t2.path))
        )
    ) AS `Result Field`
FROM your_table t1
LEFT JOIN your_table t2
ON arrayIntersect(t1.arrKW, t2.arrKW) != []
GROUP BY t1.path, t1.arrKW
ORDER BY t1.path;

逻辑说明 #

1. 自关联匹配：通过LEFT JOIN将表与自身关联，关联条件为两条记录的arrKW数组存在交集元素（arrayIntersect结果非空）。
2. 收集匹配路径：用groupArray(t2.path)收集所有满足匹配条件的path，此时结果包含当前记录自身的path。
3. 去重与扁平化：arrayDistinct去除重复的path（同一记录可能因多个交集元素被多次匹配），arrayFlatten确保结果为一维数组。
4. 过滤自身路径：arrayFilter剔除与当前记录path相同的元素，得到最终符合要求的结果数组。

期望输出结果 #

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