Lead and LAG functions in Oracle (reprint)

These two functions are offset functions that can be used to detect the next or previous value in the same field.

The lead (Col_name,num,flag) col_name is the column name; num is the first value of the orientation; flag is a flag, that is, if the value below is NULL, the flag is taken; For example, the lead (Login_time,1,null) This is to take a value down, and if the value is null then the empty calculation, of course, can also be replaced with other values. Lag (Col_name,num,flag) is similar to the lead, Col_name is the column name; num is the first value of the orientation; flag is a flag, that is, if the value above is null, take flag; for example, lag (login_time,1,null This is to take a value up, and if the value is null then the empty calculation, of course, can also be replaced with other values.

For example: There is a table tmp_test (U_id,login_time) who is the person who has logged in to the machine for 7 consecutive days in this table? Created Data: 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 + R Ownum as Login_timefrom Dualconnect by Level <=3unionselect 3 Rn,sysdate + rownum as Login_timefrom dualconnect by leve L <=2unionselect 2 rn,sysdate + rownum+4 as Login_timefrom Dualconnect by level <=5;commit;

Then make a few duplicates: INSERT INTO Tmp_testselect 1 rn,sysdate + rownum as Login_timefrom Dualconnect by level <=3;

Check the data: 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/1 0 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

From the above data see actually only u_id=1 satisfies the condition, then how to implement with SQL? Sql> SELECT DISTINCT u_id 2 from (select u_id, 3 login_time last_login_time, 4 leads (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, that's the result. In fact, with lag can also achieve the same results, the following wording:

SELECT DISTINCT u_id from (select u_id, Login_time last_login_time, lags (Login_time, 6) over (partition by u_id ORDER by u_i D, Login_time) Next_login_time from (select distinct u_id, trunc (login_time) login_time from Tmp_test)) where last_login_t Ime-next_login_time = 6;

Lead and LAG functions in Oracle (reprint)