hive-sql 最认真的整理一回（=）

18、查询按各科成绩进行排序，并显示排名学生信息:
– row_number() over()分组排序功能(mysql没有该方法)

select 
	a.sid,
	s.sname,
	tmp.cid,
	tmp.score,
	tmp.rank 
from sds a 
join (
	select 
		sid,
		cid,
		score,
		row_number() over(partition by cid order by score desc) rank 
	from sc
) tmp on a.sid=tmp.sid;

结果为：
a.sid a.sname tmp.cid tmp.score tmp.rank
03 孙风 01 80 1
01 赵雷 01 80 2
05 周梅 01 76 3
02 钱电 01 70 4
04 李云 01 50 5
06 吴兰 01 31 6
01 赵雷 02 90 1
07 郑竹 02 89 2
05 周梅 02 87 3
03 孙风 02 80 4
02 钱电 02 60 5
04 李云 02 30 6
01 赵雷 03 99 1
07 郑竹 03 98 2
02 钱电 03 80 3
03 孙风 03 80 4
06 吴兰 03 34 5
04 李云 03 20 6

19、查询学生的总成绩并进行排名:

select 
a.sid,
sum(a.score) total_score,
row_number() over(order by sum(a.score) desc) rank
from sc a
group by sid;

结果为：
Total MapReduce CPU Time Spent: 6 seconds 610 msec
OK
a.sid total_score rank
01 269 1
03 240 2
02 210 3
07 187 4
05 163 5
04 100 6
06 65 7

20、查询不同老师所教不同课程平均分从高到低显示:

select 
a.tid,
a.tname,
tmp1.cname,
tmp1.avg_score
from tc a
join (
select 
b.tid,
b.cname,
tmp.avg_score
from cs b
join (
select 
cid,
round(avg(score),2) avg_score
from sc
group by cid
order by avg_score desc
) tmp on b.cid=tmp.cid
) tmp1 on a.tid=tmp1.tid

结果为：
Total MapReduce CPU Time Spent: 9 seconds 680 msec
OK
a.tid a.tname tmp1.cname tmp1.avg_score
01 张三 数学 72.67
03 王五 英语 68.5
02 李四 语文 64.5

221、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:

select 
tmp.*
from(
select 
sc.*,
row_number() over(partition by cid order by score desc) rank
from sc) tmp
where tmp.rank>=2 and tmp.rank <=3 ;

方案二
(不建议这样写)

select * from (select tmp1.* from
    (select * from sc where cid='01' order by score desc limit 3)tmp1
    order by score asc limit 2) a
union all 

select * from (select tmp2.* from
    (select * from sc where cid='02' order by score desc limit 3)tmp2
    order by score asc limit 2) b
union all 
select * from (select tmp3.* from
    (select * from sc where cid='03' order by score desc limit 3)tmp3
    order by score asc limit 2)c;

结果为：
Total MapReduce CPU Time Spent: 5 seconds 0 msec
OK
tmp.sid tmp.cid tmp.score tmp.rank
01 01 80 2
05 01 76 3
07 02 89 2
05 02 87 3
07 03 98 2
02 03 80 3

22、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select
tmp.cid,
a.cname,
tmp.unpass_num,
tmp.pass_num,
tmp.unpass_per,
tmp.pass_per,
tmp.good_num,
tmp.good_per,
tmp.prefect_num,
tmp.prefect_per,
tmp.total_num
from cs a
join (
select
cid,
unpass_num,
concat(100*round((unpass_num/total_num),2),'%') unpass_per,
pass_num,
concat(100*round((pass_num/total_num),2),'%') pass_per,
good_num,
concat(100*round((good_num/total_num),2),'%') good_per,
prefect_num,
concat(100*round((prefect_num/total_num),2),'%') prefect_per,
total_num
from(
select
cid,
sum(case when score >=0 and score <60 then 1 else 0 end) unpass_num,
sum(case when score >=60 and score <70 then 1 else 0 end) pass_num,
sum(case when score >=70 and score <85 then 1 else 0 end) good_num,
sum(case when score >=85 and score <=100 then 1 else 0 end) prefect_num,
count(1) total_num
from sc
group by cid)a)tmp on a.cid=tmp.cid

结果为：
tmp.cid a.cname tmp.unpass_num tmp.pass_num tmp.unpass_per tmp.pass_per tmp.good_nut
mp.good_per tmp.prefect_num tmp.prefect_per tmp.total_num
01 语文 2 0 33.0% 0.0% 4 67.0% 0 0.0% 6
02 数学 1 1 17.0% 17.0% 1 17.0% 3 50.0% 6
03 英语 2 0 33.0% 0.0% 2 33.0% 2 33.0% 6

23、查询学生平均成绩及其名次

select 
	tmp.*,
	row_number()over(order by tmp.avgScore desc) Ranking 
from (
	select 
		a.sid,
		a.sname,
		round(avg(b.score),2) as avgScore 
	from sds a 
	join sc b on a.sid=b.sid 
	group by a.sid,a.sname
)tmp 
order by avgScore desc;

结果为：

Total MapReduce CPU Time Spent: 11 seconds 950 msec
OK
tmp.sid tmp.sname tmp.avgscore ranking
07 郑竹 93.5 1
01 赵雷 89.67 2
05 周梅 81.5 3
03 孙风 80.0 4
02 钱电 70.0 5
04 李云 33.33 6
06 吴兰 32.5 7