TOP N问题

废话少说

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="北航";