Hive SQL中如何分别对多列分组统计并聚合为指定格式？

针对多列统计不同值出现次数并聚合为ARRAY<MAP<BIGINT, BIGINT>>的需求，推荐用列转行+分层聚合的思路替代重复的子查询+UNION ALL，核心是先把多列转成键值对格式，再逐层完成统计与聚合，写法简洁且易维护。

通用实现（适配Hive等支持LATERAL VIEW的引擎） #

SELECT collect_list(col_stats) AS final_result
FROM (
    -- 按列名聚合，生成每列的统计MAP
    SELECT col_name, collect_map(col_val, cnt) AS col_stats
    FROM (
        -- 统计每个列下各值的出现次数
        SELECT col_name, col_val, COUNT(*) AS cnt
        FROM t
        -- 将所有需要统计的列转成（列名, 列值）的键值对
        LATERAL VIEW EXPLODE(map(
            'foo', foo,
            'bar', bar,
            'col3', col3,
            'col4', col4
            -- 继续添加其他需要统计的列，格式为 '列名', 列名
        )) exploded_cols AS col_name, col_val
        GROUP BY col_name, col_val
    ) stats_per_value
    GROUP BY col_name
) stats_per_column

Spark SQL简化版（支持UNPIVOT语法） #

如果使用Spark SQL，可直接用UNPIVOT语法实现列转行，代码更简洁：

SELECT collect_list(col_stats) AS final_result
FROM (
    SELECT col_name, collect_map(col_val, cnt) AS col_stats
    FROM (
        SELECT col_name, col_val, COUNT(*) AS cnt
        FROM t
        -- 直接指定要转置的列
        UNPIVOT (col_val FOR col_name IN (foo, bar, col3, col4))
        GROUP BY col_name, col_val
    ) stats_per_value
    GROUP BY col_name
) stats_per_column

优势说明 #

• 避免重复代码：只需在map()或UNPIVOT的IN列表中列出所有目标列，新增/删除列仅需修改此处
• 逻辑清晰：从列转行→值统计→列聚合→数组收集，分层实现易理解
• 性能更优：减少了UNION ALL带来的多次表扫描，仅需扫描原表一次即可完成所有统计

内容的提问来源于stack exchange，提问作者Ch'en Meng