oracle中rows和range的理解

select deptno,empno,ename,sal,
last_value(sal) over(partition by deptno order by sal ROWS BETWEEN unbounded preceding AND current row ) max_sal,
last_value(sal) over(partition by deptno order by sal  ) max_sal2,
FIRST_VALUE(sal) over(partition by deptno order by sal ROWS BETWEEN unbounded preceding AND current row ) mix_sal,
FIRST_VALUE(sal) over(partition by deptno order by sal ) mix_sal2
from emp

rows 其实是物理上的概念     range 是逻辑上的概念

 

select deptno,empno,ename,sal,
last_value(sal) over(partition by deptno order by sal rows between current row and 2 following) max_sal,
FIRST_VALUE(sal) over(partition by deptno order by sal rows between current row and 2 following ) mix_sal
from emp

上面 rows   当前行和下面两行  的意思

测试一个range  我来解释一下range  后面一般是数字  

select deptno,empno,ename,sal,
last_value(sal) over(partition by deptno order by sal range between current row and 500 following) max_sal,
FIRST_VALUE(sal) over(partition by deptno order by sal range  between current row and 500 following ) mix_sal
from emp

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/52hadoop/p/3364301.html