如何计算员工每日考勤时长？使用LAG函数遇问题求助

我有一张存储员工每日考勤进出数据的表，某员工的考勤记录如下：

NO, EMP ID      date                IN-OUT
1   MS00093 12-Jun-24 10:43:27 AM   0
2   MS00093 12-Jun-24 10:50:18 AM   1
3   MS00093 12-Jun-24 1:20:08 PM    1
4   MS00093 12-Jun-24 1:20:27 PM    0
5   MS00093 12-Jun-24 1:21:08 PM    1
6   MS00093 12-Jun-24 1:21:16 PM    0
7   MS00093 12-Jun-24 1:30:13 PM    1
8   MS00093 12-Jun-24 1:56:05 PM    0
9   MS00093 12-Jun-24 2:38:34 PM    1
10  MS00093 12-Jun-24 2:38:34 PM    1
11  MS00093 12-Jun-24 2:40:21 PM    0
12  MS00093 12-Jun-24 2:40:21 PM    0
13  MS00093 12-Jun-24 2:42:14 PM    1
14  MS00093 12-Jun-24 2:42:14 PM    1
15  MS00093 12-Jun-24 2:42:40 PM    1
16  MS00093 12-Jun-24 3:08:05 PM    0
17  MS00093 12-Jun-24 3:08:05 PM    0
18  MS00093 12-Jun-24 3:10:22 PM    1
19  MS00093 12-Jun-24 3:12:40 PM    1
20  MS00093 12-Jun-24 3:15:27 PM    0
21  MS00093 12-Jun-24 3:36:38 PM    0
22  MS00093 12-Jun-24 3:38:36 PM    1
23  MS00093 12-Jun-24 3:38:55 PM    1
24  MS00093 12-Jun-24 4:29:57 PM    1
25  MS00093 12-Jun-24 5:12:17 PM    1

规则与预期输出 #

数据中0代表签到（IN），1代表签退（OUT）。计算有效考勤时长需遵循以下规则：

• 连续多条签到（0）记录，取最后一条作为有效签到
• 连续多条签退（1）记录，取第一条作为有效签退
• 有效配对为行1-2、4-5、8-9、12-13、17-18、21-22，需计算每次配对的时长并得到如下预期输出：

NO  EMP ID    DATE                IN-OUT sum of Hour
1   MS00093 12-Jun-24 10:43:27 AM   0   06 mi 51 sec
2   MS00093 12-Jun-24 10:50:18 AM   1   
3   MS00093 12-Jun-24 1:20:08 PM    1   
4   MS00093 12-Jun-24 1:20:27 PM    0   41 sec
5   MS00093 12-Jun-24 1:21:08 PM    1   
6   MS00093 12-Jun-24 1:21:16 PM    0   08 mi 57sec
7   MS00093 12-Jun-24 1:30:13 PM    1   
8   MS00093 12-Jun-24 1:56:05 PM    0   42 mi 29sec
9   MS00093 12-Jun-24 2:38:34 PM    1   
10  MS00093 12-Jun-24 2:38:34 PM    1   
11  MS00093 12-Jun-24 2:40:21 PM    0   
12  MS00093 12-Jun-24 2:40:21 PM    0   01mi 53 sec
13  MS00093 12-Jun-24 2:42:14 PM    1   
14  MS00093 12-Jun-24 2:42:14 PM    1   
15  MS00093 12-Jun-24 2:42:40 PM    1   
16  MS00093 12-Jun-24 3:08:05 PM    0   
17  MS00093 12-Jun-24 3:08:05 PM    0   2.17 sec
18  MS00093 12-Jun-24 3:10:22 PM    1   
19  MS00093 12-Jun-24 3:12:40 PM    1   
20  MS00093 12-Jun-24 3:15:27 PM    0   
21  MS00093 12-Jun-24 3:36:38 PM    0   2 sec
22  MS00093 12-Jun-24 3:38:36 PM    1   
23  MS00093 12-Jun-24 3:38:55 PM    1   
24  MS00093 12-Jun-24 4:29:57 PM    1   
25  MS00093 12-Jun-24 5:12:17 PM    1   

当前尝试的SQL #

我尝试用LAG函数编写脚本，但无法得到预期输出：

select SZEMPID,SZDT,SZINOUT,LAG_DAY from (
select a.*, lag(SZINOUT) over( order by SZDT) lag_day from temp_pmp a)
where SZINOUT <>LAG_DAY ;

解决方案 #

要实现需求，需要先对连续相同的IN-OUT状态进行分组，再筛选每组的有效记录，最后配对计算时长并关联回原始表。以下是适配大多数SQL数据库（如MySQL、PostgreSQL、SQL Server）的实现：

WITH grouped_attendance AS (
    SELECT 
        NO,
        EMPID AS "EMP ID",
        SZDT AS "DATE",
        SZINOUT AS "IN-OUT",
        -- 标记连续相同状态的组
        SUM(CASE WHEN prev_inout != SZINOUT THEN 1 ELSE 0 END) OVER (ORDER BY SZDT) AS group_id
    FROM (
        SELECT 
            NO,
            EMPID,
            SZDT,
            SZINOUT,
            LAG(SZINOUT) OVER (ORDER BY SZDT) AS prev_inout
        FROM temp_pmp
    ) t
),
valid_records AS (
    SELECT 
        group_id,
        "IN-OUT",
        CASE 
            WHEN "IN-OUT" = 0 THEN MAX(NO)  -- 多签到取最后一条的NO
            WHEN "IN-OUT" = 1 THEN MIN(NO)  -- 多签退取第一条的NO
        END AS valid_no,
        CASE 
            WHEN "IN-OUT" = 0 THEN MAX("DATE")  -- 有效签到时间
            WHEN "IN-OUT" = 1 THEN MIN("DATE")  -- 有效签退时间
        END AS valid_time
    FROM grouped_attendance
    GROUP BY group_id, "IN-OUT"
),
paired_records AS (
    SELECT 
        v1.valid_no AS in_no,
        v1.valid_time AS in_time,
        v2.valid_no AS out_no,
        v2.valid_time AS out_time,
        -- 计算时长，格式化为分钟/秒（根据数据库调整函数）
        CONCAT(
            FLOOR(TIMESTAMPDIFF(SECOND, v1.valid_time, v2.valid_time) / 60), ' mi ',
            MOD(TIMESTAMPDIFF(SECOND, v1.valid_time, v2.valid_time), 60), ' sec'
        ) AS "sum of Hour"
    FROM valid_records v1
    JOIN valid_records v2 
        ON v1.group_id + 1 = v2.group_id 
        AND v1."IN-OUT" = 0 
        AND v2."IN-OUT" = 1
)
SELECT 
    t.NO,
    t."EMP ID",
    t."DATE",
    t."IN-OUT",
    pr."sum of Hour"
FROM temp_pmp t
LEFT JOIN paired_records pr ON t.NO = pr.in_no
ORDER BY t.NO;

说明 #

• 不同数据库的时间差函数可能略有不同：
  • MySQL用TIMESTAMPDIFF
  • PostgreSQL用CONCAT(FLOOR(EXTRACT(EPOCH FROM (v2.valid_time - v1.valid_time))/60), ' mi ', MOD(EXTRACT(EPOCH FROM (v2.valid_time - v1.valid_time)),60), ' sec')
  • SQL Server用CONCAT(FLOOR(DATEDIFF(SECOND, v1.valid_time, v2.valid_time)/60), ' mi ', MOD(DATEDIFF(SECOND, v1.valid_time, v2.valid_time),60), ' sec')
• 若需处理跨天考勤，需在窗口函数中添加PARTITION BY DATE(SZDT), EMPID确保每日独立计算。

内容的提问来源于stack exchange，提问作者Kiran Patil