PARTITION BY 分区函数使用实例

1、PARTITION BY 分区函数

PARTITION BY 分区函数  可以返回一个组别中多条记录

GROUP BY      分组函数   一般只有一条反映统计值的结果

 

2. 使用语法

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

 

3.使用案例

求每个班级取成绩最高的一名同学的id，分数，班级

 

 

SELECT ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY degree desc), id,class_id,degree
     FROM myTable
where RowNum = 1;