Hive學生選課情況統計

編寫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
表示有id為1,2,3的學生選修了課程a,b,c,d,e,f中其中幾門
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

create table t_stu_course
(
id int,
course string
) row format delimited fields terminated by ",";
load data local inpath "/root/t_stu_course.txt" into table t_stu_course;

select tmp.id
,max(tmp.a) as a
,max(tmp.b) as b
,max(tmp.c) as c
,max(tmp.d) as d
,max(tmp.e) as e
,max(tmp.f) as f
from (
select id
,case when course="a" then 1 else 0 end as a
,case when course="b" then 1 else 0 end as b
,case when course="c" then 1 else 0 end as c
,case when course="d" then 1 else 0 end as d
,case when course="e" then 1 else 0 end as e
,case when course="f" then 1 else 0 end as f
from t_stu_course
) tmp
group by tmp.id;

select collect_set(course) as courses from t_stu_course;

set hive.strict.checks.cartesian.product=false;

select t1.id as id,t1.course as id_courses,t2.course courses
from
( select id as id,collect_set(course) as course from t_stu_course group by id ) t1
join
(select collect_set(course) as course from t_stu_course) t2;

啟用嚴格模式：hive.mapred.mode = strict // Deprecated
hive.strict.checks.large.query = true
該設置會禁用：1. 不指定分頁的orderby
　　　　　　 2. 對分區表不指定分區進行查詢
　　　　　　 3. 和數據量無關，只是一個查詢模式
hive.strict.checks.type.safety = true
嚴格類型安全，該屬性不允許以下操作：1. bigint和string之間的比較
　　　　　　　　　　　　　　　　　　2. bigint和double之間的比較
hive.strict.checks.cartesian.product = true
該屬性不允許笛卡爾積操作