请求将含CROSS APPLY的SQL Server查询转换为Hive查询

你的SQL Server查询核心是把ek.student表中每个学生的6组科目成绩（math_1到computer_6）拆分成多行记录，每组成绩对应一行。在Hive中可以通过以下两种方式实现：

方法1：使用UNION ALL（兼容性好） #

这种方式适用于所有Hive版本，逻辑直观，将每组成绩单独查询后合并结果：

SELECT a.id, a.name, a.math_1 AS math, a.english_1 AS english, a.draw_1 AS draw, a.computer_1 AS computer
FROM ek.student a
UNION ALL
SELECT a.id, a.name, a.math_2 AS math, a.english_2 AS english, a.draw_2 AS draw, a.computer_2 AS computer
FROM ek.student a
UNION ALL
SELECT a.id, a.name, a.math_3 AS math, a.english_3 AS english, a.draw_3 AS draw, a.computer_3 AS computer
FROM ek.student a
UNION ALL
SELECT a.id, a.name, a.math_4 AS math, a.english_4 AS english, a.draw_4 AS draw, a.computer_4 AS computer
FROM ek.student a
UNION ALL
SELECT a.id, a.name, a.math_5 AS math, a.english_5 AS english, a.draw_5 AS draw, a.computer_5 AS computer
FROM ek.student a
UNION ALL
SELECT a.id, a.name, a.math_6 AS math, a.english_6 AS english, a.draw_6 AS draw, a.computer_6 AS computer
FROM ek.student a

方法2：使用LATERAL VIEW + EXPLODE（更简洁） #

如果你的Hive版本在0.13及以上（支持复杂数据类型），可以用这种更紧凑的方式，将每组成绩打包成结构体数组后炸开：

SELECT 
    a.id, a.name, 
    score.math, score.english, score.draw, score.computer
FROM ek.student a
LATERAL VIEW EXPLODE(
    ARRAY(
        NAMED_STRUCT('math', math_1, 'english', english_1, 'draw', draw_1, 'computer', computer_1),
        NAMED_STRUCT('math', math_2, 'english', english_2, 'draw', draw_2, 'computer', computer_2),
        NAMED_STRUCT('math', math_3, 'english', english_3, 'draw', draw_3, 'computer', computer_3),
        NAMED_STRUCT('math', math_4, 'english', english_4, 'draw', draw_4, 'computer', computer_4),
        NAMED_STRUCT('math', math_5, 'english', english_5, 'draw', draw_5, 'computer', computer_5),
        NAMED_STRUCT('math', math_6, 'english', english_6, 'draw', draw_6, 'computer', computer_6)
    )
) exploded_scores AS score;

说明 #

• UNION ALL方式无需依赖复杂数据类型，适合低版本Hive，但代码量较大；
• LATERAL VIEW方式代码更紧凑，维护性更好，但要求Hive支持NAMED_STRUCT和EXPLODE数组的功能。

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