案例
现在有一张案例表
selelct * form aa;
所有行相加
select sid ,sum(tid)over(partition by sid ) as count from aa;
结果如下:
组内数据相加
select sid ,sum(tid)over(partition by sid order by tid) as count from aa;
起始行到当前行
select sid ,sum(tid)over(partition by sid order by tid) as count from aa;--按sid分组,组内数据tid累加
#与下面这个等价
select sid ,sum(tid)over(partition by sid order by tid rows between unbounded preceding and current row) as count from aa;--按sid分组,组内数据tid累加,就是起始行到当前行
结果如下:
当前行的上一行到当前行
select sid ,sum(tid)over(partition by sid order by tid rows between 1 preceding and current row) as count from aa;--当前行的上一行到当前行 相当于每两行聚合
结果如下:
当前行的上一行到当前行的下一行
select sid ,sum(tid)over(partition by sid order by tid rows between 1 preceding and 1 following) as count from aa;--当前行的上一行到当前行的下一行
结果如下:
当前行和后面所有行
select sid ,sum(tid)over(partition by sid order by tid rows between current row and unbounded following) as count from aa;--当前行及后面所有行