本文介绍在 Polars SQL API 环境下，如何将表中数组列（如 ['Manchester', 'Paris']）的每个元素，基于另一张查找表（如城市→国家映射）转换为对应值，并最终聚合回数组形式（如 ['Britain', 'France']）。核心方法是利用 UNNEST 展开、条件连接与 ARRAY_AGG 重建。
本文介绍在 polars sql api 环境下，如何将表中数组列（如 `['manchester', 'paris']`）的每个元素，基于另一张查找表（如城市→国家映射）转换为对应值，并最终聚合回数组形式（如 `['britain', 'france']`）。核心方法是利用 `unnest` 展开、条件连接与 `array_agg` 重建。
在标准关系型 SQL 中，原生数组操作受限，但 Polars 的 SQL 接口（遵循 PostgreSQL 风格扩展）支持 UNNEST、ARRAY_AGG 和数组成员判断（如 = ANY(array_col)），使其能优雅处理此类“数组映射”需求。
✅ 推荐方案：纯 SQL（Polars SQL API 兼容）
使用 CTE 分三步完成：
-
展开数组：用
UNNEST(Cities)将每个用户的城市数组拆为多行； -
精准匹配：
JOIN查找表时，同时匹配首都（Capital_City）和非首都城市（City = ANY(Other_cities)）； -
聚合回数组：按
Name分组，用ARRAY_AGG(DISTINCT Country)去重并重建数组。
WITH Exploded AS (
SELECT Name, UNNEST(Cities) AS City
FROM Data
),
Matched AS (
SELECT e.Name, l.Country
FROM Exploded e
JOIN Lookup l
ON e.City = l.Capital_City OR e.City = ANY(l.Other_cities)
)
SELECT Name, ARRAY_AGG(DISTINCT Country) AS Countries
FROM Matched
GROUP BY Name;✅ 优势：逻辑清晰、完全 SQL 驱动、无需 Python 介入，适合嵌入 Polars
.sql()调用中。
⚠️ 注意事项与常见陷阱
-
ANY()语法兼容性：Polars SQL 支持= ANY(array_col)，但部分传统 SQL 引擎（如 SQLite）不支持。若报错，可改用e.City IN (SELECT UNNEST(l.Other_cities))（需引擎支持嵌套UNNEST）。 -
重复城市导致重复国家：若某用户城市在多个国家条目中出现（如数据异常），
DISTINCT可避免冗余；若业务允许一对多映射（如“London”既属 Britain 又属虚构“Empire”），请移除DISTINCT并明确业务规则。 -
空匹配处理：若某城市在
Lookup表中完全未定义，该行将在JOIN后丢失——如需保留（显示NULL或默认值），应改用LEFT JOIN并配合COALESCE处理。 -
性能提示：对大数据集，建议为
Lookup.Capital_City和Lookup.Other_cities（若支持 GIN 索引）建立索引；Polars 内部会自动优化，但逻辑上仍推荐预展平Other_cities（见下方 Python 方案）。
? 替代方案：Polars DataFrame API（更灵活、更易调试）
当 SQL 表达复杂或需精细控制时，Python 层操作更直观：
import polars as pl
# 展开 Data 表的城市数组
data_exploded = data.explode("Cities")
# 展开 Lookup 表的 Other_cities 数组（使每城一行）
lookup_exploded = lookup.explode("Other_cities")
# 两次连接：一次匹配首都，一次匹配其他城市
capitals_join = data_exploded.join(
lookup.select(["Capital_City", "Country"]),
left_on="Cities",
right_on="Capital_City",
how="left"
).select(["Name", "Country"])
others_join = data_exploded.join(
lookup_exploded.select(["Other_cities", "Country"]),
left_on="Cities",
right_on="Other_cities",
how="left"
).select(["Name", "Country"])
# 合并结果，过滤空值，去重聚合
result = (
pl.concat([capitals_join, others_join])
.filter(pl.col("Country").is_not_null())
.group_by("Name")
.agg(pl.col("Country").unique().alias("Countries"))
)此方式显式分离步骤，便于插入日志、验证中间态，且天然支持 unique()、sort() 等高级数组操作。
✅ 总结
| 场景 | 推荐方式 | 关键要点 |
|---|---|---|
| 快速集成、SQL 优先工作流 |
UNNEST + JOIN + ARRAY_AGG
|
确保使用 DISTINCT 防重，ANY() 是关键语法糖 |
| 数据质量存疑、需调试或扩展逻辑 | Polars DataFrame API |
explode() 双向展平 + concat() 合并 + unique() 聚合 |
| 跨引擎迁移需求 | 避免 ANY()，改用子查询 IN (SELECT UNNEST(...))
|
提升兼容性，但可能影响性能 |
无论选择哪种路径，核心思想一致：先解构（explode/unnest），再关联（join），最后重构（array_agg/unique）——这是处理 SQL 数组映射问题的通用范式。
