oracle lead分析函数

Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

 

以下是Lead的范例：

    找出上下两条记录中，borrow_nid,user_id,account,addip相同、且addtime之差少于5秒的记录

 

    for r1 in (
      select id,borrow_nid,user_id,account,addip,addtime from 表名1 a4  
      where exists ( 
        select 1 from ( 
          select a2.id,a2.borrow_nid,a2.user_id,a2.account,a2.addip,a2.addtime,id2,(a2.addtime2-a2.addtime) as addtime2 from (
              select a1.id,a1.borrow_nid,a1.user_id,a1.account,a1.addip,a1.addtime,
              lead(a1.id,1,0) over(order by rownum) as id2, 
              lead(a1.borrow_nid,1,0) over(order by rownum) as borrow_nid2, 
              lead(a1.user_id,1,0) over(order by rownum) as user_id2, 
              lead(a1.account,1,0) over(order by rownum) as account2, 
              lead(a1.addip,1,0) over(order by rownum) as addip2, 
              lead(a1.addtime,1,0) over(order by rownum) as addtime2 
              from (
                  -- data start 
                  select t1.id,t1.borrow_nid,t1.user_id,t1.account,t1.addip,t1.addtime 
                  from 表名1 t1
                  where exists (
                      select 1 from (
                          select borrow_nid,user_id,account,addip,count(id) as count from 表名1
                          where addtime>=stime and addtime<etime
                          group by borrow_nid,user_id,account,addip
                          having count(id)>1
                      ) t2 where t2.borrow_nid=t1.borrow_nid and t2.user_id=t1.user_id and t2.account=t1.account
                  ) and addtime>=stime and addtime<etime
                  order by t1.borrow_nid,t1.user_id,t1.account,t1.addip,t1.addtime
                  -- data end
              ) a1
          ) a2 where borrow_nid=borrow_nid2 and user_id=user_id2 and account=account2 and addip=addip2 and (addtime2-addtime)<5000
      ) a3 where a4.id=a3.id or a4.id=a3.id2  
    ) order by a4.borrow_nid,a4.user_id,a4.account,a4.addip,a4.addtime
  ) loop
    v_data_id := r1.id;
    v_borrow_nid := r1.borrow_nid;
    v_user_id := r1.user_id;
    v_account := r1.account;
    v_addip := r1.addip;
    v_addtime := r1.addtime;
    
    insert into 表名2(id,data_type,data_id,borrow_nid,user_id,account,addip,addtime) 
    values (seq_repeat_datas_alarm.nextval, 'tender', v_data_id, v_borrow_nid, v_user_id, v_account, v_addip, v_addtime);
  end loop;