PostgreSQL中如何将连续重复的员工打卡记录分组并统计条目数量

这个需求完全可以在PostgreSQL中通过间隙岛屿（Gaps and Islands）算法结合窗口函数实现，具体实现如下：

实现逻辑 #

我们按entry_id的顺序识别连续的同员工打卡段：

• 第一步：判断当前记录是否是新分组的起点：对比当前记录的emp_name与上一条记录的emp_name，不一致则标记为新分组起点
• 第二步：累加起点标记得到每个连续段的唯一分组ID
• 第三步：按员工和分组ID聚合，统计每个组的最晚打卡时间和条目数

完整SQL代码 #

WITH mark_group_start AS (
    -- 标记新分组起点：上一条记录员工姓名和当前不同则记为1，否则记为0
    SELECT 
        entry_id,
        emp_name,
        entry_time,
        CASE WHEN LAG(emp_name) OVER (ORDER BY entry_id) = emp_name THEN 0 ELSE 1 END AS is_new_group
    FROM your_attendance_table -- 替换为实际业务表名
),
calc_group_id AS (
    -- 累加起点标记得到分组ID
    SELECT 
        entry_id,
        emp_name,
        entry_time,
        SUM(is_new_group) OVER (ORDER BY entry_id) AS group_id
    FROM mark_group_start
)
-- 聚合得到最终结果
SELECT 
    emp_name,
    MAX(entry_time) AS "last entry_time",
    COUNT(entry_id) AS "no of entries"
FROM calc_group_id
GROUP BY emp_name, group_id
ORDER BY group_id;

注意事项 #

如果你的entry_time字段存储为字符串格式（如示例中的DD/MM/YYYY），需要先转换为日期类型才能保证取到正确的最晚时间，将MAX(entry_time)替换为：

MAX(TO_DATE(entry_time, 'DD/MM/YYYY')) AS "last entry_time"

如果需要查看中间的分组标记结果，可以直接查询calc_group_id CTE的内容，和你预期的分组逻辑完全匹配。

内容的提问来源于stack exchange，提问作者Okechukwu Ossai