求Hive中将两组列合并转换为多行新列的SQL查询语句

这是个很常见的列转行需求，我给你准备了两种实用的Hive SQL方案，都能达成你的目标：

方案一：使用UNION ALL（逻辑直观，适合小数据量） #

这种方法的思路是把原表的两组列分别作为独立的查询结果，再合并起来，逻辑非常容易理解：

SELECT maths1 AS maths, physics1 AS physics
FROM your_table_name
UNION ALL
SELECT maths2 AS maths, physics2 AS physics
FROM your_table_name;

说明： #

• 第一个查询取maths1和physics1作为第一行数据
• 第二个查询取maths2和physics2作为第二行数据
• UNION ALL会保留所有行（包括重复行，如果有的话），如果需要去重可以换成UNION，但你的场景不需要这一步

方案二：使用LATERAL VIEW EXPLODE（性能更优，适合大数据量） #

如果你的表数据量很大，推荐用这种方法，因为它只需要扫描一次原表，比UNION ALL的两次扫描效率更高：

SELECT 
  t.maths, 
  array(physics1, physics2)[t.pos] AS physics
FROM your_table_name
LATERAL VIEW posexplode(array(maths1, maths2)) t AS pos, maths;

说明： #

1. 用array(maths1, maths2)把两列打包成一个数组，posexplode会把数组拆成多行，同时返回每个元素的位置索引pos
2. 再用array(physics1, physics2)[t.pos]根据位置索引取出对应的physics值，保证maths和physics的对应关系不会出错

测试验证 #

假设你的原表名为student_scores，数据如下：

+-------+-------+---------+---------+
|maths1 | maths2|physics1 |physics2 |
+-------+-------+---------+---------+
| 10    | 20    |30       |40       |
+-------+-------+---------+---------+

执行上述任意一种SQL，都会得到你期望的结果：

+-------+---------+
|maths  | physics |
+-------+---------+
| 10    |30       |
| 20    |40       |
+-------+---------+

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