基于collect_set生成的列表转换为0-1宽表（Hive环境，禁用PIVOT）

针对Hive不支持PIVOT的场景，可通过侧写拆分数组+条件聚合实现需求，同时适配170万行数据的处理效率，具体步骤如下：

步骤1：拆分数组为多行 #

使用LATERAL VIEW EXPLODE将list_center数组拆分为每行对应一个元素的结构，同时统一元素大小写（原数据存在Lorem和lorem，需对齐结果中的小写格式）：

WITH exploded_data AS (
    SELECT 
        id,
        LOWER(elem) AS tag  -- 统一转为小写，消除大小写差异
    FROM your_table
    LATERAL VIEW EXPLODE(list_center) exploded AS elem
    -- 空数组的行不会被拆分，后续聚合时自动处理为全0
)

步骤2：条件聚合生成宽表 #

基于拆分后的数据，按id分组，通过MAX(CASE WHEN)逻辑生成每个标签的0/1列。若标签数量较多，可先自动获取所有唯一标签，再动态生成SQL：

第一步：获取所有唯一标签 #

先执行该查询得到所有需要生成的列名：

SELECT DISTINCT LOWER(elem) AS tag FROM your_table LATERAL VIEW EXPLODE(list_center) exploded AS elem;

第二步：生成最终聚合SQL #

将上述查询得到的标签代入以下模板，替换对应tag字段即可：

WITH exploded_data AS (
    SELECT 
        id,
        LOWER(elem) AS tag
    FROM your_table
    LATERAL VIEW EXPLODE(list_center) exploded AS elem
)
SELECT 
    t.id,
    MAX(CASE WHEN ed.tag = 'lorem' THEN 1 ELSE 0 END) AS lorem,
    MAX(CASE WHEN ed.tag = 'ipsum' THEN 1 ELSE 0 END) AS ipsum,
    MAX(CASE WHEN ed.tag = 'who' THEN 1 ELSE 0 END) AS who,
    MAX(CASE WHEN ed.tag = 'exercise' THEN 1 ELSE 0 END) AS exercise,
    MAX(CASE WHEN ed.tag = 'train' THEN 1 ELSE 0 END) AS train,
    MAX(CASE WHEN ed.tag = 'airplane' THEN 1 ELSE 0 END) AS airplane,
    MAX(CASE WHEN ed.tag = 'tariff' THEN 1 ELSE 0 END) AS tariff
FROM your_table t
LEFT JOIN exploded_data ed ON t.id = ed.id
GROUP BY t.id
ORDER BY t.id;

关键细节说明 #

• 空数组处理：通过LEFT JOIN保留原表所有id，空数组的行因无匹配拆分数据，所有CASE WHEN逻辑会返回0，符合需求。
• 性能适配：170万行数据需确保id列有索引或利用分区减少扫描范围；若标签数量极大（上千个），动态生成SQL时需注意Hive的列数限制。

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