Oracle数据库over()函数
基本语法
over(partition by col1 order by col2)
--根据col1分区,然后按照col2排序
该函数不能单独使用,需要与row_number()
、rank()
、dense_rank()
、lag()
、lead()
和sum()
配合使用。
partition by
和group by
的区别:
group by
会对结果按照指定字段进行聚合,结果集会缩减。例如:统计部门人数,总工资等。
partition by
会对结果集按照指定字段分层排列,结果集不会缩减。例如:按部门分区。
搭配不同函数的使用效果
-
row_number()
、rank()
、dense_rank()
与
over
连用时,order by
不能省略,省略partition by
将结果集默认为一个分区。row_number() over(partition by col1 order by col2) --按照col1分区,按照col2排序,排序从1开始,遇到相同数据,排名照样增加
rank() over(partirion by col1 order by col2) --按照col1分区,按照col2排序,排序从1开始,遇到相同数据,排名相同,排名可能不连续
dense_rank() over(partirion by col1 order by col2) --按照col1分区,按照col2排序,排序从1开始,遇到相同数据,排名相同,排名连续
-
lag()
、lead()
与
over
连用时,order by
不能省略,省略partition by
将结果集默认为一个分区。lag(col3) over(partirion by col1 order by col2) --按照col1分区,按照col2排序,在分区内获取前一行数据的col3字段的值,第一行为空
lead(col3) over(partirion by col1 order by col2) --按照col1分区,按照col2排序,在分区内获取后一行数据的col3字段的值,最后一行为空
-
sum()
与
over
连用时,order by
可以省略,省略partition by
将结果集默认为一个分区。此处sum不是对整个分区进行求和,而是根据ordey by 后面的字段分区排序之后,求出分区中小于等于自身所在分层等级的和,即连续求和。sum(col3) over(partirion by col1 order by col2) --按照col1分区,按照col2排序,在分区内求出小于等于自身所在分层等级的和,如遇到等级相同的,求和不会增加