lag() 偏移

with t1 as(
select d.p-d.dlrt cha,d.* from
(select c.*,
rank() over(partition by c.cos_id order by c.dlrq desc) as rank2
from (select b.cos_id,
b.dlrq,
lag(b.dlrq,1,null) OVER (ORDER BY b.cos_id,b.dlrq) as p

from test_1023 b) c)d order by d.cos_id,d.dlrq),
t2 as
(select * from t1 where t1.cha<>-1 or t1.cha is null),
t3 as
(select c.* ,rank() over(partition by c.cos_id order by c.dlrq desc) as rank3 from t2 c),
t4 as
(select b.*,lag(b.tank2,1,null) over(order by b.cos_id b.dlrq desc) as p1 from t3 b )
select
t4.cos_id ,
t4.dlrq,
case when t4.rank3=1 then rank2
when t4.t=rank3<>1 then ran2-p1
end num
from t4;