Hive实战之学生选课

 

---

数据（基表course）：

id course 
1,a 
1,b 
1,c 
1,e 
2,a 
2,c 
2,d 
2,f 
3,a 
3,b 
3,c 
3,e

需求：编写Hive的HQL语句来实现以下结果：表中的1表示选修，表中的0表示未选修。

id    a    b    c    d    e    f
1     1    1    1    0    1    0
2     1    0    1    1    0    1
3     1    1    1    0    1    0

实现需求的步骤：

　　使用case...when...将不同的课程名称转换成不同的列。

create  table student as select
id as id ,case course when a then 1 else 0 end as a,
case course when b then 1 else 0  end as b,
case course when c then 1 else 0  end as c,
case course when d then 1 else 0  end as d,
case course when e then 1 else 0  end as e,
case course when f then  1 else 0  end as f
from  group by id,course;