【Hive】笔试题 02 （列转行）

题目背景是分析学生课程成绩，相关数据如下：

1、说明

use myhive;
CREATE TABLE `course` (
  `id` int,
  `sid` int ,
  `course` string,
  `score` int 
) ;

// 插入数据
// 字段解释：id, 学号， 课程， 成绩
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);

2、需求

求：所有数学课程成绩 大于 语文课程成绩的学生的学号

1、使用case…when…将不同的课程名称转换成不同的列

create view tmp_course_view as
select sid, case course when "shuxue" then score else 0 end  as shuxue,  
case course when "yuwen" then score else 0 end  as yuwen from course;  

select * from tmp_course_view;

2、以sid分组合并取各成绩最大值

create view tmp_course_view1 as
select aa.sid, max(aa.shuxue) as shuxue, max(aa.yuwen) as yuwen from tmp_course_view aa group by sid;  

select * from tmp_course_view1;

3、比较结果

select * from tmp_course_view1 where shuxue > yuwen;

4、另外几种思路

可以用自连接来实现，确实更快速。

select c.sid from course c 
join course d 
on c.sid = d.sid 
and c.course ='shuxue' 
and d.course='yuwen' 
where c.score > d.score;

select sid
from (
select sid,
sum(case course when "shuxue" then score else 0 end) as math,
sum(case course when "yuwen" then score else 0 end) as chinese
from course group by sid
) as t1
where t1.math>t1.chinese;

select
sid
from course
group by sid
having sum(case when course='yuwen' then score
when course='shuxue' then 0-score
end) < 0;

结果都是：

+------+--+
| sid  |
+------+--+
| 1    |
| 2    |
+------+--+