SQL 考勤打卡问题

1: 业务场景

员工考勤打卡，分早，晚打卡，早上8:00到9:00 为早上打卡时间，这个时间范围内第一次打卡视为有效打卡，本时间段内其它打卡视为重复打卡;

相同情况，晚上下班打卡时间范围为17:00到18:00,这个时间范围内的第一次打开视为有效打卡，本时间内的其它打卡视为重复打卡；

其余时间范围内的打卡视为无效打卡

2：数据准备

Create table T_ClockIn (
UserId int,
CheckIn date
);Insert into T_ClockIn VALUES (1,'2018/12/1 8:25');
Insert into T_ClockIn VALUES (1,'2018/12/1 8:26');
Insert into T_ClockIn VALUES (1,'2018/12/1 17:02');
Insert into T_ClockIn VALUES (1,'2018/12/2 8:27');
Insert into T_ClockIn VALUES (1,'2018/12/2 8:37');
Insert into T_ClockIn VALUES (1,'2018/12/2 17:27');
Insert into T_ClockIn VALUES (1,'2018/12/2 18:27');
Insert into T_ClockIn VALUES (2,'2018/12/1 8:26');
Insert into T_ClockIn VALUES (2,'2018/12/1 17:03');
Insert into T_ClockIn VALUES (2,'2018/12/1 17:29');
Insert into T_ClockIn VALUES (2,'2018/12/1 18:01');
Insert into T_ClockIn VALUES (2,'2018/12/2 8:01');
Insert into T_ClockIn VALUES (2,'2018/12/2 9:27');
Insert into T_ClockIn VALUES (2,'2018/12/2 17:59');
Insert into T_ClockIn VALUES (2,'2018/12/2 18:27');

数据如下

3：过程分析

第一步：先将数据利用case when 根据打卡时间分为

1：早打卡 2：晚打卡 0:其余时间打卡三类

SELECT USERID,checkin,
CASE 
WHEN to_char(checkin,'HH24:MI:SS') BETWEEN '08:00:00' AND '09:00:00' THEN '1' 
WHEN to_char(checkin,'HH24:MI:SS') BETWEEN '16:00:00' AND '18:00:00' THEN '2' 
ELSE '0'
END as 状态
from T_ClockIn

结果

第二步：利用窗口函数over根据用户UserID,打开时间的日期部分作为分组依据，区分有效打卡和重复打卡

select t.*,row_number() 
over (partition by t.userid,t.状态,to_char(checkin,'yyyy-mm-dd') order by t.checkin) as 状态2 
from (
SELECT USERID,checkin,
CASE 
WHEN to_char(checkin,'HH24:MI:SS') BETWEEN '08:00:00' AND '09:00:00' THEN '1' 
WHEN to_char(checkin,'HH24:MI:SS') BETWEEN '16:00:00' AND '18:00:00' THEN '2' 
ELSE '0'
END as 状态
from T_ClockIn) t

结果

难点：可以配合以下SQL结果理解：剔除状态为0非要求时间范围之外打卡记录，剩下的都是时间范围内打卡，

1：有效打卡 2 重复打卡

select t.*,row_number() 
over (partition by t.userid,t.状态,to_char(checkin,'yyyy-mm-dd') order by t.checkin) as 状态2 
from (
SELECT USERID,checkin,
CASE 
WHEN to_char(checkin,'HH24:MI:SS') BETWEEN '08:00:00' AND '09:00:00' THEN '1' 
WHEN to_char(checkin,'HH24:MI:SS') BETWEEN '16:00:00' AND '18:00:00' THEN '2' 
ELSE '0'
END as 状态
from T_ClockIn) t WHERE t.状态>0

结果

第三步

利用状态(区分时间范围内的打卡) 状态2（时间范围内的有效打卡和重复打卡）组合分析判断达到打卡结果统计

状态=0 then '无效' 状态0直接为无效打卡 ----->无效
状态>0 AND a.状态2=1 时间范围内的打卡并且有效打卡 ----->有效打卡
状态>0 AND a.状态2=2 时间范围内的打卡并且重复打卡 ----->重复打卡
最终SQL如下

SELECTa.userid,a.checkin,CASEWHEN a.状态 = 0 THEN'无效'WHEN a.状态 > 0AND a.状态2 = 1 THEN'有效'WHEN a.状态 > 0AND a.状态2 = 2 THEN'重复'END AS 状态
FROM(SELECTt.*,ROW_NUMBER() OVER(PARTITION BY t.userid, t.状态, to_char(checkin, 'yyyy-mm-dd')ORDER BYt.checkin) AS 状态2FROM(SELECTuserid,checkin,CASEWHEN to_char(checkin, 'HH24:MI:SS') BETWEEN '08:00:00' AND '09:00:00' THEN'1'WHEN to_char(checkin, 'HH24:MI:SS') BETWEEN '16:00:00' AND '18:00:00' THEN'2'ELSE'0'END AS 状态FROMt_clockin) t) a
ORDER BYa.userid,a.checkin

结果