首先数据格式
select * from emp
SQL:
select *
,first_value( sal)over(partition by deptno order by sal) as a
,last_value(sal)over(partition by deptno order by sal) as b
,last_value(sal)over(partition by deptno order by sal rows between unbounded preceding and unbounded following) as c
from emp a
order by deptno,sal
数据:
从结果看出来 a first_value() 没什么问题 b c 的差异 体现在 rows between
显然c 才是我们想要的结果。
为何会出现b 是因为 默认的order by 列 等同于 order by 列 range between unbounded preceding and current row (计算的是第一行到当前行的 lastvalue) ,所以使用last_value
的时候需要注意行的条件。