OVER (PARTITION BY) 分组取前n条数据

OVER (PARTITION BY):

row_number() over (partition by)：取前n条rank() over (partition by)：按名次顺序依次增加下的排序，前n名dense_rank() over (partition by)：按数量顺序依次增加下的排序，前n名

---

数据表： 有一个名为GRADE 的表格记录学生id, 学生多次考试成绩score, 考试日期dt等数据, 取每个id对应的的最高三次成绩

1. row_number() over (partition by)， rank() over (partition by)

SELECT * FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY SCORE DESC) ROW_NUMBER FROM GRADE) TB WHERE ROW_NUMBER < 3

查询结果：

iddtgrade12019-05-0510012019-06-059822019-06-059522019-05-0595

id2的成绩排名:

dtscorerow_number2019-06-059512019-05-059522019-07-05943

2. rank() over (partition by)

SELECT * FROM (SELECT *,rank() OVER(PARTITION BY ID ORDER BY SCORE DESC) RANK FROM GRADE) TB WHERE RANK < 3

查询结果：

iddtgrade12019-05-0510012019-06-059822019-06-059522019-05-0595

id2的成绩排名:

dtscorerank2019-06-059512019-05-059512019-07-05943

3. dense_rank() over (partition by)

SELECT * FROM (SELECT *,dense_rank() OVER(PARTITION BY ID ORDER BY SCORE DESC) DENSE_RANK FROM GRADE) TB WHERE DENSE_RANK < 3

查询结果：

iddtgrade12019-05-0510012019-06-059822019-06-059522019-05-059522019-07-0594

id2的成绩排名:

dtscoredense_rank2019-06-059512019-05-059512019-07-05942