Oracle lag ()/Lead () over () Analysis Function

Recently, I encountered a problem in determining the value of attendance materials. After several discussions on the Forum, I finally got a solution.

The URL of the Forum is as follows:

Http://topic.csdn.net/u/20090415/15/4d934ccf-134d-4a3d-846e-cc71a1cebebd.html? 647544693

 

The main process is:

1. Create a data table

-- Create Table
Create Table hr_test
(
Ic_card_id varchar2 (10) Not null,
Ic_cad_no varchar2 (10 ),
Revise_date date,
Times varchar2 (50 ),
Nexttimes varchar2 (50 ),
Subvalues varchar2 (10)
)
Tablespace users
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64 K
Minextents 1
Maxextents Unlimited
);
-- Add comments to the table
Comment on table hr_test
Is 'invalid region ';
-- Create/recreate primary, unique and foreign key constraints
Alter table hr_test
Add constraint uk_emp_date unique (ic_card_id, times)
Using Index
Tablespace users
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 64 K
Minextents 1
Maxextents Unlimited
);
II. The use of the lag ()/Lead () over function is mainly used to analyze the difference between the next document and this document.

Select ic_card_id, mac_id, times, nexttimes, (NEXTTIMES-TIMES) * 1440 subvalues from (
Select ic_card_id, mac_id, times, lead (mac_id) over (partition by ic_card_id order by times) nextmac_id,
Lead (times) over (partition by ic_card_id order by times) nexttimes,
Lag (mac_id) over (partition by ic_card_id order by times) premac_id,
Lag (times) over (partition by ic_card_id order by times) pretimes
From hr_wolf)

III. further analysis is performed by sorting and processing the data punched out by the same employee within the same day to determine whether the employee has been punched out multiple times within 10 minutes within the same day, take the first piece of information to be punched out.

Select ic_card_id, mac_id, times
From (select ic_card_id, mac_id, times, lag (Times, 1, to_date ('20170101', 'yyyymmdd') over (order by ic_card_id, times) times1
From (select ic_card_id, mac_id, times from hr_wolf order by ic_card_id, times ))
Where TIMES-TIMES1> = 10/(24*60 );

If times is varchar2, convert the date as follows:

Select ic_card_id, mac_id, times
From (select ic_card_id, mac_id, to_date (times, 'yyyy-MM-DD hh24: MI: ss') as times, lag (to_date (times, 'yyyy-MM-DD hh24: MI: SS '), 1, to_date ('20140901', 'yyyymmdd') over (order by ic_card_id, times) times1
From (select ic_card_id, mac_id, times from hr_wolf order by ic_card_id, to_date (times, 'yyyy-MM-DD hh24: Mi ')))
Where TIMES-TIMES1> = 10/(24*60) and ic_card_id = '000000'

The above syntax has passed the test.