Oracle处理当前行与其之前行

平移： lag/lead over 函数

1. oralce内置的高级函数，可以获取到当前查询结果集中每一行的前几行，相当于做了垂直方向的平移
2. 要注意的函数作用域是此次查询结果集，虽然提供了分组的功能，只是对当前结果集的分组
3. lagover
4. sample

with tmp as(
select '1' id ,'aa' name ,'19' age from dual union all
select '31' id ,'aa' name ,'31' age from dual union all
select '2' id ,'bb' name ,'20' age from dual union all
select '3' id ,'CC' name ,'21' age from dual
)
select a.*,
       lag(age,1) over (order by name, id asc) lag,
      a.age - lag(age,1) over (order by id asc) lag1
from tmp a;

填充： 关联

1. 通过利用rownum做一次逆序排列，对null值用其之前不为0的数值填充
2. with a as (), b as () select * from a; 缓存表数据
3. sample

with test as (
select rownum rn, id, age from(
select '1' id , 19 age from dual union all
select '2' id , null age from dual union all
select '3' id , null age from dual union all
select '4' id , 21 age from dual union all
select '5' id , 0 age from dual  )
),
test2 as (select * from test order by rn desc)
select tm.id, tm.age, 
  ( select t2.age from test2 t2 
  where t2.rn <= tm.rn and t2.age > 0 and rownum = 1) agepad
      from test tm;