如何用SQL识别timesheet_data表中员工连续2个月以上考勤记录中断

原查询的核心问题是未正确捕捉考勤记录之间的连续中断，仅检查了单条记录后1-2个月的存在性，无法覆盖“中断后恢复填写”的场景。以下是修正后的思路和代码：

核心思路 #

1. 对每个员工（按id+countrynm分组）的考勤记录按日期排序，计算相邻记录的时间间隔。
2. 同时检查员工最后一条考勤记录到查询截止日期的间隔，避免遗漏“最后一次记录后长期中断”的情况。
3. 筛选出任意间隔超过2个月的员工，去重后得到结果。

修正后的SQL（以BigQuery为例） #

WITH ordered_timesheets AS (
  -- 为每个员工的考勤记录排序，标记上一条记录日期和最后一条记录日期
  SELECT
    id,
    countrynm,
    date_column,
    LAG(date_column) OVER (PARTITION BY id, countrynm ORDER BY date_column) AS prev_attendance_date,
    MAX(date_column) OVER (PARTITION BY id, countrynm) AS last_attendance_date
  FROM timesheet_data
  WHERE 
    countrynm = 'India'
    AND date_column BETWEEN '2023-03-01' AND '2023-07-01'
),
gap_calculations AS (
  -- 计算相邻考勤记录的间隔
  SELECT
    id,
    countrynm,
    DATE_DIFF(date_column, prev_attendance_date, MONTH) AS months_since_last_attendance
  FROM ordered_timesheets
  WHERE prev_attendance_date IS NOT NULL -- 排除第一条记录（无前驱）
  
  UNION ALL
  
  -- 计算最后一条记录到查询截止日的间隔
  SELECT
    id,
    countrynm,
    DATE_DIFF('2023-07-01', last_attendance_date, MONTH) AS months_since_last_attendance
  FROM ordered_timesheets
  WHERE date_column = last_attendance_date -- 仅取每个员工的最后一条记录
)
-- 筛选出存在超过2个月中断的员工
SELECT DISTINCT id, countrynm
FROM gap_calculations
WHERE months_since_last_attendance > 2
ORDER BY id;

针对双周考勤的精准调整 #

如果需要更精准匹配双周考勤规则（比如中断超过2个月即缺失≥4个双周记录），可将时间间隔改为按天数计算：

-- 修改gap_calculations中的条件为天数
WHERE DATE_DIFF(date_column, prev_attendance_date, DAY) > 60 -- 2个月按60天估算
-- 或针对最后一条记录：
WHERE DATE_DIFF('2023-07-01', last_attendance_date, DAY) > 60

原查询的问题总结 #

• 逻辑错误：通过LEFT JOIN查找单条记录后1-2个月的记录，无法识别“中间中断后恢复”的场景。
• 信息缺失：仅返回最后考勤日期，未捕捉中间的中断间隔。
• 冗余关联：关联subscriber_base表但未使用，可直接移除。

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