HIVE 50 练习

– 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:

select a.s_id,tmp1.s_score as chinese,tmp2.s_score as math,tmp3.s_score as english,
    round(avg (a.s_score),2) as avgScore
from score a
left join (select s_id,s_score  from score s1 where  c_id='01')tmp1 on  tmp1.s_id=a.s_id
left join (select s_id,s_score  from score s2 where  c_id='02')tmp2 on  tmp2.s_id=a.s_id
left join (select s_id,s_score  from score s3 where  c_id='03')tmp3 on  tmp3.s_id=a.s_id
group by a.s_id,tmp1.s_score,tmp2.s_score,tmp3.s_score order by avgScore desc;

增加成绩表中没有成绩的同学

select 
temp2.s_id ,
sum(case temp2.c_id when '01' then temp2.sscore else 0 end ) as c01 ,
sum(case temp2.c_id when '02' then temp2.sscore else 0 end ) as c02 ,
sum(case temp2.c_id when '03' then temp2.sscore else 0 end ) as c03 ,
round(AVG(temp2.sscore))  as avgScore
from 
( select 
temp.s_id ,
temp.c_id ,
nvl(sco.s_score,0) as sscore
from 
( select 
stu.s_id  ,
cou.c_id  
from student stu join course cou ) temp left join score sco on temp.s_id = sco.s_id  and temp.c_id = sco.c_id ) temp2
group by temp2.s_id  order by avgScore desc 

Map 处理

select 
temp3.s_id ,
temp3.resmap['01'] as c01 ,
temp3.resmap['02'] as c02 ,
temp3.resmap['03'] as c03 ,
temp3.avgScore 
from 
 ( select 
temp2.s_id ,
str_to_map(concat_ws(',',collect_set(concat(temp2.c_id,'=',temp2.sscore))),',','=') as resmap ,
round(AVG(temp2.sscore))  as avgScore
from 
( select 
temp.s_id ,
temp.c_id ,
nvl(sco.s_score,0) as sscore
from 
( select 
stu.s_id  ,
cou.c_id  
from student stu join course cou ) temp left join score sco on temp.s_id = sco.s_id  and temp.c_id = sco.c_id ) temp2
group by temp2.s_id   ) temp3 order by temp3.avgScore desc

Map 简化

select 
temp3.s_id  ,
temp3.c01  ,
temp3.c02  ,
temp3.c03  ,
round((temp3.c01 + temp3.c02 + temp3.c03) / 3 ,2) as avgRes

from 
(select 
temp2.s_id ,
cast (nvl(temp2.resmap['01'],0) as int) as c01 ,
cast (nvl(temp2.resmap['02'],0) as int)  as c02 ,
cast (nvl(temp2.resmap['03'],0) as int)  as c03 

from 
( select 
stu.s_id ,
temp.resmap

from 
student stu
left join 
(select 
s_id ,
str_to_map(concat_ws(',',collect_set(concat(c_id,'=',s_score))),',','=') as resmap 
from score  group by  s_id) temp  on stu.s_id  = temp.s_id ) temp2 ) temp3 order by avgRes desc