lag和lead函数可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现，不过使用lag和lead有更高的效率。
复制代码 代码如下:
create table salarybymonth
(
employeeno varchar2(20),
yearmonth varchar2(6),
salary number
) ;
insert into salarybymonth (employeeno, yearmonth, salary)
values (1, ‘200805’, 500);
insert into salarybymonth (employeeno, yearmonth, salary)
values (1, ‘200802’, 150);
insert into salarybymonth (employeeno, yearmonth, salary)
values (1, ‘200803’, 200);
insert into salarybymonth (employeeno, yearmonth, salary)
values (1, ‘200804’, 300);
insert into salarybymonth (employeeno, yearmonth, salary)
values (1, ‘200708’, 100);
commit;
select employeeno
,yearmonth
,salary
,min(salary) keep(dense_rank first order by yearmonth) over(partition by employeeno) first_salary — 基比分析 salary/first_salary
,lag(salary, 1, 0) over(partition by employeeno order by yearmonth) as prev_sal — 环比分析，与上个月份进行比较
,lag(salary, 12, 0) over(partition by employeeno order by yearmonth) as prev_12_sal — 同比分析，与上个年度相同月份进行比较
,sum(salary) over(partition by employeeno, substr(yearmonth, 1, 4) order by yearmonth range unbounded preceding) lj –累计值
from salarybymonth
order by employeeno
,yearmonth