SQL怎样实现复杂的考勤工时计算_窗口函数处理时间重叠-SQL-PHP中文网

用lag()和lead()识别打卡重叠：先按employee_id、work_date、clock_time排序，再用lag()获取上一次打卡时间，通过间隔判断是否为无效插入，精准匹配有效进出对。

sql怎样实现复杂的考勤工时计算_窗口函数处理时间重叠

怎么用 `LAG()` 和 `LEAD()` 识别打卡时间重叠

考勤原始数据常是按人、按时间排序的打卡记录，但一人一天可能多次打卡（如上午迟到补卡、午休前/后打卡、加班再打卡），直接用 MIN()/MAX() 算上下班会把中间重叠段也包进去，导致工时虚高。关键不是“最早和最晚”，而是“有效进出对”。LAG() 和 LEAD() 能让你看到当前行和上下行的时间关系，从而标记出哪些打卡是“无效插入”。

实操建议：

先按 employee_id, work_date, clock_time 排序，确保时序正确
用 LAG(clock_time) OVER (PARTITION BY employee_id, work_date ORDER BY clock_time) 拿到上一次打卡时间
若当前打卡与上一次间隔 is_duplicate = 1
注意：不能只看绝对时间差，要结合打卡类型（IN/OUT）——比如连续两个 IN 才算可疑，IN→OUT 就是正常对

用 `ROW_NUMBER()` 配合状态机分组有效进出对

单纯去重还不够，得把剩下的打卡按“进→出→进→出…”逻辑配对。靠人工写 CASE 很难覆盖所有异常（如漏打、多打、倒置），更稳的方式是用 ROW_NUMBER() 构造伪状态序列，再通过奇偶性分组。

实操建议：

对清洗后的打卡记录（已剔除明显重复），按人+日期+时间排序，用 ROW_NUMBER() OVER (PARTITION BY employee_id, work_date ORDER BY clock_time) 编号
再用 ROW_NUMBER() OVER (PARTITION BY employee_id, work_date, clock_type ORDER BY clock_time) 单独给 IN 和 OUT 各自编号
两编号相减得到“组号”：grp = rn_total - rn_by_type —— 同一组内的 IN 和 OUT 才是逻辑配对的
最后 GROUP BY employee_id, work_date, grp，取每组 MIN(clock_time) 为进，MAX(clock_time) 为出

用 `OVERLAPS` 或自连接检测并合并时间区间（PostgreSQL / SQL Server）

即使配对成功，多个进出段之间仍可能重叠或紧邻（如 09:00–12:00，12:05–18:00，中间只隔5分钟，按规则应合并为 09:00–18:00）。标准 SQL 的 OVERLAPS 可直接判断两区间是否重叠，但不支持聚合合并；多数场景需手动处理。

实操建议：

PostgreSQL 可用 (start1, end1) OVERLAPS (start2, end2) 做条件过滤，但合并仍需递归 CTE 或窗口累计
更通用做法：对每个员工每日的进出段，用自连接找“能被前一段覆盖或紧邻的后一段”，然后用 MAX(end_time) OVER (ORDER BY start_time ROWS UNBOUNDED PRECEDING) 累计右边界
关键陷阱：必须先 ORDER BY start_time，且窗口定义里不能用 RANGE（因时间是连续值，RANGE 易误触发）
MySQL 8.0+ 不支持 OVERLAPS，得用 start2 = start1 模拟宽松重叠

为什么不能跳过排序和分区，直接套用窗口函数

窗口函数本身不改变行数，只基于当前窗口做计算。如果 PARTITION BY 漏了 work_date，跨天打卡（如夜班 22:00–06:00）会被错误地和第二天的早班混在一起；如果 ORDER BY 缺失或不准，LAG() 返回的就是随机上一行，重叠判断全失效。

实操建议：

PARTITION BY 至少包含 employee_id 和日期维度（CAST(clock_time AS DATE) 或业务 work_date 字段）
ORDER BY 必须是确定性排序：时间字段为主，必要时加 clock_type（IN 优先于 OUT）或自增 ID 防止并列
测试时故意构造一条跨天记录 + 一条同天重复打卡，看 LAG() 是否返回预期的上一条，这是最简单的验证手段