Hive SQL如何对double数组列执行平均池化聚合？

Hive没有内置的AVG_POOL这类直接实现数组平均池化的聚合函数，但可以通过组合内置函数来实现你需要的“每个位置元素取平均值”的效果，具体步骤如下：

实现思路 #

• 拆分数组：用posexplode函数将数组拆分为带位置索引的行，保留每个元素对应的位置，确保后续能按位置计算平均值。
• 按位置求平均：按id和元素位置分组，计算每个位置上所有元素的平均值。
• 重组数组：将同一id下的各位置平均值按顺序重组为新数组，得到最终的池化结果。

完整SQL示例 #

SELECT 
    id,
    collect_list(avg_val) AS pooling_vector
FROM (
    -- 按id和位置分组，计算每个位置的平均值
    SELECT 
        id,
        pos,
        AVG(val) AS avg_val
    FROM TABLE_A
    -- 拆分数组为位置和元素
    LATERAL VIEW posexplode(vector) exploded AS pos, val
    GROUP BY id, pos
    -- 按位置排序，确保重组数组的顺序正确
    ORDER BY id, pos
) t
GROUP BY id;

结果验证 #

执行上述SQL后，输出结果与预期一致：

补充说明 #

• 该方法支持同一id下数组长度不一致的场景，缺失位置的元素仅计算存在的元素平均值，重组后的数组长度为该id下所有数组的最大长度。
• collect_list会保留元素顺序，因此子查询中的ORDER BY pos是确保数组元素位置与原数组一致的关键。

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