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如下

SELECT
    a.userid,
    a.checkin,
    CASE
        WHEN a.状态 = 0 THEN
            '无效'
        WHEN a.状态 > 0
             AND a.状态2 = 1 THEN
            '有效'
        WHEN a.状态 > 0
             AND a.状态2 = 2 THEN
            '重复'
    END AS 状态
FROM
    (
        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
    ) a
ORDER BY
    a.userid,
    a.checkin

 结果

 知识点：窗口函数的使用     case when