Oracle数据库over()函数

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会对结果集按照指定字段分层排列，结果集不会缩减。例如：按部门分区。

搭配不同函数的使用效果

1. 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开始，遇到相同数据，排名相同，排名连续
   

2. 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字段的值，最后一行为空
   

3. sum()

   与over连用时，order by可以省略，省略partition by将结果集默认为一个分区。此处sum不是对整个分区进行求和，而是根据ordey by 后面的字段分区排序之后，求出分区中小于等于自身所在分层等级的和，即连续求和。

   sum(col3) over(partirion by col1 order by col2)
   --按照col1分区，按照col2排序，在分区内求出小于等于自身所在分层等级的和，如遇到等级相同的，求和不会增加