oracle求同比,环比函数(LAG与LEAD)的详解

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