Oracle lag()/lead() over()分析函数

最近在处理考勤资料时,遇到了取值判断的问题,在论坛上经过多次讨论,终于得到了解决方案.

论坛网址如下:

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

 

主要处理过程为:

一.创建数据表

-- 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 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table HR_TEST
  is '測試';
-- 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 64K
    minextents 1
    maxextents unlimited
  );
二.lag()/lead() over函数的运用,主要用来分析下一笔资料与本笔资料之间的差值

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)

三.进一步分析,以同一员工在同一天内打卡的资料为排序进行处理,判断该员工在同一天内如果存在10分钟内多次打卡的情况,就取最早打卡的那笔资料.

select IC_CARD_ID,MAC_ID,TIMES
from (select IC_CARD_ID,MAC_ID,TIMES,lag(TIMES,1,to_date('11111111','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);

如果遇到TIMES为VARCHAR2的情况,作一下日期转换即可,具体转换如下:

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('11111111','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 = '10025'

以上语法均通过测试.