如何通过SQL将Data表中的城市数组转换为对应的国家数组（基于Lookup表映射）

当然可以实现！针对你用的Polars SQL，我们可以通过「拆分数组→匹配国家→重新聚合数组」的三步法来解决这个问题，具体思路和完整查询如下：

核心思路 #

数组类型的映射在SQL里确实需要特殊处理——我们没法直接对数组整体做JOIN匹配，所以得先把数组拆成单个元素（变成常规的行结构），用普通的JOIN完成城市到国家的映射后，再把结果重新组合成数组，最后关联回原表的用户名称。

完整SQL查询 #

WITH exploded_cities AS (
    -- 第一步：把Data表的城市数组拆分为单个城市行，同时保留用户名
    SELECT
        Name,
        explode(Cities) AS City
    FROM Data
),
mapped_countries AS (
    -- 第二步：将每个城市与Lookup表关联，匹配对应的国家
    SELECT
        ec.Name,
        l.Country
    FROM exploded_cities ec
    JOIN Lookup l
    -- 匹配逻辑：城市是国家首都，或者属于国家的其他城市列表
    ON ec.City = l.Capital_City OR ec.City IN l.Other_cities
)
-- 第三步：按用户名分组，把对应国家重新聚合为数组
SELECT
    Name,
    array_agg(Country) AS Countries
FROM mapped_countries
GROUP BY Name

关键细节说明 #

1. explode(Cities) 拆分数组：这个函数会把每个用户的城市数组拆成多行数据，比如Mark的一行数据会拆成('Mark', 'Manchester')和('Mark', 'Paris')两行，这样就能对每个城市单独做匹配操作。
2. JOIN的匹配条件：完美覆盖了你Lookup表的两种城市存储方式——首都以单个值存储，其他城市以数组存储。用IN操作符就能判断城市是否在目标数组中。
3. array_agg(Country) 聚合数组：按用户名分组后，这个函数会把该用户对应的所有国家重新组合成数组，而且会保留原城市数组的顺序（因为explode会严格遵循原数组的元素顺序，array_agg默认按输入顺序聚合）。

测试数据的输出结果 #

执行上面的查询后，会完全符合你期望的输出：

可选优化：处理无匹配的城市 #

如果你的数据中存在Lookup表里没有的城市，上面的查询会自动过滤掉这些城市。如果想保留原数组的所有元素（无匹配的城市可以显示'Unknown'或其他默认值），可以把JOIN改成LEFT JOIN，并在聚合时处理空值：

WITH exploded_cities AS (
    SELECT Name, explode(Cities) AS City FROM Data
),
mapped_countries AS (
    SELECT
        ec.Name,
        COALESCE(l.Country, 'Unknown') AS Country
    FROM exploded_cities ec
    LEFT JOIN Lookup l
    ON ec.City = l.Capital_City OR ec.City IN l.Other_cities
)
SELECT Name, array_agg(Country) AS Countries
FROM mapped_countries
GROUP BY Name

备注：内容来源于stack exchange，提问作者DarthVlader