PySpark中如何将数组值转为列名并匹配对应数组元素

输入表包含ID、标签数组col_List和对应ID数组col_ID，需要将标签作为列名，同索引的ID作为对应列的值。尝试用array_contains函数未成功，因为该函数仅检查元素存在性，无法关联数组索引。

输入表：

+---------------+------------------+--------------------+
|ID             |col_List          |col_ID              |
+---------------+------------------+--------------------+
|10001          |["T1","T2"]       |["100","200"]       |
+---------------+------------------+--------------------+
|10002          |["T2"]            |["300"]             |
+---------------+------------------+--------------------+
|10003          |["T1","T2","T3"]  |["150","220","580"] |
+---------------+------------------+--------------------+

预期输出：

+---------------+--------+--------+--------+
|ID             |T1      |T2      |T3      |
+---------------+--------+--------+--------+
|10001          |100     |200     |null    |
+---------------+--------+--------+--------+
|10002          |null    |300     |null    |
+---------------+--------+--------+--------+
|10003          |150     |220     |580     |
+---------------+--------+--------+--------+

核心思路是先通过索引关联两个数组的元素，再将标签列转行，具体步骤以Spark SQL为例：

1. 用posexplode展开两个数组，保留索引位置，确保标签与ID严格按索引配对：

SELECT
    ID,
    col_List_item AS tag,
    col_ID_item AS value
FROM
    input_table
LATERAL VIEW posexplode(col_List) AS pos, col_List_item
LATERAL VIEW posexplode(col_ID) AS pos2, col_ID_item
WHERE pos = pos2

这一步会将每组索引对应的标签和ID拆分为单行，保证索引完全匹配。

2. 对拆分结果进行透视，将标签转为列：

SELECT
    ID,
    MAX(CASE WHEN tag = 'T1' THEN value END) AS T1,
    MAX(CASE WHEN tag = 'T2' THEN value END) AS T2,
    MAX(CASE WHEN tag = 'T3' THEN value END) AS T3
FROM (
    SELECT
        ID,
        col_List_item AS tag,
        col_ID_item AS value
    FROM
        input_table
    LATERAL VIEW posexplode(col_List) AS pos, col_List_item
    LATERAL VIEW posexplode(col_ID) AS pos2, col_ID_item
    WHERE pos = pos2
) t
GROUP BY ID

如果使用其他SQL引擎，可替换为对应索引展开函数：比如PostgreSQL用unnest ... WITH ORDINALITY，Hive同样支持posexplode。

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