废话少说
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 create table exercise5_1 (year int, school string, grade int, name string, course string, score int) row format delimited fields terminated by ','; load data local inpath '/export/datas/exercise5_1.txt' into table exercise5_1; select * from exercise5_1; +-------------------+---------------------+--------------------+-------------------+---------------------+--------------------+--+ | exercise5_1.year | exercise5_1.school | exercise5_1.grade | exercise5_1.name | exercise5_1.course | exercise5_1.score | +-------------------+---------------------+--------------------+-------------------+---------------------+--------------------+--+ | 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 | +-------------------+---------------------+--------------------+-------------------+---------------------+--------------------+--+ desc exercise5_1; +-----------+------------+----------+--+ | col_name | data_type | comment | +-----------+------------+----------+--+ | year | int | | | school | string | | | grade | int | | | name | string | | | course | string | | | score | int | | +-----------+------------+----------+--+ --1、分组TopN，选出今年每个学校、每个年级、分数前三的科目 select e.school, e.grade, e.course, e.score, e.rn from (select school,grade,score,course, row_number() over(partition by school,grade order by score desc) as rn from exercise5_1) e where rn<=3; +-----------+----------+-----------+----------+-------+--+ | e.school | e.grade | e.course | e.score | e.rn | +-----------+----------+-----------+----------+-------+--+ | 北大 | 1 | 语文 | 97 | 1 | | 北大 | 1 | 语文 | 85 | 2 | | 北大 | 1 | 语文 | 52 | 3 | | 北理工 | 3 | 物理 | 81 | 1 | | 北科 | 4 | 化学 | 92 | 1 | | 北航 | 2 | 数学 | 77 | 1 | | 北航 | 2 | 数学 | 70 | 2 | | 北航 | 2 | 数学 | 49 | 3 | | 清华 | 0 | 英语 | 61 | 1 | | 清华 | 0 | 英语 | 59 | 2 | +-----------+----------+-----------+----------+-------+--+ select school, grade, course, score, sum(score) over (order by score range between 2 preceding and 2 following) sscore from exercise5_1 where year = "2017" and school="北航";