oracle中lead和lag函數 （轉載）

標籤：

這兩個函數，是位移量函數，其用途是：可以查出同一欄位下一個值或上一個值。

lead(col_name,num,flag)col_name是列名；num是取向下第幾個值；flag是一個標誌，也就是如果向下第幾個值是空值的話就取flag；例如lead(login_time,1,null)這個是向下取一個值，如果這個值為空白則按空算，當然也可以用其他值替換。lag(col_name,num,flag)和lead類似，col_name是列名；num是取向上第幾個值；flag是一個標誌，也就是如果向上第幾個值是空值的話就取flag；例如lag(login_time,1,null)這個是向上取一個值，如果這個值為空白則按空算，當然也可以用其他值替換。

舉個例子：有一個表tmp_test(u_id,login_time)查一下這個表中連續7天都有登入機器的人是誰？造下資料：create table tmp_test(u_id number,login_time date);

insert into tmp_testselect 1 rn,sysdate + rownum as login_timefrom dualconnect by level <=8unionselect 2 rn,sysdate + rownum as login_timefrom dualconnect by level <=3unionselect 3 rn,sysdate + rownum as login_timefrom dualconnect by level <=2unionselect 2 rn,sysdate + rownum+4 as login_timefrom dualconnect by level <=5;commit;

然後造幾條重複資料：insert into tmp_testselect 1 rn,sysdate + rownum as login_timefrom dualconnect by level <=3;

查下資料：select * from tmp_test; U_ID LOGIN_TIME---------- ----------- 1 2012/3/8 6:33:24 1 2012/3/9 6:33:24 1 2012/3/10 6:33:24 1 2012/3/11 6:33:24 1 2012/3/12 6:33:24 1 2012/3/13 6:33:24 1 2012/3/14 6:33:24 1 2012/3/15 6:33:24 2 2012/3/8 6:33:24 2 2012/3/9 6:33:24 2 2012/3/10 6:33:24 2 2012/3/12 6:33:24 2 2012/3/13 6:33:24 2 2012/3/14 6:33:24 2 2012/3/15 6:33:24 2 2012/3/16 6:33:24 3 2012/3/8 6:33:24 3 2012/3/9 6:33:24 1 2012/3/8 6:37:35 1 2012/3/9 6:37:35 1 2012/3/10 6:37:35

從上面資料看出其實只有u_id=1滿足條件，那麼怎麼用sql實現呢？SQL> select distinct u_id 2 from (select u_id, 3 login_time last_login_time, 4 lead(login_time, 6) over(partition by u_id order by u_id, login_time) next_login_time 5 from (select distinct u_id, trunc(login_time) login_time 6 from tmp_test)) 7 where next_login_time - last_login_time = 6; U_ID---------- 1

ok，就是這個結果。其實用lag也可以實現相同結果，寫法如下：

select distinct u_id from (select u_id, login_time last_login_time, lag(login_time, 6) over(partition by u_id order by u_id, login_time) next_login_time from (select distinct u_id, trunc(login_time) login_time from tmp_test)) where last_login_time - next_login_time = 6;

   

oracle中lead和lag函數 （轉載）