最近在处理考勤资料时,遇到了取值判断的问题,在论坛上经过多次讨论,终于得到了解决方案.
论坛网址如下:
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'
以上语法均通过测试.