Statistics of Hive students' elective courses
Write the HQL statement of Hive to achieve the following results: 1 in the table indicates optional, and 0 in the table indicates that it is not optional.
Id a b c d e f
1 1 1 0 1 0
2 1 0 1 1 0 1
3 1 1 1 0 1 0
It indicates that some students whose id is 1pr 2je 3 have taken a few of the classes in the course aPercience brecrium crec rep e pr.
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 ase
, 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
Enable strict mode: hive.mapred.mode = strict / / Deprecated
Hive.strict.checks.large.query = true
This setting disables: 1. Do not specify an orderby for paging
two。 Query the partition table without specifying a partition
3. It has nothing to do with the amount of data, it's just a query mode.
Hive.strict.checks.type.safety = true
Strict type safety, this property does not allow the following operations: 1. Comparison between bigint and string
2. Comparison between bigint and double
Hive.strict.checks.cartesian.product = true
This property does not allow Cartesian product operations.
