如何在ClickHouse中用关联表数据替换数组内的ID

解决方案 #

我们可以通过WITH子句构建ID-姓名映射Map，再结合ArrayMap函数直接遍历数组完成替换，无需GROUP BY。

对应的SQL语句如下：

WITH (SELECT map(id, name) FROM person) AS id_to_name
SELECT
    not_unique,
    arrayMap(id -> id_to_name[id], people) AS people_names
FROM somestats;

执行结果 #

执行上述查询后，会得到符合预期的输出：

┌─not_unique─┬─people_names───────────┐
│ FUNEVENT   │ ['John','Jane','Doe']  │
│ FUNEVENT   │ ['John','Doe']         │
│ OTHEREVENT │ []                     │
└────────────┴────────────────────────┘

原理说明 #

1. 构建映射Map：通过WITH (SELECT map(id, name) FROM person)将person表的id和name转换为一个键值对Map，一次性加载所有ID与姓名的对应关系。
2. 数组映射替换：arrayMap(id -> id_to_name[id], people)会遍历somestats表中people数组的每个ID元素，从预构建的Map中取出对应的姓名，最终生成姓名数组。
3. 空数组处理：如果原数组为空，arrayMap会直接返回空数组，无需额外处理。

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