Hive SQL技术问询：基于指定列将多列合并为数组生成单记录

这问题我做数仓的时候经常碰到，用Hive的内置函数就能轻松搞定，分两种常见场景给你拆解：

场景1：多行同分组列，合并单列的无重复值为数组 #

如果你的数据是同一个Name对应多行不同的值，要把这些值去重后合并成数组，每个Name只留一条记录，直接用collect_set()函数就行。

示例表结构与数据 #

假设我们有一张用户偏好表：

CREATE TABLE user_preferences (
  user_name STRING,
  favorite_fruit STRING
);

-- 插入测试数据
INSERT INTO user_preferences VALUES
('Alice', 'Apple'),
('Alice', 'Banana'),
('Alice', 'Apple'), -- 重复值
('Bob', 'Date'),
('Bob', 'Elderberry');

实现SQL #

SELECT
  user_name,
  collect_set(favorite_fruit) AS unique_fruits_array
FROM user_preferences
GROUP BY user_name;

输出结果 #

场景2：同一行多列合并，且跨多行去重合并为数组 #

如果你的数据是同一个Name对应一行里的多个列，甚至跨多行有重复值，需要先把多列转成行，再用collect_set()去重合并。

示例表结构与数据 #

比如用户的多个兴趣列：

CREATE TABLE user_hobbies (
  user_name STRING,
  hobby1 STRING,
  hobby2 STRING,
  hobby3 STRING
);

-- 插入测试数据
INSERT INTO user_hobbies VALUES
('Alice', 'Reading', 'Hiking', 'Reading'), -- 同一行重复
('Alice', 'Cooking', 'Hiking', 'Painting'), -- 跨行重复
('Bob', 'Gaming', 'Fishing', NULL);

实现SQL #

先用LATERAL VIEW explode()把多列转成多行，再分组收集去重：

SELECT
  user_name,
  collect_set(COALESCE(hobby, 'No Hobby')) AS unique_hobbies_array -- 处理NULL值
FROM user_hobbies
LATERAL VIEW explode(array(hobby1, hobby2, hobby3)) exploded_hobbies AS hobby
GROUP BY user_name;

输出结果 #

关键注意事项 #

• collect_set()是核心：它会自动对收集到的值去重，并且分组后每个分组只返回一条记录，完全匹配你的需求
• 顺序问题：collect_set()返回的数组是无序的，如果需要保留值的出现顺序，得用collect_list()配合去重逻辑（比如自定义UDF或者窗口函数先去重再收集）
• NULL值处理：collect_set()会自动忽略NULL，如果需要把NULL也纳入数组，记得用COALESCE()把NULL替换成你需要的标记值

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