基本语法:
函数名称([参数, ...])over(
partition by 子句 字段, ...
[order by 子句 字段, ...[asc|desc][nulls first|nulls last]]
[windowing 子句]);
函数名称:类似于统计函数(count(),sun()等),还有更多函数支持
over子句:为分析函数指明一个查询结果集,此语句在select子句之中使用
partition by 子句:将一个简单的结果集分为N组(分区),按不同的组对数据进行统计
order by 子句:明确指定数据在每组内的排序顺序,分析函数的结果与排序顺序有关
nulls first|nulls last:表示返回数据行中包含null值是出现在排序序列前还是尾
windowing 子句:给出定义变化的固定数据窗口方法,分析函数对此数据进行操作
简单实例:
select deptno,ename,sal,sum(sal) over(partition by deptno order by sal) sum from emp
windowing 子句使用:
分窗子句主要是用于定义一个变化或者固定的数据窗口方法,主要用于定义分析函数在操作行的集合,分窗子句有两种实现方式:
1,值域窗(range window),逻辑偏移。当前分区之中当前行的前N行到当前行的记录集
2,行窗(rows window),物理偏移。以排序的结果顺序计算偏移当前行的起始行记录集
若要指定range或者rows的偏移量,可采用下列排序列:
range|rows 数字 preceding
range|rows between unbounded preceding and current row
range|rows between current row and unbounded following
preceding:设置一个偏移量,这个偏移量可以是用户设置的数字,或者是其他标记
between...and:设置偏移量的操作范围
unbounded preceding:不限制偏移量大小
current row:表示当前行
following:如果不写此语句表示使用上N行与当前行指定数据比较,如果加上,表示当前行与下N行数据比较
简单实例:
range使用:
select deptno,ename,sal,sum(sal) over(partition by deptno order by sal range 300 preceding) sum from emp
following(向下匹配):
select deptno,ename,sal,sum(sal) over(partition by deptno order by sal range between 0 preceding and 300 following) sum from emp
current row(表示当前行):
select deptno,ename,sal,sum(sal) over(partition by deptno order by sal range between 0 preceding and current row) sum from emp
unbounded preceding:
select deptno,ename,sal,sum(sal) over(partition by deptno order by sal range between unbounded preceding and current row) sum from emp
rows使用:
select deptno,ename,sal,sum(sal) over(partition by deptno order by sal rows 2 preceding) sum from emp