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,然后在算开窗函数,也就是会影响开窗函数