计算出账的上一个入账时间差

--计算各个商户每一笔出款距离其上一笔入款的时间间隔（分钟）
with tablea as (
select 1 id,date'2018-1-1'+1 dt,'0' off from dual union all --0代表入账 1 代表出账
select 1 id,date'2018-1-1'+0.5 dt,'0' off from dual union all
select 1 id,date'2018-1-1'+0.75 dt,'0' off from dual union all
select 1 id,date'2018-1-1' dt,'0' off from dual)
,tableb as (
select 1 id,date'2018-1-1'+0.85 dt,'1' off from dual union all
select 1 id,date'2018-1-1'+0.55 dt,'1' off from dual union all
select 1 id,date'2018-1-1'+0.15 dt,'1' off from dual
)
select /*+ use_nl(a2,a1)*/
       a2.id
       ,a1.dt_start_date --上一次入账时间
       -a2.dt --出账时间
  from (select t.id,t.dt dt_start_date ,nvl(lead(dt) over(partition by t.id order by t.dt),sysdate+30) dt_end_date
         from tablea  t) a1 --入账部分数据
        ,tableb a2  --出账部分数据
where a1.id = a2.id
  and a2.dt>= a1.dt_start_date
  and a2.dt < a1.dt_end_date