Spark SQL中UNION列数不匹配时如何保留三列结果？

要实现新增event列且保证UNION操作列数匹配，只需给每个参与UNION的子查询都补充event列：第一个子查询从chess_wc_history_game_info表中选取真实的event值，另外两个子查询用空值（NULL或空字符串）填充event列即可。

修改后的Spark SQL代码如下：

spark.sql("""
(SELECT game_id, winner as player_name, event 
 FROM chess_wc_history_game_info 
 WHERE winner != 'draw' 
 GROUP BY game_id, event, winner)
UNION
(SELECT game_id, player as player_name, NULL AS event 
 FROM chess_wc_history_moves 
 WHERE black_queen_count = 0 AND color = 'Black')
UNION
(SELECT game_id, player as player_name, NULL AS event 
 FROM chess_wc_history_moves 
 WHERE white_queen_count = 0 AND color = 'White')
""").show()

关键说明： #

• UNION操作要求所有子查询返回的列数、列类型必须一致，因此必须为后两个子查询显式添加event列，用NULL AS event（或'' AS event）填充空值
• 原第一个子查询中的DISTINCT可以去掉，因为GROUP BY game_id, event, winner已经保证了结果的唯一性，不会影响原有结果
• 最终结果中，来自chess_wc_history_game_info的行会显示真实的event值，来自chess_wc_history_moves的行event列会显示NULL（若用空字符串则显示空白）

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