sql 位置偏移函数

      位置偏移函数用于返回分组内相对位置的成员，主要有FIRST_VALUE、LAST_VALUE、LEAD、LAG，后面跟 over (partition by 分组列名 order by 排序列名）子句。

    FIRST_VALUE( )：用于返回分组内首项成员记录

    LAST_VALUE( )：用于返回分组内尾项成员记录

    LEAD(N)：用于返回分组内成员当前行的后N行记录

    LAG( M)：用于返回分组内成员当前行的前M行记录

 

select t.employee_id, t.first_name,t2.department_name,t.salary,
first_value(t.salary) over ( partition by t.department_id order by t.salary rows between unbounded preceding and unbounded following) as first_value,
last_value(t.salary) over ( partition by t.department_id order by t.salary rows between unbounded preceding and unbounded following) as last_value,
lead(t.salary,1) over ( partition by t.department_id order by t.salary ) as lead_value,
lag(t.salary,1) over ( partition by t.department_id order by t.salary ) as lag_value
from hr.employees t,hr.departments t2 where t.department_id=t2.department_id