with tmp as(
select'1' id ,'aa' name ,'19' age from dual unionallselect'31' id ,'aa' name ,'31' age from dual unionallselect'2' id ,'bb' name ,'20' age from dual unionallselect'3' id ,'CC' name ,'21' age from dual
)
select a.*,
lag(age,1) over (orderby name, id asc) lag,
a.age - lag(age,1) over (orderby id asc) lag1
from tmp a;
填充: 关联
通过利用rownum做一次逆序排列,对null值用其之前不为0的数值填充
with a as (), b as () select * from a; 缓存表数据
sample
with test as (
select rownum rn, id, age from(
select'1' id , 19 age from dual unionallselect'2' id , null age from dual unionallselect'3' id , null age from dual unionallselect'4' id , 21 age from dual unionallselect'5' id , 0 age from dual )
),
test2 as (select * from test orderby rn desc)
select tm.id, tm.age,
( select t2.age from test2 t2
where t2.rn <= tm.rn and t2.age > 0and rownum = 1) agepad
from test tm;
平移: lag/lead over 函数oralce内置的高级函数,可以获取到当前查询结果集中每一行的前几行,相当于做了垂直方向的平移要注意的函数作用域是此次查询结果集,虽然提供了分组的功能,只是对当前结果集的分组lagover samplewith tmp as(select '1' id ,'aa' name ,'19' age from dual union allse...