1、场景举例.教育背景学生成绩分析
成绩的数据格式:时间,学校,年纪,姓名,科目,成绩
样例数据如下
2013,北大,1,裘容絮,语文,97
2013,北大,1,庆眠拔,语文,52
2013,北大,1,乌洒筹,语文,85
2012,清华,0,钦尧,英语,61
2015,北理工,3,冼殿,物理,81
2016,北科,4,况飘索,化学,92
2014,北航,2,孔须,数学,70
2012,清华,0,王脊,英语,59
2014,北航,2,方部盾,数学,49
2014,北航,2,东门雹,数学,77
问题:
1. 情景题:分组TOPN
# 1.分组TOPN选出 今年每个学校,每个年级,分数前三的科目.
hive -e "
set mapreduce.job.queuename=low;
select t.*
from
(
select
school,
class,
subjects,
score,
row_number() over (partition by school,class,subjects order by score desc) rank_code
from spark_test_wx where partition_id = "2017"
) t
where t.rank_code <= 3;
"