oracle 分析函数和性能

分析函数语法

sum（）over (partition by aa order by bb rows between unbounded preceding and unboundedfollowing )

partition by 为分组

order by 和order by 之后 为开窗函数 控制数据范围

控制数据范围的语句

unbounded preceding 第一行，可以理解为当前行的最上面一行，就是第一行

1 preceding 当前行的上一行

unbouned following 最后一行，可以理解为当前行的最后一行，就是最后一行

1 following     当前行的下一行

current row  当前行

测试

select name,
       shangjin,
       zhenying,
       sum(shangjin) over(partition by zhenying order by name  ROWS between unbounded  PRECEDING AND unbounded   FOLLOWING)
  from xzhdx;

求每个分组中第一行到最后一行的和，就是该分组的和。

 

select name,
       shangjin,
       zhenying,
       sum(shangjin) over(partition by zhenying order by name  ROWS between 1  PRECEDING AND unbounded   FOLLOWING)
  from xzhdx;

求每个分组当前行上一行到分组最后一行的和

 

select name,
       shangjin,
       zhenying,
       sum(shangjin) over(partition by zhenying order by name  ROWS between 1  PRECEDING AND 1   FOLLOWING)
  from xzhdx;

求每个分组当中，当前行的上一行（ 1  PRECEDING）到当前行的下一行（1   FOLLOWING）的和

 

select name,
       shangjin,
       zhenying,
       sum(shangjin) over(partition by zhenying order by name --省略后面的ROWS between )
  from xzhdx;

表示分组求每个分组的第一行到当前行的和，当order by 之后不跟ROWS between 就是默认第一行到current row 相当于 order by name ROWS between  unbouned following  and current row。

select name,
       shangjin,
       zhenying,
       sum(shangjin) over(partition by zhenying  )--省略后面的order by name )
  from xzhdx;

相当于求每组的第一行和最后一行的和

 

select name,
       shangjin,
       zhenying,
       sum(shangjin) over(--省略partition by order by name  ROWS between 1  PRECEDING AND 1   FOLLOWING)
  from xzhdx;

表示不分组 求所有数据的当前行上一行 到 当前行下一行的 和

 

select name,
       shangjin,
       zhenying,
       sum(shangjin) over( --省略partition by order by  )
  from xzhdx;

就是求所有数据的和

 总结

sum（）over（order by rows between）

partition by 忽略的时候，表示不分组，求的是order by 子句范围的行的汇总

 sum（）over（partition by  order by  ）

rows between 忽略的时候表示第一行到当前行的汇总

sum（）over（partition by  ）

order by 忽略的时候，就表示组的第一行到最后一行的汇总

sum（）over （）

partition by，order by 忽略的时候一起忽略的时候表示求数据的汇总。

其他分析行数

lag（1,2,3）over（partition by  order by rows between）

参数1代表 列

参数2代表该列的当前行向上找几行

参数3默认值，如果没有默认为空

lead（1,2,3）over（partition by  order by rows between）

 参数1代表 列

 参数2代表该列的当前行向下找几行

参数3默认值，如果没有默认为空

rank () over()

排序1,2,2,4

dense_rank()over()

排序1,2,2,3

row_number()over()

排序1,2,3,4

sql语句order by 和over(order by )先后顺序

如果sql语句有order by,开窗函数中也有order by ,先是计算开窗函数,最后才计算sql中的排序.,sql中的order by 不会影响开窗函数结果,

如果sql语句有order by,开窗函数中没有order by ,那么先是计算sql中的order by,然后在算开窗函数,也就是会影响开窗函数