hive求不及格课程数大于等于2的学生的平均成绩及其排名
需求
给定一个表,包含三个字段;sid 学生ID,score 课程分数,cid 课程ID ,求不及格课程数大于2的学生的平均成绩及其成绩平均值后所在的排名
数据准备
create table scores_info_0208 ( sid int, score int, cid int ) row format delimited fields terminated by '\t' stored as orc tblproperties ("orc.compress" = "snappy"); insert into scores_info_0208 values (1, 90, 1), (1, 59, 2), (1, 48, 3), (2, 61, 1), (2, 39, 2), (2, 70, 3), (3, 87, 1), (3, 13, 2), (3, 63, 3), (4, 36, 1), (4, 9, 2), (4, 59, 3); select * from scores_info_0208;
解决思路
第一步:首先找出不及格成绩课程数大于等于2的学生,确定学生范围
select sid, sum(1) cnt from scores_info_0208 where score < 60 group by sid having cnt >= 2;
sid
|
cnt
|
4
|
3
|
1
|
2
|
第二步:计算所有学生课程的平均成绩
select *, cast(avg(score) over (partition by sid) as decimal(10, 2)) avg_score from scores_info_0208;
sid
|
score
|
cid
|
avg_score
|
1
|
90
|
1
|
65.67
|
1
|
59
|
2
|
65.67
|
1
|
48
|
3
|
65.67
|
2
|
61
|
1
|
56.67
|
2
|
39
|
2
|
56.67
|
2
|
70
|
3
|
56.67
|
3
|
87
|
1
|
54.33
|
3
|
13
|
2
|
54.33
|
3
|
63
|
3
|
54.33
|
4
|
36
|
1
|
34.67
|
4
|
9
|
2
|
34.67
|
4
|
59
|
3
|
34.67
|
第三步:在第二步的基础上按 avg_score 排名
select sid, avg_score, pm from (select sid, avg_score, dense_rank() over (order by avg_score) pm from (select *, cast(avg(score) over (partition by sid) as decimal(10, 2)) avg_score from scores_info_0208) t) y group by sid, avg_score, pm;
sid
|
avg_score
|
pm
|
1
|
65.67
|
4
|
3
|
54.33
|
2
|
2
|
56.67
|
3
|
4
|
34.67
|
1
|
第四步:将第三步结果和第一步结果进行关联即可
两种关联方式
1、第一步结果集作为主表
select t.sid, t1.avg_score, pm from (select sid, sum(1) cnt from scores_info_0208 where score < 60 group by sid having cnt >= 2) t left join (select sid, avg_score, pm from (select sid, avg_score, dense_rank() over (order by avg_score) pm from (select *, cast(avg(score) over (partition by sid) as decimal(10, 2)) avg_score from scores_info_0208) t) y group by sid, avg_score, pm) t1 on t.sid = t1.sid
sid
|
avg_score
|
pm
|
1
|
65.67
|
4
|
4
|
34.67
|
1
|
2、第三步结果集作主表 使用 left semi join
select t.sid, t.avg_score, pm from (select sid, avg_score, pm from (select sid, avg_score, dense_rank() over (order by avg_score) pm from (select *, cast(avg(score) over (partition by sid) as decimal(10, 2)) avg_score from scores_info_0208) t) y group by sid, avg_score, pm) t left semi join (select sid, sum(1) cnt from scores_info_0208 where score < 60 group by sid having cnt >= 2) t1 on t.sid = t1.sid
推荐使用 left semi join 进行计算,left semi join 比 in 效率更高,推荐使用